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

Query Writing 101 for Quickbase

advanced formula queries how to intermediate pipelines Oct 18, 2021
Query Writing 101 for Quickbase

Subscribe on YouTube

>> Quiz: Test Your Query Knowledge <<

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.

Operator Description   Operator Description
EX Equals   XEX Does Not Equal
CT Contains   XCT Does Not Contain
HAS Has   XHAS Does Not Have
SW Starts With   XSW Does Not Start With
BF Before   OBF On Or Before
AF After   OAF On Or After
IR Is During   XIR Is Not During
LT Less Than   LTE Less Than Or Equal to
GT Greater Than   GTE Greater Than Or Equal to
TV Has the True Value   XTV Does Not Have the True Value

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

About Formula Queries

Demos using Formula Queries

 

Feeling like a Junkie?  Subscribe Now!

Recent Posts & Videos from the Blog