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!

Back to the Blog

Oh So Fantastic Formula Query Functions

advanced formula queries formulas intermediate Oct 18, 2021
Oh So Fantastic Formula Query Functions

Subscribe on YouTube

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:

Demo Videos:

Quickbase Documentation

Formula Writing Courses:

 

Feeling like a Junkie?  Subscribe Now!

Recent Posts & Videos from the Blog