• Registered: 2019-12-24
  • Posts: 169

Topic: Calculate between two rows (without ID order)

Hi MVD,
I cannot figure out a way to calculate the running miles between each stop during a trip.
Using the OD readings (16737 - 16641 = 96), etc.
The best example from the sample data provided, is trip ID 8 (screenshot) because the input for each stop is not in order (circled in red). Being able to allow out of order input is necessary for user corrections (or adjustments).
Note: The values in red are edits.

Post's attachments

Attachment icon T2.123-screenshot.png 29.31 kb, 162 downloads since 2020-02-12 

"Energy and persistence conquer all things."
  • Registered: 2019-12-24
  • Posts: 169

Re: Calculate between two rows (without ID order)

And here is the project

Post's attachments

Attachment icon T2.123_fleet.zip 13.65 kb, 312 downloads since 2020-02-12 

"Energy and persistence conquer all things."

3 Reply by derek (edited by derek 2020-02-12 11:33:40)

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,681

Re: Calculate between two rows (without ID order)

Hi Joshua,
I've put a formula into the cflegmileage calculated field - please have a look and see if this solves your issue.  As part of this, I've had to add atrip.id to frmtripdetail.tablegrid1 (but it's hidden with a couple of lines of script).  You also need to explicitly carry atrip.id into frmlegadd (but again, it's invisible).
I also noticed that frmtripdetail.tbtripmileage and frmtripdetail.tbodcf aren't updating when you close frmlegadd - I didn't change them but just thought I'd mention it.
I've not had time to test any of the above so it needs checking (caveat emptor and all of that - LOL!!).
Regards,
Derek.

Post's attachments

Attachment icon fleet.zip 346.69 kb, 364 downloads since 2020-02-12 

  • Registered: 2019-12-24
  • Posts: 169

Re: Calculate between two rows (without ID order)

Thanks a lot Derek!
You uncovered several techniques there that I have been trying to figure out. And I had no idea how to do that calculation with SQL either.  I really appreciate it!
I'm sure I'll have other questions along the way wink

"Energy and persistence conquer all things."