Quick Base Junkie Blog

Short videos and posts to skyrocket your Quickbase skills!

Get tips in your inbox

Join the Quick Base Junkie Community email list!

Easy Metrics table Using Formula Queries

advanced formula queries formulas how to intermediate Oct 17, 2021

Back in the day, one of my responsibilities was to report out on my department's performance over the prior week.

The report-out meeting with the SLT (Senior Leadership Team) took place on Monday afternoons.

That meant I had to scramble all Monday morning to gather the data from the prior week (through Saturday) to prepare for the meeting.

It's no wonder I "had a case of the Mondays" every Monday!

If I had a time machine, I'd put in place what I just implemented for one of my clients.

In his app, several query formulas pull data from across his app into one table with a nicely formatted form (thanks to additional HTML formulas) that displays everything, real-time, in a presentable format.

OMG Mondays would have been a walk in the park!

My only job then would be to tell the story of the data and share what comes next.

Using a combination of query functions (GetRecords, Size, SumValues, and GetFieldValues) gathering metrics really can be that easy.

In this video, I demonstrate how to use multiple query functions to create a metrics table in the Simple CRM app from the App Exchange.

There are 3 basic steps to creating a metrics table:

Identify the metrics you want to track.
Add a new table to house your metics.
Write your query formulas.

Formulas from the video:

# of Opportunities - Numeric formula

var text QUERY = "{15.OAF.'" & [Month] & "'}AND{15.OBF.'" & LastDayOfMonth([Month]) & "'}";
Size(GetRecords($QUERY,"mytableid"))

$ of Opportunities - Numeric formula (formatted as currency)

var text QUERY = "{15.OAF.'" & [Month] & "'}AND{15.OBF.'" & LastDayOfMonth([Month]) & "'}";
SumValues(GetRecords($QUERY,"mytableid"),7)

# YTD - Numeric Formula

var text QUERY = "{15.OAF.'" & FirstDayOfYear([Month]) & "'}AND{15.OBF.'" & LastDayOfMonth([Month]) & "'}";
Size(GetRecords($QUERY,"mytableid"))

$ YTD - Numeric Formula (formatted as currency)

var text QUERY = "{15.OAF.'" & FirstDayOfYear([Month]) & "'}AND{15.OBF.'" & LastDayOfMonth([Month]) & "'}";
SumValues(GetRecords($QUERY,"mytableid"),7)

Largest Opportunity - Multi-select text formula

var text QUERY = "{15.OAF.'" & [Month] & "'}AND{15.OBF.'" & LastDayOfMonth([Month]) & "'}AND{36.EX.'1'}";
GetFieldValues(GetRecords($QUERY,"mytableid"),6)

Note: {36.EX.'1'} refers to an additional helper field added to the Opportunity table (see the video for details).

New Accounts - Multi-select text formula

var text QUERY = "{15.OAF.'" & [Month] & "'}AND{15.OBF.'" & LastDayOfMonth([Month]) & "'}AND{37.EX.'1'}";
GetFieldValues(GetRecords($QUERY,"mytableid"),8)

Note: {37.EX.'1'} refers to an additional helper field added to the Opportunity table (see the video for details) .

In all of the above, "mytableid" is the 9 digit dbid that comes after the 'db/' in the URL bar for the Opportunities table where the records are being queried. The table alias may also be used.