Help

One-to-Many relationship is not enforced

Topic Labels: Base design
1290 2
cancel
Showing results for 
Search instead for 
Did you mean: 
FlyingPenguin
4 - Data Explorer
4 - Data Explorer

Hi All,

I'm trying to create a simple base that allows me to track items in boxes. An item can only be in one box so a one-to-many relationship seems like the right thing here. I added the link field to my Items table and made sure that "Allow linking to multiple records" is off in the settings. This works when I'm adding items and putting them into boxes. However, when I open the Boxes view and add an existing item to a box, it does not get removed from the previous box. Instead it ends up being in multiple boxes at the same time, exactly as if it was a many-to-many relationship.

This looks like a bug to me. Is there any way to move an item without having to remove it manually from the old box?

Thanks for your help, P

2 Replies 2

Airtable is working as designed. The “allow linking to multiple records” is a user interface setting preference only and this choice is not enforced anywhere else in Airtable. 

I’m not saying that I agree with this design choice. I’m saying it is a long established fact. 

A common workaround to avoid an item being in multiple boxes is to create a filtered view that only shows items not in boxes. Then in the linked record configuration in the boxes table, limit selection of records to that filtered view. 

Note that it will still be possible to create a many-to-many situation by setting linked record values on other ways, such as copy/paste, scripting, and the web API.

As for moving an item from one box to another, that is a different matter. You could group your items by box and drag your items from group to group. Note that this is done from the items table and not the boxes table. 

You could use an automation that watches for changes in an item’s box and when an item has two boxes, the automation removes it from the previous box. But automations are slow.

FlyingPenguin
4 - Data Explorer
4 - Data Explorer

Thank you for your response, kuovonne and for providing tips how to work around this issue.

I found this blog post from Airtable: https://blog.airtable.com/how-to-establish-relationships-and-rules-within-a-database/

It says:

"For example, if you specifically define a relationship as one-to-many, you can enforce a rule that the records on the “many” side of the relationship can, indeed, only ever be linked to one record in the other table."

With what I'm observing and what you're confirming, this is not the case then, right? As in, I can define a relationship as one-to-many and still link records on the "many" side to multiple recodes in the other table.

Is there a way to file a but report with Airtable about this? It's a strange oversight that you can't more items from the view on the many side.