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

• 1
• Question
• Updated 1 year 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
• 860 Points

Posted 1 year ago

• 1
• 702 Points
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)
• 860 Points
I have a Vehicles table already set up and related to this table, Routes
• 2,100 Points
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
• 31,698 Points
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.
• 31,698 Points
We've recently developed a route optimizer that sorts stops based on a start/end destinations then sequences the stops for the route.
• 860 Points
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.
• 860 Points
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!
• 31,698 Points
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
• 31,698 Points
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.
• 860 Points
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!
• 31,698 Points
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

(Edited)