Forum Discussion

AmyChristensen1's avatar
AmyChristensen1
Qrew Trainee
6 months ago

Create retrospective summary report of record counts over time

Hello all,

I'm having some trouble conceptualizing the best way to achieve a report that would provide a retrospective look over time at the number of active assignments on any given date, to allow us to track changes in various related metrics over time.

Basically I have a table of technician assignments where each assignment has a start date and an end date, and the same for vehicle assignments (and there are probably other things we may want to apply this concept to in the future).

What I would like to end up with is a report that can query and display the number of active assignments as of, say, each Friday this year. To do this, I need some way to test whether each Friday date falls after the start date and before the end date of each assignment, then count the assignments where this is true.

I've tried coming at this via a summary report, considered a pipeline, and attempted a record query formula field, but these each seem to fit well for one piece of the equation then get stuck on another.

I know I could just set up a pipeline to record the counts on a daily basis, but it's not unusual that adjustments are made after the fact so I'd prefer something more dynamic. The idea I hit on was to create a "Daily Records" table with a record for each day of the year and a formula field to count the assignment records that encompass the date assigned to each record. 

The issue that I'm hitting on is that I can't find a way to do a summary field that summarizes records that fall between the values in two fields (and there may not even be an end date input on many records, just to add a little more complexity). I thought a record query formula field would do the trick, but I didn't have any luck with the size or count functions and I'm not sure how else I could go at it.

Any ideas are welcome! I'm sure I'm not the first to want to do this, but I haven't found the magic search terms if the solution is already out there.



------------------------------
Amy Christensen
------------------------------

4 Replies

  • A formula a query run off your days table will work. Use the Size function. Can you post you last attempt using a formula Query and we can help get it working.



    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------
    • AmyChristensen1's avatar
      AmyChristensen1
      Qrew Trainee

      I had to go back a few iterations to reconstruct that attempt, but this is what I have:

      Size(
        GetRecords("{99.OAF.'"&[Date]&"'}AND{77.OBF.'"&[Date]&"'}", "[_DBID_TASKS]")
      )

      I confirmed that all three fields (Date in this table, and fields 77 and 99 in the table being queried) are the same type. 77 is the assignment start date and 99 is the end date (which remains blank until the assignment is completed).

      The formula is accepted, but returns zero records.



      ------------------------------
      Amy Christensen
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend

        I see two issues.

        The first is that the table name should not be in quotes [_DBID_TASKS].

        Size(
          GetRecords("{99.OAF.'"&[Date]&"'}AND{77.OBF.'"&[Date]&"'}", [_DBID_TASKS])
        )

        The second is that the end date fid 99 might be blank.  I suggest that you make a new field in the Assignments table called [End Date or Today] with a formula of 

        IF(IsNull([End Date]), Today(), [End Date]) so that it will always have a date.   The use that field's Field ID instead of 99 in your Formula Query.



        ------------------------------
        Mark Shnier (Your Quickbase Coach)
        mark.shnier@gmail.com
        ------------------------------