I know this should be easy but it is making my head hurt.

I have a start and end time with a date that returns number of hours worked on that day. What I also need to total up is how many hours have been worked in the previous 3,7,28 and 365 days.

Can anyone start me off, I've hit a block.

thanks

seaDog

Dealing with date ranges is not so easy, so don't feel like you missed something obvious.

The basic idea is to use an unstored calc as the parent key for each end of the date range, and use FM7's multi-criteria relationships with the new range operators to match on the Date in the child table.

For example, in our parent table, we can add an unstored calc to represent the date three days ago, and another to represent today:

cDateLessThree (calculation, date result) = get(currentdate)-3

cDateToday (calulation, date result) = get(currentdate)

Then the relationship for looking at the past three days would be:

Interface <=> Hours

Interface::cDateLessThree <= Hours::Date

AND Interface::cDateToday >= Hours::Date

You might also add a criteria in there to show just a particular employee (Interface::EmployeeID = Hours::EmployeeID.)

The other date ranges work the same way. Use a separate parent key and corresponding relationship for each.

With the relationships created, you can use a Sum(Hours::Number of Hours) function to total the hours for each range.

Thanks Ender, I get the gist of what you are saying but I'm not sure I'm going about it in the right way. I currently am going along the lines of each day has its own record. The data gets presented like this

Date Hours 3day 7day 28day 365day

12-8 1 1 1 1 1

13-8 1 2 2 2 2

14-8 1 3 3 3 3

15-8 1 3 4 4 4

Does that make sense, I really appreciate your help.

seaDog

Sorry seaDog, your data makes no sense to me.