GetNthRecord calculation changes based on sort order

[Ziphius]

I have a couple of issues with the attached "mock" database and hoping to get some resolution here. I have aerial survey data. Surveys are conducted each day and each day several legs are flown (one to many relationship). Legs can be "on effort" or "off effort" and the duration of the leg is the time between the next leg timestamp and the current leg timestamp unless its the last leg of the day. A timestamp is created whenever the leg effort status changes (each new record). My objective is to total the amount of time flown "on effort" and "off effort" separately for each survey day (sub-summary when sorted by Survey day). Sometimes survey data are imported from another iPad so the unique serial fields (record numbers) may not be in chronological order.  Since the GetNthRecord function is dependent on the sort order, the values aren't always the same. Other issues I am having are dealing with time and timestamp formats when using calculations, I can't seem to get them to look right.

Clear as mud? Any assistance would be appreciated.

GetNthRecordCalculationProblem.fmp12

[Wim Decorte]

This is a case where you probably want to script the creation of the legs and surveys so that you have full control and can can calculate and set the values properly on each record instead of relying on calculations.

[Ziphius]

Would you mind providing a suggestion on how the script would work, especially for the "value setting" portion?

Thanks!

[bruceR]

"Sometimes survey data are imported from another iPad so the unique serial fields (record numbers) may not be in chronological order."

However, all of them DO correctly show their parent SurveyID?

I see that your report is sorted STRICTLY by date and is NOT grouped by SurveyID. Is this correct?

Seems doubtful. You only care about the time sequence of the survey legs, independent of what actual survey they are associated with?

Doubtfulness amplified by the fact that you do show a portal based strictly on survey ID.

I don't see any need for GetNth at all.

SurveyFlights.fmp12

Edited November 5, 2016 by BruceR

[Ziphius]

Wow, I'm always amazed how you guys figure this stuff out with the simplest solution, this is great, and I truly appreciate you attaching an example file, which makes it easier for me to wrap my head around it.

 

 

Edited November 7, 2016 by Ziphius
Found error on my part, solution working great.