Platform: FileMaker Pro 17.
Scenario: I've set up a portal that allows me to capture a list of organizations related to a given person. This portal is using a relational table that I created to house the relationship between people and organizations. (A many-to-many scenario.) This is necessary since organizations are not limited to one person, meaning that other persons can share the same organization IDs, or each person may be affiliated with another set of organizations.
In order to populate the portal, I've placed a button that adds a new row to the portal. With the new row in focus, there's a pop-up menu field that allows me to pick from a list of organizations (LOVs). Please note that organizations have their own table and its the data source for the LOVs. As I select the organization from the list, there's a script that captures the organization ID and the person's ID and populates them into the relational table. The portal is working for me, as expected.
Question: After a person has a list of organizations populated in the relationship table, how do I then omit the already selected organizations from the remaining list of values under that same given person, so that, it can't be selected again? Is there a way to reduce the list of values after a given person/organization combination is created in the relationship table?
Example: Person-A has organizations A, B, and C already populated in the portal under their record. The expectation is that the LOVs should no longer show organizations A, B, and C. Yet for Person-B, that doesn't have any selected organizations yet, all organizations should be available in the LOVs for selection. I suspect that the LOV setup may require some type of SQL, but I'm not 100% certain if this is the right approach. Any suggestions?