Jump to content

GetNthRecord calculation changes based on sort order


This topic is 2724 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

"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 by BruceR
Link to comment
Share on other sites

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 by Ziphius
Found error on my part, solution working great.
Link to comment
Share on other sites

This topic is 2724 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.