Forum Discussion

JamesTrory's avatar
JamesTrory
Qrew Assistant Captain
7 years ago

Identify two records that match

In a record I have a field called [ShotID] (text) and a field [Shoot Date] (date). I want to compare records that have identical text in [ShotID] and then calculate in a third field the duration between these identical records using [Shoot Date].

I can use Contains to match [ShotID] records but how do I then calculate the duration between the two matching records? I'm really struggling with that part.
App builders
Ask about Quickbase functionality
Formulas and functions
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    What you will need is a table of uniquer Shot ID's where the Key field is set to Shot ID.

    The initial load of this table can be done by making a summary report off your shots table summarized by Shot ID and then using the More ... button to copy those records to this new table.

    Then make a relationship to the shots table (and this is a bit tricky) via a new formula field called [Shot ID mirror] which will be a formula field with a formula equal to the [Shot ID].

    Great, so now on that relationship you can summarize the Maximum date and then another summary field for the Minimum date and you will now be able to calculate the # of days apart they were.  If you like, look those up down to the detail shots record.

    So that will solve your problem ... except! yikes how to maintain that Unique shots table without you needing to reload that manually every day.

    What I don't know here ( and info I need to suggest how to best update that Unique shots table automatically is how that Shots table is loaded with shots.  Are these imported via excel? How often are they uploaded?  Are they ever loaded up manually one by one?