API_DoQuery | ADVANCED

API_DoQuery is the API equivalent to doing a search in a Quickbase table for a subset of data. At its most fundamental level, DoQuery means passing Quickbase a query ID (qid) or a custom query. This returns table data that matches what you’re looking for.

From that perspective, API_DoQuery can be treated similarly to a report with a similar setup when it comes to picking your fields, filters, and sort criteria.

Why Use API_DoQuery vs. a Regular Quickbase Report?

Using DoQuery is needed to get data from Quickbase without actually being in the Quickbase interface. The most common example is custom scripting or setting up server-side scripting that requires you to extract and manipulate Quickbase data from a different interface.

If you are using things such as Quickbase dbpages or have an external system that needs to extract data from Quickbase, API_DoQuery is the method to do that.

The setup for your API call is the same as most API calls and is invoked on a table-by-table basis. You cannot query your entire Quickbase application at one time for data – you must query each table individually.

Your set up then looks similar to this:

https://yourrealm.quickbase.com/db/yourtableID?a=API_DoQuery

The next step is to define your query parameters. For a more in-depth review of how/what you can query, read through the Quickbase guide on the Components of a Query.

Two Ways to Set Up API_DoQuery in Quickbase

1. Using a pre-defined QID

This would mean using an existing table report you already have set up and using the QID of that report to run your DoQuery. Let's say you had a report of records created today that you’ve already saved (qid 18 for demo).

If that's the case, then you could invoke your DoQuery like so:

https://yourrealm.quickbase.com/db/yourtableID?a=API_DoQuery&qid=18

From this – your doQuery will return the same set of records that would show in report 18 if you ran it in Quickbase manually.

2. Using a Manual/Custom Query

This means using query= and using the guide from Components of a Query to define the parameters yourself in the actual API call.

Using the same example from above, if you wanted to write out a query that looked for all records created today, your DoQuery would look like so:

https://yourrealm.quickbase.com/db/yourtableID?a=API_DoQuery&query={'1'.EX.'today'}
//The character string '1' represents querying the built-in field 'Date Created'

In either format – you will get the same result

With the above, you have defined what records you want to return from a Quickbase table. Beyond that, there are several different elements that should/can be added where appropriate to tweak and optimize what is returned. Additional parameters include:

clist in Quickbase

This will define what fields/columns you wanted to be returned. Your clist is comprised of the field ID numbers of the particular fields you want to be returned. If you enter a clist such as “API_DoQuery&qid=1&clist=1.2.3.4.5” – you will return the ‘List All’ Report, and include the 5 built-in Quickbase fields Date Created, Date Modified, Record ID#, Record Owner, Last Modified By

• If you do not include a clist – it will default to whatever your default table columns are and return those

list in Quickbase

This will define what sort-order you want your records to appear in so that the records you return are already pre-sorted how you’d like them. Your slist is comprised of the field ID numbers of the particular fields you want to sort on.

For example: “API_DoQuery&qid=1&slist=1.4” will sort your records first by Date Created, then sort by Record Owner

• If you do not include a slist, it will default the sort based on whatever the default table sort field is.

Options in Quickbase

The options parameter allows you to manipulate the response in ways that you can’t do with standard Quickbase reporting. Using the options feature lets you combine any of the below inputs, separated by a period to manipulate the response

• num-n => Define a set number of records you want (num-100 will return only the first 100 records that meet your criteria)
• skp-n => Lets you skip over a set number of records (skp-100). This is helpful in larger data sets where you have to batch/chunk your data set and iterate over the same query multiple times and don’t want to keep starting from the beginning
• onlynew => Returns only records that are new or have been updated recently
• sort-A => Sort your records in ascending order
• sort-D => Sort your records in descending order

An example to see it might be “API_DoQuery&options=num-100.skip-250.sort-A”

As a final requirement – you’ll need an apptoken/ticket or usertoken to handle the authentication part of using the API.

To see an example of API_DoQuery, click here. The link will open up a DoQuery of our Quickbase knowledge Base App and show a list of 5 records.

Once you’ve mastered API_DoQuery, you can start expanding your Quickbase application by incorporating DB pages and custom interfaces to enhance your workflows and processes.

Check out Code Pages and how to use them for all the ways you can take this new knowledge to the next level.

• Author: Chayce Duncan
• Email: chayceduncan@quandarycg.com
• Date Submitted: 2/27/2019