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:
- prevent this from happening
- 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.
3 Likes
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.
5 Likes
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.
3 Likes
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
3 Likes
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
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?
3 Likes
I hope improved Link field is now inthe roadmap.
2 Likes
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.
2 Likes
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.
2 Likes
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.
2 Likes
This worked for me! Thanks for the update Dan.