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 to deploy a quick and easy-to-use polling app that uses your number to dispatch SMS messages to your group, and then logs their replies in one convenient Base. Build on Standard Library Twilio Airtable 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 , and then 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. visiting this link click here to add our Base template to your Airtable Workspace. —This table tracks your current and past questions. Questions go in column one, and the other columns will be handled by our application Questions — This will be a table of individuals who are a part of your group. The only required field here is the phone number( + + 14155309876 for example). The name field is optional Contacts country code area code phone number. — 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 Replies 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 (it’s free!) and head on over to to begin building out your workflow. For this project we will want to trigger our survey by visiting a URL, so underneath choose as your event, and proceed to put in the following options: Standard Library account https://build.stdlib.com When This Event Happens HTTP or Webhook 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 button to be presented with the following screen: Link Resource If you have previously linked Airtable Bases, they will appear here. If this is your first time linking your Airtable account, click the button and input a display name on the following screen. You will also need to and include that here as well. It should look something like this: Add New Account retrieve your API key from Airtable Click on and proceed to choose your Base! Finish 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 and then click Twilio Survey, 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 or , 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 button and move on to the next step. Add New Account Link New Resource Choose If you do not have a number linked yet, proceed to click either or After the loading screen finishes, you should see something like the following: Add New Account Link New Resource. Twilio numbers purchased on Standard Library will show up here. 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, Note: 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 button, and you will see the following: Finish [Link Phone Number] With your you can now click on button. Identity Generated Next 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 ‘ ’ fill in ‘ ’. 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: table Contacts Our first query to grab all of the numbers in our table. Contacts Now click on the six dots next to the second row of our workflow, that says → You will see a new blank query. Fill it in with the following: Airtable Select Rows by querying a Base. table → Questions where → key: → select: wasSent is NULL Click on the next to blue plus sign Add a new AND clause to this KeyQL Query operation In this new box, enter: where → key: → select: → type in: Status is equal to Pending Searching for an eligible question. Questions Next, click on the six dots to the left of 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 field enter: Twilio to: ${result.step1.selectQueryResult.rows[ ].fields.Number} 0 Then, in the field enter: body: ${result.step2.selectQueryResult.rows[ ].fields.Question} 0 Set up the Twilio workflow like this. Click on the green 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! Run with Test Event 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 button to . Developer Mode 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. result = {}; .log( ); result.selectContacts = lib.airtable.query[ ].select({ : }); (result.selectContacts.rows.length === ) { { : }; } .log( ); result.selectQueryResult = lib.airtable.query[ ].select({ : , : [ { : , : }, ] }); (result.selectQueryResult.rows.length === ) { { : }; } (result.selectQueryResult.rows[ ].fields.Question === ) { { : }; } seen = (); nums = result.selectContacts.rows.reduce( { (!seen.has(cur.fields.Number)) { acc.push(cur); seen.add(cur.fields.Number); } acc; }, []); .log( ); ( row nums) { result.result = lib.twilio.messages[ ].create({ : , : , : + + + , : }).catch( { .log( ); }); }; .log( ); result.updateQueryResult = lib.airtable.query[ ].update({ : , where: [ { : } ], : { : } }); // Prepare workflow object to store API responses let // [Workflow Step 1] console `Running airtable.query[@0.3.3].select()...` await '@0.3.3' table `Contacts` if 0 return 'message' 'No contacts found. Please update your contacts table with valid contacts and try again.' // [Workflow Step 2] console `Running airtable.query[@0.3.3].select()...` await '@0.3.3' table `Questions` where wasSent__is null Status `Pending` if 0 return 'message' 'No valid questions found. Please check the questions table and try again.' else if 0 null return 'message' 'Whoops, looks like you left the question blank!' // [Workflow Step 3] let new Set let ( ) => acc, cur if return console `Running twilio.messages[@0.1.0].create()...` for let of await '@0.1.0' from null to ` ` ${row.fields. } Number body `Your friend sent you a Twilio Survey:` `\n` `\n` ` ` ${result.selectQueryResult.rows[ ].fields.Question} 0 mediaUrl null => err console `Oops, not a valid number!` // [Workflow Step 4] console `Running airtable.query[@0.3.3].update()...` await '@0.3.3' table `Questions` // required Question ` ` ${result.selectQueryResult.rows[ ].fields.Question} 0 fields wasSent true Please be aware that toggling to will cause all of your changes to be lost, so best to keep it activated until we have shipped the project. Note: Developer Mode Off The first half of this will allow us to grab 1. all of the numbers that we have included in the table, and 2. the question that we want to send from the table. Note that the criteria for selecting a question is that the column is (null), and that the column reads In the event that there are two or more questions that match this query, Contacts Questions wasSent unchecked Status Pending. only the most recently added one will be sent. This second half of this code allows us to perform the action from our linked Twilio account to every user that we have inserted into our table, and then changes the value for the question to . Now click on the green button again, and you should receive a message with your first question! Send a message Contacts wasSent true Run with Test Event Let the debate begin. If you received the text message, and if the column in your table was updated to (that is to say, the column now has a green check!), then proceed to click on the blue button. On this final page you will name your project (name it ) and then go ahead and click on the blue button. You will receive a message informing you that you are awesome. Well done! wasSent true Next twilio-survey Alright, Ship It! It is important to note before moving ahead to the next section that in order to conduct our survey going forward, . If you recall, when we began setting up our workflow we decided that 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: you will need to ping the URL that is being generated during this step HTTP Request is sent to Project Endpoint https: //<Your-Username>.api.stdlib.com/twilio-survey@dev/send-message/ Where <Your-Username> is replaced with your Standard Library account name. is the name of our project, and is the name of the endpoint that we set up when we determined our event. twilio-survey send-message 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 link on your project’s homepage: dev (click to manage) 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 and Event Source When this Event happens… For this workflow, we want to choose: Twilio → sms.received Your section should now look like this: Integrations After you have clicked on set your workflow up as follows on the dialog page: [+] Add New Workflow, Airtable → Select Rows by querying a Base Airtable → Select Rows by querying a Base Airtable → Insert a row into a Base Proceed by clicking , and you should see the previously linked resources. Feel free to simply click . Here, we will once again be taking the plunge into . Toggle this to , and paste the following snippet into the editable portion of the box: Next Next Developer Mode On result = {}; .log( ); result.selectQuestion = lib.airtable.query[ ].select({ : , : [ { : , : } ] }); n = event.From.split( ); num = (n[ ]); result.selectContact = lib.airtable.query[ ].select({ : , : [ { : num } ] }); contact = result.selectContact.rows[ ].id; question = result.selectQuestion.rows[ ].id; repliesQueryResult = lib.airtable.query[ ].select({ : , : [ { : contact, : question } ] }); (repliesQueryResult.rows.length === ) { result.insertQueryResult = lib.airtable.query[ ].insert({ : , : { : , : [contact], : [question] } }); lib.twilio.messages[ ].create({ : , : , : , : }); }; // Prepare workflow object to store API responses let // [Workflow Step 1] console `Running airtable.query[@0.3.4].select()...` await '@0.3.4' table `Questions` where wasSent__is true Status__is `Pending` // [Workflow Step 2] let '+' let parseInt 1 await '@0.3.4' table `Contacts` where Number // [Workflow Step 3] let 0 let 0 await '@0.3.4' table `Replies` where Respondent__contains Question__contains if 0 await '@0.3.4' table `Replies` fields Reply ` ` ${event.Body} Respondent Question await '@0.1.0' 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: We are querying the Base to determine which question is currently active We are finding the user associated with the responding phone number We are creating a new record in the table, and linking this reply to the previously selected question and user, respectively Replies 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 button, you will see something that looks like this: Run with Test Environment Here, we need to update our phone number. From Our workflow is attempting to find a user that has the number associated with the ‘ ’ key inside this event. Change this value ( ), to a number that is present in your table (i.e. your phone number with a preceding “+”, like so: . 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 “ ” in your table, then congrats! You’re all finished. Proceed to click on , and finally From “+15555555555” Contacts “+18155451993” Hello from Twilio! Replies Next 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 with a wasSent value of , 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 , and then proceed to add a new question. Navigate to your URL endpoint to deliver the new question. Happy polling! Pending, true Finished 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!
