Calculate time in between records.

[DB_Noob]

Hello, trying to see if this is possible:

I've got a table that is basically an activity log (simplified)

TRACKING_ID (unique)

TASK_ID (the TASK being tracked, multiple)

ACT_DATE (when something happens to that TASK)

ACT_DESCRIPTION (what happened to that TASK)

DURATION (time in between)

So example records would look like this:

1, 50, May 1, Submitted, x

2, 33, May 3, Submitted, x

3, 50, May 5, Reviewed by John, 4 days

4, 50, May 7, Returned to John, 2 days

5, 33, May 10, Rejected, 7 days

The counted days are what I'm trying to calculate (2 days between the act of TASK_ID 50 being reviewed by John and then being return to John.)

Is it possible to create the DURATION field as a calculation so it auto-fills itself upon the creation of the record?

I can fill the DURATION field via a script when I create the new records, but for other reasons, it wouldn't be the best way.

Thanks...I hope I was clear.

Edited May 21, 2008 by Guest

[smdig]

Hi,

I am running filemaker pro 9 advanced on Mac osx, so do not know if this works in your environment. However, if you are trying to auto calculate the date between any given date, the easiest way in filemaker, is first to create the fields for comparison, so for example you could have a date submitted field and a date reviewed field, both set up as date fields. Then you create a calculation field for days out, using the following formula:

GetAsNumber ( date reviewed ) - GetAsNumber ( date submitted ). This will then work out the differnece between the 2 date fields and return a number as the answer.

Hope this helps.

regards,

Smdig.