Formula queries

In this article:

What are formula queries?

Before you start

Formula query functions

Building queries for your formulas

Formula field types and variables

Limitations and considerations

Optimizing performance

When to use formula queries instead of table relationships

Use cases and examples

What are formula queries?

Most formulas in Quickbase reference information in a single record. Formula queries can query information across multiple records and even unrelated tables in the same app.

Basic formula: Formula references data on a single record.

Formula with queries:Formula uses a query to reference data on related and unrelated tables. Example formula referenced records from a related field and an unrelated table.

With formula queries, you can do things like:

  • Find duplicate records in a table

  • Keep track of running totals

  • Autonumber records

  • Create advanced calculations

  • Create advanced report filters

  • Create fields with outputs similar to lookup summary fields without creating additional table relationships

Before you start

To successfully build formula queries, you need to have a basic understanding of Quickbase formulas. Read the Using Formulas in Quickbase help article to learn about formulas.

It is also helpful to understand Quickbase API query language. Basic query principles are covered in this article, but you can learn more in the Components of a Query help article.

Formula query functions

GetRecord()

You cannot use this function on its own. Use it within the GetFieldValues, SumValues, or Size functions, all of which can use a list of records as a parameter.

Use this function to fetch a specific record for reference.

  • Only Quickbase generated record ids (and not custom key fields) can be used to fetch records using GetRecord().

Examples:

  To query the same table To query a different table Result
Function layout

GetRecord(Numeric field)

GetRecord(Numeric field, Table Alias)

Returns the record with the specified record id

Example using hard-coded value

GetRecord(101)

Returns the record with record id 101

GetRecord(101, [_DBID_PROJECTS])​​

Returns the record with record id 101 on the table assigned alias DBID_PROJECTS

Not applicable

(this function cannot be used on its own, so results depend on the other function it is used within)

Example using field reference

GetRecord([Primary Contact ID#])

Returns the record with the record id that matches whatever value is in the Primary Contact ID# field of the record that the formula is being calculated on

GetRecord([Primary Contact ID#], [_DBID_CUSTOMERS])

Returns the record with the record id that matches whatever value is in the Primary Contact ID# field of the record that the formula is being calculated on. In this case, the record is on the table with alias DBID_CUSTOMERS.

Not applicable

(this function cannot be used on its own, so results depend on the other function it is used within)

GetRecords()

You cannot use this function on its own. Use it within the GetFieldValues, SumValues, or Size functions, all of which can use a list of records as a parameter.

Use this function to dynamically search for records using a custom query.

Examples:

  To query the same table To query a different table Result
Function layout

GetRecords("{query}")

GetRecords("{query}", Table Alias)

List of records

Example using hard-coded value

GetRecords("{6.EX.'In Progress'}")

Returns a list of record ids where the value of field id 6 is equal to “In Progress”

GetRecords("{6.EX.'In Progress'}", [_DBID_PROJECTS])​​

Returns a list of records from the table with alias DBID_PROJECTS where the value of field id 6 is equal to “In Progress”

Not applicable

(this function cannot be used on its own, so results depend on the other function it is used within)

Example using field reference

GetRecords("{6.EX.'"&[Manager name]&"'}")

Returns a list of record ids where the value of field id 6 is equal to whatever value populates the Manager name field of this record

GetRecords("{6.EX.'"&[Manager name]&"'}",[_DBID_PROJECTS])

Returns a list of record ids from the table with alias DBID_PROJECTS where the value of field id 6 is equal to whatever value populates the Manager name field of this record

Not applicable

(this function cannot be used on its own, so results depend on the other function it is used within)

GetFieldValues()

Use this function to get values from a specific field of the records referenced in the GetRecord or GetRecords function.

  • This function provides an actionable output to manipulate or display.

Examples:

  To query the same table To query a different table Result
Function layout

GetFieldValues(GetRecords("{query}"), Field ID)

GetFieldValues(GetRecords("{query}", Table Alias), Field ID​)

List of field values. Values will be displayed like multi-select fields in the table.

Example using hard-coded value

GetFieldValues(GetRecords("{6.EX.'In Progress'}"), 3)

Returns the value of the field with field id 3 in records where field id 6 is equal to “In Progress”

(Field id 3 is the Record ID# field, so this will return a record id number.)

GetFieldValues(GetRecords("{6.EX.'In Progress'}", [_DBID_PROJECTS]), 3)

Returns the value of field 3 in records where field id 6 is equal to “In Progress”. In this case, field 3, field 6 and the records are all from the table with table alias DBID_PROJECTS.

(Field id 3 is the Record ID# field, so this will return a record id number.)

Example field titled: Other tasks In-Progress. Example values returned from formula: 2,3. Values in the field are separated in a small box, just like multi-select fields display. There is no separating punctuation between the values.

GetFieldValues(GetRecord(101), 5)

​​Returns the value of the field with field id 5 in the record with record id 101

GetFieldValues(GetRecord(101, [_DBID_PROJECTS]), 5)

Returns the value of the field with field id 5 in the record with record id 101 from the table with alias DBID_PROJECTS

Example field titled: Prioritization. Example value returned: Med. Value in the field are separated in a small box, just like multi-select fields display.
Example using field reference

GetFieldValues(GetRecords("{6.EX.'"&[Manager name]&"'}"), 10)

Returns the value of the field with field id 10 in records where field id 6 is equal to the Manager name field in the record the formula is being calculated on

GetFieldValues(GetRecords("{6.EX.'"&[Manager name]&"', [_DBID_PROJECTS]), 10)

Returns the value of the field with field id 10 in records where field id 6 is equal to the Manager name field in the record the formula is being calculated on

In this case, field 10, field 6 and the records are all from the table with table alias DBID_PROJECTS.

Example field titled: Also managing these projects. Example values returnes: Annual conference, Market Analysis. Values in the field are separated in a small box, just like multi-select fields display. There is no separating punctuation between the values.

GetFieldValues(GetRecord([Primary Contact ID#]), 8)​​

Returns the value of field id 8 from the record where the field Primary Contact ID# matches with the same value as the record the formula is being calculated on

GetFieldValues(GetRecord([Primary Contact ID#], [_DBID_CUSTOMERS]), 8)​​

Returns the value of field id 8 from the record where the field Primary Contact ID# matches the same value as the record the formula is being calculated on. In this case, field id 8 and the records are on the table assigned table alias DBID_CUSTOMERS.

Example field titled: Primary Contact Phone Number. Example value returned: (583)669-2868. Value in the field are separated in a small box, just like multi-select fields display.

SumValues()

Use this function to calculate the sum of values in a field for records that match the query.

Examples:

  To query the same table To query a different table Result
Function layout

SumValues(GetRecords("{query}"), Field ID)

SumValues(GetRecords("{query}", Table Alias), Field ID))

Numeric value

Example using hard-coded value

SumValues(GetRecords("{6.EX.'In Progress'}"), 4)

Calculates the sum of the values in field id 4 where field id 6 is equal to “In Progress”​

SumValues(GetRecords("{6.EX.'In Progress'}", [_DBID_PROJECTS]), 4)

Calculates the sum of the values in field id 4 where field id 6 is equal to “In Progress”​

In this case, field 4, field 6 and the records are all from the table with table alias DBID_PROJECTS.

Example field titled: Total hours spent on all In-Progress projects. Example value returned: 5

Example using field reference

SumValues(GetRecords("{6.EX.'"&[Manager name]&"'}"), 4)

Calculates the sum of the values in field id 4 where field id 6 is equal to the value that populates the Manager name field in the record this formula is being calculated on

SumValues(GetRecords("{6.EX.'"&[Manager name]&"'}", [_DBID_PROJECTS]), 4)

Calculates the sum of the values in field id 4 where field id 6 is equal to the value that populates the Manager name field in the record this formula is being calculated on

In this case, field 4, field 6 and the records are all from the table with alias DBID_PROJECTS.

Example field titled: Total manager hours spent. Example value returned: 7

Size()

Use this function to count the number of records in a text list, user list, or record list.

  • This function can use GetRecords as a parameter, or it can simply use a user list field or multi-select text field.

Examples:

  To query the same table To query a different table Result
Function layout

Size(GetRecords("{query}"))

Size([Mulit-Select Field Name])

Size(GetRecords("{query}", Table Alias))

Size([Mulit-Select Field Name], Table Alias)

Numeric value

Example using hard-coded value

Size(GetRecords("{6.EX.'In Progress'}"))

Counts the number of records where field id 6 is equal to “In Progress”

Size(GetRecords("{6.EX.'In Progress'}", [_DBID_PROJECTS]))

Counts the number of records in the table assigned alias DBID_PROJECTS where field id 6 is equal to “In Progress”

Example field titled: Total # of projects In-Progress. Example value returned: 3

Example using field reference

Size([Assignees])

Counts the number of assignees (a list-user field)

Size([Assignees], [_DBID_PROJECTS])

Counts the number of assignees (a list-user field) in the table assigned alias DBID_PROJECTS

Example field titled: Total # of employees assigned to project. Example field value: 4

Size(GetRecords("{6.EX.'"&[Status]&"'}")​)

Counts the number of records where field id 6 is equal to the value found in the Status field of the record this formula is being calculated on

Size(GetRecords("{6.EX.'"&[Status]&"'}", [_DBID_PROJECTS]))

Counts the number of records on the table assigned alias DBID_PROJECTS where field id 6 is equal to the value found in the Status field of the record this formula is being calculated on

Example field titled: Total manager hours spent. Example field value: 7

Building queries for your formulas

Formula query functions use Quickbase API query language.

Query components

A query consists of at least one query string composed of:

  • a field id (fid)

  • an uppercase comparison operator (see the Components of a Query help page for a list of available operators)

  • the value to be compared against

Separate each of these query string components using a period and enclose the entire query string in curly braces. The value to be compared against must be enclosed in single quotes:

{'fid'.operator.'matching_value'}

{fid.operator.'"&[field reference]&"'}

Note: In the context of formula queries, the single quotes around the field id are optional.

Special instructions for queries in formulas

When using a query in a formula query function, you must also enclose the entire query (everything in the curly braces) in double quotation marks.

Example

GetFieldValues(GetRecords("{'5'.EX.'In Progress'}"),3)

This example query (everything in the curly braces) returns all records where the the value of field id 5 is equal to the value “In Progress.” Used within the GetFieldValues function, the output of the formula will be the values of field id 3 in records returned by the query.

The ampersand symbol (&) concatenates, or links, elements the query string. If you want to use a field reference instead of a literal value in your query, connect the field reference to the query by enclosing it in ampersands.

Example

GetFieldValues(GetRecords("{'5'.EX.'"&[Manager name]&"'}"), 3)

In this example, the GetRecords query string starts with a hardcoded value (the field id). The formula reference is linked to the value using the ampersand and then linked to the closing bracket of the query. The query returns all records where the value of field id 5 is equal to the value in the Manager name field of the record on which the formula calculates. Used within the GetFieldValues function the output of the formula will be the values of field id 3 in records returned by the query.

Note: If the value to be compared against is a field reference, surround the ampersand symbols with double quotation marks, as shown in the example. In the context of formula queries, double quotation marks signal that the ampersand and field reference should not be treated as literals.

Grouping multiple query strings

You can group several query strings together, separating the query strings with the AND or OR operators:

Example

GetFieldValues(GetRecords("{8.EX.'"&[Email]&"'}AND{7.EX.'"&[Last Name]&"'}"), 3)

This example finds the value of field 3 on records where field 8 is the same email on the record on which the formula calculates and field 7 is the same last name as the record on which the formula calculates. This formula could help you determine if there are any duplicate records on the table that share the same last name and email.

Finding field ids, record ids, and table aliases to use in queries

When you use a query in a formula query function, you will potentially need to reference field ids, record ids, and table aliases.

To view field ids:

  1. Click on a table in the app.

  2. Click on the settings settings icon, image of a cog wheel.

  3. Click Fields. On the Fields setting screen, you can view Field Label, Type, Relationship, and Field ID.

To view record ids:

The record id field is automatically created for each table. By default it is named Record ID#, and it will always be assigned field id 3. To see the record id field when you view a record, add it to a form.

You can also add the record id field to a table report.

If you cannot find the Record ID# field, it is possible someone has changed the default field name. The field id cannot be changed, however, so you can locate which field is serving as the Record ID# by identifying the field with id 3.

To view table alias:

  1. Click on the table in the app.

  2. Click on the settings settings icon, image of a cog wheel.

  3. Click Advanced settings. The table alias appears in all capital letters at the end of the Advanced Table Settings section.

*While it is possible to search records from other tables using the table id, it is best practice to use the table alias.

Formula field types and variables

Formula query functions used on their own only work in certain formula field types.

However, just like other formulas, you can use them in any almost any field type by using variables or type conversion functions. Type conversion functions allow you to convert one or more data types within your function.

Without transforming data or using variables, formula query functions will work with the following formula field types:

Formula query function Compatible formula field type

GetFieldValues

Formula - Multi-select text

SumValues

Formula - Numeric

Size

Formula - Numeric

Example using a type conversion

Formula query in a Formula - Text field type:

ToText(GetFieldValues(GetRecords("{9.EX.'"&[Last Name]&" '}AND{8.EX.'"&[Email]&"'}"), 11))

This example returns the field value of field 11 in records where field id 9 is equal to the last name on which the formula calculates and field id 8 is equal to the email of the record on which the field id calculates.

The ToText function converts the values of field 11 to text so that the formula result can be displayed in a formula - text field. For example, if the query returns the value of field 11 from two records, the values will appear as text separated by a semi-colon:

field with text values a;b

Using variables in complex formulas

Formula variables can help shorten complex formulas and help you save time when writing formulas that rely on formula queries.

In addition to variable types listed in the Formula variables help article, you can also use var recordlist when building formula queries.

Here are some examples of how var recordlist could be defined:

var RecordList singleRecord = GetRecord(8);

var RecordList records = GetRecords("{6.EX.'&[Manager name]&'}");

More examples using formula variables

Formula query in a Formula - Check box field type:

var TextList vals = GetFieldValues(GetRecords("{6.EX.'"&[Status]&"', [_DBID_PROJECTS]), 8)​;

If(Contains($vals, ”High”), true, false)

  1. This example first defines the variable vals using the GetFieldValues function.

    • The GetFieldsFunction will fetch the value that populates field id 8 of any records from the table with alias DBID_PROJECTS where field id 6 is equal to the same status as the record this formula is being calculated on.

  2. Then the example uses an If function. If any of the field values gathered in the GetFieldValues function contain the text “High,” the result is true. If not, the result is false.

  3. If the result is true, the check box will be checked.

Formula Query in a Formula - Date field type:

var number holidays= Size(GetRecords("{7.OAF.'"&[Start Date]&"'}AND{7.OBF.'"&([Start Date]+Days([# of Days (numeric)]))&"'}", [_DBID_HOLIDAYS]));

[Start Date]+Days([# of Days (numeric)])+Days(Size(GetRecords("{7.OAF.'"&[Start Date]&"'}AND{7.OBF.'"&([Start Date]+Days([# of Days (numeric)]+Days($Holidays))&"'}", "[_DBID_HOLIDAYS]"))

  1. The Size() function is used in this formula to count the number of holiday records that fall within a given time period to automatically calculate the end date of a specific task.

  2. The formula begins with a variable to identify the number of holidays within the duration.

    • This will be used to adjust the final result to account for situations where the original duration may end on a holiday, but the following day is also a holiday (in the United States, this is the case for the Thanksgiving holiday).

    • The query is searching the [_DBID_HOLIDAYS] (holiday table) for any holiday dates (field id 7) that fall on or after [Start Date] and on or before [Start Date] plus the task duration (calculated End Date). The size function then counts the number of records returned.

  3. The final result of the formula, the task end date, is calculated by adding [Start Date] to the total task duration (duration defined at the task level, and the number of holidays).

    • The query in this part of the formula is identical to the one defined in the variable, except it is adding the number of holidays from the variable to account for the original duration ([Start Date]+[# of Days (numeric)]) ending on a holiday.

Limitations and considerations

Override sub-field access capability

When you set override sub-field access, you define who can access a formula field and override the permissions set for the fields referenced in the formula.

Unlike other formula functions, you cannot override sub-field access when you use formula queries. This means that access to formula fields that contain a formula query is determined by the access set for fields referenced in the formula query functions.

Grid edit behavior

If you use the grid edit feature for tables that have formula query fields, keep in mind the following behaviors:

Editing a field referenced on multiple records

If you edit a field used in a query calculation, the formula query result field will be updated only for this single record. If this same field is used in formula query calculations in other records, the changes will not be reflected until after you have saved all grid edits.

For example:

  • Both field C and field F reference field A in their calculation.

  • In grid edit mode, if you change the value of field A, only field C will update (since it is on the same record).

  • The value of field F will not update until after you have saved the grid edits.

  Field Field calculated by formula query Field calculated by formula query
Record 1 A B C (references A, will update pre-save)
Record 2 D E F (references A, will not update pre-save)

Editing multiple fields impacted by formula query calculations

If you edit fields that impact formula query results, and other fields rely on the calculated results for their value, the original value will be used in the additional formula calculation until after you have saved grid edits.

For example:

  • Both field C and field F reference field A in their calculation.

  • Field H references field F in its calculation.

  • If you edit field A, only field C will update before saving the grid edit. Field F will not update.

  • Before saving the grid edit, field H will be calculated using the original value of field F.

  • After saving the grid edits, all field values will be updated.

  Field Field calculated by formula query Field calculated by formula query
Record 1 A B C (references A, will update pre-save)
Record 2 D E F (references A, will note update pre-save)
Record 3 G H (references F, will use original value pre-save) I

Note: You cannot edit the formula field itself in grid edit mode.

Using formula query fields in custom role permissions and custom data rules

Using a formula query that references its own table in the formula field used for custom permissions, or using a formula query for custom data rules may not behave as expected. This is usually because a change on one record could invalidate a related record. You can see similar issues if using lookup or summary fields in custom data rules.

However, a custom data rule that references a different table should work correctly.

For example:

You have two unrelated tables, Projects and Invoices. You don’t want to allow a new project to be started while there are outstanding invoices.

You could add a custom data rule on the Projects table:

If(Size(GetRecords("{7.XEX.'Complete'}", [_DBID_INVOICES])) > 0, "You must complete all invoices before starting a new project")

This formula uses the Size function to count how many records do not have the value “Complete” populating field id 7. If that number is greater than 0 (any records are not marked “Complete”) the user will see the message “You must complete all invoices before starting a new project.”

Using formula query fields as a filter or sort field in a report

Filter, sort, and group operations take additional processing time. As the complexity or size of your apps grow, it is best to optimize your reports by performing additional logic and work on the simplest fields possible. To optimize performance and increase scalability, it is not recommended to filter, sort, or group on formula query fields.

Using complex formula queries inside of a report formula

If using a formula query in a report formula, keep in mind that report formulas are designed for temporary use. Formulas with complex logic or formulas that act on many records, fields, or tables will perform better when created in a field on the table.

For more information on report formulas, read the Using a report formula help article.

Other limits and protections

To protect all customers on the platform, Quickbase checks to see if complex formula queries will have high performance impact. If Quickbase detects that your formula will not run efficiently on a specific table, you will see an error that says:

“Uh-oh! The way this formula is built will take too long to process. Please try refining your formula.”

You will not be able to save the formula without making adjustments. In addition to using the optimization methods listed in this article, you can leverage the Quickbase performance analyzer feature. Read the performance analyzer help article to learn how to evaluate processing time and improve performance.

When a report or API call is run, the platform checks again to protect users from performance issues. In cases where parts of the report exceed Quickbase algorithms, the following error will be returned:

“Uh-oh! The way this report is build will take too long to process.”

You can use the performance analyzer to understand what adjustments to make.

Optimizing performance

Optimizing the performance of your app is best for you and your users. Here are common ways you can optimize your queries and formulas:

  • Eliminate the most records in the first comparison strings (a comparison string is everything within the curly braces). Queries are processed sequentially, and eliminating the most records first will improve query speed.

  • Use manual input fields rather than derived fields like summary or formula fields. Derived fields must do additional queries, permission checks, and calculation before they can be evaluated against your query.

  • Use exact matches when possible to build your filter criteria. Using “is equal to” as your matching operator will be faster than using  “contains.”

To learn more about optimizing app performance, read the Optimizing Performance of Your App help article.

When to use formula queries instead of table relationships

It is not best practice to use formula queries to accomplish tasks that can be easily done using table relationships. Formula queries are best used when you need to:

Scenario Example

Display data between two tables that share similar data points but are not directly or indirectly connected via a relationship

A table titled Tasks and a table titled Out of Offices may share certain data points, but the workflow does not require them to be related. Customers may want to be able to check for a task owner’s out of office when assigning someone to the task.

Reference data from records within the same table

You may want to create a running total of data or count the number records that share a common data point.

Avoid complex app structure

Some workflows, like checking for the number of holidays that fall in a given period of time, might call for a relationship or creating additional tables in an app for the sake of summarizing data for calculation​. You can avoid these additions with formula queries.

When deciding whether or not to use a formula query, ask yourself the following questions:

  • Is the information I need already part of a parent to child relationship?

  • Is the information I need already part of a many-to-many relationship?

  • Can I accomplish this by using a direct lookup or summary field?

Read the About table-to-table relationships help article to learn more about when to use relationships.

Use cases and examples​

Quickbase Community Post: Finding Duplicates

Quickbase Community Post: Create Sequential Numbering