Airtable 3

The structure of my Airtable demo database continues to evolve. I changed the name from “Businesses” to “Clients” and have settled on these tables as the basis for exploring how Airtable works in use rather than design.

This simple diagram shows the tables and fields in the demo database.

I’ve decided to do this by describing different scenarios. I’m just going to write about these as they occur, so my apologies if there isn’t much sense to the organisation.

Previous posts about Airtable are here.

Scenario 1: Remote Entry of New Client Companies

ABC has people who add new records to the COMPANIES table so they can be followed up by someone on the SALES TEAM. Using the web app, I created a custom form. Airtable generates a web link that I can email to my data-entry person, Sue Cox, who saves the link as a bookmark.

Bookmarks for forms in Safari

Sue calls up the Company Set-up Form:

Sue enters the details for the new company, Parker Associates, but realises that there is no option for “CRM consultancy” in the Nature of Business field. So she uses another form to send a request:

Scenario 2: Updating Nature of Business

Roger gets the action request. He calls up the main view for COMPANIES and applies a filter to find Parker Associates.

Roger then chooses to customise the table to add the new option, CRM consultancy.

After adding the new option, Roger thinks that it would be helpful to sort the options into alphabetical order, which can be done in the web app.

But it dawns on him that even this will be not very user-friendly as more and more classifications are added to the CLIENTS database. Roger decides to change the Nature of Business field to a link to records type. Of course, we don’t want to lose any data, if we can help it.

The first step is create a new table, BUSINESS CLASSIFICATION by:

1. Creating a new grid view in the web app that selects only companies that have a value in the Nature of Business field. The grid display only the Company field (it’s the key field and cannot be hidden) and Nature of Business.
2. The results are exported to a CSV file.
3. The resulting CSV file is loaded to Excel. The column for Company is deleted. Artic Articles had two values for Nature of Business assigned, so this is split into two separate entries. As it happens this resulted in a list of unique categories. In some situations, a further step might be needed to remove duplicate entries. The modified CSV file is saved.
4. A new table is created by importing the revised CSV file, and is renamed BUSINESS CLASSIFICATION.
5. The COMPANIES table is selected and customise fields chosen. The Nature of Business field is changed from multiple options to link to records. Some magic happens and the existing records are modified. Airtable even manages to create two links for Artic Articles (which was the only company assigned to options for Nature of Business).

I thought that was pretty clever. And not only that the Company Set-up Form that Sue started with has also been modified to reflect the new link to BUSINESS CLASSIFICATION.

More scenarios will appear in a future post.