Table Activities
Table Activities
In Automation, tables allow you to store structured in tables that are similar to a spreadsheet. You can also use them as the data source for a For Each Loop to iterate through each row in the table.
Note:
-
Table activities have a maximum variable size of 1 MB.
-
As of October 4, 2023, the size of the Large String workflow variable type is limited to 10 MB.
Tables do not use targets or account keys.
The Tables adapter provides the following activities:
- Add Row to Table
- Delete From Table
- Read Table from JSON/Text/XML
- Select From Table
- Update Row in Table
Where Clauses
When you want to select, update, or delete rows in a table, you can provide a where clause to limit the rows affected.
Here are a few examples:
| Sample | Description |
|---|---|
| id == [$variable$] | Where the row’s ID equals the variable provided |
| username == "[$variable$]“ | Where the row’s username equals the variable provided |
| firstName == "[$variable1$] AND lastName == "[$variable2$]“ | Where the row’s first name and last name match the variables provided |
| timestamp > [$variable$/] | Where the row’s timestamp is greater than the variable provided |
Add Row to Table
Use the Add Row to Table activity to add a row to an existing table.
Usage
Complete the following properties to use this activity:
Table - Specify the following information or click the Variable Reference icon to choose a variable:
-
Input Table - Enter the table name. This should be the variable output from a workflow or activity.
Note: If you are unable to choose the table you want from the variable browser, ensure that the activity that created the table has the Persist Table check box checked in its properties.
-
New Row - Add data to the columns in the table.
Delete from Table
Use the Delete from Table activity to delete rows from a table.
Usage
Complete the following properties to use this activity:
- Select - Specify the following information or click the Variable Reference icon to choose a variable:
-
Input Table - Enter the table name. This should be the variable output from a workflow or activity.
If you are unable to choose the table you want from the variable browser, ensure that the activity that created the table has the Persist Table check box checked in its properties.
-
- Row(s) - Click the radio button to specify how many rows are deleted:
- All Rows - Delete all rows in the table.
- Rows Matching Specified Criteria - Delete only those rows that match the Where Clause.
- Where Clause - Enter the criteria for selecting rows to delete or click the Variable Reference icon to choose a variable. This example shows a Where Clause that will only delete rows where the id is equal to 1:
Read Table from JSON/Text/XML
Use the Read Table from JSON/Text/XML activities to convert source data into a table format that functions similarly to a spreadsheet. For more information, see Read Table from JSON/Text/XML.
Select from Table
Use the Select from Table activity to select rows from a table.
Usage
Complete the following properties to use this activity:
- Select - Specify the following information or click the Variable Reference icon to choose a variable:
- Input Table - Enter the table name. This should be the variable output from a workflow or activity.
-
Where Clause - Enter the criteria for selecting the rows or click the Variable Reference icon to choose a variable.
-
Persist Table - Check this check box to store the output of this activity as a variable.
-
- Input Table - Enter the table name. This should be the variable output from a workflow or activity.
If you are unable to choose the table you want from the variable browser, ensure that the activity that created the table has the Persist Table check box checked in its properties.
- Number of Rows - Click the radio button to specify how many rows are selected:
- All Rows - Select all rows that match the Where Clause.
- At Most - Select up to the number of rows specified.
- First N Rows - Enter the maximum number of rows to select.
-
Sorting - Click the radio button to specify how the selected rows are sorted:
- No Sort - The selected rows will be returned with no sorting.
- Sort By - The selected rows will be sorted by Column Name
- Column Name - Enter the name of the column to sort by.
- Order - Click the radio button to specify whether the sorting will be Ascending or Descending.
Example
This example shows how to select rows from a table.
In the Select section of the activity properties, you configure the criteria for selecting rows. The following example shows selecting rows from a table where the value in the id column is equal to 3 and for each matching row, all of the columns are selected.
The next section is where you define how many rows to select and provide sorting options. In the following example, up to 5 rows are selected and it is sorted by the id column in ascending order:
Update Row in Table
Use the Update Row in Table activity to update existing rows in a table.
Usage
Complete the following properties to use this activity:
- Table - Specify the following information or click the Variable Reference icon to choose a variable:
-
Input Table - Enter the table name. This should be the variable output from a workflow or activity.
If you are unable to choose the table you want from the variable browser, ensure that the activity that created the table has the Persist Table check box checked in its properties.
-
Result Columns - Click the drop-down menu then check the check boxes beside the columns where you want to update values.
-
Update Rows - Enter the new value you want to insert.
-
- Select - Click the radio button to specify how the affected table rows will be selected:
- All Rows - Update every row in the row in the table with the new value.
- Rows Matching Specified Criteria - Update only those rows that match the Where Clause
- Where Clause - Enter the criteria for selecting rows to update or click the Variable Reference icon to choose a variable.
Example
This example shows how to update the username column of a table with the new value of cbauch.
In the Table section of the activity properties, you configure the Input Table, Result Columns, and Update Rows values:
In the Select section of the activity properties, you configure the criteria for selecting rows. You can either select to update all of the rows in the table or provide criteria in the Where Clause. This example shows a clause that will only update rows where the id is equal to a variable:
