Using Airtable and the Code block to generate a list and use it in a Display block

In this tutorial we will use an Airtable database and an API block to retrieve the first 4 results of a search, process them in a Code block and display them in a list thanks to the Display block and an APL template.

I assume that you have already created an account and generated an API key from your Airtable account. If this is not the case, you can follow the procedure from this other tutorial:

As in the previous tutorials, we will start by creating our base in Airtable,
let’s name it “Cheese List” (but you can use the name you want).


Open your database and rename the table (I used “Cheeses” for this tutorial)

21%20AM

Double click on the first field (Name) to rename it to ID and select a field type “Autonumber”.

Rename the “Notes” and “Attachments” fields to “Name” and “ServingSize”.
For these two fields, choose “Single line text” as the field type.


Add a “Calories” and a “Country” field of type “Single line text

40%20AM

Finally, add an “ImgURL” field of type “URL”.

Your base should now look like this:

Let’s take this opportunity to delete the three records created by default.

Now we will add information to this database. To help you, you can copy the ones in my shared database here: https://airtable.com/shrDMzg6rBMSW0ZXW

Of course, the Serving Size and Calories data are only there for the example.

As we are used to doing now, use the “API documentation” link in the help menu of your Airtable database to retrieve the information needed for our API block.

We copy for later our base url and our API key.

Mine looks like this:

https://api.airtable.com/v0/appjeGf3G60korH1x/Cheeses?api_key=keyY6PccqvLBwFzSi

We are good for the configuration of the Airtable database, now let’s move on to our Voiceflow skill.

We create a new skill from the Black template

Here is what our skill will look like

And as always to help you, the preview link:
https://creator.getvoiceflow.com/preview/jMmYKPLdQE/2e79ae6094c40f8bae82a28cc3ab948f

With the variables to create in your skill

So we start with a welcome message that asks the user to give us a country to do a search

The AMAZON.Country slot is used here

We also used the new Reprompt function available from the settings of the Interaction block.

The Intents

And we map the answer to our variable {country}

The next block is just there to format the answer correctly so that it matches our data in Airtable. For example, if I get “france” as a result, it is modified to “France” to respect the format of the Country field in our database.

Nothing very complicated here, a little javascript function to transform the first letter of the user’s answer into capital letter.

For the API block, as in the previous tutorials, we will use the SEARCH formula to find records in our Airtable database.

For the method it is GET and your url should look something like this (don’t forget to change your database name appjeGf3G60korH1x and API key)

https://api.airtable.com/v0/appjeGf3G60korH1x/Cheeses?maxRecords=4&filterByFormula=SEARCH('{country}',%20%7BCountry%7D)&api_key=keyY6PccqvLBwFzSi

Here we use maxRecords=4 because we want a maximum of 4 results.

Finally, don’t forget to map the response to JSONresponse

The following code allows us to know the number of results and to carry out the actions accordingly

Just before The Magic block, we need to create the sourceArray within the Create the source block. Do do this, just add the following code in this block:

Screen%20Shot%20on%202019-03-07%20at%2010-25-22

sourceArray = [
  {
    "listItemIdentifier": "gouda",
    "ordinalNumber": 1,
    "textContent": {
      "primaryText": {
        "type": "PlainText",
        "text": "Gouda"
      },
      "secondaryText": {
        "type": "PlainText",
        "text": "Serving Size: 1oz (28g)"
      },
      "tertiaryText": {
        "type": "PlainText",
        "text": "100 cal"
      }
    },
    "image": {
      "contentDescription": null,
      "smallSourceUrl": null,
      "largeSourceUrl": null,
      "sources": [
        {
          "url": "https://d2o906d8ln7ui1.cloudfront.net/images/sm_gouda.png",
          "size": "small",
          "widthPixels": 0,
          "heightPixels": 0
        },
        {
          "url": "https://d2o906d8ln7ui1.cloudfront.net/images/sm_gouda.png",
          "size": "large",
          "widthPixels": 0,
          "heightPixels": 0
        }
      ]
    },
    "token": "gouda"
  },
  {
    "listItemIdentifier": "cheddar",
    "ordinalNumber": 2,
    "textContent": {
      "primaryText": {
        "type": "PlainText",
        "text": "Sharp Cheddar"
      },
      "secondaryText": {
        "type": "RichText",
        "text": "Serving Size: 1 slice (28g)"
      },
      "tertiaryText": {
        "type": "PlainText",
        "text": "113 cal"
      }
    },
    "image": {
      "contentDescription": null,
      "smallSourceUrl": null,
      "largeSourceUrl": null,
      "sources": [
        {
          "url": "https://d2o906d8ln7ui1.cloudfront.net/images/sm_cheddar.png",
          "size": "small",
          "widthPixels": 0,
          "heightPixels": 0
        },
        {
          "url": "https://d2o906d8ln7ui1.cloudfront.net/images/sm_cheddar.png",
          "size": "large",
          "widthPixels": 0,
          "heightPixels": 0
        }
      ]
    },
    "token": "cheddar"
  },
  {
    "listItemIdentifier": "blue",
    "ordinalNumber": 3,
    "textContent": {
      "primaryText": {
        "type": "PlainText",
        "text": "Blue"
      },
      "secondaryText": {
        "type": "RichText",
        "text": "Serving Size: 1c, crumbled (135g)"
      },
      "tertiaryText": {
        "type": "PlainText",
        "text": "476 cal"
      }
    },
    "image": {
      "contentDescription": null,
      "smallSourceUrl": null,
      "largeSourceUrl": null,
      "sources": [
        {
          "url": "https://d2o906d8ln7ui1.cloudfront.net/images/sm_blue.png",
          "size": "small",
          "widthPixels": 0,
          "heightPixels": 0
        },
        {
          "url": "https://d2o906d8ln7ui1.cloudfront.net/images/sm_blue.png",
          "size": "large",
          "widthPixels": 0,
          "heightPixels": 0
        }
      ]
    },
    "token": "blue"
  },
  {
    "listItemIdentifier": "brie",
    "ordinalNumber": 4,
    "textContent": {
      "primaryText": {
        "type": "PlainText",
        "text": "Brie"
      },
      "secondaryText": {
        "type": "RichText",
        "text": "Serving Size: 1oz (28g)"
      },
      "tertiaryText": {
        "type": "PlainText",
        "text": "95 cal"
      }
    },
    "image": {
      "contentDescription": null,
      "smallSourceUrl": null,
      "largeSourceUrl": null,
      "sources": [
        {
          "url": "https://d2o906d8ln7ui1.cloudfront.net/images/sm_brie.png",
          "size": "small",
          "widthPixels": 0,
          "heightPixels": 0
        },
        {
          "url": "https://d2o906d8ln7ui1.cloudfront.net/images/sm_brie.png",
          "size": "large",
          "widthPixels": 0,
          "heightPixels": 0
        }
      ]
    },
    "token": "brie"
  },
  {
    "listItemIdentifier": "cheddar",
    "ordinalNumber": 5,
    "textContent": {
      "primaryText": {
        "type": "PlainText",
        "text": "Cheddar"
      },
      "secondaryText": {
        "type": "RichText",
        "text": "Serving Size: 1oz (28g)"
      },
      "tertiaryText": {
        "type": "PlainText",
        "text": "113 cal"
      }
    },
    "image": {
      "contentDescription": null,
      "smallSourceUrl": null,
      "largeSourceUrl": null,
      "sources": [
        {
          "url": "https://d2o906d8ln7ui1.cloudfront.net/images/sm_cheddar.png",
          "size": "small",
          "widthPixels": 0,
          "heightPixels": 0
        },
        {
          "url": "https://d2o906d8ln7ui1.cloudfront.net/images/sm_cheddar.png",
          "size": "large",
          "widthPixels": 0,
          "heightPixels": 0
        }
      ]
    },
    "token": "cheddar"
  },
  {
    "listItemIdentifier": "parm",
    "ordinalNumber": 6,
    "textContent": {
      "primaryText": {
        "type": "PlainText",
        "text": "Parm"
      },
      "secondaryText": {
        "type": "RichText",
        "text": "Serving Size: 1oz (28g)"
      },
      "tertiaryText": {
        "type": "PlainText",
        "text": "122 cal"
      }
    },
    "image": {
      "contentDescription": null,
      "smallSourceUrl": null,
      "largeSourceUrl": null,
      "sources": [
        {
          "url": "https://d2o906d8ln7ui1.cloudfront.net/images/sm_parm.png",
          "size": "small",
          "widthPixels": 0,
          "heightPixels": 0
        },
        {
          "url": "https://d2o906d8ln7ui1.cloudfront.net/images/sm_parm.png",
          "size": "large",
          "widthPixels": 0,
          "heightPixels": 0
        }
      ]
    },
    "token": "parm"
  }
];

Now it’s time for The Magic block :wink:

//For loop to pass the JSON data from our Airtable database
//into the sourceArray

//Init of the card
var countCard = 1;
cardText = "";

for (i = 0; i < JSONresponse.records.length; i++) { 
  
  sourceArray[i].listItemIdentifier = JSONresponse.records[i].fields.Name.toLowerCase();
  sourceArray[i].textContent.primaryText.text = JSONresponse.records[i].fields.Name;
  sourceArray[i].textContent.secondaryText.text = "Serving Size: "+JSONresponse.records[i].fields.ServingSize;
  sourceArray[i].textContent.tertiaryText.text = JSONresponse.records[i].fields.Calories;
  sourceArray[i].image.sources[0].url = JSONresponse.records[i].fields.ImgURL;
  sourceArray[i].image.sources[1].url = JSONresponse.records[i].fields.ImgURL;
  
  //Making the message for the Card block
  cardText = cardText + countCard + ") "+JSONresponse.records[i].fields.Name + " | " + JSONresponse.records[i].fields.Calories + " | " + "Serving Size: "+JSONresponse.records[i].fields.ServingSize + "\n\r";
  countCard++;
}

//Resizing the Array if less than 4 records
sourceArray.length = JSONresponse.records.length;

//Convert the Array in JSON format to use it in APL code.
sourceArray = JSON.stringify(sourceArray);

In this code, we loop the number of results and modify the sourceArray variable that allows us to generate a list in our APL template.
The same thing is done for the Card block but by simplifying and deleting some information.
By default our sourceArray contains 4 arrays, if our search returns only 2 results for example we will have to reduce the size of our array. Finally, we convert everything into an acceptable format for our APL code.

Once our sourceArray and cardText variables are ready, we use them in the Card block and Display block.

50%20PM

For the Display Block, we will create an APL template from the Visuals tab in Voiceflow

and click on “New Display” or the “+” on the page

57%20PM
or

Before going further, download the following Template file:
https://s3-eu-west-3.amazonaws.com/gallagan/apl_list_template.json

On the APL Template screen, rename your template as you wish and click on the “Upload JSON File” button.

Select the file “apl_list_template.json” that you have just downloaded and click on “Open”.

In the right part, scroll down to the bottom of the code and replace “REPLACE ME” with {sourceArray}

Click Save and return to the Canvas

11%20PM

And to finish with the Display Block, select the Template you just created in the options

46%20PM

Last, adding an Intent block to manage “one shot invocation name” such as:
Alexa, ask cheeses list demo for cheese from France

png

44%20PM

Well done! You have just finished your skill.

As usual, do not hesitate if you have any questions or comments. Bon appétit !

2 Likes

Thank you for this tutorial. Very informative. However i get a prompt to initialize the {country} variable when I set up theAPI call to Airtable.

Here is my call: https://api.airtable.com/v0/appeDhH3Qq0GK7uiF/Cheeses?maxRecords=4&filterByFormula=SEARCH(’{country}’,%20{Country%7)&api_key=keyXXXXXXXX

and here is the result:

We’ve detected you are using variables in your API call, please set variables and run

Any thoughts would be appreciated.

Yes, this is for the Endpoint testing, just put anything you want to search in the Airtable database (use France for example). When you run the skill on Alexa Dev Console or a device this {country} variable will be populated with the user response from the
previous interaction block.

1 Like

Thank you for the quick response. Would it be possible to share the flow with me? Whenever I try the link, it’;s always in use and I’m locked out.

1 Like

Yes, for now the best way to open the preview link is to create another account on Voiceflow and open the link with this new account.

Sorry about that, the team is working on a fix for this.

1 Like

Ok. Much progress since last post however. I’m having difficulty downloading
https://s3-eu-west-3.amazonaws.com/gallagan/apl_list_template.json

I get:

Thank you.

Can you try again? I’ve updated it.

1 Like

Hi. I’m just wondering how the “Create the Source” code block could be modified to account for a larger number of records? Also, is there any way to not hard code the various database values (cheese, serving size, etc) and retrieve them from Airtable.

Overall, a great tutorial. Keep them coming! :+1::+1:

I’ve tried to replicate this code and I’m getting:

Failed to Execute Code "TypeError: Cannot read property 'textContent' of undefined"

It’s like defining the variable sourceArray is not enough. Any ideas?

You don’t need to modify the code, only the get url: https://api.airtable.com/v0/appjeGf3G60korH1x/Cheeses?maxRecords=4&amp;filterByFormula=SEARCH('{country}',%20%7BCountry%7D)&amp;api_key=keyY6PccqvLBwFzSi What you’re looking for is the maxRecords=4 and you can change it to maxRecords=15 for example. The APL list is scrollable so you will have the full list. I’ve added a french cheese to the Airtable database if you want to give it a try with more than 4 cheeses. About the values, you can remove them from the skill and add them directly in your Airtable database.
So for example, replace this line:

sourceArray[i].textContent.secondaryText.text = "Serving Size: "+JSONresponse.records[i].fields.ServingSize;

with this line sourceArray[i].textContent.secondaryText.text = JSONresponse.records[i].fields.ServingSize;

Have you added the variable in Voiceflow?

Yes, I’d added the variables, mostly sourceArray.

What I don’t understad is .textContent.primaryText.text , textContent.secondaryText.text…
Where is this property definition about? where is the documentation about it?
How is sourceArray getting these properties?

Actually here, we set the property. This is the “list” format we use for the APL template.
If you go here: https://developer.amazon.com/alexa/console/ask/displays
and you choose the list template

in the Data JSON source, you can see the listItems array format we need to use with this template.

That’s what we are doing with the code:

for (i = 0; i < JSONresponse.records.length; i++) { 
  
  sourceArray[i].listItemIdentifier = JSONresponse.records[i].fields.Name.toLowerCase();
  sourceArray[i].textContent.primaryText.text = JSONresponse.records[i].fields.Name;
  sourceArray[i].textContent.secondaryText.text = "Serving Size: "+JSONresponse.records[i].fields.ServingSize;
  sourceArray[i].textContent.tertiaryText.text = JSONresponse.records[i].fields.Calories;
  sourceArray[i].image.sources[0].url = JSONresponse.records[i].fields.ImgURL;
  sourceArray[i].image.sources[1].url = JSONresponse.records[i].fields.ImgURL;
  
  //Making the message for the Card block
  cardText = cardText + countCard + ") "+JSONresponse.records[i].fields.Name + " | " + JSONresponse.records[i].fields.Calories + " | " + "Serving Size: "+JSONresponse.records[i].fields.ServingSize + "\n\r";
  countCard++;
}

Hi,

I’ve tried to replicate what you say and I’m getting:
"TypeError: Cannot set property 'listItemIdentifier' of undefined"

I’ve been digging into javascript a bit, and it seems weird to me to build the object doing:

sourceArray[i].listItemIdentifier =…

Don’t we have to declare first the object inside the array {listItemIdentifier, textContent: { primaryText … ?
I mean, where is the declaration in this example? because I feel my code crashes because it cannot crate the object dynamically.

Are you using the same Airtable base example?

Not exactly, but I’m using the same datatypes (text fields) in my own table… And my API that retrieved data from the table works.
My feeling is that javscritpt doesn’t allow to create an object from scratch… I’ve tried in other example with the push function, and then I can initialize the array… But I cannot manage to do it this way you show here.

Ok, that’s why it’s not working for you. As you have change the names of your fields in Airtable, you need to do the same in your code. Ex. JSONresponse.records[i].fields.Name to JSONresponse.records[i].fields.Nameofyourfield

I’ve changed those names… It’s the other side what is not working:
sourceArray[i].listItemIdentifier =…

I’m on holiday right now so I don’t have access to my computer in the day but I will have a look at it tonight. Did you use the Intercom (chat) in Voiceflow before? Just to know if I can get your profile info from there.

No, I haven’t used Intercom before