Artisan IMG > Airtable (airtable) (8fbf07b0-8fe6-49c3-b56f-0b39b93a8f0f)
Artisan IMG > Airtable (airtable) (8fbf07b0-8fe6-49c3-b56f-0b39b93a8f0f)

Airtable
3.0

Airtable works like a spreadsheet but gives you the power of a database to organize anything.

Overview
Copy

Airtable is a spreadsheet-database hybrid, with the features of a database but applied to a spreadsheet.

API Information
Copy

The Base URL used for the Airtable connector is https://api.airtable.com/v0/{{base_id}}/{{table}}. More information can be found on their main API documentation (v1) site. API limits can be found here.

Authentication
Copy

When using the Airtable connector, the first thing you will need to do is go to your Tray.io account page and select the workflow you wish to work on. Once in the workflow builder itself, search and drag the Airtable connector from the connectors panel (on the left-hand side) onto your workflow.

With the new Airtable connector step highlighted, in the properties panel on the right, click on the Authenticate tab followed by 'Add new authentication' (located under the 'Authentication' field).

This will result in a Tray.io authentication pop-up window. The first page will ask you to name your authentication and state which type of authentication you wish to create ('Organization' or 'Personal').

As you can see, the next page asks you for your 'API key':

As of August 1st Airtable are deprecating the use of API Keys. This means users will no longer be able to use their API key to authenticate with. Instead of API Keys you will need to use your PAT (Personal Access Token). This change only affects NEW authentications. Your old authentications (built using API Keys) will continue to work as expected. For more detailed information on the changes affecting you please see Airtable's Help page: Creating and using API keys and access tokens

Head to the Developers portal: https://airtable.com/create/tokens and select the Create new token button. Give your token a unique name. This name will be visible within your record revision history and needs to be easily distinguishable.

Choose the scopes relevant for your use case. These control what API endpoints your PAT token will be able to access. Click add a base to grant the token access to your Airtable workspace. Keep in mind that the token will only be able to read and write data within the bases and workspaces that have been assigned to it.

Once your token is created, we will only show it to you once.

Copy your PAT into the API Key field:

Click the Create authentication button. Your connector authentication setup should now be complete. Please run the simplest operation available to test you have a working authentication.

Available Operations
Copy

The examples below show one or two of the available connector operations in use.

Please see the Full Operations Reference at the end of this page for details on all available operations for this connector.

Notes on using Airtable
Copy

Empty field found
Copy

Airtable records often contain empty fields. When this is the case, the Airtable API will return nothing for that field, and throw an error which will stop your workflow from progressing. This can be a problem should another field depend on the data coming in here.

In order to mitigate this scenario, please see our Setting fallback values (advanced) example below for more details.

Finding the Base & Table IDs
Copy

API LIMITATIONS: The format of Airtable's REST API is as follows: "

After you’ve created and configured the schema of an Airtable base from the graphical interface, your Airtable base will provide its API to create, read, update, and destroy records.

This means that only after users have created their base/ table/ spreadsheet will the relevant ID's be created and available for them to use.

Users must check their personalised API documentation pages to find their unique base and table ID'sIDs.

Finding your Base ID
Copy

  1. Go to Airtable's API documentation site: https://airtable.com/api and sign in.

  2. Select the base you are working on.

  3. The base ID URL will be similar to: https://airtable.com/appPDWK5ythfw2/api/docs#curl/introduction. The base ID is the hash which comes after the main URL section.

  4. In this example that makes the base ID: appPDWK5ythfw2.

Finding your Table ID
Copy

PLEASE NOTE: You can also use your table name instead of the table ID

  1. Head to the main Airtable website: https://airtable.com/ and sign in.

  2. Select your base and the table you wish to use.

  3. The table ID URL will be similar to: https://airtable.com/tblefyehd4265d/viw0T6SXtRlEIKJLR?blocks=hide. The table ID is the hash which comes directly after the main URL section (so BEFORE the second hash and URL query). Note that it begins with: tbl.

  4. In this example that makes the table ID: tblefyehd4265d.

Using the Raw HTTP Request ('Universal Operation')
Copy

As of version 2.0, you can effectively create your own operations.

This is a very powerful feature which you can put to use when there is an endpoint in Airtable which is not used by any of our operations.

To use this you will first of all need to research the endpoint in the Airtable API documentation, to find the exact format that Airtable will be expecting the endpoint to be passed in.

Note that you will only need to add the full URL. This is to ensure you will add your desired Base ID and Table Name / ID as part of the base URL.

The full URL (base URL) for Airtable is: `https://api.airtable.com/v0/[YOUR-BASE-ID]/[YOUR-TABLE-NAME-OR-ID]`

For example, say that the 'Get record' operation did not exist in our Airtable connector, and you wanted to use this endpoint. You would use the Airtable API docs to find the relevant endpoint - which in this case is a GET request called: /THE-RECORD-ID.

More details about this endpoint can be found here.

As you can see there is also the option to include a query parameter, should you wish to do so. So if you know what your method, endpoint and details of your query parameters are, you can get the record information with the following settings:

Method: GET

Endpoint: https://api.airtable.com/v0/[YOUR-BASE-ID]/[YOUR-TABLE-NAME-OR-ID]/[THE-RECORD-ID]

Final outcome being: https://api.airtable.com/v0/[YOUR-BASE-ID]/[YOUR-TABLE-NAME-OR-ID]/[THE-RECORD-ID]

INTERPOLATION: When you wish to include JSON generated data within another input/output result, use our Interpolation method as described here.

Example Usage
Copy

TRAY POTENTIAL: Tray.io is extremely flexible. By design there is no fixed way of working with it - you can pull whatever data you need from other services and work with it using our core and helper connectors. This demo which follows shows only one possible way of working with Tray.io and the Airtable connector. Once you've finished working through this example please see our Introduction to working with data and jsonpaths page and Data Guide for more details.

Adding records
Copy

Below is an example of a way in which you could potentially use the Airtable connector, to add records to an Airtable sheet when a new order is paid for. The main operation being 'Create record'.

While this scenario imagines a situation whereby you as a user have received some customer data via the Webhook trigger, and now need to process it into Airtable; in reality you may have received this data any number of ways. By pulling the records from the Salesforce or SurveyMonkey connector, through the CSV Editor etc.

The steps will be as follows:

  1. Create an Airtable table to work in conjunction with.

  2. Setup using a Webhook trigger and insert some relevant data fields

  3. Iterate through each record.

  4. Add the record data to the Airtable base table.

The final outcome should look like this:

1 - Create a table
Copy

Before we begin with the workflow, we need to create a table within Airtable itself.

Users must make sure that the column names in the Airtable table are in line with the attributes of the received JSON data.

Also, it is necessary to provide appropriate 'field type' as specified in the below table for each column in the Airtable.

Column Name Field Type
order_id Number
email Single line text
total_price Number
currency Single line text

For instance, email addresses: test@example.com will have a field type of 'Single line text'. See the image below for better understanding.

2 - Send/ receive test data
Copy

Once you are done creating the table, head to the Tray.io workflow builder dashboard.

Select the Webhook trigger from the trigger options available, and set the operation to 'When webhook is received, auto respond with HTTP 200'. Be sure to click the 'Enable' button before continuing. This makes the workflow ready to receive incoming calls.

Since this example uses the Webhook trigger, we also need to send some data to the workflow itself in order to activate it. Feel free to use the tooling of your choice, be it an API development tool, another service connector, or outside application, etc.

Below is some sample test data to use:

1
[{"order_id":1742,"email":"tbrindley0@sina.com.cn","total_price":85,"currency":"USD"},
2
{"order_id":3527,"email":"swhoston1@census.gov","total_price":54,"currency":"GBP"},
3
{"order_id":4739,"email":"bparren2@vimeo.com","total_price":53,"currency":"GBP"},
4
{"order_id":3124,"email":"ncrab3@so-net.ne.jp","total_price":60,"currency":"Yen"},
5
{"order_id":4937,"email":"bcudbertson4@gmpg.org","total_price":87,"currency":"Yen"}]

Sending the test data to the Webhook trigger will automatically activate your workflow. This will be visible from the logs displayed in the Debug panel and the output panel as well:

Remember that the format in which you receive your data will vary depending on where it is coming from. Pulling records in from Salesforce, Webform, etc will have different results/ views.

3 - Iterate through each record
Copy

Next, add a Loop connector with the 'List' field set to $.steps.trigger.body. You can use the connector-snake to generate this automatically.

JSONPATHS: For more information on what jsonpaths are and how to use jsonpaths with Tray.io, please see our pages on Basic data concepts and Mapping data between steps

CONNECTOR-SNAKE: The simplest and easiest way to generate your jsonpaths is to use our feature called the Connector-snake. Please see the main page for more details.

The loop connector will then loop through each of the records one-by-one:

4 - Add data to Airtable
Copy

Add an Airtable connector inside of the Loop connector and set the operation to 'Create record'.

Set the Base & Table ID as described above in the 'Note on Operation usage' section, Finding the Base & Table IDs.

Add all the records and their corresponding values via the use of their relevant jsonpaths. If you used the sample data above, the jsonpaths will be as follows:

  • order_id : $.steps.loop-1.value.order_id

  • email : $.steps.loop-1.value.email

  • total_price : $.steps.loop-1.value.total_price

  • currency : $.steps.loop-1.value.currency

On a successful execution of the above workflow, the table within Airtable will have updated and should look similar to this:

Advanced topics
Copy

Setting fallback values
Copy

Airtable records often contain empty fields. When this is the case, the Airtable API will throw an error for these particular records.

This can be a problem should another field depend on the data coming in here, as it will stop the workflow from progressing any further.

For example if we use the 'List records' operation in Airtable to get the following records:

Then the orders which have no email address recorded will not return the email field at all:

The following workflow illustrates how to deal with this, using Google Sheets as a generic placeholder for any service you may be wanting to push the Airtable data into:

From the properties panel of the Add order as row step you can see that we are including 'email' as a field to be pushed.

We can then also set the fallback value to be e.g.'null' (or 'not found' etc.):

Inspecting the input logs from the debug tab for the Sheets Add order as row step will then show that the email 'null' value has been passed succesfully:

And all orders have been pushed to Google Sheets:

Manage duplicate records
Copy

Over a period of time you may find duplicate records accumulating in Airtable.

The following workflow imagines a scenario, where you are comparing incoming records from the trigger with the existing records in Airtable. The records that match - i.e. the ones that already exist in Airtable - are skipped and the rest of the records that do not match are inserted into Airtable.

Thus by not allowing the existing records to be inserted again, the workflow manages the duplicate record issue.

The incoming records received through the Webhook are in JSON format and are as follows:

1
[
2
{
3
"order_id": 1742,
4
"email": "tbrindley0@sina.com.cn",
5
"total_price": 85,
6
"currency": "Som"
7
},
8
{
9
"order_id": 3527,
10
"email": "swhoston1@census.gov",
11
"total_price": 54,
12
"currency": "Yuan Renminbi"
13
},
14
{
15
"order_id": 4739,
16
"email": "bparren2@vimeo.com",
17
"total_price": 53,
18
"currency": "Yuan Renminbi"
19
},
20
{
21
"order_id": 3124,
22
"email": "ncrab3@so-net.ne.jp",
23
"total_price": 60,
24
"currency": "Yen"
25
},
26
{
27
"order_id": 4937,
28
"email": "bcudbertson4@gmpg.org",
29
"total_price": 87,
30
"currency": "Yuan Renminbi"
31
}
32
]

The existing records in Airtable are as follows:

The complete workflow looks like this:

The steps involved in the workflow are:

  1. The Webhook trigger step listens for the incoming records.

  2. The Read data from Airtable step uses 'List records' operation to read the existing records from Airtable, based on the provided 'Base' and 'Table' ID.

  3. These existing records will later be compared with the records received from the Webhook trigger using two levels of check.

  4. The Loop data from Webhook step using the 'Loop List' operation traverses the records received via the Webhook trigger ($.steps.trigger.body). Each record traversed through in this loop will be individually compared with all the existing records in Airtable.

  5. In the Flag set to false step using the Data Storage connector's 'Set Value' operation we are creating a 'Key' with name as found with initial value set to false (explained in detail below).

  6. The Loop data from Airtable step using the 'Loop List' operation traverses the existing data that we are reading from Airtable ($.steps.airtable-1.records) in the Read Data from Airtable step.

  7. The Duplicates check step is a Boolean condition that compares the records from the Webhook and Airtable to check for the existing records (explained in detail below)

  8. The Get the flag value step using the Data Storage connector's 'Get Value' operation fetches the value of the found key. This fetched value can be true or false based on if the record from the Webhook (loop-1) compared in the previous step with all the records from Airtable (loop 2) already exists in Airtable or not. If the record already exists then the Get Value operation will return true if it doesn't exist then it will return false.

  9. The Check: flag=false step is a Boolean condition step that checks if the value for the found key fetched from the previous step ($.steps.storage-3.value), is Equal to False. If it is false the record from loop-1 is inserted to Airtable.If the value is not equal to false then the record from the loop is not inserted in Airtable and the loop1 continues the same process from step-5 to step-8 with the next records of the Webhook.

  10. The last step is a Delay connector step. This step delays the workflow execution by 10 seconds before it moves forward.

In doing this we are managing the API limit of Airtable which is 5 requests per second per base. If you exceed this rate, you will receive a 429 status code and will need to wait 30 seconds before subsequent requests will succeed.

On successful execution of the above workflow the following records should be inserted into Airtable:

1
[{
2
"order_id": 4739,
3
"email": "bparren2@vimeo.com",
4
"total_price": 53,
5
"currency": "Yuan Renminbi"
6
},
7
{
8
"order_id": 3124,
9
"email": "ncrab3@so-net.ne.jp",
10
"total_price": 60,
11
"currency": "Yen"
12
},
13
{
14
"order_id": 4937,
15
"email": "bcudbertson4@gmpg.org",
16
"total_price": 87,
17
"currency": "Yuan Renminbi"
18
}
19
]

The final table in your Airtable with all the records populated should look like this:

Flag set to false step explained

The 'Key', found, created at this stage works as a flag variable. A flag variable is usually a boolean variable with two values i.e. 0 and 1. O represents false and 1 reperesents true.

It is in general used to flag if certain condition is met and based on its status, i.e., either true or false certain actions are performed.

In our example, we are using the found key as a flag variable which is initially set as false and will be later set as true in subsequent steps based on the satisfaction of the boolean check done in the Compare order id step.

Based on the value of this variable we will decide if the incoming record is a new or an existing record and then perform the insert record action on Airtable accordingly.

Duplicates check step explained

The Duplicates check is a Boolean condition that checks for a duplicate record using two level of checks. As you can see in the image below, the checks are 'order_id' and 'email' field.

We are using the two level check just for the sake of certainty. You can always do this with one level check.

The operation accepts the first value for both the checks from loop-1 ($.steps.loop-1.value.order_id, $.steps.loop-1.value.email). The loop1 is traversing the incoming records from the Webhook.

This first value is checked if it is Equal to the second value.

The second value for both the checks is fetched from loop-2 ($.steps.loop-2.value.fields.order_id, $.steps.loop-2.value.emal). Loop-2 is traversing the existing records within Airtable.

Now based on the provided values and the loops this condition will check individual record from loop-1 with each record of loop-2 to see if they match.

For example, as you can see the incoming and existing records mentioned above. To start with the record-1 from loop1 will be compared with all the records from loop-2.

Doing so if the match is found it means record-1 from loop-1 already exists, and there is no need to insert it again in Airtable. In such a situation the Key (found) that we have created in the Flag set to true step, for this particular record is set to true. After this loop-2 breaks and the workflow moves to the Get the flag value step.

If the match is not found then loop-2 continues to compare record-2 from loop-1 with each record from loop-2 and so on until all the records from loop-1 are compared with each record of loop-2.

BEST PRACTICES: Whenever you do decide to create your own workflow, be sure to check out some of our key articles such as: