Tidying up your information in Airtable
  • 07 Jul 2022
  • 7 Minutes to read
  • Dark
    Light

Tidying up your information in Airtable

  • Dark
    Light

Article Summary

360087945673s387080EB50FDC07B54D582D02093CEA333A0FC548A68CB0F9713FF9FD3917BE71555434698853WebinarFigureRightcopy.png

To get the most out of Airtable, we highly recommend that you take the time to convert fields from single line text fields to other field types, where appropriate. Read on for some of the most common conversions you should know—and some of our favorite tips for maximizing your Airtable fields.


Cleaning up imported files and converting field types

Importing an existing CSV file will create a table comprised of single line text fields by default.

To get the most out of Airtable, we highly recommend that you take the time to convert fields from single line text fields to other field types (where appropriate). To help you out, consider this list of common adjustments that will help you enjoy these immediate benefits:

Airtable provides many more field types with their own, unique functionalities. As you read on, you’ll find out how you can configure your bases’ fields for other useful purposes.

Converting text fields into checkbox fields

The humble checkbox field works like a light switch that you turn on and off. Although very simple in its nature, when used effectively it becomes incredibly powerful. When you start sorting, grouping, and filtering records, you’ll find this field type becomes an indispensable tool in your workflow.

360087945693s0EF49714D68FB0A73D6E061060C71EA4462CD4DAAA0FA89E3CA1819DABF062A71554967122268Screenshot2019-04-11001555.png

💡Pro tip: While the checkbox field fits perfectly for task management, and in various other contexts as well, a binary switch may not provide the full functionality you seek. For example, when you want to differentiate between “Yes” and “No” responses in a record, an unchecked box can look the same as no response at all. In this scenario, we recommend using a single select field with “Yes” and “No” options instead.

With that in mind, if you want to convert an important spreadsheet field filed with “Yes” and “No” entries, you will find that “Yes” becomes a checkmark (or whatever other symbol you chose when converting the field type) and “No” appears blank.

360087945633s0EF49714D68FB0A73D6E061060C71EA4462CD4DAAA0FA89E3CA1819DABF062A71554967650577CheckboxConversion.png

Converting to a text field to a checkbox doesn’t require “Yes” and “No” entries. The conversion will work in the same way if you used any of the following six common options as a “Yes”:

  • checked
  • true
  • 1
  • x
  • y
  • yes

Conversely, “false” and “0” will convert to a blank cell just like “No” would.

Please note that regardless of the original information, when you export Airtable views as CSVs, the contents of the checkbox field for each cell will either show the word “checked” or remain blank.

360086805874s0EF49714D68FB0A73D6E061060C71EA4462CD4DAAA0FA89E3CA1819DABF062A71554967919451Screenshot2019-04-11003156.png

If you’d like to change “checked” to something else, a quick find and replace operation in your spreadsheet app of choice will solve the problem.

Converting text fields into select fields

Select fields not only offer a more visually appealing and identifiable method of tagging individual records, but they also create many more ways to sort, filter, and group the information in your tables. If your imported spreadsheet contains a column with tags you can convert it to this useful field type in a couple of ways.

Select fields come in two varieties: single and multiple. If every cell in your original spreadsheet column only contains one tag each, you’ll want a single select field. This will maintain the single-entry rule so that you can change the tag but never add more than one.

360087945653s0EF49714D68FB0A73D6E061060C71EA4462CD4DAAA0FA89E3CA1819DABF062A71554968835648AirtableSpreadsheetTransition1-3Full.png

Multiple select fields, on the other hand, can handle as many tags as you like. If your original spreadsheet column contains lists of tags in each cell, separated by commas, converting the field type to multiple select will change each tag into its own selectable option.

360087945533s0EF49714D68FB0A73D6E061060C71EA4462CD4DAAA0FA89E3CA1819DABF062A71554968164703Screenshot2019-04-11003542.png

360087945553s0EF49714D68FB0A73D6E061060C71EA4462CD4DAAA0FA89E3CA1819DABF062A71554968164667Screenshot2019-04-11003558.png

In the event you have any tags that contain a comma in them, however, the conversion process will split that tag where it finds that comma. You can avoid this problem by enclosing any tag that includes a comma in double quotes (“”). To learn more read on, or you can click here to learn more about converting text fields containing commas.

Converting text fields into linked record fields

When you import multiple spreadsheets that result in more than one table in your new base, you can use linked record fields to connect separate tables together. Linked records offer some of the most potent benefits of Airtable because they establish relationships that you can use in so many different ways.

360086805814s0EF49714D68FB0A73D6E061060C71EA4462CD4DAAA0FA89E3CA1819DABF062A71554968477893LinkedRecordSetup.png

Throughout this guide, we’ll cover many use cases that leverage the power of linked records. Broadly speaking, however, linked records work best whenever you have a field that contains values important enough to warrant their own table. For example, a field that contains the names of employees could, instead, link to another table that contains those names along with additional information like phone numbers, email addresses, and job titles. Linked records allow you to store a reference to that information instead of making one giant table that feels cluttered and overwhelming because it contains everything.

Because you can’t establish this type of relationship in a spreadsheet, it might seem odd that you would convert an imported column into a linked record field. However, consider the previous example that resulted in the creation of an employee directory. You already have a complete list of names typed out and ready to go.

360086805794s0EF49714D68FB0A73D6E061060C71EA4462CD4DAAA0FA89E3CA1819DABF062A71554968707761Screenshot2019-04-11004451.png

To set up the link, simply create a new table to store the employee information.

360087945513s0EF49714D68FB0A73D6E061060C71EA4462CD4DAAA0FA89E3CA1819DABF062A71554968615717Screenshot2019-04-11004326.png

Next, return to your original table and change the field type containing the employee names to a linked record field. Choose the newly created table as the link’s target and you’ll find records created for you automatically in that new table.

360086805754s0EF49714D68FB0A73D6E061060C71EA4462CD4DAAA0FA89E3CA1819DABF062A71554968753892Screenshot2019-04-11004544.png

360086805774s0EF49714D68FB0A73D6E061060C71EA4462CD4DAAA0FA89E3CA1819DABF062A71554968772397Screenshot2019-04-11004548.png

This process will also create a linked record field in that table that shows the relationship you established from that table’s perspective as well. Any time you add or delete a linked record in one table’s field the change will occur in the other. If you delete a linked record by accident, you can always add it back. It won’t delete the record itself, but only the reference that shares its information across both tables.

360086805714s0EF49714D68FB0A73D6E061060C71EA4462CD4DAAA0FA89E3CA1819DABF062A71554968992190Screenshot2019-04-11004949.png

Linked record fields serve as the necessary source for other field types as well, such as lookup, count, and rollup fields. Any time you want to bring information from another table to work with it in another to avoid redundant information, you’ll start that process by creating linked records. Later on in the guide we’ll cover all sorts of ways you can use linked records to do amazing things with your Airtable bases. For now, focus on identifying opportunities to create these powerful relationships so that you can do more with them in the future.

To read more about how to leverage this field type, jump to our guide on setting up linked records.

Converting text fields containing commas

When converting text fields to select fields, you may remember that commas tell Airtable how to separate all your options during this process. The same rule applies to linked record conversions as well.

If you wish to create a link to a record with a comma in its name, you will need to delimit the desired record name with single or double quotation marks.

Let’s take a look at an example. The following list of publishing campaigns categorize books into five genres:

  • Young Adult
  • Biographies, Memoirs
  • Fiction
  • Non-fiction
  • Poetry.

Left as a single line text field, you run the risk of you or your collaborators entering the same genre with different spelling or as an abbreviation (e.g. Nonfiction or YA). Converting this list of genre categories into a select field will standardize the options to mitigate this type of issue.

360086805734s0EF49714D68FB0A73D6E061060C71EA4462CD4DAAA0FA89E3CA1819DABF062A71554969094848Screenshot2019-04-11005131.png

However, if you were to attempt to convert the example list of genre categories into a multiple select field you’d notice that Airtable would separate the option Biographies, Memoirs into separate options due to the comma between them.

360087945493s0EF49714D68FB0A73D6E061060C71EA4462CD4DAAA0FA89E3CA1819DABF062A71554969183937Screenshot2019-04-11005224.png

To solve this problem, you can surround it with quotation marks so that it becomes “Biographies, Memoirs” instead.

360087945473s0EF49714D68FB0A73D6E061060C71EA4462CD4DAAA0FA89E3CA1819DABF062A71554969209487Screenshot2019-04-11005237.png

When you’re sifting through a large number of these comma-separated tags, you can speed up the process significantly with the sort records function (or alternatively, the grouped records along with a little copying and pasting (or use of the fill handle). Just follow these steps:

  1. Sort on the Category field alphabetically
  2. Change one instance of Biographies, Memoirs into “Biographies, Memoirs”
  3. Copy “Biographies, Memoirs” (or use the fill handle)
  4. Select and paste “Biographies, Memoirs” over all instances of Biographies, Memoirs (unless you used the fill handle to accomplish this in the previous step)

360087945453s0EF49714D68FB0A73D6E061060C71EA4462CD4DAAA0FA89E3CA1819DABF062A71554969232210Screenshot2019-04-11005244.png

Now, if you attempt to convert the Category field into a select field type you will see the five options displayed as expected.

360086805694s0EF49714D68FB0A73D6E061060C71EA4462CD4DAAA0FA89E3CA1819DABF062A71554969238160Screenshot2019-04-11005245.png


Was this article helpful?