Don't forget to check out our JSON RESTful APIs, they can help you utilize and extend Quickbase with ease.
A query typically includes the following parameters:
The Query parameter
The query parameter determines which records are returned. You can build a new query string inside the <query></query> tag or you can choose to reference a saved query string using the <qid> or <qname> tags.
Building the query string
When you build a query, you must build at least one query string. A query string is composed of:
- a field ID (fid)
- an uppercase comparison operator (see the table below for a list of available operators).
Note: If you use a lowercase operator, you may encounter unexpected behavior. Use only uppercase operators. - the value to be compared against
You should separate each of these query string components using a period and enclose the entire query string in curly braces, as shown below:
{'fid'.operator.'matching_value'}
Example:
<query>{'5'.CT.'Ragnar Lodbrok'}</query>
This example specifies that Quickbase should return all records where the “fid 5” field contains the value “Ragnar Lodbrok.”
Grouping multiple query strings
You can group several of query strings together, separating the query strings with the AND or OR operators, as shown here:
Example:
<query>{'5'.CT.'Ragnar Lodbrok'}AND{'7'.CT.'Acquisitions'}</query>
The next example illustrates how to group query strings together when using a URL:
Example:
&query={'5'.CT.'Ragnar Lodbrok'}AND{'7'.CT.'Acquisitions'}
Note: You can obtain the fids for a table by calling API_GetSchema, or from any call to API_DoQuery that sets the fmt parameter to “structured.”
Sorting queries
To select a sort order, you need to include a specific attribute in the options parameter. See Options Parameter for more information.
Queries with numeric values that include decimals
When you are using a query for a numeric field with a decimal, you will need to note your syntax. For example, {12.EX.5.6} would be the same as {12.EX.5}. You can query for decimal values by using apostrophes {12.EX.'5.6'}. Note that EX isn't the only filter that this applies to.
Query string comparison operators
Note: Query string comparison operators must be in uppercase.
| Comparison Operator | Description | Applicable field types |
|---|---|---|
|
CT |
Contains either a specific value or the value in another field of the same type. |
Do not use this operator with list-user fields; instead, use HAS. |
|
XCT |
Does not contain either a specific value, or the value in another field of the same type. |
Do not use this operator with list-user fields; instead, use XHAS. |
|
HAS |
Contains a specific set of users. For each user you are trying to find, you must enter the user's ID, user name, or email address. You can also enter placeholder names. Be sure to surround placeholder names with double quotes. The query parameter must be surrounded by single quotes. Separate users in the list using a semi-colon. For example:
|
Used with list-user fields and multi-select text fields only. |
|
XHAS |
Does not contain a specific set of users. The query parameter must contain the user's ID, email address, or user name. You can also specify a placeholder name. Placeholder names must be enclosed in double quotes. The entire query parameter must be surrounded by single quotes. Separate users in the list using semi-colons. Note that a matching record must contain all users you specify. This query:
...specifies that you want to see records that do not contain BOTH of these users. Therefore, the query will return records that contain either one or neither, but not both, of these users. |
Used with list-user fields and multi-select text fields only. |
|
EX |
Is When specifying values to query from List - User and Multi-select Text fields, enclose the entire query parameter in single quotes. Separate the values you're looking for using semi-colons. When you use a query like 10.ex. where 10 is a user field, ex is going to compare to the textual representation that the builder has configured on field props which is either full name, last name first, or username (which is email unless configured otherwise). |
Is equal to either a specific value, or the value in another field of the same type. See Filtering records using fields with multiple values for more information. |
|
TV |
True Value (compares against the underlying foreign key or record ID stored in relationship fields). When you use tv, you are comparing against the unique value of the user which is either the hashed UID like 123456.abcd OR the email address OR the screen name.
|
Also used for queries on User fields. |
|
XTV |
Not True Value (compares against the underlying foreign key or record ID stored in relationship fields). |
Also used for queries on User fields. |
|
XEX |
Is not Is not equal to either a specific value, or the value in another field of the same field type. |
When specifying values to query from List - User and Multi-select Text fields, enclose the entire query parameter in single quotes. Separate the values you're looking for using semi-colons. |
|
SW |
Starts with |
Starts with either a specific value or the value in another field of the same type. |
|
XSW |
Does not start with |
Does not start with either a specific value or the value in another field of the same type. |
|
BF |
Is before |
Is before either a specific value or the value in another field of the same type. |
|
OBF |
Is on or before a specific date |
Is on or before either a specific date or the value in another date field |
|
AF |
Is after a specific date |
Is after either a specific date or the value in another date field |
|
OAF |
Is on or after a specific date |
Is on or after either a specific date or the value in another date field |
|
IR |
Is in range. |
Use this operator with date fields, to determine whether a particular date falls within particular date range relative to the current date. Learn more about relative date ranges. |
|
XIR |
Is not in range. |
Use this operator with date fields, to determine whether a particular date falls within a particular date range relative to the current date. Learn more about relative date ranges. |
|
LT |
Is less than |
Is less than either a specific value or the value in another field of the same type. |
|
LTE |
Is less than or equal to |
Is less than or equal to either a specific value or the value in another field of the same type. |
|
GT |
Is greater than |
Is greater than either a specific value or the value in another field of the same type. |
|
GTE |
Is greater than or equal to |
Is greater than or equal to either a specific value or the value in another field of the same type. |
Sample queries
To query a particular field and exclude a string:
{'7'.XCT.'Fred'}
To query any field for a date:
{'0'.OAF.'3/31/01'}
To combine multiple query strings:
{'9'.SW.'Wilma'}OR{'10'.XSW.'Dino'}
To query for records owned by the current user:
{'4'.TV.'_curuser_'}
To query for records modified by the current user:
{'5'.TV.'_curuser_'}
Handling Special Characters for a Query Field Values
If you are searching for a value that includes special characters, be sure to enclose the matching value in quotes. For example, if you are searching for this value:
"Joe B. Briggs"
...you should be sure to enclose the entire string in double quotes, as follows:
""Joe B. Briggs""
Querying on Dates and Times
You can query on dates (yyyy-MM-dd) or on times in milliseconds, but you cannot query on date-time values in the standard "yyyy-MM-dd hh:mm:ss" format.
Special Matching Values in a Query String
Certain field types use special values with a given comparison operator. For example, you may want to query on a Checkbox field. You may want all records where a Checkbox is either checked or not. In cases like this, you can use special matching values in your query string to return records with the value you want.
| Field Type | Description |
|---|---|
|
Checkbox |
1 or 0 |
|
Date and Date/Time |
today or <num> days ago or -<num> days ago or mm-dd-yyyy |
|
User |
_curuser_ |
Referencing a Common Report
In the Quickbase UI, a user can customize a report and save it as a common report, which will then be available as a saved query. Each saved query has an ID (qid) and a user-supplied name (qname). Creating a saved query is an easy and quick way to construct a query, compared to building the query string yourself.
To use a saved query, you refer to it by its qid (e.g., <qid>3</qid>) or by the name you gave it when you saved it (e.g., <qname>My Custom
View</qname>. For the qname value, do not replace blanks spaces with underscore.
You can use API_DoQuery to get the qid and qname of common reports. If you use the fmt parameter, Quickbase returns a list of all saved queries inside a <queries> aggregate, including their qid and qnames. (You can also use API_GetSchema to get the qid or qname of a saved query).
Quickbase tables have two default saved queries: List All, and List Changes.
Column List (clist) Parameter
The clist specifies which columns will be included in each returned record and how they are ordered in the returned record aggregate.
XML Example:
<clist>4.7.9.11</clist>
URL Example:
&clist=4.7.9.1
...where each number is the fid of the field (column) you want returned, with a period separating the fids. The columns will be listed in the returned record in the same order as they appear in the clist. If you don’t specify a clist, Quickbase returns your table’s default columns and order.
If no column list parameter is supplied, the default report layout and field order is used.
To change the default field order, see the Quickbase online help:
-
Changing the default field order in a table.
-
Changing the default field order on a form.
How to Return ALL of the columns
Specify the value a in the clist param to specify that all columns should be returned.
Sort List (slist) Parameter
The sort list parameter determines the order in which the returned records are displayed as follows:
<slist>fid</slist>
The following slist parameter sorts all records by the field whose fid is 7.
XML Example:
<slist>7</slist>
You can specify a secondary sort by including another fid in the string (separating each fid with a period)
XML Example:
<slist>7.12</slist>
URL Example:
&slist=7.12
If you don’t specify the sort list, the default sort specified on the Report Layout page determines the order in which records are displayed.
Options Parameter
You use the options parameter to further control the return of records. You can use any or all of the options, separating the options with a period.
-
num-n--specifies a maximum of n records in the return -
onlynew--returns only those records marked with new or updated flags -
skp-n--skips the first n records returned by the query -
sortorder-A--specifies an ascending order -
sortorder-D--specifies a descending sorts.
XML Example:
<options>num-15.sortorder-A.skp-15</options>
URL Example:
&options=num-15.sortorder-A.skp-15
This tells Quickbase to skip the first 15 records the query would normally return and give you the next 15 records. It also specifies that all sorts should be in ascending order.
If you don’t supply the options parameter, the records are sorted in ascending order and the number of records returned will be the same number as the Quickbase is set to return.
About the Sort Order Option
You can apply a different sort order to different returned columns in the clist by supplying several period-delimited sortorder parameters:
<options>sortorder-A.sortorder-D.sortorder-D.sortorder-A</options>
This example sorts the first column in the clist in ascending order, the second and third columns in descending order, and the fourth column in ascending order.
The onlynew parameter produces a different result for each user that is logged into Quickbase: it won’t work with the Quickbase anonymous user.
Format Parameter
Use the fmt parameter to specify structured returns. If you don't specify this parameter, query returns are not structured; Quickbase returns the table name, any DBVars, and the record data with the fields you specified, all with the field names, like this:
<record>
<business_phone_number>(123) 103-1234 x12345</business_phone_number>
<email>b@c.com</email>
<file>car.jpg</file>
<text>justatest</text>
<update_id>1206568990479</update_id>
</record>
In contrast, if you specify structured returns using the fmt parameter, Quickbase returns field IDs (fids) instead of field names. Quickbase will also return URLs of file attachments. (For an alternative way to download files without this URL, see Downloading Files.) Using structured format, Quickbase also returns:
-
table dbid
-
metadata information
-
a list of all saved queries you can use
-
field properties and the default values for all the fields you asked for in your query.
When you use structured format, the field data is presented in a more compact form so you can immediately spot the fid and the actual field value.
<record>
<f id="5">112149.bhsv</f>
<f id="6">(650) 123-1234</f>
<f id="7">cu@later.com</f>
<f id="22"> Model_T.jpg
<url>https://target_domain.quickbase.com/up/bdb5rjd6g/g/rz/ey/va/Model_T.jpg
</url>
</f>
<update_id>1206568990479</update_id>
</record>
In the example, target_domain is the domain against which you are invoking this call, for example, quickbase.com. Read about this notation.