API_DoQuery

Don't forget to check out our JSON RESTful APIs, they can help you utilize and extend Quickbase with ease.

• Overview

• Request parameters

• Query String Comparison Operators

• Response values

• Sample XML Request

• URL alternative

• Sample Response (structured format)

• API_DoQuery in Pipelines

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 Quickbase should return structured data.

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

• the value to be compared against (the matching value)

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 Quickbase 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 Quickbase should return all records where both of the following conditions are true:

• fid 7 equals Ragnar Lodbrok

• fid 9 contains Erich

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

This example specifies that Quickbase 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:

• All records where the projects were completed on the originally forecasted completion date. Here, your query should find all records where value in the Actual Completion date field matches exactly the value in the Projected Completed date field.

• All records where the Notes field contains the name of one of your business contacts. Here, your query should find all records where the Notes field contains the value in the Contact field.

• All records where tasks are assigned to users who are not project leads. Here, your query should finds records where the value in the Assigned To field IS NOT EQUAL TO the value in the Project Lead field

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.

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

• Predecessor

• iCalendar

• vCard

• File Attachment

• Report Link

• Duration

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

top

Request parameters

top

Query string comparison operators

Note: Query string comparison operators must be in uppercase.

top

Response values

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>

API_DoQuery in Pipelines

Here is an example of an advanced query in a pipeline:

This query means that field id 6 is not equal to the previous step ‘a’ ’ id. This query syntax is a blend of
API_DoQuery in that you surround the query with curly braces and use the keywords and pipelines jinja to reference any steps.