Save daily totals from a summary report into another table to track totals over time

  • 1
  • 2
  • Question
  • Updated 4 months ago
  • Answered
Let's say I have a Summary table that totals all the records in a table where [Status]="Open". These are then grouped in various ways so I have subtotals for the various kinds of records that are open. So far, so good.

However, let's say my boss asks me: what were the open totals yesterday? Or the day before? What have the totals been like over time? The Summary table only tells me what they are right now but it doesn't track history, so far as I know. And with the way my data is structured, I don't have a good way of calculating what the open totals would have been because I don't have "opened on"/"closed on" dates to rely on...the only thing that's tracked is the current Status, which is either open or closed. 

So far I've just been manually keeping track of these totals in another table, where each row/record is a day and each column/field is some specific subtotal. But it seems like Quick Base should have a way of automatically logging this somehow. How can I do this in an automated way?


Here's the summary report. This is what I'd want to automatically log each day.



This is how I've been manually tracking the daily totals.
Photo of Jake Rasmussen

Jake Rasmussen

  • 186 Points 100 badge 2x thumb

Posted 5 months ago

  • 1
  • 2
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
you can do this to fully automate this.

Make a table with just 1 record in it.  Call it Todays Open Totals.  It will be record ID# of 1.

Make a relationship to all your details records with a formula field called [Link to Totals (=1)] with a formula of 1.

Make 6 Summary total fields on that relationship to get at those same totals you see in the summary report.

Great, now you have the totals in a place that is real (not just on a report).  How can you Automatically on a daily scheduled basis create 6 records in a new table you will create called Open Totals History.  Of course to Automate we will use an "Automation"  And it will be a Scheduled Automation.  See the link for help.
Photo of Jake Rasmussen

Jake Rasmussen

  • 186 Points 100 badge 2x thumb
Thanks! Sorry for the late reply, I haven't had a chance to test this out until recently. But it seemed to work!