Linked field allowing multiple entries when set to single

[Julian_Kirkness]

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.

[Julian_Kirkness]

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:

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.

[Matt_Bush]

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.

[Julian_Kirkness]

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

[Thomas_Phillips]

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

[Andrew_Ballem]

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?

[Atoumbre_KOUASSI]

I hope improved Link field is now inthe roadmap.

[DS_1234]

This is still a problem that needs to be addressed. We need to be able to designate one person on an Accounts table record as the Primary Contact by linking to Contacts, while another column on Accounts contains other related contacts like in Salesforce.

[Dan_Smellow]

A workaround is to create a new view in the related table with a filter that will never show any records, then restrict the link to use that view.

My use case was Client and Enrollment. I created a new new grid view in Enrollment labeled ‘utilityViewNoRecords’ and matched a calculated field to an impossible outcome so no records would ever display. Then in client, I unmarked ‘Allow linking to multiple records’ and marked ‘Limit record selection to a view’, choosing the no records grid view I just created. Now when I’m in Client and add an enrollment, no previous enrollment records display in the entry dialog and there’s no chance of cross-linking a record.

[Marc_Trumpi]

I agree. I’m not very experienced yet with Airtable, but it’s really counterintuitive to see multiple records coming up in a linked field set to single records.
That, combined with lack of any kind of input validation makes it a bit…amateuristic… I mean, it’s nice for home projects that way and I love the simple interface, but having all these possibilities for database corruption…I don’t see me using this for business applications.

[Brian_Sturm]

This worked for me! Thanks for the update Dan.