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
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.
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.
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.
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.
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.
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.
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.
More to follow in the next post about Airtable.
oddrops 
Leave a Reply