I'm willing to bet you've created more than one report with filters specifying which records you want the report to include.

That filter...

That's a query!

When you build reports in Quickbase, you have an easy interface to construct your query, so you probably don't think much about it.

But, queries can be used in a variety of Quickbase locations to gather and refine lists of records, including:

• Formula fields using the query function GetRecords()
• Pipeline search steps using the Advanced Query section
• API calls in the JSON, XML, or URL

 The Basics

A query string is composed of 3 parts. A Field ID, an Operator, and a Value.

{fid.OPERATOR.'value'}

• fid = the field ID to search
• OPERATOR = the comparison operator (capitalized)
• value = value to compare against

Example Query

Field ID 22 does not equal "Closed"

{22.XEX.'Closed'}

 Comparison Operators

Use the table below to identify the correct syntax for your comparison operator.

IMPORTANT: These comparison operators are ALWAYS capitalized.

For more detailed information on these Operators visit this Quickbase help page. 

 Comparing Multiple Conditions

Using "AND" or "OR" operators between individual query strings allows you to fine tune your results to include multiple conditions.

IMPORTANT: These operators are always capitalized.

Field ID 22 equals "Closed" and Field ID 13 is on or after "01-01-2021"

{22.EX.'Closed'}AND{13.OAF.'01-01-2021'}

Field ID 22 equals "Closed" or "Canceled"

{22.EX.'Closed'}OR{22.EX.'Canceled'}

Field ID 22 equals "Closed" or Field ID 22 equals "Canceled", and Field ID 13 is on or after "01-01-2021"

({22.EX.'Closed'}OR{22.EX.'Canceled'})AND{13.OAF.'01-01-2021'}

Note the placement of parentheses above to control the order of operations.

Field ID 22 equals "Closed", or Field ID 22 equals "Closed" and Field ID 13 is on or after "01-01-2021"

{22.EX.'Closed'}OR({22.EX.'Canceled'}AND{13.OAF.'01-01-2021'})

Note the placement of parentheses above to control the order of operations.

 Test Your Query Knowledge

Think you're getting the hang of queries?

Step right up and test your Quickbase query writing knowledge.

This quiz contains 10 query-centric (multiple choice) questions.

Get 8 right to pass as a Query Pro.

Get all 10 right and claim your title as Query Master! 🏆

Take the Quiz >> Test Your Query Knowledge

 Query Examples by Location

Formula Fields

Formula Queries allow you easily to query your Quickbase data and return the values directly in a field.

What's even better is that you can also take advantage of all the other formula functions and field references.

With a static value

Size(GetRecords("{7.OAF.'01-01-2021'}")) 

With a value determined by the value in a specific field

Size(GetRecords("{7.OAF.'" & [Start Date] & "'}")) 

More on Formula Queries

Pipelines

Occasionally the built-in query options just don't cut it.

Advanced Queries to the rescue!

On Pipeline steps that perform a Search or Query, there is a section to write "Advanced Queries". Note, you can only use Advanced Queries or the built-in Query section, not both.

With a static value

{22.XEX.'Canceled'}

With a value determined by a relative operator

{22.EX.'today'}

With a value determined by the value in a specific field

{22.EX.'{{a.status}}'}

Warning: You can only reference fields that are available for use in that Pipeline step.

More on Jinja for Pipelines

Advanced Uses

The following additional query locations are only recommended for builders with advanced Quickbase experience.

URL Strings

URL Strings may include a Query for a variety of purposes, such as filtering a report or identifying records for subsequent display or processing. Some elements, like the single quotes, may need to be URL encoded.

https://mycompany.quickbase.com/db/mydbid?a=q&qid=1&query={22.EX.%27Closed%27})

Example URL String from Quickbase

XML Format

XML is often used in Quickbase webhooks. Below is an excerpt of the query line from a webhook that uses the API_DoQuery.

<query>{22.EX.'Closed'}</query>

More on Queires in XML from Quickbase

JSON Format

Quickbase's RESTful APIs enable many actions to be controlled through the use JSON (a programming language).

"where": "{22.EX.'Closed'}",

More on using Queries in JSON from Quickbase

 Troubleshooting

I've had my fair share of queries returning the wrong values or no values at all.

When composing the query in a formula it can be especially tricky to get all the formatting just right.

If you're experiencing issues, it may be for one of these reasons.

• The value to compare against (the 3rd part of the query string) is not enclosed in a set of single quotations. These single quotes are not necessary if you have a continuous string value such as a number or single word, but when there's a break/space, those single quotes can make all the difference.
• The proper query format hasn't been used {fid.operator.'value'}
  • Did not enclose the query strings in curly brackets
  • Included extra spaces within your query string
  • Didn't separate the comparison operator from your fid or value with a period
• The wrong field ID (fid) has been used. Double-check the field properties.
• There are missing or mismatched parentheses.
• The comparisons operator is wrong, misspelled, or not capitalized.
• When combining multiple queries, the "AND" or "OR" operators are not capitalized.
• The field type of the fid and the value to compare against are not compatible for comparison.
• If utilizing the query string in a URL, parts of it may need to be URL encoded.

 Additional Query Resources

Quickbase Documentation

• Quickbase API_DoQuery documentation - This documentation is written to help developers with the API_DoQuery, but contains a lot of general query information.
• Quickbase Relative Date Range documentation - Detailed information to support the use of "IR" (is during)  and "XIR" (is not during) operators for periods like "today", "last week", or "this year".

About Formula Queries

• Oh So Fantastic Formula Query Functions - Learn all about the 5 Query Functions: GetRecord( ), GetRecords( ), GetFieldValues( ), Size( ), SumValues( ).

Demos using Formula Queries

• Rank Records Using the 'Size' Query Function - Quickbase demo using the Size( ) and GetRecords( ) functions
• Running Total with 'SumValues' Query Function - Quickbase demo using the SumValues( ) and GetRecords( ) functions
• Easy Metrics Table Using Formula Queries -  Quickbase demo using multiple formula query functions including GetFieldValues( ).

Feeling like a Junkie?  Subscribe Now!