1 of 1 people found this helpful
The typical many to many relationship uses two foreign keys in the join table, one for each of the two parent tables that link to it:
Products::__pkProductID = Inks Selected::_fkProductID
Inks::__pkInkID = Inks Selected::_fkInkID
While the value in _fkProductID is cannot be required to be unique over all the records of the join table and the same is true for _fkInkID, you can specify that the combined values of these two fields must be unique.
Define a new field in Inks Selected as a text field: UniqueKey. Specify this auto-enter calculation:
_fkProductID & "|" & _fkInkID
Clear the "do not replace existing values" check box.
Then set a unique values validation on this newly added text field.
It's also possible to design the methods for selecting inks for a product in such a way that it is not possible to select the same ink twice. A dwindling value list can be used such that each time the user selects an ink, it drops out of the value list that appears for that product. Or what looks like a set of check boxes displayed in a portal can be used for selecting the inks. Clicking a "check box" for an ink the first time selects it. Clicking it a again removes that selection (by deleting the join table record) and thus it is not possible to select an ink more than once for a given product.
When I use this method to prevent duplicate entries it allows me to input duplicate entries until I commit the parent Product record. It then provides me an error. Is there anything I can do about that?
Note: I integrated this approach also using a Dwindling Value List method explained here (filemakerhacks.com). I'm hoping that this isn't causing a conflict. Thanks for the giving me the term to find out how to do create that.
I found your response here portal validation and I tried integrating that. I wasn't super satisfied with the responding behavior but I'm going to work on a script that dialogs and tells the user it is going to delete the portal row on validation fail.
Possibly a silly question. How do you write an IF statement in filemaker where the test is for a unique value?
I was thinking something like this:
Commit Records/Requests [ No dialog ]
If [ Project_ColorsSelected::IDf_Project plus IDf_Color ]
Show Custom Dialog [ Title: "WARNING"; Message: "You entered a duplicate Ink. This second occurance will be deleted"; Default Button: “OK”, Commit: “Yes” ]
Delete Portal Row
1 of 1 people found this helpful
I described the validation method as it is the simplest method to set up. I would actually use an interface design for this that keeps the user from making this mistake in the first place. This validation then just becomes "insurance" in case an oversight in my interface design still allows a user to produce a duplicate.
For the script you have, I would use:
Set Error Capture [on]
IF [ Get ( LastError ) // record is a duplicate ]
Show custom dialog
I think that will work with the revert record step, but since this is a record in a related table, you'll need to test this to be sure that this works for you.
My measure in the interface design for prevention is the dwindling value list but I'm wanting to use a drop-down-list control style so that a user can speedily enter data without mousing around if desired. The allows the user to quickly enter a number from memory but opens the potential for a duplicate thus the "extra" validation measure. The standard Pantone ink set consists of 1,341 inks alone which is just one of many ink books. Do you have a suggestion that you may think may be a better way?
A dwindling value list is frequently a drop down list. a 1000+ values is not an insurmountable list of values to use with that method as far as I know. A script triggered by OnObjectSave or OnObjectValidate can also do the duplication check by confirming if the entered value is not in the value list.
I don't know how practical it would be, but a portal with a list of "check box" controls could be set up to work with a field placed near the portal that controls which sub set of all possible inks appear. You'd select a general category to get a manageable list of inks and then click to select one. Or you'd type in a few characters into a different type of search field and only records that start or contain those characters appear in the portal.
I'm brainstorming as I type here. Another option might be to use a separate data entry field for entering/selecting the ink with a trigger performed script that takes that info and creates a new join table record only after checking to be sure that such a value isn't already in a join table record for the current product.
The problem is one project may use Pantone 100 and Pantone 874 while the next will use Pantone 382. It really is completely random. I was considering using a sort based on calculation for the most used inks to be at the top - this could even be amplified by doing it on a per customer or on per customer per brand basis as similar products in a brand family are very likely to use the same inks.
Yet such a control could bring up the colors in numeric ranges or by the closest Primary color, or....