Using Field from NextRecord in CurrentRecord Calculation

[DeLuca]

 

Database has over 1000 records; one table and a dozen or so fields.

Each record is one of a continuous sequence of timed events. Each record has an event Start_time field but not an end time. The Start_time field is of type Time resulting in H:MM:SS format  Events are minutes long and always within one day.

I would like each record to have a Duration field of type Time that calculates event duration (MM:SS) by subtracting the event Start_time of the current record from the event Start_time of the next sequential record. 

Only need to do this once to populate all the Duration fields saving me the time, date entry effort and errors of a thousand manual calculations.  I think this has to be done with a script but I don't see the functions needed to do it. Need something like this:

Set Variable [$duration; Value: Get field (NextRecord::Start_time) - Get field (CurrentRecord::Start_time)]  #Can you reference a Record::Field?

Go to Field or Set Field?  [Duration; Value: $duration]  # syntax obviously wrong but I want to set Duration field equal to the value in the temporary variable $duration  

Then loop through all records except the last record which has no NextRecord  

Any way of accomplishing this?  Thanks. 

Edited April 24, 2020 by DeLuca

[Steve Martino]

I think you should also have an end time too, then the calculation field will be easy and automatic.  Then when you create a new record, you can have a script enter the end time of the previous record, and the duration field will simply calculate.  Even better would be that a user must enter the end time before they are allowed to create a new record, and that can be scripted also.

As I don't know what you are actually doing with the database, I can only guess.  For a one time cleanup, your duration field should be a calculation (initially unstored) of End Time-Start Time.

If you post a copy of the DB, I or someone could easily do the script.

For your script you should disable after you use it once to avoid a catastrophe. Pseudo script:

First you'd want to show all records & correctly sort the records by start time descending.

Go to first record.

Then Loop,

Grab the start time in a variable.

Omit record

Set End time field to the variable

Commit Records (if needed)

End If (Get(foundcount)=0

Of course, this is untested, and try it on a copy of the database first.

There's may be even an easier way if you are using 18.

 

[DeLuca]

Steve, Just implemented your flow.  Works perfectly!  Thank-you.