Airtable Part 2

In a recent post I introduced Airtable, a cloud-based database app available as a web client and an iOS app. In this post I’ll describe building a simple database. I have not done any real design for this (making it up as I go along), but the basic idea is for a client management system for some company (I’ll call it ABC) selling some unspecified products and services.

The Main Database Screen

Airtable Companies Grid

The standard display of records in a table looks like this

As I played around with Airtable the structure of the database evolved, but it did show one of the benefits of Airtable: it is very easy to modify things on the fly. What I’ve have got so far is a structure where:

  • the SALES TEAM manage relationships with COMPANIES
  • the COMPANIES have EMPLOYEES
  • the SALES TEAM has visits with EMPLOYEES
  • the SALES TEAM have ACTIONS, which may link to a specific client contact (EMPLOYEE)

The screenshot shows the BUSINESSES database with the COMPANIES table selected— one table is always selected. Company records are displayed in rows. A new record can be created by tapping the big + symbol in the lower middle of the screen. A new table can be added by clicking the small + at the bottom right. Sorts and filters can be applied on the fly by tapping the relevant command at the top right.

Airtable Filters

Multiple criteria for filters can be applied to records in a table

Records can be sorted on multiple fields

Touching the drop down arrowhead to the right of the database name, BUSINESSES, opens the Database Edit dialogue where you can change the name, choose a colour and an icon to represent the database. The bottom half of the dialogue box lists the tables within the database. The order can be changed using the handles next to the table name. Reordering can also be done from the main screen by clicking and dragging a table to a new position.

Touching a table name in this screen or on the main screen opens the Edit Table dialogue.

COMPANIES

The default display for records is the row (or spreadsheet layout). If you select a specific record, then a form view appears. It’s quite basic: one field per row with the field name above and all fields are included. The web app has the capability to build custom forms—more on that later.

Airtable Companies 1

This is the top part of the entry form for COMPANIES

I want to make some remarks about these fields and how they are used.

Company

The first field in any table is always the key field. As I noted in my previous post, it does not have to be unique. When I was building this demo database, I experimented with a unique key, using an auto number field or entering codes designed to be unique. This proved to be a hindrance because in some circumstances the record associated with the code could not be seen. Consequently, picking a related record became dependant on remembering the correct code—not very user-friendly—so I reverted to using meaningful keys like company name.

Website

When a field is typed as URL, this is recognised by the app. You can see the “go to” icon on the right-hand side, which when clicked sends the URL to the browser—even when the URL is bogus.

Airtable Go to URL

Telephone Number

Similar useful functionality is attached to telephone number fields that can initiate a chat, or a phone call. For email fields, a new email is started in the Mail app. This seems to be one area where is iOS app scores over the web app: only web links are recognised in the web app.

Airtable Sending An Email

Nature of Business

This is an example of a multiple options field. What’s useful here is the ability to add a new option as data are being entered. There’s no need to stop work and go to the configuration screen.

Company Type

This is a single select field, and like the multiple options fields new values can be added during data-entry.

EMPLOYEES and SALES TEAM

Airtable is a relational database so records that have the same key in different tables can be linked together. When you choose “link to a record”, you specify the table to link to and whether linking to multiple records is allowed. In the COMPANIES form, you can see that multiple employees can be added using “+ Link to a record in the EMPLOYEES table”, but as a member of the SALES TEAM has already been allocated to this company that option is not available because multiple record links have been turned off.

Airtable Linking Records

Airtable Company Links

Linked records in EMPLOYEES (1 to many) and SALES TEAM (many to 1)

Constraining links

Links can only be created with one field. Though you can create more than one link between the same two tables using different. One of the Airtable videos shows this with links created between two tables, Movies and People, using the Actor and Director fields in Movies. One of the things I could not work out how to do was to automatically constrain the records when adding a new link. I had COMPANIES, DEPARTMENTS and EMPLOYEES. I wanted a company to have multiple departments and multiple employees; and I wanted an employee to belong to only one company and one department. The links were in place, but nothing stopped me from linking an employee to a department belonging to the wrong company.

I realised that I could use a formula to create the department primary key. So I set this to be CONCATENATE( Company, “-”, Department). Company was picked up when I linked the new DEPARTMENT record to a company, and Department was the label that I entered.

Airtable Creating Dept ID 1

Airtable Creating Dept ID 2

Airtable Creating Dept ID 3

 

This doesn’t stop an employee being linked to the wrong company, but since the company name is part of the department key it can be used in the search box so only relevant DEPARTMENT records are displayed.

Airtable Searching Dept ID

More to follow in the next post about Airtable.

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: