Hope the title is not misleading, anyway, let me try and explain.
I have a small database that to form teams and keep track of scores.
This database has three tables: Events, Competitors and Teams.
There are several different events through the year and competitors can participate in or many of these events. On the events in which they participate they must form a team (two men team) and these teams could be different for every event.
When a competitor signs up a new record is created in the Competitors table with a unique signup code, which allows me to validate through a unique value (the signup code)...so I got that covered.
On competition day the two men teams are conformed. For this I have two fields "Competitor A" and "Competitor B" that are drop down lists that pull data from the competitors that signup in the Competitors table.
My big conundrum is that I can validate for unique competitors in the Competitor A field and likewise in the Competitor B field, but I cannot find the way to validate in such a way that when I enter a competitor's name, from the drop-down list, on Competitor A field it can also validate that this name is also unique on the Competitor B field...and of course the other way around, that competitors on the B field are not duplicated in the A field.
Any help will be most appreciated and if I haven't made myself clear, please ley me know so I can clarify whatever is needed.
BTW, this database is for a single user and its being developed with FileMaker Pro 16 Advanced on a Mac with High Sierra, but will eventually be used in the field on an iPad with FileMaker Go.
Thanks in advance,