Discussions

View Only

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

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

Posted 04-03-2018 15:02
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

• 2.  RE: How to construct a formula to calculate time from one record to the next

Posted 04-04-2018 07:51
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".

• 3.  RE: How to construct a formula to calculate time from one record to the next

Posted 05-08-2018 19:52
I have a Vehicles table already set up and related to this table, Routes

• 4.  RE: How to construct a formula to calculate time from one record to the next

Posted 05-08-2018 21:20
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

• 5.  RE: How to construct a formula to calculate time from one record to the next

Posted 05-09-2018 15:57
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.

• 6.  RE: How to construct a formula to calculate time from one record to the next

Posted 05-09-2018 15:59
We've recently developed a route optimizer that sorts stops based on a start/end destinations then sequences the stops for the route.

• 7.  RE: How to construct a formula to calculate time from one record to the next

Posted 05-09-2018 18:56
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.

• 8.  RE: How to construct a formula to calculate time from one record to the next

Posted 05-09-2018 18:51
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!

• 9.  RE: How to construct a formula to calculate time from one record to the next

Posted 05-09-2018 19:18
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

• 10.  RE: How to construct a formula to calculate time from one record to the next

Posted 05-09-2018 19:20
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.

• 11.  RE: How to construct a formula to calculate time from one record to the next

Posted 05-09-2018 19:37
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!

• 12.  RE: How to construct a formula to calculate time from one record to the next

Posted 05-09-2018 19:46
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