5 Replies Latest reply on Feb 7, 2012 9:37 AM by philmodjunk

    Search Field on Exit

    ZoocMan85

      Title

      Search Field on Exit

      Post

      Hey Everyone,

      I’m trying to write a script to search a field on exit but I’m drawing a blank. I have SSN field and i want the SSN field to be searched when a user exits the field to see if there is any duplicates in the system before continuing. What’s an easy script to make this happen? If duplicate exists with that social security number then give user option to go to that related record through a custom dialog and delete the new record they were entering or hit continue and override it. That’s what I’m looking to accomplish. Any help on this would be appreciated. Thanks.

        • 1. Re: Search Field on Exit
          philmodjunk

          Define a "self join" that matches by SSN.

          YourTable::SSN = YourTable 2::SSN

          In Manage | Database | relationships, make a new table occurrence of YourTable by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box.

          We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

          Now this script, set to run with OnObjectExit or OnObjectSave can check for duplicate records:

          If [ Count ( YourTable 2::SSN ) > 1 ]
             Show Custom Dialog ["A duplicate record exists...."]

          • 2. Re: Search Field on Exit
            ZoocMan85

             Do i have to link both of these by SSN for the relationship?

            • 3. Re: Search Field on Exit
              philmodjunk

              "both"? there's only one table here linked to itself by the SSN field. That's what the first part of my last post describes.

              • 4. Re: Search Field on Exit
                ZoocMan85

                 it seems it only works if the record is commited first.

                • 5. Re: Search Field on Exit
                  philmodjunk

                  That doesn't suprise me. Before the record is committed, If [ Count ( YourTable 2::SSN ) = 1 ] is probably the indication that you have a duplicate. (Once committed, a record wil match to itself in this relationship so a count greater than 2 will tell you that you have at least one duplicate.)

                  You may want to commit the record as part of this test to get consistent results. You might also experiment with using the OnObjectValidate trigger with = 1 as your test for duplicates.