The Query column allows you to gain powerful insights into your data. You can create complex relations and apply multiple filters, sorting, and limits—all in one place!
The Query column feature can be applied to an entire table, a multiple relation, or even another query column. It works in all tables, including Glide Big Tables.
In the Data Editor, open the table you want to edit.
Add a new column by clicking the plus (+) symbol to the right of the table.
New columns added with the plus button will always appear on the right. If you'd like to add a column somewhere else in your data, you can select the dropdown menu on an existing column and choose Add column right. You can also click and drag columns to rearrange them.
Name the column.
For Type, search for Query or navigate to Computed -> Query.
Select a Source. This can be any table, a multiple relation in the same table, or even another Query column in the same table.
You can add one or more conditions to filter by.
Control the ordering of the results with sort by, either ascending or descending by the values of any column in the source.
Limit how many matching rows will be returned by the query.
Because Query columns and relations can have overlapping functionality, it can sometimes be hard to know which type of computed column to use. If you are just trying to link records together with one-to-one or one-to-many relationships:
In Glide Tables and any other non-SQL data source, Relations are faster.
In Big Tables and any SQL data source, there is no difference.
When configuring your Query column, Glide will tell you in the configuration screen if a multiple relation will be faster.
The This row filter allows you to match your query results to specific rows in your table. With it, you can relate the rows in a source table with the relevant rows in another table.
For example, the table below uses a Query column to find a list of records for each Product from the Orders table. Rows are filtered for where the Quantity is more than 100, and the Type is “Sell”. The This row query automatically matches the results with the relevant rows in the table, organized by SKU.
The Query column could then be used as the source of a Collection on a Product Details screen, to show all sales made of that product that fit the filter settings:
The image below is a table containing data for 1,000 sales people, including data for their salaries and sales figures:
Now you'd like to know what percentage of total sales is accounted for by your top 100 sales people. You can determine this by using the Query column:
Start by calculating the total sales across all sales people. For this, use a Rollup column to calculate the sum of all sales:
Next, use a Query column to find the top 100 sales people. This column would be configured as follows:
Sort by: Sales descending
Now, calculate the total sales of the top 100 sales people. Again, use a Rollup column taking the sum of all sales found in the Query column.
Finally, use a Math column to calculate what percent of sales can be attributed to the Top 100 sales people. The formula for this is 100*Top Sales/Total Sales where Top Sales is the total sales of the top 100 sales people, and Total Sales is the total of all sales.
You can see that the Top 100 Sales people have contributed 19.6% of the Total Sales.
For this example, imagine that you reward your users with points, and you've defined a points table with a number of levels and a range of points for each level:
In your Users Table, you track the points balance for each User. Now, you need to determine their “Reward Level” based on their current points balance.
If you look at your first user, Alma, you can see that she has a current points balance of 157, which puts her at the Silver level (100-199). To include each user’s Reward Level on their User Profile page, you need to calculate their level based on points. The Query column automates the whole process.
First, create a Query column in the Users table and configure as follows:
Source: Reward Levels
Filter by: Min Points is less than or equal to
This row -> Points AND Max Points is greater than or equal to This row -> Points.
The Query in this case will only return a single match, but it will be different for each row in our Users table.
Importantly, although the Query column only returns a single match, it is the same as a Multiple Relation, so it cannot be used in any component that expects a text value. Here, you want to use it in a Title component, so you need to retrieve the text value. To do this, use a Single Value column:
This gives you a text value that you can use in a Title component on the User Profile screen: