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:

1. Identify the metrics you want to track.
2. Add a new table to house your metics.
3. 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.

RESOURCES:

Query Writing:

• Query Writing 101 for Quickbase

More Formula Query Help:

• Oh So Fantastic Formula Queries
• Quickbase documentation

Formula Writing Courses:

• Mastering the Text Formula
• HTML Formula Crash Course
• Formula Bundle

Feeling like a Junkie?  Subscribe Now!