Working with tables

A quickstart on using Retool tables to interact with your data.

What we're going to do

Most internal tools involve viewing and interacting with your data. Let's take a look at how we can do that in Retool with tables and a few surrounding components. Here's a quick overview of what we're going to do:

  1. Get our user data displayed in a table
  2. Add some table filters
  3. Add a search field
  4. Edit table values

After we build a simple table setup, we'll also explore some more advanced table properties and features.

Let's go!

Getting data into a table

Once you create a new app in Retool, you'll start with an empty canvas. To add new components, you drag them over from the sidebar on the right (click the right sidebar icon in the menu bar to toggle it). We'll start by dragging a table component onto your app's canvas. If you don't see the table component in the right sidebar, you can search for it in the "Search components" bar.

If you don't have any queries in your app, the table you just added will be populated with some sample data. You can click on the table component to see its properties in the right sidebar. In the "data" property, you'll see something like this:

[{
  "id": 1,
  "name": "Hanson Deck",
  "email": "[email protected]",
  "sales": 37
}, {
  "id": 2,
  "name": "Max Conversation",
  "email": "[email protected]",
  "sales": 424
}, {
  "id": 3,
  "name": "Jason Response",
  "email": "[email protected]",
  "sales": 55
}, {
  "id": 4,
  "name": "Sue Shei",
  "email": "[email protected]",
  "sales": 550
}, {
  "id": 5,
  "name": "Eric Widget",
  "email": "[email protected]",
  "sales": 243
}]

These people look nice, but let's replace them with your data! To populate this table with your information, we'll need to build a query. In Retool, you write queries in the bottom bar: if it's not already displayed, click on the bottom bar icon in the menu bar to toggle it.

You should see a query already created (query1), but if you don't, click the "New Query" button on the right to create a new one.

If you've already connected your data source(s) to Retool, select it from the Resource dropdown in the query bar. If you haven't, check out our docs, or use one of the sample resources we have as default. We'll use a database we've built called ecommerce. This is what things should look like:

In the query editor, we'll write a simple query to pull user data from our users table.

select *
from users

You can click preview (also Command + Enter) to run your query and see the results, and then save (Command + S) to save your query. Let's also change the query name to something more useful by clicking on it and entering something new: users. Here's what our finished query should look like:

Take a look at the table though, and note nothing has changed – still full of sample data. That's because we need to connect our queried data to our table. To do that, click on the table component to bring up the right sidebar, and replace the sample data (the data property) with a reference to our query.

Aside from queries, everything code in Retool gets written in double brackets. So to reference our query data in this table, we replaced the sample data with {{ users.data }}. Recall that we named our query users, and we're referencing its results with the .data property.

Adding table filters

Retool tables come pre-built with filtering functionality, so there's no need to write any custom SQL or Javascript. Click on the little filter icon on the bottom left of the table, and the filter options should appear.

You can choose any column in your table's data and filter through operators like contains, equals and others. You can also add multiple filters using and or or logic. For more details about using tables and filters, check out the table reference here.

Adding a search bar

📘

Video walkthrough

We made a quick video walkthrough for adding search to your table here!

Adding search is a bit more complex – we'll need to add in a text input component, and update the SQL in our users query. Let's start with dragging a text input component onto the canvas just above our table.

Let's also name the search bar so we can use it in our query. Click on the text input component and rename it search_bar.

Typing into that search bar won't really do anything now – we need to connect our query to it. To do that, we'll add in a new line to the users query that filters the user's name for whatever you've typed into the search bar. We'll reference the search bar's contents with the .value property in our query. Note that when we're referencing Retool components in our query, we use double brackets notation, just like above.

/* our original query */
select *
from users
/* our new filter for search */
where {{ !search_bar.value }}
or first_name || last_name ilike {{ '%' + search_bar.value + '%' }}

There are two components to this where clause:

  1. where {{ !search_bar.value }} tells our query to ignore the search_bar if there's nothing typed into it
  2. or first_name || last_name ilike {{ '%' + search_bar.value + '%' }} concatenates first_name and last_name, and filters using ilike for what you've typed in the search bar

The magic operators (%) tell your query language to search the whole name, and be less than perfectly exact. Preview and save your query, and you should have working search!

Editing table values (making data editable)

After displaying data, we might want to also change values directly. Let's say that we want to build a quick tool to pull our users into a table and then want to change their names. To enable this feature, we'll want to create a Bulk Update Query. A Bulk Update Query lets you save the changes you make while editing values in the table. The query can be anything you want. In this example, we'll use a "Bulk Update via Primary Key" query.

📘

What's the recordUpdates property on a Table?

The .recordUpdates property on a table is an array of objects that tell us which rows have changed. For example, if we've changed the first_name property of one of the rows, the value will return all of the fields in that row which might look something like this:

[{ customer_id: 24, first_name: 'New Name', last_name: 'Same Name'}]

Don't forget to make this query also retrigger the query that pulls data into the table! This will make sure that the data in the table will always be up to date. You can do this in the "After this query runs" section in the query editor.

After we've configured this, let's choose which columns our users should be able to edit. In this case, we'll make the first_name and last_name editable:

Great - now we can start using the tool! Here's a quick GIF of it in action.

Server side table pagination

In many cases you only want to display one page of data at a time, but still require the ability to access the entire range of data. Retool tables support server side pagination: you can enable and configure it in the Advanced Settings section of your table component.

Enable the 'Server side paginated?' checkbox and create two queries.

The query.data is used to fetch the data, while the queryCount.data.count is a different query that retrieves the number of total results.

In this case, here is what the query for fetching the data might look like:

select * from customer where first_name ilike {{ '%' + textinput1.value + '%' }}
limit {{ table1.pageSize }}
offset {{ table1.paginationOffset }}

The important part of this query is the last two lines - the limit and offset statements use values from the table to fetch the correct range of rows.

The query to count the total results would look like this:

select count(*) from customer where first_name ilike {{ '%' + textinput1.value + '%' }}

The key here is that the filter condition in both queries are identical so that the counted number of rows is accurate. Your table should now automatically fetch the correct page of data whenever a user changes the selected page.

Server side pagination with dynamic column sorting

In order to hook the table's column sorting up with a server side paginated, setup you will need to utilize the sortedColumn and sortedDesc properties of the Table. Here is an example of how you might do that:

select * from customer
order by

-- For each column you want to enable sorting by you must add the following two rows - including the ending comma.
case when {{ table1.sortedColumn == 'email' }} and {{ table1.sortedDesc }} then email end desc,
case when {{ table1.sortedColumn == 'email' }} and {{ !table1.sortedDesc }} then email end asc,

case when {{ table1.sortedColumn == 'first_name' }} and {{ table1.sortedDesc }} then first_name end desc,
case when {{ table1.sortedColumn == 'first_name' }} and {{ !table1.sortedDesc }} then first_name end asc

limit {{ table1.pageSize}}
offset {{ table1.paginationOffset }}

While verbose, this is necessary because SQL queries are converted into parameterized queries safe from SQL injection.

Limit - Offset based pagination

This is most suitable to be used with SQL databases and APIs that support providing a page number / limit to responses.

1. Create a query and display the data in the Table, and then enable server side pagination.

2. Use the .pageSize and .paginationOffset in the query that pulls the data into your table

See below for an example.

select * from customer
limit {{ table1.pageSize}}
offset {{ table1.paginationOffset}}

3. Provide the number of results to the query separately

You can also set the table up to allow for jumping to arbitrary pages. To do this, you need to provide the Table the total number of results in the entire dataset being paginated through. Usually, you can do this in a separate query like below:

select count(*) from customer

Cursor based pagination

📘

What is cursor based pagination?

For a brief introduction on cursor based pagination, you can read this excellent blog post from Slack.

Here's a quick guide on how you can use cursor based pagination in Retool. In this example, we'll be connecting a Table to Stripe's REST API that implements pagination with cursors

1. Connect a Stripe REST API resource

First, let's create a Stripe API REST API resource so that we can make authenticated API calls to Stripe. Use the screenshot below as a template.

2. Show Stripe customers in a Table

After creating the resource, we can make an API call to Stripe to retrieve a list of our customers and render them in a Table.

🚧

Wait, what's going on?

If you're not sure how this works check out our Quickstart and then come back to this.

3. Setting up the Table's pagination settings

Stripe's API only returns a maximum of 100 records at a time, so if you'd like to be able to see the full list of data you'll need to use Stripe's Pagination API.

Scroll down in the Table's property inspector and in the Pagination section, enable Server-side pagination and choose Cursor based in the Pagination type dropdown.

Then in the Next page cursor field, we need to specify to the Table what the next page's cursor should be. In this case, this is just the id of the last customer in the Table.

4. Connecting the query to the table's pagination settings

Next, we'll need to modify the API query to make use of the Table's pagination properties. You can use the screenshot below as a template.

📘

What's up with {{ table1.afterCursor ? 'starting_after' : null }}?

Stripe's API doesn't allow you to specify a null argument to the starting_after parameter. To get around this, we use the expression, {{ table1.afterCursor ? 'starting_after' : null }}, to remove the starting_after parameter if the table's afterCursor property is null.

After that, save your query and your table should now paginate correctly.

How this works

When you click the next page button in the Table, the Table updates it's afterCursor property to be the value we specified in the Next page cursor property, which causes the API query to re-run. The new set of data is then appended to the Table's data. Since the Table stores the previously loaded results from the API, it's possible to go back to a previous page in the Table without re-running the API call.

GraphQL cursor based pagination

For more information on this, you can see https://facebook.github.io/relay/graphql/connections.htm

1. Create a GraphQL query with parameters that support pagination.

See below for an example how to do this with the Github GraphQL API. We've filled in the arguments with placeholder values so that the query can run without errors.

2. Display the data in a Table

3. Turn on server side pagination in the Table and choose "GraphQL Cursor based" in the pagination type

After choosing it, use a {{ }} expression to describe the cursor that would be used to pull the previous page, and another expression to describe the cursor that would be used to show the next page of data.

4. Replace the placeholder variables in the query with table properties

Note how we alternate between using first and last depending on whether or not the beforeCursor or afterCursor property is defined on the table.

5. Testing it out and understanding how it works

There's two scenarios:

A) The user tries to move to the next page on the table

When this happens the table sets the afterCursor property to be whatever the current "Next Page Cursor" value is currently. At the same time, it sets the 'beforeCursor" property to null. Doing this solves for the ambiguity when paginating forwards vs paginating backwards.

B) The user tries to move to the previous page on the table

Similarly to the previous example, the table sets the beforeCursor property to the current Previous Page Cursor and then sets the afterCursor property to null.

Coloring table columns

After selecting a table component, you can access the Background color setting after expanding out the column settings in the property inspector. You can use Javascript inside of a {{ }} tag here, and access the current value of the cell by using self. Common color names or hex codes as strings can be used to define the color to use.

Sorting table columns

Click on column to sort it. This works well enough for up to 10k rows, but after that, you want to be sorting in your query.

Action buttons in tables

Want to add a button to each row? Check the "Add action button" checkbox, and you'll get a button on every row. You can customize its text, as well as what the button does.

When you trigger a query from the action button, it'll have the magic variable i defined. This represents the index of the selected row. To access the row data, use {{ table1.data[i] }} if it's a SQL query or {{ table1.data[i].columnName }}` otherwise. In the future, we'll provide the actual row data.

Adding an action button which resets the user's Stripe balance.

Copying table data to the clipboard

You can use a button component to copy data from your table component with just a simple line of Javascript. Drag a button component next to your table, and click on it to bring up the properties sidebar on your right. Under the "On click" header, choose "Copy to clipboard."

The JS that you'll put in the "Value to copy" field depends on what kind of data is in your table. If you're using a SQL database, you can use {{ Papa.unparse(formatDataAsArray(table1.data), { delimiter: '\t' }) }}. If you're using something something JSON based, you can use {{ Papa.unparse(table1.data, { delimiter: '\t' }) }}. Let's rename the button "Copy to clipboard" and you should be good to go!

Custom / calculated columns

You can add a calculated column to your table that's computed based on other values in your Retool app. You can calculate these values based on other data in the table using {{ currentRow }} or any other components / data sources. For example, if you have a column monthlyIncome, to calculate a yearlyIncome column, you would use {{ currentRow.monthlyIncome * 12 }}

Custom columns have the same attributes that regular table columns have including:

  1. Column types
  2. Custom titles
  3. Background color
  4. HTML, overflow, sorting, and dynamic visibility

📘

Accessing custom column attributes

While you can rename a custom column, each column still has a key that can't be changed. For example, if you have named your second custom column "xyz", if you want to access the mapper dynamically, you would still need to use {{ table1.columnMappers['Calculated Column 2']}}.

🚧

Accessing custom column data in other components

Custom column data is not accessible in the {{ table1.data }} or other properties of the table object at this time. If you want to access these calculated values in other components (e.g. using {{ table1.selectedRow.data.custom_column }}, we recommend that you skip the custom column and instead use a Query Transformer to calculate the additional values, which will then propagate to your table.

Adding rows to tables

If you want an easy way to add a new row to your table, you can configure a little "plus" button on your table component and connect it to an insert query. To do this you can follow this step-by-step guide. But here is an overview:

Head over to the inspector in your right sidebar by clicking on your table component:

Scroll down and you'll find the "Table Edit Queries" section. Here you'll pick an insert query to run when a new row is added.

Your insert query should reference the .newRow property of your table component.

There's one more thing you need to do: configure all of your table columns where you want to allow user input to be editable. You can do that by clicking on each column in the inspector and toggling "Make Editable" to be on:

After that, you should be able to add a row to your table via the "+" icon:

❗️

Only the columns of the table where you select "Make Editable" will allow input in the new row UI, and only columns with a value entered will have keys defined inside of {{tableName.newRow}}.

Using dynamic values while adding rows to tables

For adding new rows with some columns that end users are not able to edit, it can be useful to pass in dynamically defined values. For example, defining a date created using {{ moment() }} or automatically filling in the email address of the user who created the row using {{current_user.email}}.

Any {{ }} values can be used while creating new rows in a table

Dynamic Column Settings

Dynamic column settings allow you to programmatically interact with your column types. The valid values for "type" are:

  • default
  • modal
  • string
  • date
  • datetime
  • datetime_tz
  • link
  • object
  • integer
  • percent
  • float
  • usd_dollars
  • usd_cents
  • boolean

Updated 3 days ago


Working with tables


A quickstart on using Retool tables to interact with your data.

Suggested Edits are limited on API Reference Pages

You can only suggest edits to Markdown body content, but not to the API spec.