Oh So Fantastic Formula Query Functions
Oct 17, 2021Formula 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:
- Rank Records Using the 'Size' Query Function
- Running Total with 'SumValues' Query Function
- Easy Metrics Table Using Formula Queries
Quickbase Documentation
Formula Writing Courses:
Feeling like a Junkie? Subscribe Now!
