Introduction
Airtable is Web-based third-party software positioned as a "human-friendly database". Among other uses, Airtable can be an excellent cloud-hosted content management system for Intuiface experiences thanks to Intuiface API Explorer.
This article covers all the methods for reading and writing between an Intuiface experience and an Airtable "base".
Throughout the article we use this photo gallery base as an example:
Prerequisite: get your Airtable API key
Before being able to use Airtable API within Intuiface, you will need to generate an Airtable API key on your account.
First, go into your Airtable account.
Then click on the Generate API key button.
Please refer to Airtable documentation here if you need more information.
Airtable API documentation
About Airtable API documentation
Once you have created your Airtable base, you should view its API documentation to determine which request you need to enter within the API Explorer. Airtable API documentation is dynamic and will adjust to reflect your base and the tables it contains. All of the snapshots below are from the Webinar Gallery Photos base and its PhotoList table.
Accessing the documentation
When logged into your base, click the help icon in the top right corner of the page, then click API Documentation:
Next, choose the particular table you're interested in. For this example our objective will be to retrieve photos found in the Photolist table.
Here is the table for which we'll need API documentation:
And here is the associated documentation page:
Airtable Authentication
Common mistakes to avoid:
1. By default, the autogenerated Airtable documentation hides your API key. Check the Show API key checkbox to make sure all requests in the documentation can be directly copied / pasted into API Explorer. Do not submit a request whose authorization contains the value is "Bearer YOUR_API_KEY".
2. When you copy and paste a request into API Explorer, don't forget to include the "curl" at the start.
Based on the screenshot above, the full line pasted into API Explorer should be:
curl "https://api.airtable.com/v0/app7y5ixPnlntWcl7/PhotoList?maxRecords=3&view=MainView" \
-H "Authorization: Bearer YOUR_API_KEY"
with YOUR_API_KEY replaced with the correct value as stated previously.
3. The "Authorization" parameter must be set to a "Header" parameter type and start with the word Bearer before the actual key.
Using Intuiface API Explorer with Airtable
Display all content in a table
To display all table contents in your Intuiface experience, use the list records option. Select the "List records" option in the left nav bar. The curl statement in the right panel is what you would enter into API Explorer - i.e. it is your request URL.
Optionally:
- Remove the maxRecords parameter in the curl statement to eliminate a limit on the number of items retrieved.
- Keep the view parameter to ensure you get table items in the same order as you see them in your Airtable view. The value of the view parameter is the name or ID of a table view you've created.
By default, even if you remove the maxRecords parameter, Airtable will only send up to 100 records in a request response. If you have more than 100 items in your table, you'll need to use Airtable pagination feature and add an offset parameter to your query and pass the appropriate value when trying to retrieve the next records from your table.
Refresh already-displayed content
When using a REST-based Web Service Interface Asset to load content via a Web API, the content is loaded once, when starting the experience. If you need to refresh it, either on a user action or regularly, you need to call the Interface Asset's Refresh action. The retrieved content will not refresh on its own.
You can use any trigger to call this action, such as a button press, a timer, a scheduler or even a local network or Web trigger.
Using Airtable Views
Views are specific to each individual table in your base; you can use views to show only specific fields or records, and apply other configurations to manage the information in that view. Each view can have its own unique configurations to hide, sort, and filter records within a table.
For more details about Views, we recommend the Views article in the Airtable Help Center.
Using Views is an alternate and simpler way to display your records sorted and/or filtered. If you want to filter and sort directly through your API request, please continue to the next dedicated chapters.
Note that, by default, every Airtable base comes with a default view that you are calling in a parameter as shown earlier, this view is commonly named Grid View or MainView in our example above and the request looks like this:
curl "https://api.airtable.com/v0/app7y5ixPnlntWcl7/PhotoList?maxRecords=3&view=MainView" \
-H "Authorization: Bearer YOUR_API_KEY"
Create a new view
To do so, on your Airtable database, click one of the option provided at the bottom left of the screen.
Once it's done, you will be able to rename this new View by using the contextual menu on top. Let's change its name to MyView.
Sort and Filter your View
Now that you have your newly created View, you're free to add sorting and filtering conditions in order to display the records you want, the way you want.
To do so, select your View and apply the desired Filters and/or Sorting criteria. Note they will be only applied to this View and you probably will end up displaying less records than what you had before with the Main View.
As these are only modifying what you display through Sorting and Filtering conditions, it doesn't alter the content of the database itself. Your records won't change, they will just be shown or hidden based on chosen criteria.
Some option that could be added, considering the sample database for instance:
- show artworks from a specific year
- show artwork below a specific price
- sort artwork by artist name etc.
Use your View in Intuiface
As you probably guessed, it's pretty simple to call your newly created view, just use its name as parameter in your request. For instance, if your view is called MyView, use the following request
curl "https://api.airtable.com/v0/app7y5ixPnlntWcl7/PhotoList?maxRecords=3&view=MyView" \
-H "Authorization: Bearer YOUR_API_KEY"
Airtable will return the records in the same order and filtered the same as what you've set in your view.
Filter content before displaying it
Filtering content within an Airtable request is achieved by adding the filterByFormula query parameter in the List records request URL. You can read more about Airtable formulas in their documentation but we will give you some examples below.
Objective: Only display photos from a specific collection. Ex: Vintage Cars
First, you need to add this collection parameter to your request.
- Edit your Interface Asset in API Explorer.
- Add the filterByFormula parameter with a default value. In our sample, we will use {Collection}="Vintage Cars", where Collection is the field name we want to use to filter the records, and Vintage Cars is the value to use within this filter.
Then, to let a user dynamically change the filter value, you can call the Set filterByFormula action on the Interface Asset from any trigger (ex: a button). It can either be:
- a static value, ex: {Collection}="African Wildlife"
OR - a dynamic value, using binding to retrieve that value from user input (e.g. from a Text Input asset), and then using the Enclose operation of the Text Manipulation binding converter.
To remove a filter, you need to call the Set filterByFormula action with an empty parameter.
Search text fields for a specific string
To identify records where a particular text field contains specific text, the value of the filterByFormula parameter should be set to {FieldName}="value"
.
This formula will return records where the FieldName property is strictly equal to the passed value. NOTE: The entire formula is case sensitive.
Ex: {Collection}="African Wildlife"
To create a case insensitive formula, you need to convert both the field and value content to lower case strings, using the following formula: LOWER({FieldName})=LOWER("value")
.
Search text fields for a substring
To identify records where a particular text field contains text anywhere in their value, the value of the filterByFormula parameter should be FIND("value", {FieldName}) > 0
.
This formula will return all records where the FieldName property contains the passed value. NOTE: The entire formula is case sensitive.
Ex: FIND("Vintage", {Collection}) > 0
To create a case insensitive formula, you need to convert both the field and value content to lower case strings, using the following formula: `FIND(LOWER("value"), LOWER({FieldName})) > 0
Search numeric fields for amounts more or less than a specific value
To identify records where a particular numeric field contains a number more than a specified value, the value of the filterByFormula parameter should be set to {FieldName}>"value"
.
This formula will return all records where the FieldName parameter contains a number greater than value.
Ex: {Creation Date}>"1992"
Conversely, to identify records for whom a particular numeric field contains a number less than a specified value, the value of the filterByFormula parameter should be set to {FieldName}<"value"
.
Conditional request with AND/OR
If you want all items from Collection Wildlife AND with Creation Date greater than 1992, you can use the following formula
AND( {Collection}="African Wildlife", {Creation Date}>"1992" )
Sort items
Sorting items returned by an Airtable request is achieved by adding two parameters to the List records request:
- sort[0][field]: the field to use for sorting.
- sort[0][direction]: the direction for sorting, can be asc for ascending or desc for descending.
Example with the PhotoList table:
sort[0][field]
= Pricesort[0][direction]
= asc
To enable a user to dynamically change the field used to sort records or the direction of the sorting, you can call the Set sort[0][field] or Set sort[0][description] action with a new value for the parameter.
Calling the Set sort[0][field] with an empty parameter will remove the sorting and present the records in the way they are ordered in the view you are using in your request.
Create a record
To create a record in Airtable from within your Intuiface experience, use the "Create a record" option found within the Airtable API Documentation. Select the "Create a record" option in the left nav bar.
The request that will be displayed is to create multiple records, we are not going to use this one.
Click the link on the left part to see the request to create a single record. That is the request we will use.
The curl statement in the right panel is what you would enter into API Explorer - i.e. it is your request URL.
IMPORTANT: All Airtable API requests need to be authenticated. To make things easier, check the Show API key checkbox to make sure all requests in the documentation can be directly copied / pasted in the API Explorer.
WARNING: When you submit your request in the API Explorer, it will be processed and a new record will be created using the parameters you specified. Don't forget to delete that record directly in Airtable if you don't want it.
Finally, within API Explorer, choose a name and category for this new interface asset then submit it.
Now that your Interface Asset is available in Intuiface, we need to build a scene to create a record. One approach could be:
- add two Input Text Asset
- add a Button
- for the Button, associate the "Is released" trigger with a call to your "create" action. In that action:
- bind the value passed to field.Name to the Text property of the first Text Input
- bind the value passed to field.City to the Text property of the second Text Input
That's it!
Start your Experience, fill both of Input Text Assets and, upon tapping the Button, a new record will be created in your Airtable base.
Delete a record
To delete a record in Airtable from within your Intuiface experience, use the "Delete a record" option found within the Airtable API Documentation. Select the "Delete a record" option in the left nav bar.
Airtable documentation will display a sample request to delete multiple records (up to 10). We will not use this request as the API Explorer cannot handle it.
Click the link on the left part to show the request to delete a single record. That is the request we will use.
The curl statement in the right panel is what you would enter into API Explorer - i.e. it is your request URL.
IMPORTANT: All Airtable API requests need to be authenticated. To make things easier, check the Show API key checkbox to make sure all requests in the documentation can be directly copied / pasted in the API Explorer.
WARNING: When you submit your request in the API Explorer, it will be processed and the record you specified in the documentation will be deleted. After creation of your interface asset, you may want to return to your Airtable base and manually recreate the deleted record.
We are not finished because the curl statement hard codes a record ID - in this example it's the value "recfm4w9AKdRYtE1U". This flat value needs to be replaced with a variable parameter so our Intuiface experience can change it on the fly.
You can make this modification in real-time while using API Explorer or, if it's more convenient, modify it in an editor (like Notepad) then paste the result in API Explorer.
Within the curl statement, wrap the record ID in brackets and assign it the key name "recordID" so you can use it as a parameter value you can modify on the fly. Use a colon as separator between the name and the value. No spaces!
Thus recfm4w9AKdRYtE1U will become {recordID:recfm4w9AKdRYtE1U}.
Submit the updated curl statement in API Explorer. As you will see, there is now a new parameter named recordID. Please note that submitting the request will delete the record (as stated previously) so you should consider readding it manually.
Finally, within API Explorer, choose a name and category for this new interface asset then submit it.
Now that your interface asset is available in Intuiface, we need to build a scene to delete records. As you need to know which record ID(s) to target for deletion, we are first going to generate a list containing all records as explained above.
After generating the list, the following is one approach to deleting a record:
- set up a Collection displaying all retrieved records onscreen
- add a Button, with the label "Delete record", to the data feed template for the collection
- on the Button, associate the "Is released" trigger with a call to the "Delete" action in our new interface asset, binding the "recordID" parameter of the "Delete" action to the ID property pulled from the selected record.
That's it!
Run your experience. By tapping the "Delete Record" button for a particular record, it will be deleted from your Airtable base. You can add an action after the deletion to refresh the request that lists all records in order to update your display of the collection.
Update a record
To update a record in Airtable from within your Intuiface experience, use the "Update a record" option found within the Airtable API Documentation. Select the "Update a record" option in the left nav bar.
The request that will be displayed is to update multiple records, we are not going to use this one.
Click the link on the left part to see the request to update a single record. That is the request we will use.
The curl statement in the right panel is what you would enter into API Explorer - i.e. it is your request URL.
WARNING: When you submit your request using API Explorer, it will be processed and the record you specified in the documentation will be updated.
IMPORTANT: All Airtable API requests need to be authenticated. To make things easier, check the Show API key checkbox to make sure all requests in the documentation can be directly copied / pasted in the API Explorer.
We are not finished because the curl statement hard codes a record ID - in this example it's the value "recDrEaFC4eEvhDdR". This flat value needs to be replaced with a variable parameter so our Intuiface experience can change it on the fly.
In addition, notice how this request focuses on only one field - "Name". It would be good to know how to update multiple fields.
First we need to transform the record ID given by the Documentation sample in order to use it as a variable parameter. We will proceed the same way we did in the previous section about deletion.
Within the curl statement, wrap the record ID in brackets and assign it the key name "recordID" so you can use it as a parameter value you can modify on the fly. Use a colon as separator between the name and the value. No spaces!
Thus recDrEaFC4eEvhDdR will become {recordID:recDrEaFC4eEvhDdR}.
Next, we want to update the "City" field in addition to the "Name" field. In order to do this, we are going to add a new parameter. Using API Explorer:
- click on Add a parameter... at the end of the list of parameters in the left pane
- set the Parameter name to fields.City ("City" is the name of the column in Airtable)
- set the Parameter value to anything you want (we picked "Anchorage")
- click on the Parameter type icon (little grey box with a Q) and set it to Body (B) as this is the type we need to use here
- submit your request again by clicking the big blue arrow button in the top right
You have now a recordID parameter and two field parameters, one for Name and one for City.
Finally, within API Explorer, choose a name and category for this new interface asset then submit it.
Now that your Interface Asset is available in Intuiface, we will build a scene for updating a record. As we need to know which record ID(s) to target and update, we will first generate a list displaying all records as explained above.
Once that list is generated, we are also going to reuse elements from the Create Record scene above:
- set up a Collection displaying all retrieved records onscreen
- add a Button to the data feed template for that collection
- add two Input Text Assets somewhere outside the collection, in the scene.
- for the Button in the data feed template, associate the "Is released" trigger with a call to the "Update" action in your new interface asset. Then:
- bind the action parameter recordID to the ID property pulled from the selected record
- bind the action parameter field.Name to the Text property of the first Text Input Asset
- bind the action parameter field.City to the Text property of the second Text Input Asset
That's it!
Run your experience then type two new values in the two Text Input assets. Then, when you tap the "Update" button associated with any record, that record will be updated in Airtable using the two entered values. You can add an action after the update to refresh the request that lists all records in order to update your display of the collection.
Copy & reuse a base from a Marketplace sample
Some sample in the Marketplace such as Real Estate Shop Window or QSR Digital Menu Board were built upon an Airtable base that can be duplicated and reused for your own purpose.
The first thing to do will be to copy the base used in the sample into your own Airtable account. The links to access this base is provided in each sample documentation article.
Then, in the sample you downloaded from the Marketplace, you will need to replace some properties of the Airtable related Interface Asset:
- Authentication: enter your Airtable API key. See this section to retrieve your key.
- BaseID: each request will have to target your own Airtable base instead of the one used in the sample. To find your base ID, you need to use the list records entry in the API documentation of your table, as described in this section, and retrieve the base ID in the path. See picture below.
Once you have these 2 properties, you can update them in the Interface Asset you want to reuse.
Limitations
- Sending values from Intuiface (post/patch) on Airtable will not work if the target entry is defined as numeral value;
- Audio files cannot be played;
- Starting with Intuiface 6.4, video files can be played using VLC video style. Video rendered through Windows Media Player is not supported.
Comments
0 comments
Article is closed for comments.