This is a guest post courtesy of database design expert and author Tim Dietrich, an honorary Airtable MVP.
The primary column (field) is a concept that many new Airtable users struggle with. In this tech brief, you'll learn what the primary column is, how it works, and why it is important.
Every Airtable table includes a single primary column. Its purpose is to give users a way to identify a record in the table. This becomes particularly important when relating records.
It's Not A Primary Key
If you're familiar with relational database terms, it might seem to you that the primary column is a table's primary key - but it isn't. The real primary key is handled behind the scenes by Airtable. Airtable has its own internal record ID that is formatted as an alphanumeric ID, and its value is unique within each database.
It's Not An Alternate Primary Key
You might also think of the primary column is an alternate primary key. However, it isn't that, either. It's entirely possible for a record to contain no value in the primary column. It's also possible for multiple records to contain the same value. Therefore, it doesn't meet the requirements that are necessary for it to serve as an alternate primary key.
It's A Way to Identify and Select Records
The primary column provides an informal way to identify a record in a table. That becomes especially important when you are linking related records.
Airtable's Internal Record ID
It is possible to see the record IDs that Airtable is using behind the scenes. To do so, create a formula column, and set the formula to RECORD_ID().
One More Thing...
When you're importing records, Airtable will assign the first column in the file to be the primary column. Therefore, before you import records, make sure that the first column in your file is something that will serve well as the primary column.
[Note: In this version of the post, instances of "Name column" have been replaced with "Primary column."]
This is the second part of a guest post from database design expert and author Tim Dietrich, an honorary Airtable MVP. Check out part one here.
In Part 1 of this guide, you learned how to migrate data from FileMaker to Airtable. You migrated the Prospects table from the sample FileMaker database to Airtable, and then configured the new table with the best possible column types.
In Part 2, you'll migrate the Activity table from FileMaker to Airtable, and learn how to "reconnect" the activity records with the correct records in the Prospects table.
Export the Activity Table from FileMaker
The process of exporting the records from the Activity table is very similar to the process that you used to export the Prospects.
Start by navigating to a layout that is based on the Activity table. Then make sure that you have all of the table's records in your found set. The easiest way to do that is to select Records > Show All Records.
Next, select File > Export Records. The "Export Records to File dialog will appear.
Select a location and a file name to export your data to. For the file Type, select "Merge."
Click the Save button to continue. The "Specify Field Order for Export" dialog will appear. Remember to select the "Current Table" option.
Select all of the fields that are in the Current Table. You can simply click the Move All button to so.
You also want to export the "Full_Name" field from the Prospects table. (You'll use the "Full_Name" field to re-establish the relationships between the Activity and Prospects table in Airtable.) To select that field, select Prospects from the drop-down menu, locate the field in the list, and either double-click it or click the "Move" button to add it to the list of fields that are to be exported.
Set the field export order so that the "Activity_Name" field is the first field in the list. As you learned in Part 1, Airtable uses the first field as the primary column when importing tables. Scroll down to find the field, then drag it to the top of the list.
Finally, click the Export button to export the records to the file.
Import Data Into Airtable
Before importing the Activity table, remember to rename the file that you created earlier. Change the file's extension from ".mer" to ".csv." As we discussed in Part 1, this will make it easier for you to select the file for importing into Airtable.
When you imported the Prospects table, you created an entirely new Airtable database. You're going to want to import the Activity table into that same Airtable database. To get started, log into Airtable and open the Prospects database.
To import the table, click the "+" button, which is located next to the "Prospects" tab. Select the "Import a spreadsheet" option. The Import Spreadsheet dialog will appear.
Click the "Choose a .CSV file" button. The Choose File dialog will appear.
Next, either drag and drop the file that you created earlier on to the window, or click the "Choose File" to locate and select the file. Airtable will immediately create your new database, as well as a table for your data, and load the data into the table. When it is done, a new icon will appear on the Airtable Home screen.
Change the name of the new table to "Activity." The table should look something like this.
Cleaning Up The Airtable Database
As you did in Part 1, you might want to take a few minutes to configure the new Activity table with the best possible the column types. For example, you might want to change the Date_Created column to a Date type, and change the Activity_Type to a "Single select" type.
You might also want to hide some of the columns that aren't necessary for this view, including Activity_UUID, Prospect_UUID, and Author.
Re-Establishing Relationships
The last step is to re-establish the relationships between Prospects and their Activity records. Airtable makes that an incredibly easy thing to do.
When you exported the Activity records from FileMaker, I had you export the "Full_Name" field from the Prospects table. In Airtable, "Full_Name" was also used as the primary column on the Prospects table.
Therefore, all that's involved in establishing relationships between the two tables—and preserving existing relationships—is to change the column type for the "Full_Name" column. Change it from "Single line text" to "Link to another record." Select the "Prospects" table, click Save, and Airtable will take it from there.
At that point, the relationship between the Prospects and Activity tables will be established. And again, any existing relationships will be restored. You can see this in both the Activity table...
And the Prospects table...
How cool is that?!?
Here's what happened: Airtable took the values in the Full_Name column from the Activity table, and matched them to the corresponding records in the Prospects table. Behind the scenes, those relationships are using Airtable's "real" record IDs. So if you change a prospect's name, the relationship to records in the Activity table will be maintained.
Wrapping Up
In Part 2 of this guide, you've learned how to:
Migrate additional tables from FileMaker to Airtable.
Re-establish relationships between related tables.
Now that you have your data migrated from FileMaker, you can take full advantage of the features that Airtable offers.
Click here and here to check out some of Tim's other guest posts.
[Note: In this version of the post, instances of “Name column” have been replaced with “Primary column.”]
This is a guest post courtesy of database design expert and author Tim Dietrich, an honorary Airtable MVP. To check out the second part of this post, click here.
FileMaker users who are exploring Airtable often ask me what's involved in migrating data from FileMaker to Airtable. So I've created this guide to show what's involved.
In Part 1, you'll learn how to migrate data from FileMaker to Airtable. You'll also learn how to configure Airtable tables with the best possible column types, so that you can get the most out of Airtable.
When migrating databases that contain multiple related tables, it's important that you be able to "reconnect" related records. You'll learn how to do that in Part 2.
The Sample Database
In this guide, we'll migrate data from a simple FileMaker database. It's a database of prospects, being used by a team of salespeople. It includes basic information about each prospect, and that data is stored in a Prospects table.
The database also contains information on prospect activity. When a salesperson interacts with a prospect, they add a record to the Activity table via a portal, indicate the type of interaction that they had (email, phone call, meeting), as well as notes or comments.
Below are a few screenshots of the database.
The tables are related using a Prospect_UUID field, which is the primary key of the Prospects table. A field with the same name serves as the foreign key in the Activities table.
While this is a basic FileMaker database, the steps involved in migrating data to Airtable will apply to any database, regardless of its complexity.
If you'd like to follow along, a copy of the FileMaker database is available here for you to download.
The FileMaker database includes sample (fake) data. There are 500 records in the Prospects table, and approximately 200 records in the Activities table.
Export Data From FileMaker
We have two tables to migrate from FileMaker to Airtable. Let's start with the Prospects table.
First, make sure that you are on a layout that is based on the parent table. You also want to be sure that you have all of the table's records in your found set. The easiest way to do that is to select Records > Show All Records.
To export the records, select File > Export Records. The "Export Records to File dialog will appear.
Next, select a location and a file name to export your data to. For the file Type, select "Merge."
Note: Airtable supports importing CSV files. However, you're going to use a Merge file (instead of FileMaker's "Comma-Separated Text" option) because it is very similar to a CSV. The important difference between the two file types is that the Merge file will include a header row with the name of each field.
Click the Save button to continue. The "Specify Field Order for Export" dialog will appear. Be sure to select the "Current Table" option. Otherwise, only the fields that are actually on the current layout will be exported - and you might end up missing a few fields that are in the table but not actually on the layout.
Select all of the fields that are in the Current Table. You can simply click the Move All button to so.
There is one last important thing that you'll need to do before exporting the data, and that is to set the field export order so that the "Full_Name" field is the first field in the list. Scroll down to find the field, then drag it to the top of the list. (The importance of this step will be explained shortly.)
Finally, click the Export button to export the records to the file.
Import Data Into Airtable
Now that you have the data exported from FileMaker, you're ready to import it into Airtable. In this example, I'm going to import the data into a brand new Airtable database. Later, I'll show you how to import a table into an existing Airtable database.
Before you continue, you need to rename the file that you created earlier. Change the file's extension from ".mer" to ".csv." This will make it easier for you to select the file for importing into Airtable.
One additional change that you need to make - and this one is a little tricky - is to replace a special character that appears in the merge file. The character that you need to replace is the ¿. When creating the merge file, FileMaker substituted this character in place of the return character that you would normally find in a field that has multiple values. That's a good thing, because had FileMaker used return characters, it would be difficult to know when one record ended and another began.
You need to replace the ¿ characters with commas. It's a relatively easy change to make, and you should be able to make it using any text editor. (I use TextWrangler, and you can see how I've made the change in the screenshot below.)
Next, log into Airtable. From the Airtable home screen, click the "New Base" icon. The New Base window will appear.
Select the "Import a spreadsheet" option. The Import Spreadsheet dialog will appear.
Click the "Choose a .CSV file" button. The Choose File dialog will appear.
Next, either drag and drop the file that you created earlier on to the window, or click the "Choose File" to locate and select the file. Airtable will immediately create your new database, as well as a table for your data, and load the data into the table. When it is done, a new icon will appear on the Airtable Home screen.
By default, the new database will be named "Imported Application." For this example, I'm going to name it "Sales Prospects."
You can also select an icon that represents the contents of the database. For this example, I'll select the $ icon.
And finally, you can also change the database's button color. I've chosen green for the example database.
Exploring the Airtable Database
Let's take a moment to explore the new Airtable database. To use the database, click on its icon.
As you can see, Airtable has imported the data that you had exported from FileMaker. The records are presented in a spreadsheet-like interface, with columns and rows. Take a moment to scroll through the records.
Cleaning up the Airtable Database
The data has been loaded, but there are a few things you can do to improve the new database and take full advantage of Airtable's features.
Notice that when Airtable created the new database, it created a table to load the data into. By default, that table is named "Imported Table." Let's change the table name so that it is more clear.
To rename the table, click on the down arrow next to the table name, and choose Rename Table. The tab for the table will change to a text field.
Enter the name for the table and click return or enter. For this example, let's change the table's name to "Prospects."
Next, take a look at the columns. The column names are okay, and that's because you imported the data from a merge file (where first row included the column names).
However, notice that all of the columns are set to Airtable's "Single line text" type. (You can tell a column's type by looking at the icon to the left of the column name.) That's the default column type that Airtable uses when it imports data. And while the "Single line text" column type works, it's not ideal for several of our columns.
For example, the example database includes a "Budget" column. It contains numeric values, and you'll likely want to refer to those values as numbers instead of text (to get totals, for example). Similarly, the "Business_Overview" column will likely contain more than a single line of text. There are other columns that you can optimize as well. Let's work our way through them.
Email Address
The email address column works okay as a "Single line text" column. However, Airtable supports an Email column type, and you'll get additional functionality if you change the column to that type. To change the column's type, click the down arrow next to the column's name, and select Configure. Next, click on "Single line text." A menu will appear, showing all of the column types that Airtable supports. Select "Email" from the list, and then click save. Notice that the values in the column are now underlined, indicating that they are links. If you click one, your default mail application should open, with a new message pre-addressed for you.
Phone Number
You can make a similar change to the Phone Number column. Follow the same steps that you used to change the Email Address. However, for this column, select the "Phone number" column type.
URL
Change this column's type to "URL." Like the Email Address column, it will act as a clickable link.
Lead_Source
While you don't necessarily need to change the Lead Source column, you could use a "Single select" type to present a list of options instead of plain text. This is similar to Filemaker's value list concept. This will help to streamline the entry of additional records, and help ensure that the values are consistent. To make this change, change the column to a "Single select" type. Notice that Airtable will automatically build the list of options (again, the "value list" in FileMaker terms) based on the values that are already in the records. This is another convenient feature of Airtable - it's done all of the work of setting up the value list for you!
Industry
Like the Lead Source column, the Industry column would also work best as a "Single select" column type.
Budget
Change the Budget column to a Currency column, and select one of the precision options. I'm going to select the precision that does not include a decimal value. Notice that when you change this column's type, Airtable automatically displays the sum of the column's value in the footer area. You can click on that value to get other summary values as well, such as the column's average, median, min, max, and so on. A very cool feature!
Business Overview
While the content in the Business Overview column is text and the "Single line text" column type works, it's less than ideal. You really want to be able to see and work with values that potentially contain a great deal of text. So let's change this column to a "Long text" type. Notice that the cells in the column behave a little differently now. If you click on one, an Expand cell icon will appear. Click on it, and the cell expands into its own modal window.
Interests
Several of the records contain multiple "Interests" values. For example, some contain Services and Software, others contain Software and Hardware, and so on. So for this column, instead of changing it to a "Single select" column, you'll change it to a "Multiple Options" type instead. (This is similar to configuring a field in FileMaker so that it uses a "Checkbox set" control style.) Once again, Airtable will do most of the work for you. It will automatically build the list of options to present to the user (based on existing values in the column), and automatically update the column, selecting the correct options for each record.
Status
A prospect will only ever have one Status value (Active or Inactive). Therefore, you can configure the column as "Single select" type. However, it might be nice to color-code these values to make it a little easier to identify inactive prospects. Notice that when you configure the options for a "Single select" column, you can indicate the background color to be used for each option. (This feature also applies to the "Multiple Options" column type.) You can set the color of "Active" to green, and the color of "Inactive" to red. This is another convenient and helpful feature of Airtable.
Address State
Finally, the State column should also be configured to be a "Single select" type.
Airtable's Primary Column
Airtable's primary column is a concept that many FileMaker users struggle with. There's nothing quite like it in the FileMaker world.
Every Airtable table includes a single primary column. Its purpose is to give users a way to identify a record in the table. This becomes particularly important when relating records.
To learn more about the primary column, click here.
Earlier, when you exported the data from FileMaker, I stressed the importance of setting the export field order so that the Full_Name column was the first field in the list. The reason that this is so important is that, when importing a file into Airtable, the first column in the file is used as the primary column. So you want to be particularly careful when you exporting data from FileMaker. You want the first field to be something that will serve well as the primary column.
Wrapping Up
In this guide, you've learned how to:
Migrate data from FileMaker to Airtable.
Configure an Airtable table with the best possible column types.
And you also have a good understanding of the Airtable primary column.
In Part 2 of the guide, you'll learn how to migrate the Activities table from FileMaker to Airtable, and re-establish the relationships between the two tables.
[Note: In this version of the post, instances of "Name column" have been replaced with "Primary column."]
This is a guest post courtesy of database design expert and author Tim Dietrich, an honorary Airtable MVP.
Another great feature of Airtable is the "Rollup" column type. With this type of column, you can summarize data from records in a linked table.
For example, suppose that you have an Organizations table that is linked to a table of Prospects, and that each prospect has an annual spending budget value (stored in a "Budget" column). It might be interesting to get the total budget amounts for each organization. With a rollup column, that's easy to do.
In this example, you could go to the Organizations table, click to add a new column. and give the column a descriptive name. Next, you would select "Rollup" from the list of column types. You want to summarize data coming from the Prospects table, so would select that table. Then you would select "Budget" as the column that you want to summarize. And finally, you would select "SUM" as the rollup function to use.
It's really that easy!
Note that you could setup similar rollup columns to get summaries for minimum, maximum, averages, and more.
This is a guest post courtesy of database design expert and author Tim Dietrich, an honorary Airtable MVP.
The process of creating databases is often very fluid. While you typically have a pretty good idea of the types of information that you'll want to track in the database, and what you'll want to do with it, it is often the case that you'll realize later in the process that you forgot something, or that you could have modeled the information in a better way.
For example, suppose that you have created an Airtable database that tracks Prospects and Activity. What if you wanted to work with the data, or analyze it, at an organizational level? You don't have a table for Organizations, but Airtable makes it very easy to set one up. Using an Organization column in the Prospects table, Airtable can automatically create a new table, load it with data from the Organization column, and establish the relationships between the records in the Prospects table and the new Organizations table automatically.
Here's how you would do it: In the Airtable database, select the Prospects table, and locate the Organization column. Then, change its type from "Single line text" to "Link to another record." Then select "Create a new table," and give the new table a name. I'm using "Organizations" in the screen shot below.
That's all there is to it! You now have the new Organizations table, loaded with data, and with all of the relationships to Prospects.
Tim Dietrich shares his thoughts on making a living as a full time FileMaker consultant: Earlier this week, I was speaking with a friend who is in the process of going out on his own as a FileMaker consultant. He wanted to “pick my brain” about what’s involved, get some advice that I have based on my experience, and get answers to some of his questions. And while it feels a little strange to be…
Building an online form can be tricky, and linking it to FileMaker even more so. Tim Dietrich attacked the problem from a different perspective: Use an online form builder, Wufoo, to take the pain out of the web building side. Next, design a FileMaker solution, fmfoo, using PHP to link to Wufoo to FileMaker: I released FMEasyWeb, which makes it easy to publish information in FileMaker databases…
FMEasyWeb is now in Beta! Check out this Free and easy way to share your FileMaker data directly to the web: FMEasyWeb With EasyWeb you can quickly and easily provide Web-based access to your FileMaker databases without needing to write code. With EasyWeb, you can: • Provide visitors with access to data stored in a FileMaker database. • Make the data available to the public or only to authorized…
