Add a Running Total as a field in a table.

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)

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. 

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

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!

There is probably not a native solution, but I will ask the question.  What defines the order?

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. 

Embrace the script and all your problems are solved:




Count My Steps
https://haversineconsulting.quickbase.com/db/bm2katxzw?a=td

Pastie Database
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=591

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.

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.

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".