Running Total with 'SumValues' Query Function
Oct 17, 2021Remember what it was like to manually balance your checkbook?
Thank goodness that is a thing of the past!
(unless it is the sort of thing you enjoyed, then keep at it my friend)
Even though checkbook registers are a thing of the past, it's still good to know your account's running balance, which is why it's now displayed online.
And if your bank is like mine, it shows the ending balance next to each transaction, with the latest balance at the top.
Sadly, summary reports just don't cut it, and recreating this experience in Quickbase hasn't been simple.
I'll spare you the convoluted details of how it was done in the past because there is now a MUCH simpler solution involving one, that's right, just one formula.
The "SumValues" formula query function combined with the "GetRecords" function makes it possible – without any relationships, lookups, or summary fields.
SumValues is great for running totals, but it can also be used to:
- Sum values from records on the same table with matching criteria.
- Sum numbers, durations, and checkboxes for any list of records.
- Aggregate data from multiple tables onto one.
In this video, I'll demonstrate how to use the SumValues function to display a running total of opportunity Amounts in order of Closed Date, where the most recent is on top.
In addition, I'll address possible issues stemming from values falling on the same day.
There are 3 basic steps to this query formula:
- Identify the field IDs for your query (Amount, Close Date, & Record ID#) and the table ID (if the formula is on a different table from your records).
- Write your query. Include field references in the query string as necessary. I prefer to write my queries in a variable to make them easy to read and troubleshoot. See Query Writing 101 for more information on query writing
- Create the formula. Use the SumValues and GetRecords functions, combined with any other formula functions/field references, to achieve the desired result.
NOTE: An empty query returned in the SumValues function will result in a null value, not zero. Use the Nz( ) function to convert it to zero (see example in the formula below).
RESOURCES:
Final formula from the video:
Sum the values in the Amount field for the record list where the close date is before the record's Closed Date. If the result is null, return 0, otherwise, return the number. Then add that number to the sum of the values in the Amount field for the record list where the Close Date equals the record's Close Date and the Record ID# is less than or equal to the record's Record ID#. If that result is null, return 0, otherwise, return the number.
var text QONE = "{15.BF.'" & [Close Date] & "'}";
var text QTWO = "{15.EX.'" & [Close Date] & "'}AND{3.LTE.'" & [Record ID#] & "'}";
Nz(SumValues(GetRecords($QONE),7),0)+Nz(SumValues(GetRecords($QTWO),7),0)
Format if summing records from another table: GetRecords($QUERY,"mytableid")
where mytableid is the 9 digit dbid that comes after the 'db/' in the URL bar. The table alias may also be used.
Query Writing:
More Formula Query Help:
Formula Writing Courses:
Feeling like a Junkie? Subscribe Now!
