We live in an era of collaboration and teamwork. Meaning that even the most straightforward task in this day and age (think ordering lunch, for example) is often brought before a committee vote. Thankfully, ours is also an era of great technological achievement, and there is perhaps no better way to arrive at a consensus than through the use of polling and surveys. Today we are going to see how we can use Build on Standard Library to deploy a quick and easy-to-use polling app that uses your Twilio number to dispatch SMS messages to your group, and then logs their replies in one convenient Airtable Base.

What our Airtable Base will look like upon completion!

What You’ll Need Beforehand

- 1x Airtable account — https://www.airtable.com

- 1x Twilio account — https://www.twilio.com

- 1x Standard Library Account — https://www.stdlib.com

Step 1: Setting Up Airtable

The first thing that we want to do is set up our Airtable Base so that it is ready to begin receiving data. If you are a new user, sign up for an account by visiting this link, and then click here to add our Base template to your Airtable Workspace. You should see a Base that looks like the above screenshot, but without the polarizing question already populated. As you can see, our base has three tables, two of which require some input from you, and one that will be entirely populated by Standard Library. Let’s take a closer look at what each of these tables is responsible for tracking.

Questions —This table tracks your current and past questions. Questions go in column one, and the other columns will be handled by our application

Contacts — This will be a table of individuals who are a part of your group. The only required field here is the phone number(country code + area code + phone number. 14155309876 for example). The name field is optional

Replies — This table will be entirely populated by our application. It stores the returned text message, along with who sent the reply, and which question the reply was meant for

For the sake of this tutorial, you will need to add at least one number to the contacts table (I would recommend testing with your own cell phone number) and one question to the questions table (feel free to continue the GOAT debate if you would like). Now that the Base is set up, let’s start our workflow on Standard Library!

Step 2: Build Your Workflow on Standard Library

If you haven’t done so yet, get yourself a Standard Library account (it’s free!) and head on over to https://build.stdlib.com to begin building out your workflow. For this project we will want to trigger our survey by visiting a URL, so underneath When This Event Happens choose HTTP or Webhook as your event, and proceed to put in the following options:

When This Event Happens

HTTP or Webhook → HTTP Request is sent to Project Endpoint → send-survey

This Workflow Will be Triggered

Airtable → Select Rows by querying a Base

Airtable → Select Rows by querying a Base

Twilio → Send a message

When you have finished, your screen should look like this:

You are now ready to click Create Workflow!

Step 3: Link Your Resources

The next order of business is linking your Twilio and Airtable accounts with an Identity on Standard Library. For those unfamiliar, linking a resource on Standard Library allows you to securely set up your accounts once, and then makes them available to you across all of your workflows. Let’s start by linking an Airtable account and choosing a Base. Click on the Link Resource button to be presented with the following screen:

If you have previously linked Airtable Bases, they will appear here.

If this is your first time linking your Airtable account, click the Add New Account button and input a display name on the following screen. You will also need to retrieve your API key from Airtable and include that here as well. It should look something like this:

Click on Finish and proceed to choose your Base!

Now you will arrive at a screen presenting you with all of your Bases. Choose the one that you added earlier from our template called Twilio Survey, and then click Finish [Link Base].

An inventory of all your Airtable Bases.

That takes care of Airtable, so now you can proceed to link a Twilio number to your project. The process will look very similar to the Airtable process at first, and you will see the same pop-up screen asking you to Add New Account or Link New Resource, depending on whether or not you have used Twilio on Standard Library in the past. If you see a number that you would like to use for this project here, click on the green Choose button and move on to the next step.

If you do not have a number linked yet, proceed to click either Add New Account or Link New Resource. After the loading screen finishes, you should see something like the following:

Twilio numbers purchased on Standard Library will show up here.

Note: If you have numbers that you have purchased on Twilio outside of Standard Library, you will notice that they are absent from this screen. This is the result how Twilio Connect apps work. To read more about Twilio Connect apps and sub-accounts, click here.

You can now either select a previously linked number that you have purchased through Standard Library, or purchase a new number to use for this project. Once you have done that, click on the blue Finish [Link Phone Number] button, and you will see the following:

With your Identity Generated you can now click on Next button.

Part 4: Set Up Your Workflow

The following screen is where we will configure the workflow APIs that we selected earlier. We will start with our initial query. Where the interface asks for a ‘table’ fill in ‘Contacts’. Leave all other fields blank, since we want the query to return all of the numbers in the table. Your window should now look like this:

Our first query to grab all of the numbers in our Contacts table.

Now click on the six dots next to the second row of our workflow, that says Airtable → Select Rows by querying a Base. You will see a new blank query. Fill it in with the following:

table → Questions

where → key: wasSent → select: is NULL

Click on the blue plus sign next to Add a new AND clause to this KeyQL Query operation

In this new box, enter: where → key: Status → select: is equal to → type in: Pending

Searching Questions for an eligible question.

Next, click on the six dots to the left of Twilio at the top of our dialog box. We have the option to enter up to four values here, but we only need two. In the to: field enter:

${result.step1.selectQueryResult.rows[0].fields.Number}

Then, in the body: field enter:

${result.step2.selectQueryResult.rows[0].fields.Question}

Set up the Twilio workflow like this.

Click on the green Run with Test Event button at the bottom of the dialog box and you should receive a text with the question that you entered into Airtable. If you did, then it is working!

While it is possible to do most of what we need to do in the Build interface, we will need to make some customizations to our code to store the results of multiple queries (what if our Contacts table has more than one number?). In order to do all of these things, we will need to briefly dive under the hood of Build by switching the Developer Mode button to On.

Here you have a behind the scenes look at what all your hard work has been producing. This is the code that has been generated by our workflow. Replace everything inside of here with the following snippet.

// Prepare workflow object to store API responses

let result = {};

// [Workflow Step 1]

console.log(`Running airtable.query[@0.3.3].select()...`);

result.selectContacts = await lib.airtable.query['@0.3.3'].select({
  table: `Contacts`
});

if (result.selectContacts.rows.length === 0) {
  return {'message': 'No contacts found. Please update your contacts table with valid contacts and try again.'};
}

// [Workflow Step 2]

console.log(`Running airtable.query[@0.3.3].select()...`);

result.selectQueryResult = await lib.airtable.query['@0.3.3'].select({
  table: `Questions`,
  where: [
    {
      wasSent__is: null,
      Status: `Pending`
    },
  ]
});

if (result.selectQueryResult.rows.length === 0) {
  return {'message': 'No valid questions found. Please check the questions table and try again.'};
} else if (result.selectQueryResult.rows[0].fields.Question === null) {
  return {'message': 'Whoops, looks like you left the question blank!' };
}

// [Workflow Step 3]
let seen = new Set();
let nums = result.selectContacts.rows.reduce((acc, cur) => {
  if (!seen.has(cur.fields.Number)) {
    acc.push(cur);
    seen.add(cur.fields.Number);
  }
  return acc;
}, []);

console.log(`Running twilio.messages[@0.1.0].create()...`);
for (let row of nums) {
  result.result = await lib.twilio.messages['@0.1.0'].create({
    from: null,
    to: `${row.fields.Number}`,
    body: `Your friend sent you a Twilio Survey:` + `\n` + `\n` + `${result.selectQueryResult.rows[0].fields.Question}`,
    mediaUrl: null
  }).catch(err => {
    console.log(`Oops, not a valid number!`);
  });
};

// [Workflow Step 4]

console.log(`Running airtable.query[@0.3.3].update()...`);

result.updateQueryResult = await lib.airtable.query['@0.3.3'].update({
  table: `Questions`, // required
  where: [
    {
      Question: `${result.selectQueryResult.rows[0].fields.Question}`
    }
  ],
  fields: {
    wasSent: true
  }
});

Note: Please be aware that toggling Developer Mode to Off will cause all of your changes to be lost, so best to keep it activated until we have shipped the project.

The first half of this will allow us to grab 1. all of the numbers that we have included in the Contacts table, and 2. the question that we want to send from the Questions table. Note that the criteria for selecting a question is that the wasSent column is unchecked (null), and that the Status column reads Pending. In the event that there are two or more questions that match this query, only the most recently added one will be sent.

This second half of this code allows us to perform the Send a message action from our linked Twilio account to every user that we have inserted into our Contacts table, and then changes the wasSent value for the question to true. Now click on the green Run with Test Event button again, and you should receive a message with your first question!

Let the debate begin.

If you received the text message, and if the wasSent column in your table was updated to true (that is to say, the column now has a green check!), then proceed to click on the blue Next button. On this final page you will name your project (name it twilio-survey) and then go ahead and click on the blue Alright, Ship It! button. You will receive a message informing you that you are awesome. Well done!

It is important to note before moving ahead to the next section that in order to conduct our survey going forward, you will need to ping the URL that is being generated during this step. If you recall, when we began setting up our workflow we decided that HTTP Request is sent to Project Endpoint would be the event that triggers these actions. Meaning that if you ever want to conduct another survey, you will need to ping the URL generated by this workflow. It will look like this:

https://<Your-Username>.api.stdlib.com/twilio-survey@dev/send-message/

Where <Your-Username> is replaced with your Standard Library account name. twilio-survey is the name of our project, and send-message is the name of the endpoint that we set up when we determined our event.

Part 5: Storing Replies

We now need some way to track replies. This will require setting up a new event in our project to insert SMS messages to our Twilio number into our Airtable Base. Navigate back into your project by clicking on the dev (click to manage) link on your project’s homepage:

Find the box that enables you to add new events. It will be located just under the previous workflow that you created. You will see greyed out text that reads Event Source and When this Event happens…

For this workflow, we want to choose:

Twilio → sms.received

Your Integrations section should now look like this:

After you have clicked on [+] Add New Workflow, set your workflow up as follows on the dialog page:

Airtable → Select Rows by querying a Base

Airtable → Select Rows by querying a Base

Airtable → Insert a row into a Base

Proceed by clicking Next, and you should see the previously linked resources. Feel free to simply click Next. Here, we will once again be taking the plunge into Developer Mode. Toggle this to On, and paste the following snippet into the editable portion of the box:

// Prepare workflow object to store API responses

let result = {};

// [Workflow Step 1]

console.log(`Running airtable.query[@0.3.4].select()...`);

result.selectQuestion = await lib.airtable.query['@0.3.4'].select({
  table: `Questions`,
  where: [
    {
      wasSent__is: true,
      Status__is: `Pending`
    }
  ]
});

// [Workflow Step 2]

let n = event.From.split('+');
let num = parseInt(n[1]);

result.selectContact = await lib.airtable.query['@0.3.4'].select({
  table: `Contacts`,
  where: [
    {
      Number: num
    }
  ]
});

// [Workflow Step 3]

let contact = result.selectContact.rows[0].id;
let question = result.selectQuestion.rows[0].id;

repliesQueryResult = await lib.airtable.query['@0.3.4'].select({
  table: `Replies`,
  where: [
    {
      Respondent__contains: contact,
      Question__contains: question
    }
  ]
});

if (repliesQueryResult.rows.length === 0) {
  result.insertQueryResult = await lib.airtable.query['@0.3.4'].insert({
    table: `Replies`,
    fields: {
      Reply: `${event.Body}`,
      Respondent: [contact],
      Question: [question]
    }
  });

  await lib.twilio.messages['@0.1.0'].create({
    from: null,
    to: `${event.From}`,
    body: `Thanks for submitting your reply! Your response has been logged.`,
    mediaUrl: null
  });
};

Let’s cover quickly what we are doing here:

1. We are querying the Base to determine which question is currently active
2. We are finding the user associated with the responding phone number
3. We are creating a new record in the Replies table, and linking this reply to the previously selected question and user, respectively

Testing this code will result in an error, however, so we need to make one change to the test event.

If you click the gear icon next to the Run with Test Environment button, you will see something that looks like this:

Here, we need to update our From phone number.

Our workflow is attempting to find a user that has the number associated with the ‘From’ key inside this event. Change this value (“+15555555555”), to a number that is present in your Contacts table (i.e. your phone number with a preceding “+”, like so: “+18155451993”. The “+” and the country code are required here). Leave all the other values as they are, and attempt to run the test again. If you now see the message “Hello from Twilio!” in your Replies table, then congrats! You’re all finished. Proceed to click on Next, and finally Ship It!

Only the first reply sent by each user will be acknowledged and stored.

You’re All Set!

That is it! Sit back and watch the replies come rolling in. As we mentioned earlier in this tutorial, ensure that there is only one question that carries a Status of Pending, with a wasSent value of true, as the first question to meet both of these criteria will be the one that replies are logged to. When you feel that a question has ample replies, simply change the Status field for that question to Finished, and then proceed to add a new question. Navigate to your URL endpoint to deliver the new question. Happy polling!

Kevin Brimmerman is a Software Engineer at Standard Library. Outside of work he is an avid runner and a die-hard Chicago sports fan. Go Cubs!