Forum Discussion

TylerParker's avatar
TylerParker
Qrew Assistant Captain
8 years ago

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

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
APIs and custom code
App builders
Ask about Quickbase functionality

11 Replies

Sort By
  • EstherGrandal_D's avatar
    EstherGrandal_D
    Qrew Cadet
    8 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's avatar
    TylerParker
    Qrew Assistant Captain
    8 years ago
    I have a Vehicles table already set up and related to this table, Routes
  • ChuckGrigsby's avatar
    ChuckGrigsby
    Qrew Cadet
    8 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's avatar
    MCFNeil
    Qrew Captain
    8 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's avatar
      MCFNeil
      Qrew Captain
      8 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's avatar
      TylerParker
      Qrew Assistant Captain
      8 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's avatar
    TylerParker
    Qrew Assistant Captain
    8 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's avatar
      MCFNeil
      Qrew Captain
      8 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's avatar
      MCFNeil
      Qrew Captain
      8 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's avatar
      TylerParker
      Qrew Assistant Captain
      8 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!