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 pmVehicle B stops at 2:01 pmVehicle A stops at 2:10 pmVehicle 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

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".

I have a Vehicles table already set up and related to this table, Routes

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

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 stopor (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.

We've recently developed a route optimizer that sorts stops based on a start/end destinations then sequences the stops for the route.

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

11 Replies

EstherGrandal_D
Qrew Cadet
7 years ago
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".
TylerParker
Qrew Assistant Captain
7 years ago
I have a Vehicles table already set up and related to this table, Routes
ChuckGrigsby
Qrew Cadet
7 years ago
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
MCFNeil
Qrew Captain
7 years ago
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.
- MCFNeil
  Qrew Captain
  7 years ago
  We've recently developed a route optimizer that sorts stops based on a start/end destinations then sequences the stops for the route.
- TylerParker
  Qrew Assistant Captain
  7 years ago
  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.
TylerParker
Qrew Assistant Captain
7 years ago
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!
- MCFNeil
  Qrew Captain
  7 years ago
  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
- MCFNeil
  Qrew Captain
  7 years ago
  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.
- TylerParker
  Qrew Assistant Captain
  7 years ago
  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!