Date Calc problem
Author Message
Date Calc problem

FM Pro5.0v3  win 95/98/NT/2000  - small network

Have an attendance file and an employee file

All attendance tracking takes place in the attendance file with a portal in
the employee record file.

Two fields: (Attendance File)
Absence number  = number
Total Absences = Sum(Absence number)

The Total Absences is important because if it gets greater than 9, an
employee is placed on probation, etc.

Some of the simple rules:

Every day a person is absent - Absence number =  1;
if the absence is no call or no show -  Absence number = 2

that part is easy,  however there are a couple of rules which are dependent
upon date periods

1. If he has 3 tardys in any 30 day period; the Total Absences  increases by
1

2. If he works 1 calendar month with no absences; the Total Absences
decreases by 1.

I figure the only way to trigger the change in the Total absences from the
date period rules would be to invoke a script that involves a calculation.
(how do this efficiently eludes me)

Ideally I would like to use some type of automatc trigger that monitors for
the two date period rules and automatically makes the necessary adjustment.
Again I haven't yet figured that out.

I could really use some expert advice.

Thanks,

Pat Venditti

Thu, 31 Jul 2003 13:14:54 GMT
Date Calc problem

Patrick,

For the tardies in any given 30 day period, is the period a calendar month,
or any 30 days?

And...

How are you marking a Tardy? Did you mean Absence?

--
John Weinshel
Datagrace
Associate Member, Filemaker Solutions Alliance
Vashon Island, WA
(206) 463-1634

Quote:
> FM Pro5.0v3  win 95/98/NT/2000  - small network

> Have an attendance file and an employee file

> All attendance tracking takes place in the attendance file with a portal
in
> the employee record file.

> Two fields: (Attendance File)
> Absence number  = number
> Total Absences = Sum(Absence number)

> The Total Absences is important because if it gets greater than 9, an
> employee is placed on probation, etc.

> Some of the simple rules:

> Every day a person is absent - Absence number =  1;
> if the absence is no call or no show -  Absence number = 2

> that part is easy,  however there are a couple of rules which are
dependent
> upon date periods

> 1. If he has 3 tardys in any 30 day period; the Total Absences  increases
by
> 1

> 2. If he works 1 calendar month with no absences; the Total Absences
> decreases by 1.

> I figure the only way to trigger the change in the Total absences from the
> date period rules would be to invoke a script that involves a calculation.
> (how do this efficiently eludes me)

> Ideally I would like to use some type of automatc trigger that monitors
for
> the two date period rules and automatically makes the necessary
> Again I haven't yet figured that out.

> I could really use some expert advice.

> Thanks,

> Pat Venditti

Thu, 31 Jul 2003 14:13:20 GMT
Date Calc problem
tardy in any given 30 day period is any 30 days  not a calendar month.

I forgot about the tardys fields
Tardy number  = number
Total Tardys = Sum(Tardy number)

so, for the first date period rule -
1. If he has 3 tardys in any 30 day period; the Total Absences  increases

Quote:
> by 1

The logic reasoning I tried in a script was:

If(Sum(Tardiness number)= "3" and Tardiness date = Status( CurrentDate) - 30
SetField(Absence number  + 1)
End If

alas this doesn't work!  I hate working with date calcs - but I have to
learn, right!
Thanks for any assistance.

Quote:
> Patrick,

> For the tardies in any given 30 day period, is the period a calendar
month,
> or any 30 days?

> And...

> How are you marking a Tardy? Did you mean Absence?

> --
> John Weinshel
> Datagrace
> Associate Member, Filemaker Solutions Alliance
> Vashon Island, WA
> (206) 463-1634

> > FM Pro5.0v3  win 95/98/NT/2000  - small network

> > Have an attendance file and an employee file

> > All attendance tracking takes place in the attendance file with a portal
> in
> > the employee record file.

> > Two fields: (Attendance File)
> > Absence number  = number
> > Total Absences = Sum(Absence number)

> > The Total Absences is important because if it gets greater than 9, an
> > employee is placed on probation, etc.

> > Some of the simple rules:

> > Every day a person is absent - Absence number =  1;
> > if the absence is no call or no show -  Absence number = 2

> > that part is easy,  however there are a couple of rules which are
> dependent
> > upon date periods

> > 1. If he has 3 tardys in any 30 day period; the Total Absences
increases
> by
> > 1

> > 2. If he works 1 calendar month with no absences; the Total Absences
> > decreases by 1.

> > I figure the only way to trigger the change in the Total absences from
the
> > date period rules would be to invoke a script that involves a
calculation.
> > (how do this efficiently eludes me)

> > Ideally I would like to use some type of automatc trigger that monitors
> for
> > the two date period rules and automatically makes the necessary
> > Again I haven't yet figured that out.

> > I could really use some expert advice.

> > Thanks,

> > Pat Venditti

Thu, 31 Jul 2003 23:28:38 GMT
Date Calc problem
Create some fields in Attendance:

Date (date of occurrence)
Record Number (unstored= Status(CurrentRecordNumber)
RN_Plus (number)
Employee ID (if you don't already have it)
Constant (calc, returning number, set to 1)
ID_Constant(Id & 1)
ID_Tardy (ID & Tardy)
ID_Absent (ID & Absent)
ID_RN (ID & Record Number)
ID_RN Plus (ID & RN_Plus )

The Tardy be a number field, set to Boolean (1 or 0). In other words, a
record only exists if an employee is either tardy or absent. If they are
absent, the Absent field gets a 1 or 2 (for not calling); if tardy, the
tardy field gets a 1. Best format them both with validation, so that the
other cannot also be not empty, or create a warning calc field (Case(Tardy=1
and (Absent=1 or Absent=2), "Warning!").

Create 3 (self join) relationships in Attendance:

Absent: ID_Constant to ID_Absent
Tardy: ID_Constant to ID_Tardy
Too Tardy: ID_RN to ID_RN Plus

You should already have a relationship ("Employee"), in Employee, to
Attendance, using the Employee ID as the match field.

In Employee, the Script is:

Go to related record [Show, "Employee"]
Perform Script[Sub-script, External: Attendance.fp5] (the script that
follows)

In Attendance, the script is:

Sort (by Date, ascending)
Go to Record/Request/Page[First]
Set Field["RN_Plus", "Record Number+2"]
Loop
Go to Record/Request/Page[Exit after last, Next]
Set Field["RN_Plus", "Record Number+2"]
End Loop

To calculate when 2 or more tardies have occurred within 30 days, create a
calculation field ("So_Tardy"), returning a number:

Case(IsValid(Too Tardy::Date) and Date-Too Tardy::Date <=30, 1)

To count all absences, in Attendance create a field ("Sum_Absent):

Sum(Absent::Absent) + Sum(Tardy::So_Tardy),

and in Employee:

Sum(Employee::Sum_Absent).

It's a bit rough, and you could probably loose the Tardy relationship, but
it should work. The main problem is creating a dependable way to identify
records within a 30 day period, without polling the whole 30 days, although
that would work as well.

I just noticed you also want to reduce the absences by 1 for a good month.
You should be able to work that out using the same reasoning.

(There should also be a way to achieve this result without using a script,
similar to a golf problem we had last August).
--
John Weinshel
Datagrace
Associate Member, Filemaker Solutions Alliance
Vashon Island, WA
(206) 463-1634

"Tardy"= Constant to

Quote:
> tardy in any given 30 day period is any 30 days  not a calendar month.

> I forgot about the tardys fields
> Tardy number  = number
> Total Tardys = Sum(Tardy number)

> so, for the first date period rule -
> 1. If he has 3 tardys in any 30 day period; the Total Absences  increases
> > by 1

> The logic reasoning I tried in a script was:

> If(Sum(Tardiness number)= "3" and Tardiness date = Status( CurrentDate) -
30
> SetField(Absence number  + 1)
> End If

> alas this doesn't work!  I hate working with date calcs - but I have to
> learn, right!
> Thanks for any assistance.

> > Patrick,

> > For the tardies in any given 30 day period, is the period a calendar
> month,
> > or any 30 days?

> > And...

> > How are you marking a Tardy? Did you mean Absence?

> > --
> > John Weinshel
> > Datagrace
> > Associate Member, Filemaker Solutions Alliance
> > Vashon Island, WA
> > (206) 463-1634

> > > FM Pro5.0v3  win 95/98/NT/2000  - small network

> > > Have an attendance file and an employee file

> > > All attendance tracking takes place in the attendance file with a
portal
> > in
> > > the employee record file.

> > > Two fields: (Attendance File)
> > > Absence number  = number
> > > Total Absences = Sum(Absence number)

> > > The Total Absences is important because if it gets greater than 9, an
> > > employee is placed on probation, etc.

> > > Some of the simple rules:

> > > Every day a person is absent - Absence number =  1;
> > > if the absence is no call or no show -  Absence number = 2

> > > that part is easy,  however there are a couple of rules which are
> > dependent
> > > upon date periods

> > > 1. If he has 3 tardys in any 30 day period; the Total Absences
> increases
> > by
> > > 1

> > > 2. If he works 1 calendar month with no absences; the Total Absences
> > > decreases by 1.

> > > I figure the only way to trigger the change in the Total absences from
> the
> > > date period rules would be to invoke a script that involves a
> calculation.
> > > (how do this efficiently eludes me)

> > > Ideally I would like to use some type of automatc trigger that
monitors
> > for
> > > the two date period rules and automatically makes the necessary
> > > Again I haven't yet figured that out.

> > > I could really use some expert advice.

> > > Thanks,

> > > Pat Venditti

Fri, 01 Aug 2003 10:48:01 GMT
Date Calc problem

"...and in Employee:

Sum(Employee::Sum_Absent)..."

The correct calc, in Employee, is simply the related field:

Employee::Sum_Absent.

Sorry for the error.

John

Quote:
> Create some fields in Attendance:

> Date (date of occurrence)
> Record Number (unstored= Status(CurrentRecordNumber)
> RN_Plus (number)
> Employee ID (if you don't already have it)
> Constant (calc, returning number, set to 1)
> ID_Constant(Id & 1)
> ID_Tardy (ID & Tardy)
> ID_Absent (ID & Absent)
> ID_RN (ID & Record Number)
> ID_RN Plus (ID & RN_Plus )

> The Tardy be a number field, set to Boolean (1 or 0). In other words, a
> record only exists if an employee is either tardy or absent. If they are
> absent, the Absent field gets a 1 or 2 (for not calling); if tardy, the
> tardy field gets a 1. Best format them both with validation, so that the
> other cannot also be not empty, or create a warning calc field
(Case(Tardy=1
> and (Absent=1 or Absent=2), "Warning!").

> Create 3 (self join) relationships in Attendance:

> Absent: ID_Constant to ID_Absent
> Tardy: ID_Constant to ID_Tardy
> Too Tardy: ID_RN to ID_RN Plus

> You should already have a relationship ("Employee"), in Employee, to
> Attendance, using the Employee ID as the match field.

> In Employee, the Script is:

> Go to related record [Show, "Employee"]
> Perform Script[Sub-script, External: Attendance.fp5] (the script that
> follows)

> In Attendance, the script is:

> Sort (by Date, ascending)
> Go to Record/Request/Page[First]
> Set Field["RN_Plus", "Record Number+2"]
> Loop
>    Go to Record/Request/Page[Exit after last, Next]
>    Set Field["RN_Plus", "Record Number+2"]
> End Loop

> To calculate when 2 or more tardies have occurred within 30 days, create a
> calculation field ("So_Tardy"), returning a number:

> Case(IsValid(Too Tardy::Date) and Date-Too Tardy::Date <=30, 1)

> To count all absences, in Attendance create a field ("Sum_Absent):

> Sum(Absent::Absent) + Sum(Tardy::So_Tardy),

> and in Employee:

> Sum(Employee::Sum_Absent).

> It's a bit rough, and you could probably loose the Tardy relationship, but
> it should work. The main problem is creating a dependable way to identify
> records within a 30 day period, without polling the whole 30 days,
although
> that would work as well.

> I just noticed you also want to reduce the absences by 1 for a good month.
> You should be able to work that out using the same reasoning.

> (There should also be a way to achieve this result without using a script,
> similar to a golf problem we had last August).
> --
> John Weinshel
> Datagrace
> Associate Member, Filemaker Solutions Alliance
> Vashon Island, WA
> (206) 463-1634

> "Tardy"= Constant to

> > tardy in any given 30 day period is any 30 days  not a calendar month.

> > I forgot about the tardys fields
> > Tardy number  = number
> > Total Tardys = Sum(Tardy number)

> > so, for the first date period rule -
> > 1. If he has 3 tardys in any 30 day period; the Total Absences
increases
> > > by 1

> > The logic reasoning I tried in a script was:

> > If(Sum(Tardiness number)= "3" and Tardiness date = Status(
CurrentDate) -
> 30
> > SetField(Absence number  + 1)
> > End If

> > alas this doesn't work!  I hate working with date calcs - but I have to
> > learn, right!
> > Thanks for any assistance.

> > > Patrick,

> > > For the tardies in any given 30 day period, is the period a calendar
> > month,
> > > or any 30 days?

> > > And...

> > > How are you marking a Tardy? Did you mean Absence?

> > > --
> > > John Weinshel
> > > Datagrace
> > > Associate Member, Filemaker Solutions Alliance
> > > Vashon Island, WA
> > > (206) 463-1634

> > > > FM Pro5.0v3  win 95/98/NT/2000  - small network

> > > > Have an attendance file and an employee file

> > > > All attendance tracking takes place in the attendance file with a
> portal
> > > in
> > > > the employee record file.

> > > > Two fields: (Attendance File)
> > > > Absence number  = number
> > > > Total Absences = Sum(Absence number)

> > > > The Total Absences is important because if it gets greater than 9,
an
> > > > employee is placed on probation, etc.

> > > > Some of the simple rules:

> > > > Every day a person is absent - Absence number =  1;
> > > > if the absence is no call or no show -  Absence number = 2

> > > > that part is easy,  however there are a couple of rules which are
> > > dependent
> > > > upon date periods

> > > > 1. If he has 3 tardys in any 30 day period; the Total Absences
> > increases
> > > by
> > > > 1

> > > > 2. If he works 1 calendar month with no absences; the Total Absences
> > > > decreases by 1.

> > > > I figure the only way to trigger the change in the Total absences
from
> > the
> > > > date period rules would be to invoke a script that involves a
> > calculation.
> > > > (how do this efficiently eludes me)

> > > > Ideally I would like to use some type of automatc trigger that
> monitors
> > > for
> > > > the two date period rules and automatically makes the necessary
> > > > Again I haven't yet figured that out.

> > > > I could really use some expert advice.

> > > > Thanks,

> > > > Pat Venditti

Sat, 02 Aug 2003 03:38:01 GMT

 Page 1 of 1 [ 5 post ]

Relevant Pages