9 Replies Latest reply on Oct 31, 2014 3:04 PM by philmodjunk

    Prevent duplicate records on join-table per parent Record

    mknightnps

      Title

      Prevent duplicate records on join-table per parent Record

      Post

      I have a products layout with a portal to a join table where a user can assign inks to the product. I want to prevent a user from assigning the same ink to a product multiple times. The inks can be used with many products.

      Products Table > Inks Selected Table < Inks Table

      How do you resolve this? I searched around and perhaps I'm phrasing it wrong but I could not find an answer.

        • 1. Re: Prevent duplicate records on join-table per parent Record
          philmodjunk

          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.

          1 of 1 people found this helpful
          • 2. Re: Prevent duplicate records on join-table per parent Record
            mknightnps

            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.

            • 3. Re: Prevent duplicate records on join-table per parent Record
              mknightnps

              Phil,

              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.

              Many thanks!

              • 4. Re: Prevent duplicate records on join-table per parent Record
                mknightnps

                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:

                OnObjectExit

                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

                End If 

                • 5. Re: Prevent duplicate records on join-table per parent Record
                  philmodjunk

                  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]
                  Commit Records
                  IF [ Get ( LastError ) // record is a duplicate ]
                     Show custom dialog
                     Revert record
                  End IF

                  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.

                  1 of 1 people found this helpful
                  • 6. Re: Prevent duplicate records on join-table per parent Record
                    mknightnps

                    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?

                     

                    • 7. Re: Prevent duplicate records on join-table per parent Record
                      philmodjunk

                      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.

                      • 8. Re: Prevent duplicate records on join-table per parent Record
                        mknightnps

                        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.

                        • 9. Re: Prevent duplicate records on join-table per parent Record
                          philmodjunk

                          Yet such a control could bring up the colors in numeric ranges or by the closest Primary color, or....