Using Tables in Automations

Overview

Tables can be manipulated using workflow actions. The Table related actions are listed under the Tables collection in the actions explorer.

Query a Table (Select Records)

Querying a table for records can be performed using the Select Action on the tables' collection. The Select action allows you to specify a query parameter which follows RQL query pattern, in the following format:

{
    "select": [] // list of fields to select
    "limit": 100 // limit the amount of records that will return (max is 1000)
    "offset": 0 // the offset from where to return the data
    "filter": {
        key: value // this option lets you filter a specific key with its value
        // for example "name": "Public"
        key: {
            "$gt", "$gte", "$lt", "$lte",
            "$contains_any", "$contains_all", "$like": value
            // this options lets you do more advanced filtering, for example checking
            // if a record was created in the past 25 minutes.
            // "$gt": {{ to_epoch(now() - '25m') * 1000 }}
        }
    }
}

• Select - Specify which fields to return by specifying fields name using the Select option.
• Filter - specify which records to return using the Filter option - only records that is the Is Admin value is false.
• Sort - Define the sorting criteria by designating the relevant field and order. For ascending order, use '+' before the field name; for descending order, use '-' instead.

Parameter	Description
Table Name	The name of the table.
Query	-

Query a Table Using SQL

Querying a table for records can also be performed by using the SQL Query action.

Parameter	Description
SQL Query	The SQL query to run.
Output Format	The desired output format.

info

To fetch the correct scheme of record creation, select existing records using the Select action and use the scheme provided in the output.

Add a Record

Add a record to a table. Two ways:

• Entering the Values Manually
• JSON Format

Entering the Values Manually

Add a record to a table by manually entering the table field name and the table field value.

Parameters:

Parameter	Description
Table Name	The name of the table.
Record	Data of record, manually enter the field name and field value.

JSON Format

Records can be added by inputting values in JSON format. Bulk creation is also supported, allowing the input of a list of JSON objects.

Parameters:

Parameter	Description
Table Name	The name of the table.
Record	Data of record, a JSON format containing key : value pairs. Can also be a list of JSON objects.

Get a Record

Get record(s) from a table. Two ways:

• RQL Format
• Table Fields

RQL Format

Get a Record by entering values using RQL format.

Parameters:

Parameter	Description
Table Name	The name of the table.
RQL	The RQL query to run.

Table Fields

Get a Record from a table by entering the specified table fields.

Parameters:

Parameter	Description
Table Name	The name of the table.
Fields	Table Fields.
Condition (optional)	Condition that compares two table field values.

Update a Record

Update a record on a table.

Parameter	Description
Table Name	The name of the table.
Record ID	The record unique identifier. Can be obtained via the Select action.
Record	Data of record, a JSON format containing key : value pairs.

In the below example, the Admin field is updated:

Delete a Record

Delete a record from a table. Two ways:

• By Record ID
• Conditional Statements

By Record ID

1. Delete a record from a table by the record ID.

Parameters:

Parameter	Description
Table Name	The name of the table.
Record ID	The record unique identifier. Can be obtained via the Select action.

Conditional Statements

2. Delete a record from a table only when a certain condition is met.

Parameters:

Parameter	Description
Table Name	The name of the table.
Condition (Optional)	Condition that compares two table field values.

Create a Table

Create a table based on a schema.

Parameters:

Parameter	Description
Table Name	The name of the table.
Table Schema	The schema of the table in JSON format. For more information, see the Copy table schema.
Skip	Skips table creation if table with the same name already exists.

Does Table Exist

Check if a table with a given display name exists.

When using the Create Table action, and the inserted name of a Table already exists, the Create Table action will fail. In order to prevent this scenario, you are recommended to use the Does Table Exist Action before the Create Table step.

Parameters:

Parameter	Description
Table Name	The name of the table.
Search By	Search by name, display name or both. Default is display name.

Clear a Table

Clear all table records.

Parameters:

Parameter	Description
Table Name	The name of the table.

Delete a Table

Deletes table.

Parameters:

Parameter	Description
Table Name	The name of the table.

Copy table schema

For each table that was created, there is a table schema.

Click the 3 dots menu of the table > Copy table scheme.

Example schema

{
  "fields": [
    {
      "display_name": "Date",
      "description": "",
      "type": "date",
      "attributes": {
        "hidden": false,
        "include_time": false,
        "options": [],
        "required": false,
        "width": 165
      }
    },
    {
      "display_name": "Action",
      "description": "",
      "type": "text",
      "attributes": {
        "hidden": false,
        "options": [],
        "required": false,
        "width": 165
      }
    },
    {
      "display_name": "User",
      "description": "",
      "type": "user",
      "attributes": {
        "hidden": false,
        "options": [],
        "required": false,
        "width": 165
      }
    }
  ]
}

On New Record - Event-Based Trigger

The New Table Record option is an event-based trigger that executes your automation every time a new record is added to your table.

note

Please note that the "Table Name" in the trigger setup must exactly match the name you assigned to your table.