Add a Running Total as a field in a table.

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered
We have a Parent-child relationship. One of the fields in each child record has an amount. We want to include a running total of the amount in each subsequent record where the total has only the previous record IDs associated in the relationship. 

I looked at creating a summary report and am able to generate the information in a report but do not see how to incorporate it into the table.

I can image using a formula with a case loop statement on record ID for all record ID's. 

Can anyone tell me if this is done and which direction to head?
Photo of Jim Harrison

Jim Harrison

  • 1,768 Points 1k badge 2x thumb

Posted 1 year ago

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

QuickBaseCoach App Dev./Training, Champion

  • 51,006 Points 50k badge 2x thumb
If you have never deleted any records, you can related the table to itself and have a field called [Link to previous record ID] with a formula of Record ID# - 1].  Then lookup the previous running total and add the current value to it, so like a daisy chain.

On a going forward basis you can set the role permissions to ensure that no records get deleted, only made inactive by a check box field to filer off reports.

Then no doubt you have deleted some records, so the daisy chain will be broken several times along the way.  You will need to have a [Parent Record ID# override] field so you can skip the missing links.

So the formula needs to be

IF([Parent Record ID# override]>0, [Parent Record ID# override], [Record ID#] -1)
Photo of Jim Harrison

Jim Harrison

  • 1,768 Points 1k badge 2x thumb
I did not include a key part, The parent table is a child so there are multiple parents. In other words the child table records are not in order. They are related using the record ID of the parent - child relationship. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,006 Points 50k badge 2x thumb
If they are not in Record ID# order, then what is the definition of the order sequence?

The other approach which can work on a going forward basis is to do a summary total of all children for a given parent and then do a lookup down to the children.

But then make a snapshot field to freeze that value.  Then ensure that users are not allowed to delete or edit the previous records as a frozen snapshot is frozen.  But that is probably too restrictive.

The other solution is to find a way to use a summary report to get close enough to what you need.  A summary report has the feature to present a running total. if you contrive the summary report to group by say record ID# or date created, then each summary row will really just be one child record.




Mark
Photo of Jim Harrison

Jim Harrison

  • 1,768 Points 1k badge 2x thumb
I have a Summary report doing a running total and it's great, I don't know how to get the result into the table as a field that matches records. I want something that is dynamic, meaning if a record gets updated it changes all the running total records, which is why I'm not using the snapshot. Also considered webhooks but was not successful. Hoping someone knows a trick... 

Thanks for your help!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,006 Points 50k badge 2x thumb
There is probably not a native solution, but I will ask the question.  What defines the order?
Photo of Jim Harrison

Jim Harrison

  • 1,768 Points 1k badge 2x thumb
It's a good question that I do not know how to answer. I will guess that the parent table defines the order of the child table on a reference proxy named "Work Order #". Any records in the child table associated with the parent "Work Order #" are grouped together using this field.

I am imagining a numeric formula field that uses the above relationship detail to locate the previous RID. If I can get the previous RID then I can do the running total. 
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,302 Points 20k badge 2x thumb
(Edited)
Photo of Jim Harrison

Jim Harrison

  • 1,768 Points 1k badge 2x thumb
Thanks Dan, I'm getting there.

Also found this very simple example on YouTube - Accumulated Monthly Sales Example
http://www.quickbase.com/quickbase-blog/calculating-accumulated-sales-with-quickbase 
I am trying to wrap the concept around our current architecture and modify field names in my brain to make more sense, then I will know if it's doable. If not I'm off to the JS books for a refresher. 

I know it is possible because I can create a Summary report with a calculated running total.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,302 Points 20k badge 2x thumb
If you want to create all those extra fields and have endless difficulties maintaining and debugging the mess go for it:




If you want to liberate yourself and have a carefree existence go for script. With script you can solve all QuickBase problems and you get to watch classic videos to pep you up while you work. Native QuickBase Forum answers rarely have videos attached.
(Edited)
Photo of Jim Harrison

Jim Harrison

  • 1,768 Points 1k badge 2x thumb
FYI a teammate was able to use Workato to generate a Running total. We have a couple of these and they are working. 

Did you know your name is showing up like "navigator.serviceWorker.register("dbid?a=dbpage&pagename=sw.js"), Champion".