Primary key and foreign key

Hi Karl!

Welcome to Airtable - and I’m glad to hear that you’re enjoying it.

The “name” field can be a little confusing for those of us coming from database platforms such as FileMaker. Normally, we have to explicitly specify a primary key on a table, so that we can setup relationships to it from other tables.

With Airtable, all of that is done for you. Behind the scenes, each record is assigned a UUID, and it is that value which Airtable really uses as the primary key. You won’t normally see those values in the Airtable interface, but if you poke around in the API, you will see how they are used.

The “name” field is sort of like an alternate primary key, which, as users, we can see and use to identify records in a more user-friendly way. You can change the name field if you’d like. For example, you can change the column’s name, its type, and so on. You can even setup a name column so that it acts in a way similar to the auto-increment fields that we often use in FileMaker databases.

In Airtable, when you relate records from one table to another, it is the “name” field that you use to select a related record. (Again, you’ll never see the primary keys that Airtable is really using.) The nice thing about this is that you can change a record’s name value, and any relationships that the record is involved in will be maintained.

I hope this all makes sense. If not - I apologize. It’s been a very long day! Feel free to reach out to me either here or via email (timdietrich@me.com) if you need anything else.

~ Tim

Hi Karl!

Welcome to Airtable - and I’m glad to hear that you’re enjoying it.

The “name” field can be a little confusing for those of us coming from database platforms such as FileMaker. Normally, we have to explicitly specify a primary key on a table, so that we can setup relationships to it from other tables.

With Airtable, all of that is done for you. Behind the scenes, each record is assigned a UUID, and it is that value which Airtable really uses as the primary key. You won’t normally see those values in the Airtable interface, but if you poke around in the API, you will see how they are used.

The “name” field is sort of like an alternate primary key, which, as users, we can see and use to identify records in a more user-friendly way. You can change the name field if you’d like. For example, you can change the column’s name, its type, and so on. You can even setup a name column so that it acts in a way similar to the auto-increment fields that we often use in FileMaker databases.

In Airtable, when you relate records from one table to another, it is the “name” field that you use to select a related record. (Again, you’ll never see the primary keys that Airtable is really using.) The nice thing about this is that you can change a record’s name value, and any relationships that the record is involved in will be maintained.

I hope this all makes sense. If not - I apologize. It’s been a very long day! Feel free to reach out to me either here or via email (timdietrich@me.com) if you need anything else.

~ Tim

Hi Tim -
The NAME field is often the field I want to use to link from one table to another, but since we can’t specify NAME in a link, do we have to duplicate the NAME column just to link?
Example:
Vendors, Vendor applications, and Vendor Schedules are all 1-to-1 relationships, each having NAME as essentially the primary key. How to link these? I don’t want to have them all in a single table.
Thank you!
Martha

Hi Martha,

If you really want all of those to be in separate tables, I would suggest that for the vendor schedules table, you make the second column a link to the vendors table, then make the primary column a formula that return the value from the second column.

(If all of the relationships are 1:1, you could also just put them all on the same table and make different views to show/hide the application and scheduling information.)

Martha,

If you’re coming from FileMaker Pro (like Tim or like me), Airtable’s handling of relationships takes a little getting used to. Once you get used to it, you’ll probably find as I have that a lot of stuff that takes some work in FileMaker is easy as pie in Airtable. Of course, the opposite is true, too. Horses for courses, as they say. :slightly_smiling_face:

.

What the Name field is

The Name column is how Airtable protects casual users from the abstractions involved in database design (like ‘entity’). Unfortunately, calling this column ‘Name’ by default leads, I think, to some confusion.

Whether you’re working in Airtable or FileMaker (or any other database system), within a given table, each row should represent a unique instance of some thing (‘entity’). The unique primary key value for each record guarantees this uniqueness but doesn’t in any way characterize it. If you were able to see it, the primary key 03549674511 would tell you that this record is different from another record whose primary key value = 03549674512, but the key values do not tell you how one record differs from the other (more precisely, how the thing one record refers to in the real world is different from the thing referred to by the other record). In FileMaker, you could create a table that had just one field, the primary key field. This would of course be pretty pointless, but it’s possible. Airtable simply doesn’t let you do that. Instead, Airtable takes care of the ID/primary key for you invisibly, and instead asks you to answer the question “What Is It About This Record That Makes It So Darned Special?” Actually, that’d be a better name for the default column than ‘Name.’ If it weren’t so long.

Anyway, if the entity the table represents is people, ‘Name’ might not be a bad term for the specialness descriptor, especially if your data set is small. But if the table represents an entity that’s not personal, then the column name ‘Name’ can be misleading or confusing, for newbies.
.

What the Name field is NOT

So that’s what the ‘Name’ field is. It may be helpful to think about what Airtable’s ‘Name’ field is NOT.

• As I suggested already, it’s not really a name field. Luckily, you aren’t stuck with either the single line text data type or the column name ‘Name’. For example it can be a Phone Number data type and you could rename it ‘Phone Number’. It could be a long text column. It could be a formula column. And even if each record in the table represents a person, the ‘Name’ field might more usefully be used to store something else. You might make SSN the primary descriptor, or StudentNumber or something like that.
• It emphatically is NOT the primary key for the table or even a proxy or alias for the primary key. As Tim pointed out, Airtable handles all the key values invisibly.
• The ‘Name’ column doesn’t have to have a unique value. If you don’t want to store SSNs in your table (for sound reasons) and you don’t have anything else like drivers license number or student number, then you can create 47 records with the value “Kevin Bacon” in the Name column. Of course, if you do so, then, as far as Airtable is concerned, those are 47 unique and different people all coincidentally sharing the name ‘Kevin Bacon’.
• And while I’m at it I guess I should emphasize that the Name column is not ‘Column A’ in a spreadsheet!! This is where Airtable’s spreadsheet analogy might confuse some users who are familiar with spreadsheets but don’t know anything about data modeling. In a spreadsheet you might make a list of orders placed by customers, and you might put the customer name in column A, the order date in column B, the (unique) order number (if there is one) in column C, etc. If you converted that spreadsheet to a database and you knew what you were doing, you’d understand that column C ‘Order Number’ is the unique defining value, so you’d create a table named ‘ORDER NUMBERS’ and the Airtable ‘Name’ field could be renamed ‘Order Number’.

.

One-to-many (i.e. normal parent-child) relationships in Airtable

In Airtable, because keys are completely hidden from you, you don’t have to think about what’s a primary key and what’s a foreign key, at least not in those terms. You should however think about the cardinality of the relationship, that is, whether it’s one-to-many, many-to-one, one-to-one or many-to-many.

In FileMaker, you control the cardinality in both directions when you relate two tables. On the other hand, in Airtable, cardinality is controlled one way at a time, when you configure the relationship in the configure options for the related column. You do this by checking or un-checking ‘Allow linking to multiple records’. So in the INVOICES main view, you’d check that option because each Invoice record may have multiple linked line items; while over in LINE ITEMS main view, you’d want to make sure you un-checked that option for the ‘Invoice’ column, because each line item should be linked to only one invoice.

This is important because if you don’t — i.e. if the Invoices column on the Line Items view is configured to allow linking to multiple records — there’s no way in Airtable right now (Feb 2016) to prevent user from creating a line-item directly and linking it to multiple invoices. Which would be bad.

.

Many-to-many

For the record, although you have to be in a view to configure these linked columns, a change made in one view will automatically affect the same linked column when it appears in other views. Nevertheless, checking or un-checking that ‘Allow linking to multiple records’ is a UI device, not actually an element in the definition of the relationship. It simply prevents you from linking a record in table B to more than one record in table A, and vice versa. The same was true above when I was talking about cardinality. Checking or unchecking ‘Allow linking to multiple records’ for a linked column in a given table does not really control whether you can link to multiple records any and everywhere, it just controls whether it can be done in your Airtable views from that point forward. It’s a practical UI control, not a mathematical formula.

I say this is just a UI device mainly because we simply don’t have direct access to the relationships; after all, we don’t even have access to the key fields. But Airtable will give you hints about what’s going on behind the scenes, if you pay attention. For one thing, if you start out allowing table INVOICES to link to multiple items in LINEITEMS, and then you change your mind and un-check that option, you won’t immediately lose those multiple linked items that were linked earlier. To a FileMaker user, anyway, it will soon start to seem that, behind the scenes in Airtable, everything is potentially a many-to-many relationship.

To see this demonstrated, create a simply library database with three (just three) tables: GENRES, AUTHORS and TITLES. On each view, configure the linked columns to allow linking to multiple records. Now populate your tables with values, with (for example) records for History, Drama, Poetry, Novels in the GENRES table, with Shakespeare, Keats, Tolstoy and James Joyce in the AUTHORS table, and a selection of titles form each author in the TITLES table. Notice that you can link War and Peace both to History and to Novel; you can switch to GENRES and add ‘Richard III’ to the History record there. (You won’t need to add ‘War and Peace’ because it’s already there.) And so on.

I am pretty sure that what’s actually happening is that the joins are created as required. Can’t do that in FileMaker Pro natively, i.e. without using SQL. But you can do it in Airtable without breaking a sweat: You didn’t create a single join table! No worrying about circular relationships, either. Pretty remarkable, actually.

.

One-to-one

Anyway, back to your one-to-one relationship. In Airtable, you’d do this by leaving ‘Allow linking to multiple records’ UN-checked on both sides of the relationship.

But as was suggested already, you might want to ask yourself if it’s necessary. Might not be.

In FileMaker Pro, there are multiple reasons for creating one-to-one relationships: two reasons I am aware of are security and network load. I don’t have any databases in Airtable that are large enough for network load to be an issue. As for security, well, Airtable’s current security model is much more limited than FileMaker’s but seems capable of handling most of the challenges I worry about. If you don’t want certain users to see certain columns, you can easily create views in Airtable hide columns. The bigger problem with Airtable is that you can’t currently do something like give a user access to a subset of columns in a table, allow that user to edit some of those columns and and not others, and prevent the user from deleting records.

.

Apples and Oranges

FileMaker (and other RDBMSes like Access, 4D and many others) provide rich relational tools that make it easy to do certain things that are either impossible to do in Airtable (at this time) or, if not impossible, at least awkward and kludgy. On the other hand, some of the differences between FileMaker and Airtable are just differences.

In the first class (where Airtable might seem awkward or kludgy to an advanced FileMaker user) would be the fact that Airtable can’t presently see data two or more linked tables away. In FileMaker, if ZIP CODES are children of CITIES, CITIES children of COUNTIES, and COUNTIES children of STATES, it’s easy to display the name of the state that is the great-grandparent of a given zip code. In anchor-buoy, the linked field might look like this:

zipcodes_cities_counties_STATES::StateName

You can do it in Airtable! But it’s awkward: You have to use lookups to pipe values from one table to the next, to the next. Another example: Airtable doesn’t directly permit multifactor links between tables, that is, you can’t link table A to table B when column A1 matches column B7 and column A2 matches column B9. You might do it by linking table A to table B using formula columns in both tables, but that will feel kludgy to a FileMaker user.

On the other hand, this lack of easy multifactor links between tables isn’t always a weakness of Airtable: It’s often simply a difference. Airtable relies very heavily on its views and filters, almost more than FileMaker relies on layouts. The goal of a multifactor relationship in FileMaker might (in some circumstancese) fairly easily be achieved in Airtable using a filtered view. That’s part of the reason that right now my single biggest complaint about Airtable is that we cannot yet reorder and/or organize views. I expect that is coming and when it arrives it’s going to be AWESOME.

.

Sorry for the length. (I’m apologizing here to anybody who actually read the whole post. That means you, Mom!) There’s a lot more to talk about but I wanted to take a quick walk around the whole topic. Hope it helps.

Will

Martha,

If you’re coming from FileMaker Pro (like Tim or like me), Airtable’s handling of relationships takes a little getting used to. Once you get used to it, you’ll probably find as I have that a lot of stuff that takes some work in FileMaker is easy as pie in Airtable. Of course, the opposite is true, too. Horses for courses, as they say. :slightly_smiling_face:

.

What the Name field is

The Name column is how Airtable protects casual users from the abstractions involved in database design (like ‘entity’). Unfortunately, calling this column ‘Name’ by default leads, I think, to some confusion.

Whether you’re working in Airtable or FileMaker (or any other database system), within a given table, each row should represent a unique instance of some thing (‘entity’). The unique primary key value for each record guarantees this uniqueness but doesn’t in any way characterize it. If you were able to see it, the primary key 03549674511 would tell you that this record is different from another record whose primary key value = 03549674512, but the key values do not tell you how one record differs from the other (more precisely, how the thing one record refers to in the real world is different from the thing referred to by the other record). In FileMaker, you could create a table that had just one field, the primary key field. This would of course be pretty pointless, but it’s possible. Airtable simply doesn’t let you do that. Instead, Airtable takes care of the ID/primary key for you invisibly, and instead asks you to answer the question “What Is It About This Record That Makes It So Darned Special?” Actually, that’d be a better name for the default column than ‘Name.’ If it weren’t so long.

Anyway, if the entity the table represents is people, ‘Name’ might not be a bad term for the specialness descriptor, especially if your data set is small. But if the table represents an entity that’s not personal, then the column name ‘Name’ can be misleading or confusing, for newbies.
.

What the Name field is NOT

So that’s what the ‘Name’ field is. It may be helpful to think about what Airtable’s ‘Name’ field is NOT.

• As I suggested already, it’s not really a name field. Luckily, you aren’t stuck with either the single line text data type or the column name ‘Name’. For example it can be a Phone Number data type and you could rename it ‘Phone Number’. It could be a long text column. It could be a formula column. And even if each record in the table represents a person, the ‘Name’ field might more usefully be used to store something else. You might make SSN the primary descriptor, or StudentNumber or something like that.
• It emphatically is NOT the primary key for the table or even a proxy or alias for the primary key. As Tim pointed out, Airtable handles all the key values invisibly.
• The ‘Name’ column doesn’t have to have a unique value. If you don’t want to store SSNs in your table (for sound reasons) and you don’t have anything else like drivers license number or student number, then you can create 47 records with the value “Kevin Bacon” in the Name column. Of course, if you do so, then, as far as Airtable is concerned, those are 47 unique and different people all coincidentally sharing the name ‘Kevin Bacon’.
• And while I’m at it I guess I should emphasize that the Name column is not ‘Column A’ in a spreadsheet!! This is where Airtable’s spreadsheet analogy might confuse some users who are familiar with spreadsheets but don’t know anything about data modeling. In a spreadsheet you might make a list of orders placed by customers, and you might put the customer name in column A, the order date in column B, the (unique) order number (if there is one) in column C, etc. If you converted that spreadsheet to a database and you knew what you were doing, you’d understand that column C ‘Order Number’ is the unique defining value, so you’d create a table named ‘ORDER NUMBERS’ and the Airtable ‘Name’ field could be renamed ‘Order Number’.

.

One-to-many (i.e. normal parent-child) relationships in Airtable

In Airtable, because keys are completely hidden from you, you don’t have to think about what’s a primary key and what’s a foreign key, at least not in those terms. You should however think about the cardinality of the relationship, that is, whether it’s one-to-many, many-to-one, one-to-one or many-to-many.

In FileMaker, you control the cardinality in both directions when you relate two tables. On the other hand, in Airtable, cardinality is controlled one way at a time, when you configure the relationship in the configure options for the related column. You do this by checking or un-checking ‘Allow linking to multiple records’. So in the INVOICES main view, you’d check that option because each Invoice record may have multiple linked line items; while over in LINE ITEMS main view, you’d want to make sure you un-checked that option for the ‘Invoice’ column, because each line item should be linked to only one invoice.

This is important because if you don’t — i.e. if the Invoices column on the Line Items view is configured to allow linking to multiple records — there’s no way in Airtable right now (Feb 2016) to prevent user from creating a line-item directly and linking it to multiple invoices. Which would be bad.

.

Many-to-many

For the record, although you have to be in a view to configure these linked columns, a change made in one view will automatically affect the same linked column when it appears in other views. Nevertheless, checking or un-checking that ‘Allow linking to multiple records’ is a UI device, not actually an element in the definition of the relationship. It simply prevents you from linking a record in table B to more than one record in table A, and vice versa. The same was true above when I was talking about cardinality. Checking or unchecking ‘Allow linking to multiple records’ for a linked column in a given table does not really control whether you can link to multiple records any and everywhere, it just controls whether it can be done in your Airtable views from that point forward. It’s a practical UI control, not a mathematical formula.

I say this is just a UI device mainly because we simply don’t have direct access to the relationships; after all, we don’t even have access to the key fields. But Airtable will give you hints about what’s going on behind the scenes, if you pay attention. For one thing, if you start out allowing table INVOICES to link to multiple items in LINEITEMS, and then you change your mind and un-check that option, you won’t immediately lose those multiple linked items that were linked earlier. To a FileMaker user, anyway, it will soon start to seem that, behind the scenes in Airtable, everything is potentially a many-to-many relationship.

To see this demonstrated, create a simply library database with three (just three) tables: GENRES, AUTHORS and TITLES. On each view, configure the linked columns to allow linking to multiple records. Now populate your tables with values, with (for example) records for History, Drama, Poetry, Novels in the GENRES table, with Shakespeare, Keats, Tolstoy and James Joyce in the AUTHORS table, and a selection of titles form each author in the TITLES table. Notice that you can link War and Peace both to History and to Novel; you can switch to GENRES and add ‘Richard III’ to the History record there. (You won’t need to add ‘War and Peace’ because it’s already there.) And so on.

I am pretty sure that what’s actually happening is that the joins are created as required. Can’t do that in FileMaker Pro natively, i.e. without using SQL. But you can do it in Airtable without breaking a sweat: You didn’t create a single join table! No worrying about circular relationships, either. Pretty remarkable, actually.

.

One-to-one

Anyway, back to your one-to-one relationship. In Airtable, you’d do this by leaving ‘Allow linking to multiple records’ UN-checked on both sides of the relationship.

But as was suggested already, you might want to ask yourself if it’s necessary. Might not be.

In FileMaker Pro, there are multiple reasons for creating one-to-one relationships: two reasons I am aware of are security and network load. I don’t have any databases in Airtable that are large enough for network load to be an issue. As for security, well, Airtable’s current security model is much more limited than FileMaker’s but seems capable of handling most of the challenges I worry about. If you don’t want certain users to see certain columns, you can easily create views in Airtable hide columns. The bigger problem with Airtable is that you can’t currently do something like give a user access to a subset of columns in a table, allow that user to edit some of those columns and and not others, and prevent the user from deleting records.

.

Apples and Oranges

FileMaker (and other RDBMSes like Access, 4D and many others) provide rich relational tools that make it easy to do certain things that are either impossible to do in Airtable (at this time) or, if not impossible, at least awkward and kludgy. On the other hand, some of the differences between FileMaker and Airtable are just differences.

In the first class (where Airtable might seem awkward or kludgy to an advanced FileMaker user) would be the fact that Airtable can’t presently see data two or more linked tables away. In FileMaker, if ZIP CODES are children of CITIES, CITIES children of COUNTIES, and COUNTIES children of STATES, it’s easy to display the name of the state that is the great-grandparent of a given zip code. In anchor-buoy, the linked field might look like this:

zipcodes_cities_counties_STATES::StateName

You can do it in Airtable! But it’s awkward: You have to use lookups to pipe values from one table to the next, to the next. Another example: Airtable doesn’t directly permit multifactor links between tables, that is, you can’t link table A to table B when column A1 matches column B7 and column A2 matches column B9. You might do it by linking table A to table B using formula columns in both tables, but that will feel kludgy to a FileMaker user.

On the other hand, this lack of easy multifactor links between tables isn’t always a weakness of Airtable: It’s often simply a difference. Airtable relies very heavily on its views and filters, almost more than FileMaker relies on layouts. The goal of a multifactor relationship in FileMaker might (in some circumstancese) fairly easily be achieved in Airtable using a filtered view. That’s part of the reason that right now my single biggest complaint about Airtable is that we cannot yet reorder and/or organize views. I expect that is coming and when it arrives it’s going to be AWESOME.

.

Sorry for the length. (I’m apologizing here to anybody who actually read the whole post. That means you, Mom!) There’s a lot more to talk about but I wanted to take a quick walk around the whole topic. Hope it helps.

Will

Thank you very much - very helpful! The reason I want to keep the 1-1 is that each table will be constructed in dribs and drabs as various components come in. I am starting out with a full list of vendors from the prior year of our farmers’ market, and it will change quite a bit in the coming weeks. At the same time, applications from new and old vendors are trickling in, and it’s a lot easier to add the little stub of application details to the bottom of a separate table instead of locating the main record and then adding onto it.

We’ll see, though… it might ultimately be best to paste them all together.

Thanks again.

@Martha_Creedon - Check out the search capabilities of Airtable. It may be easy enough to find a record that perhaps an 1:1 relationship isn’t needed.

If not, then we may want to put in a feature request to have the web app filter the records instead of just highlighting them and jumping you to them with the arrows in the search box.

I try to avoid 1:1 relationships as a rule, but sometimes there’s an edge case that makes them necessary.

Check out the search capabilities of Airtable. It may be easy enough to find a record that perhaps an 1:1 relationship isn’t needed.

If not, then we may want to put in a feature request to have the web app filter the records instead of just highlighting them and jumping you to them with the arrows in the search box.

Mike,

Airtable has TWO totally different ways to ‘find’ records:

1. filter (kind of like a standard FileMaker ‘find’ that modifies the found set
2. find (kind of like FileMaker’s ‘find next’ function which simply highlights the find term in the next record)

What is it you think Airtable is missing?

Will

Will,

What I’d like to see is the kind of universal search that they have in the iOS app. Basically the type-ahead filter without forcing you to pick the column to filter.

Thanks,
Mike E.

Hello Mike, this is available via the “find” function (#2 in Will’s response). You can activate the “find” function by pressing ctrl+f (cmd+f on Macs).

That highlights the rows that contain the search criteria, but it doesn’t filter the list like the iOS does (unless I’m doing something wrong). It’s definitely good, but if it filtered the the list, it would be great.

Check out the search capabilities of Airtable. It may be easy enough to find a record that perhaps an 1:1 relationship isn’t needed.

If not, then we may want to put in a feature request to have the web app filter the records instead of just highlighting them and jumping you to them with the arrows in the search box.

Mike,

Airtable has TWO totally different ways to ‘find’ records:

1. filter (kind of like a standard FileMaker ‘find’ that modifies the found set
2. find (kind of like FileMaker’s ‘find next’ function which simply highlights the find term in the next record)

What is it you think Airtable is missing?

Will

Will,

#2. find
This search only will search a single column across the whole table.

If I type in column A (fruits) multi category search term (apples) and then a space and type in column B (meats) multi category search term (beef) the resulting from the search is nothing because “apples beef” is not available under a single column within the records. Say I had a long weekend and remember the beef and apples recipe was incorrect and needed to be checked but couldn’t remember the name. I wanted to do a quick search but am unable. I could create a filter or a new view with a filter, but then it wouldn’t be quick, or intuitive.

The “find” is a single column search across all columns and records within the table, but will not give back results across more than one column.

Sometimes I read these forums and am scared that focus is turning away from a simple user experience trying to do more than a spreadsheet, without the hassle. I know Access can do everything I ever needed, the problem is the learning curve is extreme. Keep up the good work Airtable crew! I am more than willing to give feedback!!

Jason,

Sorry but I’m not sure I understand you. No, that’s not right. I’m pretty sure that I do NOT understand you.

##The two types of search
Let me restate and try to clarify what I said earlier: Airtable has two ways of ‘finding’ values in a table:

1. Filters (click filter icon and define a filter)
2. Finds (click magnifying glass icon and enter some find criteria)

A filter modifies the ‘found set’, that is, it hides records that do not satisfy the filter. When a filter is defined, the column is specified and only the specified column is examined. If you define a filter asking for records that have ‘Washington’ in the Name field, you’ll find Larry Washington and Susan Washington-James, but you won’t find anybody who lives in the state of Washington or on Washington Avenue. For filters, column specification matters.

For magnifying-glass finds, the column doesn’t matter. But finds are limited in two other ways. First a find only applies to the records already returned by a filter (if there is a filter). And second, a filter applies only to the columns that are not hidden on the current view. In short, a find is only applied to what was already on display before you did the find.

Examples

Imagine a table named ‘Animals’ with a total of five rows (records). Because the forum will only let me insert one screenshot, I’ll try to mimic Airtable’s display. I’m using a bullet character • to indicate the break between one column and the next.

_ • NAME • NOTES
1 • Pig • smarter than it looks
2 • Cat • doesn’t like dogs
3 • Dog • man’s best friend
4 • Aardvark • cute!
5 • Coyote • similar to a dog

Now if I define a filter (NOTES contains ‘Dog’), I get this result:
_ • NAME • NOTES
1 • Cat • usually doesn’t like dogs
2 • Coyote • similar to a dog

This doesn’t include the actual record for Dog because the filter looked only in the Notes column.

If I deleted the filter and was once again looking at all five rows, and if I then clicked on the magnifying glass icon and typed ‘dog’, I’d see something like this:

_ • NAME • NOTES
1 • Pig • smarter than it looks
2 • Cat • doesn’t like dogs
3 • Dog • man’s best friend
4 • Aardvark • cute!
5 • Coyote • similar to a dog

Where I’ve applied boldface, Airtable would highlight every occurrence of the string “dog” in the five records that were originally found (i.e. filtered). It does so in multiple columns — values are highlighted both in the Name (for Dog) column and the Notes column (for Cat and Coyote). (Incidentally note that Airtable highlights not just the matching string but the entire cell.)

What about a filter and a find together? In that case, the filter goes first, and then the find does its thing. For example:

_ • NAME • NOTES
1 • Cat • doesn’t like dogs
2 • Coyote • similar to a dog

Here, I had defined a filter asking Airtable to display only records that have “dog” in the Notes column. And then I added a magnifying glass find for “dog”. This highlights “dog” wherever it occurs but only in the two records found by the filter. The find does NOT override the filter and display the record whose name = “Dog”.

Now, say I leave the string ‘dog’ in the magnifying glass’s find-what field, but change the filter so it asks for rows whose Name column contains the letter “o”. Now I’ll be looking at this result:

_ • NAME • NOTES
1 • Dog • man’s best friend!
2 • Coyote • similar to a dog

Here again, the filter is applied first, and then the find.

##Bravo, Airtable!
Both of these ways of searching are valuable, the ability to use them together is important — and yet it’s not really complicated at all, after a little practice. This isn’t especially innovative but I think Airtable’s developers have done a terrific job of making these tools work in your browser.

##And on the iPhone?
Okay, things do work a little differently on the iPhone. I’m not completely thrilled about that, although I think I know why Airtable’s developers have allowed this difference of behavior.

On the iPhone, the magnifying-glass find is basically the same as on the computer in your web browser, but on the iPhone, a find also behaves a bit like a quick and dirty filter, in that it modifies the apparent found set. If the filtered set of records originally contained five rows but only two of them match the find string, on the computer you’ll be looking at five records two of which are highlighted, but on the iPhone you’ll be looking at just the two records.

I’d bet five cents that it’s done for UI reasons: so that more records can be viewed on the mobile display’s limited real estate. But, like on the computer, the find on the iPhone only narrows the set returned already by any filter that is active for the view. Here again, the find does not override the filter. Here’s an example from the same base I was using above, but viewed on my iPhone:

The filter previously applied on this view (not visible in the screenshot) shows records that have ‘dog’ in the Notes field. That’s just the records for Cat and Coyote. Now, on top of that, I did a magnifying-glass find for the string ‘dog’. NOTE that we’re still only looking at two records. If there weren’t a filter active on this view, we’d be looking at three records: the records for Cat and Coyote (which have ‘dog’ in their Notes column) and the record for Dog as well.

So even on the iPhone, a magnifying glass find does not search through all records.

Will

Examples

Imagine a table named ‘Meals’ with a total of 4 rows (records) and 4 columns. I’ll try to mimic your mimic. I’m using a bullet character • to indicate the break between one column and the next.

_ • NAME • Fruits • Vegetables • Meats
SouthWestern • Tomatoes • Corn • Chicken
Breakfast Balance • Apples • Broccoli • Egg
Beef Starch Yum! • Tomatoes • Potatoes • Beef
Easy Canned “Chilli” • Tomatoes • Beans • Chicken

If I then clicked on the magnifying glass icon and typed ‘Tomatoes Beef’, I’d see something like this:

0 results

Therefore “The “find” is a single column search across all columns and records within the table, but will not give back results across more than one column.”

Examples

Imagine a table named ‘Meals’ with a total of 4 rows (records) and 4 columns. I’ll try to mimic your mimic. I’m using a bullet character • to indicate the break between one column and the next.

_ • NAME • Fruits • Vegetables • Meats
SouthWestern • Tomatoes • Corn • Chicken
Breakfast Balance • Apples • Broccoli • Egg
Beef Starch Yum! • Tomatoes • Potatoes • Beef
Easy Canned “Chilli” • Tomatoes • Beans • Chicken

If I then clicked on the magnifying glass icon and typed ‘Tomatoes Beef’, I’d see something like this:

0 results

Therefore “The “find” is a single column search across all columns and records within the table, but will not give back results across more than one column.”

Jason,

Great, thanks for replying. Let’s wrestle this to the ground.

First, you’re talking about the iPhone, right? I assume that’s true because in the web browser on the computer, your example doesn’t work as you say. If you typed ‘tomatoes beef’ on the computer, you’d still see the same records you were looking at initially. Just nothing would be highlighted.

Second, I simply don’t understand your comment that the magnifying glass finds “across all records within the table.” The way I understand your words, that’s just not true. Even on the iPhone, the scope of a magnifying glass find is the set of records returned by the active filter. If you had another column in your example that would indicate whether the meal was Hot or Cold, and you’d filtered to show only Hot meals, if “tomatoes beef” actually happened to occur in a column in one of the Cold meal records, it will wouldn’t be returned, because, as I said, the filter limits the scope of the find. So finds do not look “across all records in the table.” Not even on the iPhone.

Third, I don’t mean to quibble, but I think saying the find is a “single column search across all columns” is unclear if not downright misleading. I’d prefer to say that the value you type into the find-what field is treated as a string. Airtable will look in every column in the current view of the filtered records, but it’s only going to find exact and complete matches for the string you typed. If I modified my own example base so that the note for Coyote read “bit like a dog” (and Cat’s note still read “doesn’t like dogs”) and I asked Airtable to find “like dog”, on the computer it would highlight only the notes cell in the Cat records. It wouldn’t flag Coyote because “like a dog” does not contain the string “like dog”. That’s just how string searches work. If you want to look for xyz in column 1 and bcd in column 3, use a filter. Works same way on iPhone.

Will

Will,

I am not referring to the iPhone app. I am using Google Chrome and supplied a screen cap.
You have created a much better explanation using the like dog example and explaining that the search is a string.

In my example picture I have lots of assets and an assets table. I have different ways to label an asset. I see more value in a google like search than simply a string search. If the aim is intuitive and simplicity, then my search (as seen in screen cap) should return results. I kept the record visible in screen cap not to fan a flame and say I want or expect a feature that has no use, but because you can see the result wanted on the screen. I show the records because they could be buried deep among 100’s of other records, but they exist, just not shown through my “find” search.

I do not mean to quibble either because I love google and they have a search bar I love!! I also included a screen cap to make clear what search i intend to type and what results i expect out.

My comment earlier holds true:
" I could create a filter or a new view with a filter, but then it wouldn’t be quick, or intuitive."

If the search gave back results that had tomatoes and beef, contained somewhere within a record, across all the columns, this would be more intuitive. If you further created the ability to use commands in the “find” it could make it more intuitive. Ex. adding quotes around a string to do an exact match search.

I applaud you on explaining very clearly how the “find” search works. My intention is to share how I think the search would intuitively and quickly work, without regard to how it does work.

I will certainly create views with filters to fulfill specific needs. I look to the “find” search to be a powerful intuitive search capability when I need to find that one record that needs to be addressed.

Also, feel free to check out my base to get inside my head. You can get an idea of wtf I am doing on this site beside to quibble with people that actually understand database stuff. :stuck_out_tongue:

Also, Will, your patience should be commended!

Will,

I am not referring to the iPhone app. I am using Google Chrome and supplied a screen cap.
You have created a much better explanation using the like dog example and explaining that the search is a string.

In my example picture I have lots of assets and an assets table. I have different ways to label an asset. I see more value in a google like search than simply a string search. If the aim is intuitive and simplicity, then my search (as seen in screen cap) should return results. I kept the record visible in screen cap not to fan a flame and say I want or expect a feature that has no use, but because you can see the result wanted on the screen. I show the records because they could be buried deep among 100’s of other records, but they exist, just not shown through my “find” search.

I do not mean to quibble either because I love google and they have a search bar I love!! I also included a screen cap to make clear what search i intend to type and what results i expect out.

My comment earlier holds true:
" I could create a filter or a new view with a filter, but then it wouldn’t be quick, or intuitive."

If the search gave back results that had tomatoes and beef, contained somewhere within a record, across all the columns, this would be more intuitive. If you further created the ability to use commands in the “find” it could make it more intuitive. Ex. adding quotes around a string to do an exact match search.

I applaud you on explaining very clearly how the “find” search works. My intention is to share how I think the search would intuitively and quickly work, without regard to how it does work.

I will certainly create views with filters to fulfill specific needs. I look to the “find” search to be a powerful intuitive search capability when I need to find that one record that needs to be addressed.

Also, feel free to check out my base to get inside my head. You can get an idea of wtf I am doing on this site beside to quibble with people that actually understand database stuff. :stuck_out_tongue:

Also, Will, your patience should be commended!

Jason,

This is good. I think we’re almost there. Thanks for hanging in there with me.

So, I gather what you’d like is to be able to type a couple of unrelated terms (“beef tomatoes”) and have it parse those terms out into a search that (a) finds “beef” anywhere and then (b) finds “tomatoes” anywhere, and finally shows both sets of matching records together. You’re right, this is how Google and many other very large scale search engines work.

I remember first encountering this sort of thing in Lexus legal database searching eons ago, but back then you’d also have to use codes to look for keywords within so many words of one another (e.g. "find the words ‘parental’ and ‘rights’ within 5 words of one another).

Now this isn’t impossible, in fact, it’s not even that difficult. If Airtable implemented regex/grep searching, for example, you could do this and a whole lot more. That might be cool for people like me who own multiple editions of Jeffrey Friedl’s book on Regular Expressions, but I will tell you as a developer myself that I don’t think it’s worth the trouble of Airtable’s programmers. Not enough users will want to do this. I think the Airtable developers have other things to do more urgently. You may disagree. It could also be done if you took advantage of Airtable’s API and programmed a routine (say, in Javascript) that could do it. I’ve done it in FileMaker Pro. But it’s a little more work and as I said, I don’t think that many other users would take advantage of it.

DO keep in mind that you can get yourself part of the way to what you want by creating a formula field that concatenates several fields, and then defining a *this-OR-that" filter upon that field.

It’s NOT what you want, I understand. You want a super easy fuzzy search. But keep in mind that giving you exactly what you’ve described would either mean adding a new search method, or more likely, would require all of us who like the way things work now to start having to put quotation marks around our search strings — just as you have to do when defining a normal Google search. It works in Google because Google is searching a gazillion different databases each of which has its own idiosyncratic structure. But in Airtable (or FileMaker or Access or whatever) you’re searching a database whose structure is presumably pretty familiar with you.

And I’d gripe if I had to type quotes around my frequent string searches.

Best wishes,

Will

Hey Will,

I find it a shame you are constantly going back to this access and filemaker crap as some sort of standard. I spent hours participating in the forums and populating my base after reading an inspiring article by Howie. I thought Airtable was trying to create something more than what developers will be familiar with, and people in the database know can feel ultra familiar with. “You want a super easy fuzzy search” and I ask you, which onboarding user would not want the same from a magnifying glass? They want what search bars do. Not what this complicated explanation of why it searches just a string within a column does. (http://www.infoworld.com/article/2927917/database/the-next-generation-of-spreadsheets-will-be-a-database.html)

Databases, on the other hand, were for everything organization-related – a pet fish distributor’s shipment list or a ballet troupe’s subscriber mailings, for example.

Unfortunately, databases were never quite as easy to use as word processors or spreadsheets.

For instance, dBase required the user to design a database in code with a domain-specific language; even later programs such as Access, FileMaker, FoxPro, and Lotus Approach required users to learn a confusing schema designer interface.

For the sake of convenience, but at the cost of real data structure, ordinary users began using spreadsheets as makeshift databases.

The solution: A real database with spreadsheet simplicity

What users really need is a product that combines the fast and flexible aspects of a spreadsheet’s interface with the structure of a true relational database. By focusing on organizational use cases, this lightweight database would not compromise design elegance for the sake of number-crunching capabilities.

This is easier said than done, however. Core database concepts such as multiple views, many-to-many relationships, and forms need to be made accessible in a reimagined design that feels immediately intuitive to users.

Our design and engineering team iterated upon its interface design until it was as intuitive as possible for the user. We are the originators of this emerging category, and we hope others will join us in bringing more powerful but intuitive tools to the mainstream user. Spreadsheet or database? It doesn’t have to be a choice, and it certainly shouldn’t feel like a battle.

So I ask:
Are you developing for the petfish distributor?
For the ballet troupe?
For me?
Or was Howie wrong and his vision failed?
Is this simply for devs and DB pros?

I am simply the immediately intuitive champion.

Thank you for your time Will. I am sorry we do not see the search bar the same way and it is not worth your time.

Jason,

I’m afraid that we hijacked this thread — and we’ve probably exhausted the subject. Just for the sake of clarity as we wrap it up:

• Sorry if I wasn’t clear about this: I never meant to refer to Access or FileMaker as standards. They certainly are NOT — any more than Airtable is. They’re simply points of comparison — and have the benefit of being very widely used by the kind of people who might also be interested in Airtable.

• I didn’t say that making Airtable’s search work like Google’s was or would be a waste of my time — or anybody’s, for that matter. I simply tried to say that the Airtable developers have a lot on their plate and I doubt this is a priority for them. There are 100 things I’d like to see Airtable do differently, or better, or things I’d like to see it do that it doesn’t do at all presently. But I understand that I may have to wait for my wishes to be fulfilled.

Be happy to pursue this off the list if you’d like to email me directly. Cheers!

Will
wp at rucksack-texnology.com