2 Replies Latest reply on Jun 6, 2010 12:56 PM by user447

    How to set up the relationship when looking for a duplicate



      How to set up the relationship when looking for a duplicate


      I have a file of active people and a file of inactive people.  I want to somehow set up a relationship so that if when creating a new record in the active file, if the new person's social security number exists in the inactive file, then I get an alert that he already exists.  If he already exists in "inactive" I could then import him back into "active."

      But I can't think of how to get this set up.  Any help is always appreciated!


      -Jane in LR

        • 1. Re: How to set up the relationship when looking for a duplicate

          You need a relationship based on SSN* between the active table and the inactive table. You might want a self-relationship with the active table also, to prevent duplicate SSNs there. I can think of a few ways to do this.


          1. Script Trigger on SSN field, OnObjectSave. Script Commits, then tests for Count ( relationship::SSN ) > 1 (duplicate).**


          2. Field validation, similar tests. But awkward, as the validation kicks in before the Commit. I would use #1 above.


          The advantage of a Script Trigger in this regard is that it could tell you where the duplicate exists. It could even tell you who (check that the name is the same, to verify your typing), with an option to make the person active again (if inactive).


          Field Validation is good for some things. But if there can be more than one thing wrong, it has a hard time telling you which, as the dialog is one of the only places in FileMaker which does not allow you to use a calculation.


          Script Triggers require that you attach them to the particular instance of the field, which is awkward if it pre-exists on a lot of layouts. But an enterable SSN should only be on a few layouts.


          * I would create a stripped-down calculation field, with result number, to test. In case someone entered the dashes wrong, or left a space at the end. Or have the SSN field auto-enter by calculation, with [  ] Do not replace contents unchecked, to strip then reformat. Basic SSN as digits-only calculation:


          Filter ( SSN; "0123456789" )


          ** Actually I would use what I call a "not_me" relationship. Which is the same relationship, but adds a criteria to exclude the current record from the match. If you have a unique primary ID, "PersonID", then it would be:


          SSN (calculation) = Inactive::SSN (calculation)

          PersonID ≠ Inactive::PersonID


          Assuming you push the same ID to the Inactive table, the test is then: not IsEmpty ( Inactive~SSN_not_me::SSN )

          • 2. Re: How to set up the relationship when looking for a duplicate

            I set the relationship as

            SSN = SSN


            ID does not = ID


            Is that right?


            I'm Not sure of the steps for the script trigger.


            Still pondering.....