API_DoQuery

Overview

Use API_DoQuery to get records from a table. You invoke this call on a table-level dbid. You can use the parameters (described below) to define which columns will be returned, how many records will be returned and how they will be sorted, and whether the Quick Base should return structured data.

When you build a query, you must build at least one query string. A query string is composed of:

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

Examples:

<query>{'5'.CT.'Ragnar Lodbrok'}</query>

This example specifies that Quick Base should return all records where the field with fid 5 contains the value Ragnar Lodbrok.

<query>{7.EX.’Ragnar Lodbrok’}AND{9.CT.’Erich’}</query>

This example specifies that Quick Base should return all records where both of the following conditions are true:

<query>({'6'.EX.'Six'}OR{'6'.EX.'Seven'})AND{'7'.GTE.'2'}</query>

This example specifies that Quick Base should return all records where fid 6 is equal to Six or Seven, and all records where fid 7 is greater than or equal to 2.

Specifying a value as the matching value

You can create queries that find records where the matching value is a certain value that you specify. To create a query that finds a specific matching value, you enter the value in single quotes in the query string.

Using the value in another field as the matching value

For most field types, you can write queries that compare one field with another field. When you write queries in this way, you set the matching value to a field rather than a specific value.  For instance, in a project management application, you might want to write queries that will find any of the following results:

To compare the value in one field to the value in another field, your query should be made up of the original fid, the comparison operator, and the field ID to be compared against, preceded by the string _fid_ in the query string. The following table shows an example of a matching value set to a specific value, and one set to the value in another field.

Matching value Examples and results

Specific value

XML Example:

<query>{7.EX.'Ragnar Lodbrok'}</query>

URL Example:

http://target_domain/db/target_dbid?a=API_DoQuery&query={7.EX.'Ragnar Lodbrok'}

This query returns:

All records where the value in fid 7 is equal to the string 'Ragnar Lodbrok'.

The value in another field

XML Example:

<query>{7.XEX.'_FID_10'}</query>

URL Example:

http://target_domain/db/target_dbid?a=API_DoQuery&query={7.XEX.'_FID_10'}

This query returns:

All records where the value in fid 7 is not equal to the value in fid 10.

Note that the user must have permission to access the field in the matching value to get expected results. In addition, the field type of the matching value field must match the field type of the field being evaluated.  

Field types that can be used as a query's matching value

The following table lists the field types you can use as the matching value in a query:

Field type Notes

Text

You can compare any Text field with any other Text field. Text fields are:

  • Text

  • Text - Multi-line

  • Text - Multiple Choice

  • Formula - Text

Note that you can mix and match the types above--that is, you can compare a simple Text field with a Formula-Text field in your query.

 

Example:

<query>{16.CT.’Customer’}</query>

This query returns:

All records where the value in fid 16 contains customer.

Multi-select Text

You can compare Multi-select Text fields with other Multi-select Text fields.

 

Example:

<query>{90.HAS.’Framingham;Walpole’}</query>

This query returns:

All records where the value in fid 90 contains Framingham and Walpole.

Numeric

You can compare any Numeric field with any other Numeric field. Numeric fields are:

  • Numeric

  • Numeric - Currency

  • Numeric - Percent

  • Numeric - Rating

  • Formula - Numeric

Note that you can mix and match the types above--that is, you can compare a simple Numeric field with a Numeric - Percent field in your query.

 

Example:

<query>{72.LT.150}</query>

This query returns:

All records where the value in fid 72 is less than 150.

Date

You can compare any Date field with any other Date field. Date fields are:

Date

Date/Time

Formula - Date/Time

Note that you can mix and match the types above--that is, you can compare a Date field with a Date/Time field.

 

Example (Date):

<query>{45.BF.’01-01-2016’}</query>

This query returns:

All records where the value in fid 45 is before 01-01-2016.

 

Example (Date/Time):

<query>{2.BF.’11-01-2016 8:00AM’}</query>

This query returns:

All records where the value in fid 2 is before 11-01-2016 at 8:00 AM.

Time of Day

You can compare Time of Day fields with other Time of Day fields or Formula - Time of Day fields.

 

Example:

<query>{2.BF.’11:00AM’}</query>

This query returns:

All records where the value in fid 2 is before 11:00 AM.

Checkbox

You can compare Checkbox fields with other Checkbox fields or Formula - Checkbox fields.

 

Example:

<query>{86.EX.’true’}</query>

This query returns:

All records where the value in fid 86 is true.

Phone Number

You can compare Phone Number fields with other Phone Number fields or Formula - Phone Number fields.

 

Example:

<query>{87.EX.’(617) 250-1234’}</query>

This query returns:

All records where the value in fid 87 is (617) 250-1234.

Email Address

You can compare Email Address fields with other Email Address fields or Formula - Email Address fields.

 

Example:

<query>{77.EX.’Jeanne_Smith@acme.com’}</query>

This query returns:

All records where the value in fid 77 is Jeanne_Smith@acme.com.

User

You can compare User fields with other User fields and Formula - User fields.

 

Example:

<query>{4.EX.’Jeanne_Smith@acme.com’}</query>

This query returns:

All records where the value in fid 4 is Jeanne_Smith@acme.com.

List-User

You can compare List-User fields with other List-User fields and Formula - List-User fields.

 

Example:

<query>{88.HAS.’John_Rogers@acme.com;Jeanne_Smith@acme.com’}</query>

This query returns:

All records where the value in fid 88 contains John_Rogers@acme.com and Jeanne_Smith@acme.com.

URL

You can compare URL fields with other URL fields and Formula - URL fields.

 

Example:

<query>{89.EX.’ https://www.quickbase.com’}</query>

This query returns:

All records where the value in fid 89 is https://www.quickbase.com.

Field types that can't be used as a query's matching value

You cannot use the following field types as the matching value in a query:

For more details about how to use these parameters, see Building and Using Queries.

top

Request parameters

Parameter Value Required?

query or

qid or

qname

Specifies the query. You can use any of these three options:

  • Use <query>{myfid.operator.value}</query> to build your own query string.

  • Use <qid>n</qid>if you want to use a saved query and want to use its query ID.

  • Use <qname>myQuery</qname> if you want to use a saved query and refer to it by its query name.

Note: API_DoQuery can be used to create the query used by API_GenResultsTable.

no (returns all records if absent)

clist

A period-delimited list of field IDs to be returned. Quick Base will return values for these field IDs in the order in which you enter them here.

To return all fields in a table, set this parameter to the value a.

Omit this parameter if you want the query to return the table's default columns.

no

 

slist

A period-delimited list of field IDs used to determine sorting as follows:

<slist>fid</slist>

The following slist parameter sorts all records by the field whose fid is 7.

<slist>7</slist>

You can specify a secondary sort by including another fid in the string (separating each fid with a period)

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

no

fmt

Set this parameter to structured to specify that the query should return structured data. Omit this parameter if you do not want the query to return structured data.

If you use this parameter:

  • Field IDs (fids) are returned rather than field names.

  • URLs are returned for file attachments.

  • The display format field property is included with Numeric fields. The values for this property are:

    Value Display Format
    0 12345678.00
    3 12,345,678.00
    6 12.345.678,00
    7 12345678,00
    8 1,23,45,678.00
    10 1.23.45.678,00
  • The display format, currency symbol, and symbol placement field properties are included with Numeric - Currency fields.

    The values for the symbol placement field property are:

    Value Display Format
    0 before the number ($-1.00)
    1 between any negative sign and the number (-$1.00, default)
    2 after the number (-1.00$)

no

returnpercentage

Specifies whether Numeric - Percent values in the returned data will be percentage format (10% is shown as 10) or decimal format (10% is shown as .1).

Set this parameter to 0 to return decimal format (the default) or 1 to return percentage format.

no

options

Specifies return options for the query. 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 order

Tip: To sort on multiple fields, simply add more A's or D's onto the sortorder option: sortorder-ADA will sort ascending by the first field in the query, descending by the second field, and ascending by the third field.

nosort --returns unsorted records, ignoring the sortorder option, the slist parameter, and the default sort for the table

no

includeRids

Specifies that the record IDs of each record should be returned.

To return record IDs for each record, set this parameter to 1.

no

useFids

Set this parameter to 1 to specify that the field ids of each field should be used for the field tags in the record aggregate, instead of field names, when fmt is not specified for the request. The field tags will match those in the structured response.

no

ticket

A valid authentication ticket.

The authentication ticket is returned via the API_Authenticate call.

yes, one of:

  • ticket
  • username/password
  • user token

 

usertoken

The user token is an alternative means of authentication, used for API access. User tokens cannot be used to access the Quick Base UI.

yes, one of:

  • ticket
  • username/password
  • user token

apptoken

A valid application token.

yes, if the application requires application tokens

udata

A string value that you want returned. It will not be handled by Quick Base but it will be returned in the response.

no

top

Query string comparison operators

Comparison Operator Description

CT

Contains either a specific value or the value in another field of the same type. (Do not use this operator with List - User and Multi-select Text 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 and Multi-select Text fields; instead, use XHAS.)

HAS

Used with List - User and Multi-select Text fields only. Specifies that the field contains a specific set of values.

For each user you are trying to find in a List - User field, 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 values in the list using a semi-colon.

For example:

<query>{'6'.HAS.'-8675309; -9873297'}</query>

XHAS

Used with List - User and Multi-select Text fields only. Specifies that the field does not contain a specific set of values. See Filtering records using fields with multiple values for more information.

For each user you are trying to find in a List - User field, 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 values in the list using semi-colons.

Note that a matching record must contain all users you specify. This query:

<query>{'6'.XHAS. '-8675309; -9873297'}</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.

EX

Is equal to either a specific value, or the value in another field of the same 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. 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.) Also used for queries on user fields.

XEX

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 either a specific value or the value in another field of the same type.

XSW

Does not start with either a specific value or the value in another field of the same type.

BF

Is before either a specific value or the value in another field of the same type.

OBF

Is on or before either a specific date or the value in another date field

AF

Is after either a specific date or the value in another date field

OAF

Is on or after either a specific date or the value in another date field

IR

Is during. Use this operator with date and date/time 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 during. Use this operator with date and date/time fields, to determine whether a particular date does not fall within a particular date range relative to the current date. Learn more about relative date ranges.

LT

Is less than either a specific value or the value in another field of the same type.

LTE

Is less than or equal to either a specific value or the value in another field of the same type.

GT

Is greater than either a specific value or the value in another field of the same type.

GTE

Is greater than or equal to either a specific value or the value in another field of the same type.

top

Response values

Element Name Value

action

The originating request, for example, API_DoQuery.

errcode

Identifies the error code, if any. (See the Error Codes appendix for a list of possible error codes.)

0 indicates that no error was encountered.

errtext

Text that explains the error code.

"No error" indicates that no error was encountered.

udata

Optional. Contains any udata value supplied in the request.

records

The records returned depend on whether you specified the fmt parameter

If you did NOT specify fmt, the query returns:

  • A dbinfo aggregate

  • a variables aggregate for the DBVars

  • a chdbids aggregate (empty, since this API is called on a table)

  • one record aggregate for each returned record

If you DID specify fmt, the query returns a <table> super-aggregate containing the following aggregates:

  • original (table metadata such as next available fid, next available rid, create/mod dates, and some defaults)

  • variables (all the DBVars)

  • queries (all of the saved queries, with qid and qnames)

  • fields (properties for every field to be returned in the query)

  • records (contains 0-N <record> sub-aggregates containing the actual record data, using fids, not field names)

You can search for Dates formatted according to the app's properties, but Dates are returned as milliseconds since January 1st, 1970 00:00:00 UTC, which is the same internal representation used by JavaScript.

Numeric values are returned with no currency symbol or separator, and a period as decimal, like 12345678.00.

Text - Multi-line fields are returned with each line of the text field separated by a <br /> tag.

top

Sample XML Request

POST https://target_domain/db/target_dbid HTTP/1.0
Content-Type: application/xml
Content-Length:
QUICKBASE-ACTION: API_DoQuery

<qdbapi>
   <ticket>auth_ticket</ticket>
   <apptoken>app_token</apptoken>
   <udata>mydata</udata>
   <query>{'5'.CT.'Ragnar Lodbrok'}AND{'5'.CT.'Acquisitions'}</query>
   <includeRids>1</includeRids>
   <clist>5.6.7.22.3</clist>
   <slist>3</slist>
   <options>num-4.sortorder-A.skp-10.onlynew</options>
   <fmt>structured</fmt>
</qdbapi>

top

URL alternative

https://target_domain/db/target_dbid?a=API_DoQuery&includeRids=1
&ticket=auth_ticket&apptoken=app_token&udata=mydata
&query={'5'.CT.'Ragnar Lodbrok'}AND{'5'.CT.'Acquisitions'}&clist=5.6.7.22.3
&slist=3&options=num-4.nosort.skp-10.onlynew&fmt=structured

where target_domain is the domain against which you are invoking this call, for example, quickbase.com. Read about this notation.

top

Sample response (structured format)

<?xml version="1.0" ?>
<qdbapi>
<action>API_DoQuery</action>
   <errcode>0</errcode>
   <errtext>No error</errtext>
   <udata>mydata</udata>
   <qid>-1</qid>
   <qname />
<table>
   <name>API created Sample</name>
   <desc>This is a sample table.</desc>
   <original>
      <table_id>bh9ckdaue</table_id>
      <app_id>bh9ckc9ft</app_id>
      <cre_date>1204586581894</cre_date>
      <mod_date>1206583187767</mod_date>
      <next_record_id>34</next_record_id>
      <next_field_id>24</next_field_id>
      <next_query_id>5</next_query_id>
      <def_sort_fid>6</def_sort_fid>
      <def_sort_order>1</def_sort_order>
   </original>
<variables>
   <var name="Blue">14</var>
   <var name="Jack">14</var>
   <var name="Magenta">12</var>
   <var name="usercode">14</var>
</variables>
<queries>
   <query id="1">
      <qyname>List All</qyname>
      <qytype>table</qytype>
      <qycalst>0.0</qycalst>
   </query>
   <query id="2">
      <qyname>List Changes</qyname>
      <qytype>table</qytype>
      <qydesc>Sorted by Date Modified</qydesc>
      <qyslst>2</qyslst>
   <qyopts>so-D.onlynew.</qyopts>
   <qycalst>0.0</qycalst>
   </query>
</queries>
<fields>
   <field id="5" field_type="userid" base_type="text" role="modifier">
      <label>Last Modified By</label>
      <nowrap>1</nowrap>
      <bold>0</bold>
      <required>0</required>
      <appears_by_default>0</appears_by_default>
      <find_enabled>1</find_enabled>
      <allow_new_choices>1</allow_new_choices>
      <sort_as_given>0</sort_as_given>
      <carrychoices>1</carrychoices>
      <foreignkey>0</foreignkey>
      <unique>0</unique>
      <doesdatacopy>0</doesdatacopy>
      <fieldhelp />
      <display_user>fullnamelf</display_user>
      <default_kind>none</default_kind>
   </field>
   <field id="6" field_type="phone" base_type="text">
      <label>Business Phone Number</label>
      <nowrap>0</nowrap>
      <bold>0</bold>
      <required>1</required>
      <appears_by_default>1</appears_by_default>
      <find_enabled>1</find_enabled>
      <allow_new_choices>0</allow_new_choices>
      <sort_as_given>0</sort_as_given>
      <carrychoices>0</carrychoices>
      <foreignkey>0</foreignkey>
      <unique>1</unique>
      <doesdatacopy>0</doesdatacopy>
      <fieldhelp>This is the phone number</fieldhelp>
      <num_lines>1</num_lines>
      <append_only>0</append_only>
      <allowHTML>0</allowHTML>
      <has_extension>1</has_extension>
   </field>
   <field id="7" field_type="email" base_type="text">
      <label>Email</label>
      <nowrap>0</nowrap>
      <bold>0</bold>
      <required>0</required>
      <appears_by_default>1</appears_by_default>
      <find_enabled>1</find_enabled>
      <allow_new_choices>0</allow_new_choices>
      <sort_as_given>0</sort_as_given>
      <carrychoices>1</carrychoices>
      <foreignkey>0</foreignkey>
      <unique>0</unique>
      <doesdatacopy>1</doesdatacopy>
      <fieldhelp />
   </field>
   <field id="22" field_type="file" base_type="text">
      <label>File</label>
      <nowrap>0</nowrap>
      <bold>0</bold>
      <required>0</required>
      <appears_by_default>1</appears_by_default>
      <find_enabled>1</find_enabled>
      <allow_new_choices>0</allow_new_choices>
      <sort_as_given>0</sort_as_given>
      <carrychoices>1</carrychoices>
      <foreignkey>0</foreignkey>
      <unique>0</unique>
      <doesdatacopy>0</doesdatacopy>
      <fieldhelp />
      <max_versions>3</max_versions>
      <see_versions>1</see_versions>
      <use_new_window>1</use_new_window>
   </field>
   <field id="3" field_type="recordid" base_type="int32" role="recordid" mode="virtual">
      <label>Record ID</label>
      <nowrap>1</nowrap>
      <bold>1</bold>
      <required>0</required>
      <appears_by_default>0</appears_by_default>
      <find_enabled>1</find_enabled>
      <allow_new_choices>0</allow_new_choices>
      <sort_as_given>0</sort_as_given>
      <default_value>10</default_value>
      <carrychoices>1</carrychoices>
      <foreignkey>0</foreignkey>
      <unique>1</unique>
      <doesdatacopy>0</doesdatacopy>
      <fieldhelp />
      <comma_start>0</comma_start>
      <does_average>0</does_average>
      <does_total>0</does_total>
      <blank_is_zero>0</blank_is_zero>
   </field>
</fields>
<lastluserid>0</lastluserid>
<lusers>
   <luser id="112149.bhsv">AppBoss</luser>
</lusers>
<records>
   <record>
      <f id="5">112149.bhsv</f>
      <f id="6">(123) 333-4321 x34566</f>
      <f id="7">bo@co.com</f>
      <f id="22" />
      <f id="3">12</f>
      <update_id>1206421031556</update_id>
   </record>
   .
   .
   .
</records>
</table>
</qdbapi>

 

Go back    |     |  

© 1999-2018 QuickBase, Inc. All rights reserved. Legal Notices.