Linked field allowing multiple entries when set to single

#1

Hi

I am working on a database for a client and have set up a jon table to cater for a many to many relationship (so that I have a record for each which can be actioned by Zapier as well as good database design). I have discovered that when trying to add a new link via one on the linking main tables (Clients) that if you pick a record that is existing and already joined to another Client that it allows it and results in more than one entry in the field (in which the Allow lining to multiple records in UNTICKED):

Could you:

  1. prevent this from happening
  2. do something about the process of adding a record from one of the main tables (not the join table) - currently when you click the + (add) button it gives you a list of records to add which is rubbish with a join table - what you want to do is actually add a new one - and you should therefore get the new record popup view.
One-to-One Linking Not Working
#2

I notice that this is still an issue and thought I should give another use case where this is a problem…

I have been working on a base to deal with HR issues - and have an Employee table and an Absences table. Now, clearly, each Absence can only be associated with ONE employee and if you’re entering Absences for a person (from the Employee table) NONE of the other absence records could possibly be relevant to add - however you get this option:

and then:

Screen Shot 2016-12-20 at 10.48.19.png590x513 44.5 KB

Now, by definition, the only relevant choice is to add a new record and picking one of the other records is both incorrect from a business point of view but also results in an Absence record with two linked employees when the field is set to only allow one:

Here’s the field definition:

I appreciate that Airtable is designed to be really approachable but I do feel that it should apply basic referential integrity rules behind the scenes - otherwise there is a danger that data will be compromised accidentally.

#3

Thanks Julian for sharing this feedback and the examples, and for following up.

As of now, the “Allow linking to multiple records” option is a soft constraint that only affects the input interface for that field, not for its symmetric field, so it is possible to for a record to link to multiple records even if that checkbox is unchecked, if those links originated from the other table.

There isn’t an existing mechanism for referential integrity as you described, but as a workaround you could use Count fields to detect records with too many links to act as a validity check in filters, and could use Formulas in the primary field of your join table to easily differentiate records which are eligible to be linked from the main table.

While it’s not on our immediate roadmap, we do plan on offering more customization for the Link field type in the future - including hard-constrained linking with true referential integrity as you’ve described, plus improvements to self-linking tables, and the ability to filter suggestions when finding a record to link to.

Single selection in linked fields
#4

Hi Matt

Thank you for responding - and it’s good to hear this stuff is on your radar - I know it all sounds so technical and only of concern to database types like me - however these features are there for a reason in database design - to make sure that data integrity is maintained. As you customers build bigger databases and have more users using them this becomes much more important - being a developer I’m sure you understand this.

Julian

#5

Hello,

I noticed the same issue today when trying to prevent multiple linking of records where it is not relevant. It would be really useful to me in reducing the clutter on the interface and prevent data entry errors. So that you are forced to create a new record when relevant. Is this in the roadmap now?

Thanks,
Thomas

#6

I’ve started using this today and got really excited! I’m trying to make a database of documents and their revisions. Revisions go A, B, C etc. I set up a related field, made a revision A for document 1, it created a record in the Revisions table. Perfect! I went to document 2 and I was able to pick the revision I’d just made for Document 1 from this list! No no no! That isn’t how relational databases are supposed to work!

Each revision of a document belongs to one document only, because it has the revision date, who it was sent to, etc. Why is this functionality not built in?