Manage user preferences, store and retrieve information from an Airtable database

In this tutorial, we will use different blocks available in Voiceflow to get information about the user and save them in a database with Airtable, we will also use the new Reminder block to notify the user when he can launch the skill again to discover new content.

Here is the list of elements we will use for this tutorial

  • Permission block
  • User info
  • Reminder
  • API block
  • Code block
  • {user_id} variable

Let’s start by creating our database from the Airtable site. So let’s go to https://airtable.com/, log in and create a new base

And click on “Start from scratch

53%20AM

Name it as you wish and open it

First, let’s rename the table to “Users” by double clicking on the tab

U
37%20AM

For the first field we will use the user id provided by Alexa and Voiceflow, so let’s rename it “UserID”.

Double click on the “Name” field and enter “UserID” as the new name. Keep Single line text as field type. Click on the “Save” button

ouble

This first field is a primary field so it is a good idea to use it to store the unique identifiers of each user.

09%20AM

Double click on the “Notes” field and rename the field to “Name” and change its type to “Single line text”. Click on the “Save” button.

15%20PM

Now, double click on the “Attachments” field and rename it to “Email” and change its type to “Email”. Click on the “Save” button.

57%20PM

We will add a new field to save the user’s choice on accessing his name or email from our skill.
Click on the + to add a new field

%20

Let’s name the “Permission” and change it to “Number” with an “integer (2)” format. Click on the “Save” button.

%22

Again, let’s add a field.

20%20PM

Let’s rename the “Reminder”, type “Number” and format “Integer (2)”. Click on the “Save” button.

05%20PM

Finally, let’s add a new table to our database. We will name it “Messages”. Click on the “+” at the right of “Users” tab.

ommer

Click on “Create empty table

02%20PM

And renamed it to “Messages” and press “Enter” on your keyboard to confirm.

28%20PM

As before, we will rename the first field “Welcome” and the second field “Update”. These two fields will be of the type “Long text”.

We can delete the last “Attachments” field because we will not use it. Click on the small arrow to the right of the field and select “Delete field” from the menu that appears.

33%20PM

To clean up a little, we will check the three lines already present in the table, then, with a right click, we select “Delete all selected records”.

34%20PM
14%20PM

To be able to have a match in the documentation, we create a first example record in this table like this:

13%20PM

Let’s go back to the “Users” table and do the same.

21%20PM
48%20PM

We create a first example record like this:

If this is the first time you have used Airtable or you have never used the API, click on the user icon at the top right and on “Account” in the drop-down menu. From the window that opens, click on “Generate API key”.

38%20PM

Back to the main Airtable window, we will have to retrieve some information from the Airtable online help. Click on the “Help” menu at the top right, then, in the drop-down menu, click on “API documentation”.

43%20PM

In the window that appears, check the “show API key” option at the top right if it is not already done.

48%20PM

Then in the right menu, click on “AUTHENTICATION

01%20PM

Copy for later the url of the second example in the tab curl on the right.

Mine is looking like this:

https://api.airtable.com/v0/app2LVT8O1tRuUoqT/Users?api_key=keyY6PccqvLBwFzSi

Where app2LVT8O1tRuUoqT is the base id, Users is the table name and keyY6PccqvLBwFzSi is the API key.

You can also find more information and examples by clicking on “USERS TABLE” and “Create a record”. In the code on the right you can then access an example of a request and answer in JSON format.

24%20PM

For example here to create a new record we will have to send a POST request with the header “Content-Type: application/json” and the following raw body:

{
  "fields": {
    "UserID": "userid",
    "Name": "name",
    "Email": "email@email.com",
    "Permission": 0,
    "Reminder": 0
  }
}

Keep these web pages open in a corner for later and let’s create a new skill in Voiceflow

Select the “Blank” template and click on “Start”.

15%20PM

This is what this skill will look like.

You can start by studying the preview of this skill that I made available to you (I commented almost all the blocks and different actions).
https://creator.getvoiceflow.com/preview/15aozoDdQ6/6ac91dc6f443eacb9f36395eba15e455

And here are the variables we will use in this skill.

Let’s start at the beginning

The first block is an API block that will retrieve customized messages from our database.

Here is the API block in detail

With the information you obtained earlier in the Airtable documentation, we will create the url needed to query the Airtable database.

Mine looks like this:

https://api.airtable.com/v0/app2LVT8O1tRuUoqT/Messages/?api_key=keyY6PccqvLBwFzSi

Again, app2LVT8O1tRuUoqT is the database, Messages is the name of the second table we’ve created earlier and keyY6PccqvLBwFzSi is my API key.

Edit this informations with the informations in the Airtable doc from the page you left open earlier.

While you are on the Airtable page, edit the records in the “Messages” table as follows:

You can also copy these texts from my shared database here: https://airtable.com/shrVesr5bXdjtT7mU

Once you have updated the “Welcome” and “Updates” fields in your database and you have retrieved all the information to edit the url address, go back to your API block to map the variables.

Here we will map:

response.records.0.fields.Welcome to the Welcome variable
response.records.0.fields.Updates to the Updates variable.

In the following speak block, we use the “Welcome” variable which contains our personalized welcome message

30%20AM

In the next step we check if the user is already registered or if it is the first launch thanks to the recordID variable.

If recordID = 0 we ask the user if he wants the skill to automatically retrieve his name and email.

Then, either the user accepts and we use the User Info block to retrieve his name and email (or the Permission block if the user has not yet authorized the skill), or he does not want to use this function and we ask him if he can still give us his name with an Interaction block.

I let you study the construction of the different paths from the skill preview.
https://creator.getvoiceflow.com/preview/15aozoDdQ6/6ac91dc6f443eacb9f36395eba15e455

You will see that here we are using the API block again to create and update records in our database.

01%20AM
19%20AM

For the creation of a new record (new user), we use this type of url:
https://api.airtable.com/v0/app2LVT8O1tRuUoqT/Users/?api_key=keyY6PccqvLBwFzSi

It’s almost the same url as before but we use here the “Users” table and with a POST method.

In the “Headers” part we add a pair with as key Content-Type and as value application/json.

We map response.id to our recordID variable.

Finally, in the Body Raw Input we will use the following code:

{
  "fields": {
    "UserID": "{user_id}",
    "Permission": {permission},
    "Reminder": {reminder}
  }
}

So much for creating (POST) a recording in Airtable. Let’s now study the second API block to update a record (PATCH).

For the update so, the url looks like this:
https://api.airtable.com/v0/app2LVT8O1tRuUoqT/Users/{recordID}/?api_key=keyY6PccqvLBwFzSi

We use the recordID variable in the url to tell Airtable which record we want to modify.

The “PATCH” method is used here instead of a GET or POST

And as before, we add the header Content-Type with the value application/json.

In the Body Raw Input we use this:

{
  "fields": {
    "Permission": 3
  }
}

To update the user’s permission field.

The last API block we will see together here is the “CheckIfExist” block. Indeed, if the user has given us access to his information and in particular his email, we will be able to check if a registration with this email is not already present in our database thanks to the SEARCH formula of Airtable.
This is convenient because the UserID is replaced each time the user disables or reactivates the skill while his email remains the same.

block%20API

We come back to the GET method with a url that looks like this:

https://api.airtable.com/v0/app2LVT8O1tRuUoqT/Users/?maxRecords=1&filterByFormula=SEARCH('{email}',%20%7BEmail%7D)&api_key=keyY6PccqvLBwFzSi

Here we’ve added maxRecords=1 to retrieve only one record and filterByFormula=SEARCH('{email}',%20%7BEmail%7D) to search for our variable {email} in the “Email” field of our database.

Finally, we map response to JSONresponse variable.

In the Code block next to it, we use the Javascript language to check if we have a match

23%20AM

//Check if we get a match
isResult = JSONresponse.records.length;

if(isResult == 1){
  checkID = JSONresponse.records[0].id;
  checkEMail = JSONresponse.records[0].fields.Email;
}

isResult = JSONresponse.records.length; allows you to know how many records we have (0 if no match or 1 if the email is already registered in the database).

If isResult = 1 we set the checkID and checkEmail variables with the JSON response.

if(isResult == 1){
  checkID = JSONresponse.records[0].id;
  checkEMail = JSONresponse.records[0].fields.Email;
}

The last part uses the Reminder block and our variable {Updates} to create a reminder with a custom message every 24 hours.


52%20AM

Congratulations, you have learned to use the API block to create, update and retrieve information from an Airtable database. You now know how to use dynamic content in your Speak block, ask the user for authorization and send reminders. You even wrote your first javascript code in a Code block. But most importantly, you can now restore your users’ information even after they have disabled and reactivated a skill.

Have fun, feel free to study the preview link of the skill https://creator.getvoiceflow.com/preview/15aozoDdQ6/6ac91dc6f443eacb9f36395eba15e455 and post questions and comments below.

4 Likes

You’re unbelievable!! Thanks for posting

1 Like

Thank you! Do not hesitate if this need some edits, it’still frenglish :slight_smile:

Thanks for this. I’m learning a lot.

ps: would be great to open a shared project and work in my own project on two tabs :slight_smile:

3 Likes

You’re unbelievable!! Thanks for posting

1 Like

Very good job and great description. I will rebuild that. I can learn something there. Thank you.:grinning:

1 Like

Another great tutorial, thank you! Is there a way we can use Google Sheets at this level (to post and pull specific cells) instead of Airtable? I was getting ready to go Pro with Airtable for $20 per month until I realized they charge $20 PER USER/mth (even if the user is only an editor of any doc in your base). Airtable is awesome, but I think their revenue model is an issue for anyone who is thinking about scaling their business.

1 Like

@audiorush With Google Sheet you can’t modify the data, it’s read-only with this url sharing function principle. To be able to make changes you have to go through Google APIs and that’s another story.
About Airtable, nothing prevents you from taking a pro account and sharing the table in “Editor” mode with anyone who has an Airtable account (even free).
More infos here: https://support.airtable.com/hc/en-us/articles/202625759-Adding-a-Base-Collaborator#invite-link

1 Like

True, but let’s say I select the Pro Plan at $20/mth. If I share any base with anyone (even if they just have edit access) my cost goes to $40 per month. You can give people view only access for free, but if they have any type of editing role, you have to pay for them. Airtable%202019-02-02%2020-50-57

Thanks for clarifying this!

Hi. Could not find AMAZON.YesIntent on NoIntent…

Any ideas?

You can found them in the first tab (Choices) of you interaction block.