Advanced Table Features

Table Search

The Table component comes with a Search field enabled by default. Your search query is evaluated across all columns to see if we can find a value that contains the searched text.

You can change the following configurations of the search bar:

• Enable / Disable by default
• Set a default search text

info

NOTE: The Table search function will only search through the current page's results when using server side pagination.

Conditonally format cells

In the Properties panel, navigate to the column you'd like to format and click the edit icon.

Scroll down to the Cell Background property and click the <> Code icon.

In Cell Background reference the Computed Value in a JavaScript conditional operator to format the background color based on the value of the cell. For example, {{currentRow.price > 200 ? "green" : "yellow"}}

Filter Columns

On any Table, you can click on the filter icon to open up the filter dropdown. There are two options, "Filter by condition" or "Filter by value".

info

Table filters are not available if using server side pagination.

Filter by condition:

Filter by value:

Default Table Sort

On any Table, you can configure the default sorting using the Default Table Sort property.

Example for setting it via code:

{
"column": "<COLUMN_NAME>",
"asc": true
}

Storing Persistent Filters using S3

info

In order to follow this guide, you'll need to have set up an integration with S3.

In order to store persistent filters, leverage the onFiltersChanged trigger.

Start off by populating a table with the demo Postgres data

1. Drag a Table component on to the canvas.
2. Add a new API for the [Demo] Orders Postgres database in "Table Data" and rename it getOrders.
3. Update the default filters to {{filterSet.selectedOptionValue}}.
4. Use the following code snippet to query all orders from the table:

SELECT *
FROM orders

Create the getFilters API

1. Create a new API called getFilters that reads a json file from your S3 bucket.

   • Action: Read File
   • Bucket Name: Your bucket name
   • File Name: filters.json

2. Create a python step with the following code to serialize the json from S3:

   import json
   return json.loads(Step1.output)

3. Create a new file in the S3 bucket called filters.json with the following body:

   { "options": [] }

Create the saveFilter API

1. Create a new API called saveFilter that uses python for the first step and the following code:

   import json
   
   filterList = getFilters.response #pull in the existing filters from the getFilters API
   
   filterListOptions = json.dumps(filterList.options) #cast to a string for concatenation
   filterListOptions = filterListOptions[1:-1] #strip the leading and trailing []
   
   nextFilter = str({"id":str(len(filterList.options)+1),"name":filterName.value,"filters":Table1.filters}).replace("'", '"') #build the filter object
   
   if len(filterListOptions):
       filterListOptions = "[" + filterListOptions +","+ nextFilter + "]" #concatenate new filters in the list
   else:
       filterListOptions = "["+ nextFilter + "]" #add the first filter
   
   filterListOptions = json.loads(filterListOptions) #cast to a json object
   filterList = {"options":filterListOptions}
   
   return filterList

2. Create another step that uploads the file to S3.

   • Action: Upload File
   • Bucket Name: Your bucket name
   • File Name: filters.json
   • File Content: {{Step1.output}}

Add filter dropdown components

1. Add a dropdown component and rename it filterSet.
2. Update the Label to "Filters".
3. Update the Options to {{getFilters.response.options}}.
4. Add an input component and rename it filterName.
5. Update the Label to "Filter name to save".
6. Add a button component.
7. Update the Label to "Save Filter".
8. Update the Disabled field of the button by clicking "Code" and pasting: {{Object.keys(Table1.filters).length === 0}}.
   • This will disable the Save Filter button when no filters are applied to the table.
9. Add Run API as an Action Type to the onClick trigger and select the saveFilter API to run.
10. Add a second Action Type and select the getFilters API to have it run after the saveFilters API runs.

Add a filter to the list

1. Add filters to the table.
2. Add your first filter name into the input text box.
3. Click the "Save Filters" button to save the filter.
4. Click the dropdown filterSet and update the Value Field to "filters" and the Label Field to "name".

Now when you click the dropdown, you'll see the list of filters you have saved!

Download Table data as CSV

End users can download the content of a table as a CSV file. CSV downloads are enabled by default and can be toggled on or off in the Table's Properties panel.

To download the CSV, users can click the icon in the bottom right of the Table. All filters, data formatting, and sorting on the table will apply to the CSV download. The CSV file will be named <Application Name>-<Table Header Name>.csv

Using Server Side Pagination

You can leverage Server Side pagination in order to control the returned data from your API calls or database queries. Below we walk through how to do this in a SQL query using the provided demo data in the Snowflake [Demo] Shipping database.

info

NOTE: The Table search function will only search through the current page's results when using server side pagination.

1. Drag a Table component onto the canvas.
   
2. Update the component name to shippingHistory and the Table Header to "Shipping History".
3. Update the Pagination Type to "Server Side".
4. Clear the sample data from Table Data and create a new API that leverages a Python step.
5. Update the API name to be getShippingData and the Python step to be createLimitOffset.
6. Use the following code snippet to calculate the limit and offset we'll use in the following SQL query:

return {"limit":shippingHistory.pageSize, "offset":shippingHistory.pageSize * (shippingHistory.pageNo - 1)}

7. Create a new Snowflake step from [Demo] Shipping.
8. Use the following code snippet to query the data from Snowflake:

SELECT * FROM SHIPPING_HISTORY LIMIT {{createLimitOffset.output.limit}} OFFSET {{createLimitOffset.output.offset}};

9. Click "Run" to populate data into the Table.
10. Update onPageChange to {{getShippingData.run()}}.

Now when you click through the table, it will load the number of rows based on the size of the table and populate the next n rows as you switch pages.

Bulk edits with multi row selection

To make updates to multiple table rows at once, toggle Enable multi row selection in the table's properties panel.

Next, create an API to handle the bulk update. The API consists of a code step (JavaScript) to create a string out of the selected values, and a SQL step (Postgres) to update a table in the database based on the selected rows. In this example, our table is named Table1, and the column name on which we're basing the update is customer_id. Adjust this as needed for your use case.

Step 1 - JavaScript

return (
  "(" + Table1.selectedRows.map((row) => `'${row.customer_id}'`).join(",") + ")"
);

Step 2 - SQL

info

Referencing SQL generated from JavaScript requires disabling Parameterized SQL.

UPDATE <TABLE_NAME>
SET <COLUMN_NAME> = 'example_value'
WHERE customer_id IN {{Step1.output}}

In Superblocks, here are the steps put together in an API called bulkUpdateStatus that updates the status column of a table named credit_cards_se.

Next, configure the bulk update to run and refresh the table based on clicking a button. To do so, attach sequential triggers to the button's onClick property to run the bulkUpdateStatus API followed by the main table query, getApplicants in our example.

The end result allows us to select multiple rows, click a button to update the selected rows in the database, and refresh the table to reflect the changes.

Select all/no Table rows by default

In order to select all rows by default in a Table component, use the following code snippet in the Default Selected Row field (the code snippet below assumes that your Table data is being loaded from a Backend API called API1):

{{ [...Array(API1.response.length).keys()] }}

In order to select no rows by default in a Table component, leave the Default Selected Row field blank.

Programmatically manipulate table edit state (coming soon)

The table includes advanced editing capabilities, similar to a spreadsheet, as covered in this section.

As a user, when you edit data in the table by modifying an editable cell, inserting a new row, or deleting one or more rows, you are manipulating the table's edit state.

Edits to the table are "staged" and stored in both the editedRows and allEdits properties of the table. Typically, you will then process these updates using the onSaveChanges event handler.

Superblocks also provides functions to programmaticaly manipulate the table's edit state:

Function	Arguments	Description
updateRows()	(rows: { [index: number]: Row })	Update one or more rows in a table based on their indices. Each row can be specified as a partial update.
insertRows()	(startIndex: number, rows: Row[]),	Insert one or more contiguous rows beginning with a start index. Each row can be specified as a partial update.
deleteRows()	(rowIndices: number[])	Delete one or more rows based on their indices

When using these functions, it is critical to understand index and row data.

• The index is relative to the table's current edit state as a user would see visually on the screen. The index includes any inserted or deleted rows. For example, if I insert a new row via the UI directly after the first row (0th index), this new row is at index 1. Thus, if I called updateRows() or deleteRows on index 1, I would be modifying/deleting this newly inserted row.
• The row data structure is an object where the column is the key and the value is the data in that column. This data structure mactches what you would see when inspecting the Table data in Superblocks.

Using updateRows()

Let's imagine that we have a list of transactions to process displayed in a table. The status column in the table is used to display the status of the transaction.

We want to implement a feature to allow users to multi-select a set of rows and bulk modify their status to approved. The user will then review the statuses in the table along with any other edits, and click save to persist the updates to the database.

To do so, we can easily drag on a Button above the table and add a Run JS action to the button's onClick event handler with the following JavaScript.

const updates = {};

// loop over the selected rows to construct an object to pass to updateRows, setting each row to approved
Table1.selectedRowIndices.forEach((index) => {
  updates[index] = { status: "approved" };
});

Table1.updateRows(updates);

Now, if the user selects the first, second, and third rows:

• Table1.selectedRowIndices would be [0, 1, 2]
• The JS function would produce an updates object with the following structure, which matches the schema accepted by the updateRows() function

{
    "0": {
        "status": "approved"
    },
    "1": {
        "status": "approved"
    },
    "2": {
        "status": "approved"
    }
}

Using insertRows()

Similarly, the insertRows() function can be used to programmatically insert new rows into the table.

For example, let's imagine that we wanted to allow users to easily select a set of rows in a table and duplicate those rows below.

To do so, we could add a button above the table and add a Run JS action to the button's onClick event handler with the following JavaScript:

const insertStartIndex = Math.max(...Table1.selectedRowIndices) + 1;

Table1.insertRows(insertStartIndex, Table1.selectedRows);

This code would find the index of the last selected row and increment by 1 to idenify where to begin the row insertion. In our insertRow call, we then pass that insertStartIndex as the first argument, and as the second argument pass the row data for the selected rows.

Using deleteRows()

Finally, the deleteRows() function can be used to programmatically delete rows from the table.

For example, let's imagine that as part of the same workflow, the user wants to be able to bulk delete any transactions with the status rejected.

Rather than requiring the user to manually select each rejected transaction, we could implement a feature to allow the user to click a button and automatically mark all rows in the table as deleted.

To do so, we can easily drag on a Button above the table and add a Run JS action to the button's onClick event handler with the following JavaScript:

const rowIndicesToDelete = [];

Table1.tableData.forEach((row, index) => {
  if (row.status == "rejected") {
    rowIndicesToDelete.push(index);
  }
});

Table1.deleteRows(indicesToDelete);

info

Note: The code above only works reliably if row insertion is disabled for the table. To account for row insertion, see this example.

Example: Maintaining a process sequence in an editable table

Finally, let's demonstrate how we would maintain a sequence of steps displayed in a table in and end-to-end fashion.

For this use case, our support agents are completing a process and recording all steps they take when monitoring a transaction. The user can:

• Insert new steps
• Delete steps
• Modify steps

This data is going to be written back to the database, and the order that the steps were taken in is of critical importance.

As such, we need to ensure that the Step column is updated as the user interacts with the table.

Handling row insertions

When a row is inserted, we need to ensure that we set its step based on the position it was inserted at, and also update any subsequent rows in the table.

To do so, we can add the following Run JS step to the onRowInserted event handler on the table.

const updates = {};

const tableSize =
  Table1.tableData.length + Table1.editedRows.insertedRows.length;

const deletedRows = Table1.editedRows.deletedRowIndices;

let currentStep = 1;

// loop over the length of the table and increment the current step for any non-deleted row to construct the updates object
for (let i = 0; i < tableSize; i++) {
  if (!deletedRows.includes(i)) {
    (updates[i] = {
      step: currentStep,
    }),
      (currentStep += 1);
  }
}

// write the updates back to the table
Table1.updateRows(updates);

Handling row deletions

When rows are deleted, we will want to recalculate the step number for all subsequent steps in the table to exclude the deleted row(s).

To do so, we can add the same Run JS logic to the onRowDeleted event handler on the table.

const updates = {};

const tableSize =
  Table1.tableData.length + Table1.editedRows.insertedRows.length;

const deletedRows = Table1.editedRows.deletedRowIndices;

let currentStep = 1;

// Loop over the length of the table and increment the current step for any non-deleted row to construct the updates object
for (let i = 0; i < tableSize; i++) {
  if (!deletedRows.includes(i)) {
    updates[i] = {
      step: currentStep,
    };
    currentStep += 1;
  }
}

// Write the updates back to the table
Table1.updateRows(updates);

Keeping the rows ordered as the user modifies steps

Now let's say that when a user edits a step, for example, manually updating that they performed the Account Security step as Step 11, we want to keep the table sorted by the step column.

To do so, we can simply sort the table by the Step column. We must be sure to set the Step column type to Number so the sort is numeric.