Tulip Table Queries and Aggregations

Purpose

Learn how to query your Tulip Tables and build Aggregations that are accessible within Tulip Apps

In this article, you will learn...

• The capabilities of Table Queries
• How to build Queries and Aggregations
• How to use Queries and Aggregations in Apps

Overview of Table Queries and Table Aggregations

With Table Queries, and Table Aggregations, you can query and dynamically filter Tulip Tables within Apps.

For this article we will be referencing the following Table of Orders, that has 10 rows:

In the top right, you can see the buttons Queries and Aggregations. Click on Queries to add a new query.

The following example uses a filter to return only records that have not been shipped:

Aggregations are added on top of a query, and are built at the table level like queries. Click on Aggregations to create a new aggregation.

The following example will Sum the table field, Product Quantity:

Query Options

Filter

Filter records by comparing a specific column, or multiple columns, to either a Static Value or an App Input.

Examples of comparison options include:

• equals / does not equal
• is null / is not null
• contains / does not contain
• starts with / does not start with
• ends with / does not end with
• is in
• is greater than or equal to / is less than or equal to
• is after or at / is before or at

Static Value will be statically set when building the query, but App Input will be configured in the app itself. The App Input can be configured to be a:

• Variable
• Static Value
• App Info
• Tulip Table Record

You can now use the Created At and Updated At fields in table queries.

Sort

Customize the display of the records, by configuring the Sort option. Examples include:

• A to Z / Z to A
• 0 to 9 / 9 to 0

Limit

Place a limit on the amount of Records returned by query by configuring a limit.

After configuring the query by setting the Filter, Sort and Limit options, the query can be tested right on the Table by clicking Test. This will simulate what the query would return in an app.

Aggregation Options

Aggregations are summary values that are ran on top of queries, and can calculate one of the following values for a particular column:

• Average
• Count
• Max
• Min
• Mode
• Sum
• Unique Values

Using Queries and Aggregations in Apps

Adding Queries to Apps

To add a query to an app, first add the table to the App by clicking on the Records Tab, and +Add Table. Next, click Query.

In the modal that appears, give the query a Query Name, and if applicable, configure the App Input associated with the filter. In this example, see the query Products Not Shipped:

Now that the query is configured in the app, embed an Interactive Table, and select the Datasource to be the query.

The embedded table will display the results of the query. In this example, there are 3 records returned that fulfill the filter, 'Shipped' equals 'No':

Adding Aggregations to Apps

Similar to how a query is added to an App, add an aggregation by clicking +Add Aggregation, and assign an Aggregation Name.

In this example, the aggregation Total Qty Products Ordered is being ran on the query that returns only Records that have not been shipped. For this reason, the Aggregations name is Quantity of Products to be Shipped:

To embed the aggregation in an app, drop a Variable into the app and choose the Datasource - Tulip Table Aggregation. Next, select the Aggregation Name that was configured when adding the aggregation to the app.

In this example, the Aggregation Sum of Product Quantity was ran on top of the Query returning records that need to be shipped. This returns a total of 17:

Using Aggregations in App Logic

With aggregations, you have the ability to build app logic around an aggregation's value. See an example below:

Please note that Single Number Tulip Analytics are not currently accessible from within an app, and can't be directly leveraged with app logic. By running and aggregation off a query, you will be able target this value, and thus build app logic around it.