Unique Child Records
If you have a many to many relationship where you want the records in the join table to be unique, what do you do?
For example, lets say you have PRODUCTS and each product requires that VENDORS sign a release form to handle the product. You only want one release form for a vendor for a given product. Likewise, if you were to view things from a product table based layout, you only want to see a vendor listed once for the product release form.
It's still a many to many relationship between PRODUCTS and VENDORS
So we might have a three tables: PRODUCTS, PRODUCTRELEASE, VENDORS
Then, we might work from a vendor based layout, where each time a product release form comes across the desk, we search for the vendor, and then choose a product that hasn't already been chosen. In the event we choose a Product redundantly, we want to be able to bounce that redundant attempt without recording it. So at this point, not really knowing what a standard approach for this would be, I've created a field-aka-attribute in the ProductRelease table that concatenates ProductIDf & VendorIDf, and is defined to be unique (in the validation tab). Just concatenating ProductIDf & VendorIDf isn't enough, so the I use "P" & ProductIDf & "V" & VendorIDf.
This seems to work, but what I don't like about it is that if I make the mistake of trying to add a non-unique child record, I end up having to close two different dialogues that FileMaker presents to me. These are the revert dialogues. I'd prefer to have only one dialog, which is less confusing, especially when dealing other things during a busy day.
I'm also wondering if there isn't another way to deal with this scenario.