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.

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 Formula foundations 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)

GetRecordByUniqueField()

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. 

Unlike the GetRecord() function, the GetRecordByUniqueField() function can fetch records based on any unique field with the inputs of field id and a value parameter. This should be used in place of GetRecord() when you want to fetch records with custom key fields.

Examples:

	To query the same table	To query a different table	Result
Function layout	GetRecordByUniqueField("Unique value", Field ID)	GetRecordByUniqueField("Unique Value", Field ID, Table Alias)	Returns the record where the value in the specified field id matches the specified value
Example using hard-coded value	GetRecordByUniqueField("1001", 12) Returns the record where the value of field id 12 is 1001.	GetRecordByUniqueField("Customer Service System Update", 8, [_DBID_PROJECTS])​​ Returns the record where the value of field id 8 is "Customer Service System Update." In this case, the record is on the table with 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	GetRecordByUniqueField([Order Number], 6) Returns the record where the value in the Order Number field matches the value in field id 6.	GetRecordByUniqueField(([Order Number], 6), [_DBID_ORDERS]) Returns the record where the value in the Order Number field matches the value in field id 6. In this case, the record is on the table with alias DBID_ORDERS.	Not applicable (this function cannot be used on its own, so results depend on the other function it is used within)

Special considerations

• As stated earlier in this article, this function is ideal for custom key fields, but you can use any field that is both unique and required
  
  • If a field has been marked unique but a record with a duplicate field value exists, the formula query will return the first record that is found
  • If a field has been marked as required but a blank value exists, the formula query will not return a value for that record. Other records should be unaffected.
  • If a field was previously marked as unique and required, but it is not currently unique and required, the formula query will return a blank value

• This function will work for with any field type that can be marked unique and required. If it references a user field, however, you must enter one of the following:

  • A field reference (ex: the user field [Assigned to])

  • A user's email address(ex: abelrose@mycompany.com)

    • Entering a username like "abelrose" or the user's actual name like "Abdul Belrose" will not work

  • A complete user id (ex: 58651643.bzb4). 

    • An incomplete user id (for example, one missing the .bzb4 suffix) will not work

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, GetRecordByUniqueField(), 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.)
	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
	GetFieldValues(GetRecordByUniqueField("1001", 6), 13) Returns the value of the field with field id 13 in the record where the value of field id 6 is 1001.	GetFieldValues(GetRecordByUniqueField("1001", 6, [_DBID_SALES]), 13) Returns the value of the field with field id 13 in the record where the value of field id 6 is 1001.  In this case, field 6 and field 13 are from the table with table alias DBID_SALES.
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.
	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.
	GetFieldValues(GetRecordByUniqueField([Order Number], 6), 13) Returns the value of the field with field id 13 where the value in the field Order Number matches the value in field ID 6.	GetFieldValues(GetRecordByUniqueField([Order Number], 6, [_DBID_SALES]), 13) Returns the value of the field with field id 13 where the value in the field Order Number matches the value in field ID 6. In this case, field 6 and field 13 are from the table with table alias DBID_SALES.

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 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.

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 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
	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

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.

Example

GetFieldValues(GetRecords("{'6'.TV."&UserToID([User])&"}"), 3)

This example query returns all records where the value of field 6 is the same true value as the [User] field of the record on which the formula calculates. In this case, [User] is a user field, so the query uses the comparison operator TV instead of EX. While the EX operator will search only against the displayed value, TV will search the entire field. This means that when it is used with a user field, it will search all components that make up a user, including user id and user display name.

Because this query is used in a formula, the user field reference also needs to be wrapped in UserToID() (it could also be wrapped in UserToEmail() and work correctly). This is because when a user field is referenced in a formula it is converted to a text string that contains both the user id and the email. However, Quickbase needs either just the user id or the email to recognize the user. Wrapping the user field reference in UserToID() or UserToEmail() allows Quickbase to only look at the user ID or the email to evaluate the user.

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 .

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.

• Read the Design a form help article to learn how to add the record id field to a form.

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

• Read the Creating table reports help article to learn how to 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 .

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.

• To see all available type conversion functions, go to the Formula Functions Reference app in Quickbase. In the Category column, filter for Type Conversion.

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:

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.

• Read the Formula variables help article for more information on how to define and use variables.

Using recordlist in a formula query

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]&'}");

All of these examples use the GetRecords() function and recordlist to return a list of records.

Note: Quickbase doesn't have a field type for recordlist. So an app builder can't just call GetRecords(), and then expect Quickbase to display the results in a text field. They need to actually do something with that recordlist first.

You can cast it as text, like

ToText(GetRecords([inputs]))

Another option is to pass the result of GetRecords() to another function, like GetFieldValues(). You will still need to do something with that list as well, like summarize it or convert it to text. 

More examples using formula variables

Here are more example of 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 may not behave as expected. This is usually because a change on one record could invalidate a related record.

However, you can use formula queries that reference their own or another table in custom data rules.

Example: Custom data rule to prevent potential duplicates on a table

You may want to prevent duplicate information in certain fields to avoid duplicate accounts on a table.

You could add a custom data rule to the table, similar to this one:

If(Size(GetRecords("{6.EX.'"&[Customer Name]&"'}AND{7.EX.'"&[Region]&"'}")) > 1, "This customer already has an account entry for this region. Please choose a different region for this customer or enter a new customer name.")

This formula uses the Size function to count how many records have the same Customer Name and Region as the current record. If that number is greater than 1 (meaning a record already exists with the same customer name and region), the record will not be saved and the user will see the message: "This customer already has an account entry for this region. Please choose a different region for this customer or enter a new customer name."

Example: Custom data rule to prevent a new project if there are outstanding invoices

Assume 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, similar to this one:

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.

Making fields that contain formula queries searchable

When you search on fields that contain formula queries, Quickbase does all the necessary data analysis under the surface, just like with other derived fields. Depending on the complexity of the formula query, searching may mean first aggregating data from hundreds of records across multiple tables, running complex access permission rules, and processing advanced business logic. While this can be a powerful feature, it may also take longer to calculate. To optimize performance of your app, only make fields that contain formula queries searchable if absolutely necessary.

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.”

• Unless absolutely necessary, set all formula query fields as unsearchable in reports. To understand why, see the limitations and considerations section of this article. To learn how to make fields unsearchable, read the Excluding fields from searches help article.

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