Link to another record only once

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.

2021-05-26_16-01-42

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

2021-05-26_17-00-57

What I can still do, however, is assign the same jacket to multiple students.
2021-05-26_17-03-55

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?

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.

1 Like

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

1 Like

Sounds similar to an issue I encountered here

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

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.