Forum Discussion

WallaceYeboah's avatar
WallaceYeboah
Qrew Member
5 years ago

Duration for two separate records

Hi

So I am in the process of building a time tracking feature in Quick Base. Users clock in/clock out through their mobile device by selecting a two separate formula URL button to either clock in or clock out.

The issue I am having is currently each time a user clocks in or clocks out a new record is created (A clock in record and clock out record). As a result I am having trouble pulling the duration between the two records.

One thing to note is although they are two different records they are tied together by the related schedule that the user clocked into - I was wondering if I could use the "related schedule" field to tell the system that  specific clock in and clock out records are related and to take the duration from those two related records.

I wanted on any insight on whether this is possible, and if possible what would that formula look like.  Thanks in advance.

------------------------------
Wallace Yeboah
------------------------------
Formulas and functions
Relationships
  • WallaceYeboah's avatar
    WallaceYeboah
    Qrew Member
    5 years ago
    Forgot to post this earlier, but this is the current formula I am using 

    If([Related Schedule]=[Maximum Related Schedule], [Clock In Time]-[Clock Out Time])

    ------------------------------
    Wallace Yeboah
    ------------------------------
  • EvanMartinez's avatar
    EvanMartinez
    Quickbase Staff
    5 years ago
    Hi Wallace,

    Unfortunately to be able to use a formula the data needs to all be in one record, formulas as they exist are not able to draw in from records unless fields like summary fields and lookups are created to pull data in. One option is if they both have the same related parent you could have an automation take the time from their time out record and write it into a time out field on the time in record, since they share the same related parent you could use that as the filter for your copy value automation or pipeline. Then for each Time In record you would have both values in one place and you could then set up a formula field that would show the length of that shift. (Alternatively you could use an Automation to also write the Time In and Time Out records into one consolidated record but that seems a bit more intensive)

    Alternatively what others have done is have a single record hold both time in and time out, with the buttons set up to on Time In create a record with the time and then on time out edit the most recent related record, they do this by using a summary field to sum up the most recent Time In record that doesn't have a Time Out value captured yet. You can even do this to prevent a user from creating a Time out record without having a time in but I realize that does require changing up your existing structure a bit. I hope either of these suggestions is helpful Wallace and I hope you have a great day.

    ------------------------------
    Evan Martinez
    Community Marketing Manager
    Quick Base
    ------------------------------