Formula Queries will forever change the way Quickbase builders build apps.

Since the dawn of Quickbase time, table-to-table relationships have been required to aggregate data from one table into another – unless you invested in fancy custom code.

Consider that a thing of the past!

Now, with the 5 functions below and some query writing skills, you can forgo the complicated relationships when listing, counting, and summing data across tables.

Just a few possible uses:

• Ranking or numbering records
• Grouping and ordering records, such as "2 of 10"
• Showing top 10 records with remainder grouped as 'other'
• Adding running totals to table reports
• Identifying duplicate or other similar records
• Looking up values on another table (without a relationship)
• Looking up values from the same table for a prior time period
• Aggregating data from multiple tables onto one table

Check 'em out and be sure to watch the demos!

 GetRecord()

Returns a single record for use with other functions to Size( ), SumValues( ), or GetFieldValues( ).

Requires the target record's Record ID# (rid) and the Table DBID (dbid) if the record is not on the same table as the Query Formula.

Formats:

GetRecord(rid)

GetRecord(rid,"dbid")

GetRecord(rid,[_Table_Alias])

Examples:

• GetRecord(12)
• GetRecord(22,"brtan4571")
• GetRecord(12,[_DBID_Projects])

 GetRecords()

Returns a list of records for use with other functions to Size(), SumValues(), or GetFieldValues().

Requires a query (query) string and the Table DBID (dbid) if the record is not on the same table as the Query Formula.

For more information on query writing: Query Writing 101

Format:

GetRecords("query")

GetRecords("query","dbid")

GetRecords("query",[_Table_Alias])

Examples:

• GetRecords("{7.EX.'In Progress'}")
• GetRecords("{7.EX.'In Progress'}","brtan4571")
• GetRecords("{7.EX.'" & [Status] & "'}")
• GetRecords("{7.EX.'In Progress'}",[_DBID_Projects]) 

 Size() - Numeric Formula

Returns the count of items in a list. Used with GetRecords() when counting the items/records in a record list.

Tip: Size( ) can also be used on Multi-select Text and User List fields to return a count of items.

Format:

Size(GetRecords("query","dbid"))

Example:

For the list of records that have a Start Date with a fid of 7 on or after the record's Start Date
, count the number of records.

• Size(GetRecords("{7.OAF.'" & [Start Date] & "'}"))
• Numeric Result: 4

🎥 WATCH A DEMO: Rank Records using the 'Size' Query Function

SumValues() - Numeric Formula

Sums the values in a specified field within a record list. Used with GetRecords().

Format:

SumValues(GetRecords("query","dbid"),fid)

Example:

For the list of records where the Related Project field with a fid of 13 exactly matches the record's Related Project value on the table with the table alias _DBID_PROJECTS, sum the values in the Amount field with a fid of 7.

• SumValues(GetRecords("{13.EX.'" & [Related Project] & "'}",[_DBID_PROJECTS]),7)
• Numeric Result: 198000

🎥 WATCH A DEMO: Running Total with 'SumValues' Query Function

 GetFieldValues() - Multi-select Text Formula

Returns a list of field values for a specified field in a text format. Used with GetRecord() or GetRecords().

Requires a Record or Record List and a field ID (fid) to return.

Format:

GetFieldValues(GetRecord("query"),fid)

GetFieldValues(GetRecords("query"),fid)

Examples:

For the record with an rid of 22 on the table with dbid "brtan4571", get the value from the Start Date field which has a fid of 16.

• GetFieldValues(GetRecord(22,"brtan4571"),16)
• Text Result: "11-02-2021"

Use conversion functions to convert to other field types.

• ToDate(ToText(GetFieldValues(GetRecord(22,"brtan4571"),16)))
• Date Result: 11-02-2021

For the record list that exactly matches a Status field with a fid of 7 with a value of "In Progress", get a list of values from the Start Date field which has a fid of 16.

• GetFieldValues(GetRecords("{7.EX.'In Progress'}"),16)
• Text List Result: "11-02-2021 ; 12-08-2021 ; 11-27-2021"

🎥 WATCH A DEMO: Easy Metrics Table Using Formula Queries

Tip: Text lists use a " ; " as a separator between values. We can use this to our advantage.

Using a Text Formula or Rich Text Formula field, add ToText( ) and SearchAndReplace( ) around the GetFieldValues( ) function to replace the " ; " between values with line breaks.

• Multi-line Text Field:
  SearchAndReplace(ToText(GetFieldValues(GetRecords("{7.EX.'In Progress'}")),16)," ; ","\n")
• Rich Text Field:
  SearchAndReplace(ToText(GetFieldValues(GetRecords("{7.EX.'In Progress'}")),16)," ; ","<br>")

Learn more about HTML in Quickbase

 FAQs & Important Notes

• Cross-app formulas are not currently supported.
• Sorting values returned in GetFieldValues() is not currently possible.
• There isn't a function or option to 'de-dup' values returned in GetFieldValues( ).
  
• Queries resulting in no records will produce a null (empty) value. If you need a zero value to prevent the entire formula from being null, use the Nz() function with a value of 0. Example: [Value] + Nz(Size(GetRecords("{7.OAF.'" & [Start Date] & "'}")),0)
  

Additional Resources

Query Writing:

• Query Writing 101 for Quickbase

Demo Videos:

• Rank Records Using the 'Size' Query Function
• Running Total with 'SumValues' Query Function
• Easy Metrics Table Using Formula Queries

Quickbase Documentation

• Formula Queries

Formula Writing Courses:

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

Feeling like a Junkie?  Subscribe Now!