You need to tell us about your tables and relationships before we can give you advice. Not enough information in your question.
Oops sorry, Ok so for my tables I have the main Trust table then I have – Roles (has trustee, has beneficiary etc) – Entity (natural persons, Company etc)
For my relationships I have the TrustID in my Trust table going to TrustID in my Role table then from my Role table I have EntityID going to EntityID on my Entity table.
Many thanks for your help
Hi Shinobi, good evening. Base on my understanding of your problem, you don't want to have a duplicate value on your Roles table which contains both TrustID and EntityID. If this is the case at least from my point of view, you would want the combination of your RolesID (from your Roles table), TrustID (from your Trust table), and EntityID (from your Entity table) to be unique and the way you could do that is to add a calculation field to your Roles table that concatenates these three fields and has a validation set to "unique".
Say you have a calculation field named ConcatenatedID and have a calculation like below:
RolesID & TrustID & EntityID
after defining the calculation above, you would then set this field's validation to unique by checking "unique" on the "Validation" tab.
this will give you the combined values of RolesID, TrustID, and EntityID like Role1Trust1Entity1 and will prevent you to have another occurence of Role1Trust1Entity1.
There you have it Shinobi, hope this helps or at least give you an idea.
and the way you could do that is to add a calculation field to your Roles table that concatenates these three fields and has a validation set to "unique".
Not quite. Make that a text field with an auto-entered calculation and this will work. You can't set a field validation option on a field of type calculation, but you can with auto-entered calculations.
There are also other options where the user interface doesn't allow you to create a duplicate record in the roles table in the first place.
See the method demonstrated in this demo file. One uses Jim Paul's validation method on a field with an auto-entered calculation, but then also demonstrates two layout designs where the user is not permitted to create the duplicate value instead of catching the error after it has occurred.
This file is in the older format. If you are using FileMaker 12 or 13, use Open from FileMaker's File menu to open this file and FileMaker will produce a copy of this file converted to the newer file format.
Oops, my mistake. Thanks PhiliModJunk for pointing that out. Btw, I'm interested in the other method you have mentioned. and maybe I could use the same technique.
You are welcome to download and examine the demo file. It demo's three methods:
A validation field option
A diminishing value list where each value selected for the join table drops that value from the value list so that it cannot be selected a second time.
A "check boxes" format (that aren't really check boxes) for selecting values from a portal. Since selecting the same value a second time clears the "check box", the user does not have an option that allows them to select the same value twice.
Thank you so much for your help its really appreciated - I will have a go!