Link to another record only once

[Brian_Croxall]

I’m building a database to track uniform assignments for the local high school marching band. I have one table for students, another for the jackets, another for the pants, and so on.

In the Students table, I have a field using the link to another record option linking to a jacket. I’ve been careful to make sure to not Allow linking to multiple records.

I’ve then gone into the Jackets table, and made sure to not Allow linking to multiple records.

What I can still do, however, is assign the same jacket to multiple students.

And when I go to the Jackets table, I can see that the same jacket is assigned to two people.

I would love to have this set up in such a way so that once a jacket is assigned to one student, it is not allowed to be assigned to a second one. Is there a way to do this?

[kuovonne · Accepted solution]

You need to create a filtered view in the [Jackets] table that shows only jackets that are available (do not have a value in the linked record field).

Then, in the {Assigned Jacket} linked record field in the [2021 Students] table, limit record selection to the filtered view showing only available jackets.

If you will be assigning jackets to students, repeat the same procedure in the opposite direction.

Note that existing jackets that are assigned to multiple people will still be assigned to multiple people. And it will still be possible to assign the same jacket to multiple people using the API. However, this solution should suffice for most regular users.

[Brian_Croxall]

That was exactly what I needed to know. Thanks so much for your help!

[Simon_H]

Sounds similar to an issue I encountered here

Duplicating Records Creates Invalid Record Link Ask the community

I came across an issue recently where a linked record column in table ‘A’ without the option to allow multiple linked records, still contained some records with multiple links to records in table ‘B’. Upon researching the matter it turns out that when you duplicate a record in table ‘B’ it duplicates the entire record including the column tracking the reverse link to records in table ‘A’ and therefore causing the record in table ‘A’ to now contain a second link to the newly created record. I u…

Ultimately my solution tp prevent unwanted multiple links was to lock the link column in one of the tables