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
adjustment.
> 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
> adjustment.
> > 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
> > adjustment.
> > > 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
> > > adjustment.
> > > > 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
 
 [ 5 post ] 

 Relevant Pages 

1. need help with report calc and query calc

2. Calc Month End Date

3. How to calculate year to date and other calc members

4. time calc from date, sysdate & number fields

5. Need Script That Calc Hours Between 2 Dates

6. Need to calc time between to dates in hours and minutes

7. Need Script For Calc Hours between 2 dates

8. Help Pls: Calc Future Dates

9. Date Calc to subtract a month

10. date calc

11. Date Calc

12. Calc Date Question


 
Powered by phpBB® Forum Software