Skip to main content
Question

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

  • April 3, 2018
  • 0 replies
  • 89 views
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
    This topic has been closed for replies.

    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
    • Author
    • Registered
    • May 8, 2018
    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
    • MCFNeil
    • Registered
    • May 9, 2018
    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.
    • TylerParker
    • Author
    • Registered
    • May 9, 2018
    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!
    Terms & ConditionsAccessibility statement