Running Total with SumValues Query Function in Quickbase
Published at : October 24, 2021
More details available @ https://quickbasejunkie.com/blog/how-to-running-total
Remember 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:
1️⃣ 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).
2️⃣ 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
3️⃣ 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:
Copy the formula from the video
⇨ https://quickbasejunkie.com/blog/how-to-running-total
Query Writing 101
⇨ https://www.quickbasejunkie.com/blog/query-writing-101
About Formula Query Functions
⇨ https://www.quickbasejunkie.com/blog/formula-query-functions
⇨ https://help.quickbase.com/user-assistance/formula_queries.html
👍 If you find this helpful, please like & subscribe!
#Quickbase #QuickBaseJunkie #QuickbaseFormulaQueries
#QuickbaseFormulas #Database #NoCode #LowCode
🔥🔥🔥 FREE TOOLBOX🔥🔥🔥
Get The Action Button – 50+ Actions & URL Components EXPLAINED!
https://quickbasejunkie.com/toolbox
Feeling like a Junkie? Subscribe Now!
https://youtube.com/c/QuickBaseJunkie?sub_confirmation=1
LET'S CONNECT!
LinkedIn ➤ https://linkedin.com/company/quickbasejunkie/
Instagram ➤ https://instagram.com/quickbasejunkie/
Facebook ➤ https://fb.me/quickbasejunkie
Quick Base Junkie ➤ https://quickbasejunkie.com
Learn More About Quickbase @ https://quickbase.com
© Quick Base Junkie
COPYRIGHT NOTICE: The content, title, description, and specific ways in which the information in this video is organized, documented, and presented are the creative property of Quick Base Junkie. For use in any medium attribution is required, for questions or use permission, contact me @ https:/quickbasejunkie.com/contact.
Quick Base Junkie is unofficial and not affiliated with QuickBase Inc.
Remember 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:
1️⃣ 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).
2️⃣ 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
3️⃣ 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:
Copy the formula from the video
⇨ https://quickbasejunkie.com/blog/how-to-running-total
Query Writing 101
⇨ https://www.quickbasejunkie.com/blog/query-writing-101
About Formula Query Functions
⇨ https://www.quickbasejunkie.com/blog/formula-query-functions
⇨ https://help.quickbase.com/user-assistance/formula_queries.html
👍 If you find this helpful, please like & subscribe!
#Quickbase #QuickBaseJunkie #QuickbaseFormulaQueries
#QuickbaseFormulas #Database #NoCode #LowCode
🔥🔥🔥 FREE TOOLBOX🔥🔥🔥
Get The Action Button – 50+ Actions & URL Components EXPLAINED!
https://quickbasejunkie.com/toolbox
Feeling like a Junkie? Subscribe Now!
https://youtube.com/c/QuickBaseJunkie?sub_confirmation=1
LET'S CONNECT!
LinkedIn ➤ https://linkedin.com/company/quickbasejunkie/
Instagram ➤ https://instagram.com/quickbasejunkie/
Facebook ➤ https://fb.me/quickbasejunkie
Quick Base Junkie ➤ https://quickbasejunkie.com
Learn More About Quickbase @ https://quickbase.com
© Quick Base Junkie
COPYRIGHT NOTICE: The content, title, description, and specific ways in which the information in this video is organized, documented, and presented are the creative property of Quick Base Junkie. For use in any medium attribution is required, for questions or use permission, contact me @ https:/quickbasejunkie.com/contact.
Quick Base Junkie is unofficial and not affiliated with QuickBase Inc.
QuickBaseQuick BaseQuickBase Training