Help with getting data from previous record.

[Dan-A]

Hi all,

I am using Fm 5.5v2

I have a file that contains all workdays for all employees.

in an earlier post i asked for help on getting unique employeeID for

a date range. This would then give me a list of what staff worked on

a given week. With that i can now "build" a time card with 7 days. OK so far!!

Problem i need to solve is: the timecard (the company standard) requires that

the Finish time of the last day of the previous week be written on the current

time card. But i only want the previous end time if it is 56 hours or less from the 1st workday of the current week.

Now that i've gone thru all kinds of looping and searching

to eliminate duplicate data from a found set, i find myself needing to go

"outside the found set" to look for the last workday get the end time for

each employee that has worked in the current found set.

Since the users will have a choice of printing ALL timecard for a given

week or just the ones marked with an X should i consider looping thru all

employees (approx. 200) to get end times even if not selected??

I was wondering if i should create a calc field that "looks" at the previous

day when a new day is created and then just use the "PreviousEndTime" if needed?

Not sure how to do that or if it's possible with a calc field.

Boy I hope this is understandable!!!

A push in the right direction would be really really appreciated!!

Thanks

--Dan

[Ugo DI LUCA]

Hi,

"But i only want the previous end time if it is 56 hours or less from the 1st workday of the current week."...

Not very clear.

Now, you could use in your file :

- a calculation = c_Week = WeekofYear(your date) - indexed

- a concanation 1 = c_Week&"-"& Employee_ID

- a calculation = c_lastweek = WeekofYear(Status(CurrentDate)-1)

- a concanation 2 = c_lastweek&"-"&Emplyee_ID

Then use a relationship "RelationWeek" --> concanation 2::concanation 1

and

a Calculation = Max("RelationWeek"::Total hours worked).

[Dan-A]

Hey Ugo,

Thanks for the help

I like it, this may be the push in the right

direction was looking for.

If i understand what you suggest, i would create an index

for the week numbers then use a self-join relationship to

look for the previous week for a particular employee,

once found i could extract the end time of the last

workday for that week for that employee and use it or not

based on the elapsed time between the previous week and the

start time for that employee in the current week.

thanks

i will work on that.

--Dan

[Ugo DI LUCA]

OK,

I think I understand what you're looking for.

If the last week number of hours was <=56 hour worked, you want this number of hours to be reported.

You didn't offer much about your file structure and calc though...

The solution above would be easier if you have a running count per week.

You would therefore use this calculation

c_reportLastWeeksHours = Case(TotalHoursWorked<=56, TotalHoursWorked, " ")

Then, use this value as a related field for your new week report.

[Dan-A]

I am re-reading my post....not very clear!!

The workdays file holds info on the start & end times for each day along with workDate, employeeID.

For the calculation of certain overtime & penalties the workers must have 56 hours of rest during the weekend.

To report that on the "active" time card i need to "look"

at the last workday of the previous week and verify if less

then 56 hours have passed before the worker strated to work on his 1st day of the current week. I have to keep in mind that workers may have different work shifts (Mon-Fri, Wed-Sun) but time cards are always written with a week starting on Sunday thru Sat.

Thanks for the ideas.

--Dan

[Ugo DI LUCA]

OK,

First categorize your employees according to their respective workship.

Say

c_category = Case(workship = "Mon-Fri", 0, workship = "Wed-Sun",1)

Then use these calcs

LastFriday = YourDate - MOD(DaysofWeek(YourDate) +6, 7)-2

LastSunday = YourDate - MOD(DaysofWeek(YourDate) +6, 7)

ThisMonday = YourDate - MOD(DaysofWeek(YourDate) +5, 7)

ThisWednesday = YourDate - MOD(DaysofWeek(YourDate) +3, 7)

c_LastDayofWork = Choose(category, Last Friday, Last Sunday)

c_FirstDayofWork = Choose(category, ThisMonday, ThisWednesday)

c_ConcanateLastDayofWork = c_LastDayofWork &"-"& Employee_Id

c_ConcanateFirstDayofWork = c_FirstDayofWork &"-"& Employee_Id

Modify these 2 last calcs for concanate keys for 2 relationships :

RelLastDate = c_ConcanateLastDayofWork ::YourDate&"-"&Employee_Id

RelStartDate = c_ConcanateFirstDayofWork ::YourDate&"-"&Employee_Id

Then, use the calculation :

c_HoursFromLastWork = ((c_LastDayofWork-c_FirstDayofWork-1)*86400

+ (Hour(24;00;00)-RelLastDate::ending hour)+ (RelStartDate::starting hour))/3600

Finally,

c_checkHours = Case(c_HoursFromLastWork< = 56, c_HoursFromLastWork, " ") should be your last calc.

I just hope the following works correctly, as I did a very little test.

[Dan-A]

Hi Ugo,

I will look at your last post.

So far the first reply is the one i have been working on.

I modified: a Calculation = Max("RelationWeek"::Total hours worked)

for c_LastWeekDay = Max("RelationWeek"::Date).

That gets me the date of the last workday of the previous

week for each employee. What i think i will work on next

(unless there is a better way) is to write a FIND script

with c_LastWeekday & EmployeeID to "point" to the proper record and extract the finish time.

I forgot to mention that the work shifts are totaly flexible. An employee may have 3 days off,

may start work at 8pm on th 12th and finish 5am on the 13th.

i will have to think about that when i do the elapsed hours math.

thanks

--Dan

[Ugo DI LUCA]

Max("RelationWeek"::Total hours worked)

for c_LastWeekDay = Max("RelationWeek"::Date).

gets me the date of the last workday of the previous

week for each employee.

...But your employee may finish work on Monday as you said, so the last week isn't a good option IMO. That's why I changed to the solution above.

write a FIND script

with c_LastWeekday & EmployeeID to "point" to the proper record and extract the finish time.

This is done by relationship and quicker by the above solution.

I forgot to mention that the work shifts are totaly flexible. An employee may have 3 days off,

may start work at 8pm on th 12th and finish 5am on the 13th.

No problem with this, just extend the calculation about hours shift above implementing other Case statement.

Really, when I tested it, it worked fine for me.

[Dan-A]

Ugo,

Thanks for the help, i don't have specific work schedules

for the employees so i can't use the categories.

What i have done so far is use weekID (like your 1st post)

along with the Max & Min functions and 2 relationships to

get the date for last workday 7 date for 1st workday.

Then 2 more relationships (LastDayDate & "-" &EmpID) to

extract the Finish time of the last week and

(FirstDayDate& "-" & EmpID) to extract the Start time of the current week.

The next step should be your c_checkHours = Case...

to decide on "to display or not to display" that is the question!!

Thanks again for all the help.

Let me know if you think of a better way

--Dan

[Vaughan]

FMP's Last() function might help if the data is related (or in a self-join).

[Dan-A]

Hi Vaughan,

i thought of using the Last function but it returns last

valid NON-BLANK record.

I was affraid that if the user forgot to enter an end time for the last day (Thursday) it would return the last valid time which could be the Wednesday end time even if Thursday IS the last worday for that employee. The Max function returns that blank time and then i can deal with it thru script.

--Dan

[cjaeger]

do a self-join on record ID and recIDpreviousrec with

recIDpreviousrec= auto-enter value from previous record.

now get the time value from that relation ....

This should get you started in single-user mode.

[Dan-A]

One thought i had last night (in case someone finds this usefull)

Getting the previous week would not work when the current week is the 1st week in January.

So i addes an if statement to my previousWeek calc...

Something like

If ((WeekofYear(date)-1) = 0, WeekofYear(Date(12,31,(Year(date)-1))), WeekofYear(date)-1)

That way i get the last week of the previous year instead of week number 0 which i could never match too.