Airtable Recipe Database

I've been working on cataloging all of my cocktail books and recipes from various sources into a database of sorts using Airtable. I chose to go with Airtable as it's quick, easy to use, and easily filterable and searchable using my phone when I'm at my home bar. Jun 30, 2016 In the Recipes table, he lists each recipe that appears during the season and links it to the appropriate episode in the 'Show' linked record field. The table also displays the status of the edit for that recipe, the last editor to work on it, the current length, the date last edited, and any changes that have been requested.

1. Air Table Recipe Database Template
2. Recipe Database In Access
3. Airtable Sign In

'Air Hockey' photo by LadyDucayne is licensed under CC BY 2.0.

Airtable is an awesome product. It's kind of like a cross between a spreadsheet and a database, but with a lot more flexibility for creating dynamic views of your data. In this post you'll learn to read Airtable data from a Twilio serverless application. The advantages of doing so are as follows:

• Rapid prototyping. If you need to persistently store data, it's easier to create an Airtable base than to set up a proper database.
• Maintenance flexibility. Folks who don't know how to code can easily maintain and extend the data.
• Easy authentication. Sure, you could use Google Sheets as a lightweight database, but authenticating with Google Sheets API can be tricky.

Today we'll be building an app that returns facts about animals, but you can customize this code to fit your use case.

Prerequisites

• A Twilio account -- sign up for a free one here
• A free Airtable account
• A Twilio phone number with SMS capabilities

Airtable 101

In order to use Airtable, you need to be familiar with a few key concepts. If you're already an Airtable whiz, feel free to skip to the 'Reading From Airtable with a serverless function' section.

• Bases. According to Airtable documentation, 'A base is a database that contains all of the information you need for a specific project, and is made up of one or more tables.'
• Bases are made up of one or more tables, which are essentially like different sheets or tabs of a spreadsheet.
• Records are units of data, analogous to a row on a spreadsheet.
• Information in records is stored in fields, which are kind of like a column on a spreadsheet.
• Workspaces are folders where you can organize your bases so that other folks can collaborate with you.

To create a new base you can:

• Choose one of Airtable's many official templates
• Choose a template created by the Airtable community
• Start from scratch

I've created a small base called 'Cute Animals' that we'll be working with today. You can view it here and create your own copy with the 'Copy base' button on the right-hand side.

This base contains a 'Cute Animals' table that has 3 fields:

• Name, a single line text field
• Genus, a link to another record (in the Genus table)
• Fact, a long text field

It also contains a 'Genus' table that has 2 fields:

• Name, a single line text field
• Cute Animals, a link to another record (in the 'Cute Animals' table)

While you're in Airtable, let's get some credentials. Generate an Airtable API key and save it somewhere -- you'll need it in a minute.

Be careful not to commit your Airtable API key to any code that's going up on GitHub, or share it with anybody. If you do they can gain full access to your Airtable account.

Grab your Airtable base ID from the (nifty!) auto-generated API documentation. Save that too.

Reading Airtable data from a serverless function

We'll use a Twilio Function to receive an incoming SMS message and fetch data from our Airtable base. Open the Functions configuration dashboard. Add your Airtable API key and base IDs as environment variables, AIRTABLE_API_KEY. And AIRTABLE_BASE_ID respectively.

Air Table Recipe Database Template

While you're at it, add the Airtable Node.js SDK to the Dependencies section of the Functions configuration dashboard. Here we're using version ^0.8.1 (that is, 0.8.1 or the latest minor patch.) Hit 'Save' to save all your configuration changes.

Let's write a Function that, given an animal name, returns the animal fact. This will show you how to filter airtable records by field for a simple text search. Using the blank template, create a new Function called getAnimalFact. Give it a path, /get-animal-fact. Copy the following code into it:

What is even happening here? Let's break it down.

• First, we instantiate the airtable base.
• The select method allows you to select your Airtable view, which returns the records in the order listed in that view. Called with no args, records are returned in random order.
• The all method is asynchronous and returns every record in the view. Which is okay if you have a small number of records, but for larger bases you may want to paginate using the eachPage function. See the Airtable auto-generated API docs for an example.
• Then we iterate through the records until we find a matching one, grab data from the field we want, and wrap it in some good old-fashioned TwiML so it can be returned as a message.
• This code returns the Fact field of the first matching record.

After adding this code, save your Function and wait for it to deploy itself. On the phone numbers dashboard, configure your Twilio number to call this Function when a new message comes in.

Test it out by texting 'Cat' to your Twilio number. You should receive the following response:

Testing error paths is important too. If we try the same with zebra, we get the following output:

Fetching linked record data from Airtable

The real power of Airtable is that you can have many different field types, including:

• URLs
• Dates
• Phone numbers
• Email addresses
• Links to other records

See a full list of Airtable field types at this link.

Links to other records are powerful, and give you lightweight relational database functionality. Let's add the Genus field, which is a link to another record, to our SMS response.

Fetching the linked data requires an additional API call. In the code below, we'll use the find method to fetch one record with a specific ID. Replace the code in your Function with the following:

Save your Function again.

Text dog to the number and witness the following response:

Conclusion: backing your Twilio app with Airtable data

In this post, you've learned to:

• Fetch plain text data and linked records using the Airtable Node.js SDK
• Create a Twilio app backed backed by a serverless Function and an Airtable base

I've heard about so many awesome Airtable projects recently, such as this mutual aid tool to help community groups keep people fed during the covid crisis. If you're building something cool I'd love to hear from you. Hit me up on Twitter or send me an email: tthurium [at] twilio [dot] com.

Airtable is a cloud collaboration service headquartered in San Francisco. It was founded in 2012 by Howie Liu, Andrew Ofstad, and Emmett Nicholas.

Airtable is a spreadsheet-database hybrid, with the features of a database but applied to a spreadsheet. The fields in an Airtable table are similar to cells in a spreadsheet, but have types such as 'checkbox', 'phone number', and 'drop-down list', and can reference file attachments like images.^[1][2]

Users can create a database, set up column types, add records, link tables to one another, collaborate, sort records and publish views to external websites.

History[edit]

• 2012: Airtable founded.^[3]
• Feb 2015: Raised $3 million from Caffeinated Capital, Freestyle Capital, Data Collective, CrunchFund.^[4]
• April 2015: Airtable launches its API and embedded databases.^[5]
• May 2015: Raised $7.6 million funding from Charles River Ventures and Ashton Kutcher.^[6]
• July 2015: Introduced Airtable Forms to collect and organize data.^[7]
• August 2015: Airtable made 'Add to Slack' option available to integrate Airtable with Slack.^[8]
• December 2015: Airtable redesigned its iOS app.^[9]
• December 2015: Airtable introduced barcode as new field type.^[10]
• March 2018: Raised $52 million in Series B funding; announced the launch of Airtable Blocks.^[11]
• November 2018: Raised $100 million in Series C funding.^[12]
• September 2020: Raised $185 million in Series D funding.^[13]

• March 2021: Raised $270 million in Series E funding.^[14]

Basic components[edit]

Airtable has six basic components:^[15]

1. Bases: All the information needed to create a project is contained in a Base. Bases can be built from existing templates provided by Airtable. They can also be built from scratch, from a spreadsheet or from an existing Base.
2. Tables: A table is similar to a spreadsheet. A base is a collection of tables.
3. Views: Views show the result sets of data queries and can be saved for future purposes.
4. Fields: Each entry in a table is a field. They are not just restricted to hold text. Airtable currently offers 16 basic field types.^[16] These are: single-line texts, long text articles, file attachments, check-boxes, single select from drop-down list, multiple-selects from drop-down lists, date and time, phone numbers, email ids, URLs, numbers, currency, percentage, auto-number, formulae and barcodes.^[17]
5. Records: Each row of a Table is a Record.
6. Workspaces: A workspace is a collection of Bases in Airtable.

Key features[edit]

Linking between tables[edit]

To avoid the need to form a single large table when there is related data in multiple tables, Airtables provides an option to link records of different tables. Airtable allows linking existing tables of related records, creating a new linked table and also multiple links between existing tables.^[18]

Collaboration[edit]

Airtable allows multiple users to work simultaneously on the same Base allowing more productivity at workplace. A new collaborator is added by clicking share button found at top of Base and providing the email ID of the collaborator. The owner can set the permission level of collaborator while sharing the Base.^[19] There are three permission levels in Airtable Base, namely 'Creator', 'Edit Only' and 'Read Only'.

If multiple Bases are required then a Team is formed in Airtable.^[19] An Airtable Team can hold multiple Bases and collaborators of the Team can work on all the available Bases in the Team. There are four permission levels in Team, unlike for a Base which only has three. The extra permission level in Team is Owner, who has full access to Team Base.

Publishing views[edit]

Bases can be easily shared with the public. One need not have an Airtable account to view the published Bases. These view-only Bases shared in public are called Airtable views. These views can be embedded into one's own website and allows the website users see the real-time information in Base, without needing them to have an Airtable account. One possible use-case is Airtable Form, which can be used to take a survey of a product. The feedback received from a customer can also be shown to public by sharing the Airtable View.

Airtable Forms[edit]

Airtable introduced Forms in July 2015.^[7] Forms could be used to collect data from others like co-workers, customers or the public. A Form can be easily created from an existing Base, and the data collected by the Form is automatically organized in the Airtable Base. Airtable allows user to organize the required fields in the Form. A separate link is created for every Airtable Form. This link can be shared with others to get the required data. An Airtable Form can also be embedded in a website, to get the feedback from the website users.

Airtable integration[edit]

Recipe Database In Access

Airtable's API can be used to connect to other web services by which information can be exchanged between external web applications and Airtable. Using the Zapier platform, Airtable can connect to over 450 applications and websites. Changes can be set up as triggers for actions in connected applications.^[20]

Snapshot[edit]

Airtable provides a greater sophistication to backup the data than just allow the users to undo/redo. Airtable periodically snapshots the Base. When a previous version of a Base is needed, the appropriate snapshot is selected by the user from the snapshots list. In addition, the user can manually snapshot a Base at any time.^{[citation needed]}

Mobile apps[edit]

Airtable Sign In

Airtable introduced its version 2.0 iOS application on December 2015,^[9] allowing users to work on tables from their iPhone. Airtable for Android is also available.^[21]

API[edit]

Airtable provides an API to provide ability to users to build applications that cannot be built within the table constraints. A key, which is present at overview section, is necessary to use Airtable's API. The key should be kept secret. The API follows REST semantics, uses JSON to encode objects, and uses standard HTTP codes to signal operation outcomes.^[22]

Initially, the user has to create a Base in Airtable. Then, Airtable's API can be used to create, read, update, or delete records (i.e. CRUD). As of 2016, Airtable's API does not allow users to create or modify the Base schema.

Security[edit]

It also uses 256-bit SSL/TLS encryption in data transit.^[23]

See also[edit]

References[edit]

1. ^'Airtable review: A drop-dead easy relational database management system'. Macworld. Retrieved January 26, 2017.
2. ^Martin, James A. '3 ways Airtable for iOS can help you ditch spreadsheets'. CIO. Retrieved January 26, 2017.
3. ^'Airtable | CrunchBase'. www.crunchbase.com. Retrieved February 8, 2016.
4. ^Lawler, Ryan; Contributor. 'With $3M in Funding, Airtable Makes Complex Databases Usable on Your Mobile Phone'. TechCrunch. Retrieved February 6, 2016.
5. ^Russell, Kyle. 'Airtable Launches Its API And Embedded Databases'. TechCrunch. Retrieved February 6, 2016.
6. ^'Airtable lands $7.6M round to help build simple, extensible database apps'. VentureBeat. Retrieved February 6, 2016.
7. ^ ^ab'Create Forms in a Snap!'. Covering Bases. Retrieved February 6, 2016.
8. ^'Airtable and Slack: Keeping Your Team In Sync'. Covering Bases. Retrieved February 6, 2016.
9. ^ ^ab'Airtable Introduces Newly Redesigned iOS App to Make Database Creation Available to Anyone With an iPhone'. Marketwire. Retrieved February 6, 2016.
10. ^'A Brief History of Barcodes'. Covering Bases. Retrieved February 6, 2016.
11. ^'Airtable Raises $52 Million and Launches Airtable Blocks...'BusinessWire. March 15, 2018. Retrieved March 16, 2018.CS1 maint: discouraged parameter (link)
12. ^Lunden, Ingrid (November 15, 2018). 'Airtable, maker of a coding platform for non-techies, raises $100M at a $1.1B valuation'. TechCrunch. Archived from the original on April 15, 2019. Retrieved April 15, 2019.
13. ^'Airtable raises $185M and launches new low-code and automation features'. TechCrunch. Retrieved September 14, 2020.
14. ^'Cloud-based software company Airtable'. mg21. April 20, 2021.
15. ^'An Introduction to Databases – Airtable Guide'. Airtable Guide. Retrieved February 7, 2016.
16. ^'Setting Up Field Types – Airtable Guide'. Airtable Guide. Retrieved February 6, 2016.
17. ^'Guide to the basic field types'. Airtable – Support. Retrieved February 7, 2016.
18. ^'Linking to another record'. Airtable – Support. Retrieved February 6, 2016.
19. ^ ^ab'Collaborating with a Team – Airtable Guide'. Airtable Guide. Retrieved February 7, 2016.
20. ^'Airtable Integrations'. Airtable. Retrieved February 6, 2016.
21. ^'Here's how to use Airtable, the user-friendly spreadsheet app that's taking Silicon Valley by storm'. Business Insider. Retrieved August 8, 2018.
22. ^'Airtable – API'. airtable.com. Retrieved February 15, 2016.
23. ^'Airtable security practices'. Airtable – Support. Retrieved February 6, 2016.