How to construct a formula to calculate time from one record to the next

  • 1
  • 1
  • Question
  • Updated 11 months ago
  • In Progress
  • (Edited)
Let's say I have 2 records, both with fields that hold a time of day. They each represent the time at which a delivery van stopped at one location and then the next. Is there a way to have another field ([Time Since Last Stop] for example) calculate the passage of time from the preceding record?

If so, is there a way to delineate this function for when the vehicle ID changes? Let's say I have two vehicles making deliveries, "A" and "B"...

Vehicle A stops at 2:00 pm
Vehicle B stops at 2:01 pm
Vehicle A stops at 2:10 pm
Vehicle B stops at 2:15 pm

[Time Since Last Stop] for Vehicle A should not calculate from 2:00 to 2:01, but instead from 2:00 to 2:10, and for Vehicle B, it should calculate 2:01 to 2:15.

Thanks for any possible assistance
Photo of Tyler Parker

Tyler Parker

  • 860 Points 500 badge 2x thumb

Posted 1 year ago

  • 1
  • 1
Photo of Esther

Esther

  • 702 Points 500 badge 2x thumb
Are you willing to make another table with vehicles? It will make it simple.

If you are not, you will need to make calls to previous records in the same table and check the vehicles.

Think about a new "vehicles table". 
(Edited)
Photo of Tyler Parker

Tyler Parker

  • 860 Points 500 badge 2x thumb
I have a Vehicles table already set up and related to this table, Routes
Photo of Chuck Grigsby

Chuck Grigsby

  • 2,100 Points 2k badge 2x thumb
Make a summary field in Vehicles. For criteria summarize the Stop Time field and change from "Total" to "Maximum" Happy to demo if you're ok paying $1 for my contact info.
earn.com/chuck
Photo of Matthew Neil

Matthew Neil

  • 31,678 Points 20k badge 2x thumb
With your current set up, you will not be able to separate the difference between two records, especially if there are more than 2 'stops'. 

For example, if you have 5 stops.  Using the Min stop time, and Max stop time.  Because that will give you the first stop of the route, and last stop of the route, not the difference since the last.


However, you have 2 other advanced options.

1- If you are adding stops sequentially, you can modify the button to write the 'previous stop time' into a field on the new stop

or (2) you can create a circular (self) relationship, and the next stop will basically be a child to the previous stop.

These options are dependent on the workflow, how the stops are added and sequenced.
Photo of Matthew Neil

Matthew Neil

  • 31,678 Points 20k badge 2x thumb
We've recently developed a route optimizer that sorts stops based on a start/end destinations then sequences the stops for the route. 
Photo of Tyler Parker

Tyler Parker

  • 850 Points 500 badge 2x thumb
Our routing is generated by Amazon and pre-loaded onto each driver's Amazon-owned handheld GPS routing tool/package scanner, so fortunately we don't have to deal with that issue for the time being. I appreciate it, however.
Photo of Tyler Parker

Tyler Parker

  • 860 Points 500 badge 2x thumb
Matthew,

Our stops are automatically recorded by a device that is plugged into the engine, transmits that data to a server, and then imported into our QuickBase through an API call at the end of the day. So every record is going to be present at once. The stops are numbered, so perhaps option #1 could work if based off that order. I'm also interested in hearing more about option #2 if you have the time, because it sounds like a good way to keep routes separate. Thanks!
Photo of Matthew Neil

Matthew Neil

  • 31,678 Points 20k badge 2x thumb
What are the chances those 'logs' have any unique identifier AND they are somewhat sequential in their uniqueness?

If so that would be amazing, and somewhat simple to implement.

If not, you would need some type of script to that will sort and write the previous stop values in.

A skilled programmer could do it in about 2-3 hours
Photo of Matthew Neil

Matthew Neil

  • 31,678 Points 20k badge 2x thumb
Native option: If you want to make a mess of summary fields and lookup fields... You could make stacking summary/lookup fields (my worst nightmare).

Stop 1,  (minimum stop time for the Route)
>then send as a look up
Route - Stop 1,
>Then Stack
Stop 2 (minimum stop that is greater than "Route - Stop 1"
> Then send as a lookup
>Repeat for x number of possible stops.

Thats just the beginning:

Now you need to calculate the previous stop on the same record.
It would be a huge mess of conditional evaluations.... 
if stop 2, then use stop 1 to stop 2, 
if stop 3, then use stop 2 to stop 3,
x repeats.
Photo of Tyler Parker

Tyler Parker

  • 860 Points 500 badge 2x thumb
Unfortunately they don't have completely unique identifiers from one individual record to another. I think the script to sort stops by driver/vehicle and then write the values sequentially, low->high, based on "time of stop" is a great idea. You're right, the second option seems like a nightmare!
Photo of Matthew Neil

Matthew Neil

  • 31,678 Points 20k badge 2x thumb
Let me know if you want to get a more in depth look.  Or you can have one of your guys take a crack at it.

mneil@mcftech.com


Matthew Neil - Product Specialist
(Edited)