6 Replies Latest reply on Feb 3, 2010 11:18 AM by trialuser1111

    Finding and omitting matching records

    trialuser1111

      Title

      Finding and omitting matching records

      Post

      I am having trouble with a script meant to identify and omit matching records from a search.  The idea is for the script's search to return all the addresses for the various contacts of a given company, and then go through and delete matching records so all I am left with are unique choices--one record for each address.

       

      Here is my current script:

       

      Set Variable [$ID; Contacts::CompanyID]

      Go to Layout ["Address Chooser"]

      Enter Find Mode []

      Set Field [Contacts::CompanyID; $ID]

      Perform Find []

      Go to Record [First]

      Loop

         Set Variable [$first; Contacts::Address]

         Go to Record [Next; exit after last]

         Loop

            Set Variable [$second; Contacts::Address]

            If [$first = $second]

               Omit Record

            End If

         End Loop

      End Loop

       

      Theoretically, the first address should be comparing and omitting records until there is no longer a match, and then the second address becomes the new base for comparison and moves on through the results until finished.

       

      I'd appreciate any ideas to execute this more simply or just how to troubleshoot the script if in fact I'm going at this the right way (just incorrectly).

       

      Thanks everyone 

        • 1. Re: Finding and omitting matching records
          Steve Wright
            

          I'm doing this from the top of my head, so I apologise if there are any mistakes or it doesn't work..

           

           

           

          Set Variable [$ID; Contacts::CompanyID]

           

          Go to Layout ["Address Chooser"]

          Enter Find Mode []

          Set Field [Contacts::CompanyID; $ID]

          Perform Find []

           

          If [ Get(FoundCount) > 1]  

           

          Loop 

            Go to Record [First] 

            Set Variable [$first; Contacts::Address] 

            Omit Record 

           

            Loop

               If  [$first = Contacts::Address]

                   Omit Record

               Else

                   Go to Record [Next; exit after last]

               End If

              Exit Loop if [Get (FoundCount) = 1]

            End Loop

           

          Go to Record [Next, exit after last] 

           

          End If 

           

           

          Go to layout [ original layout ]

           

           

           

          • 2. Re: Finding and omitting matching records
            trialuser1111
              

            That did it!

             

            Thanks 

            • 3. Re: Finding and omitting matching records
              trialuser1111
                

              Okay after some more trials, I've found that this script only checks the addresses of found records against the first record.  I need to add another loop function that resets the Set Variable [$first; Contacts::Address] step.  An example:

               

              I run the script and the resulting addresses (simplified) are:

               

              New York

              New York

              Boston

              Kentucky

               

              Currently, the script will correctly identify and omit the duplicate in New York, but when it evaluates New York against Boston and sees that they are distinct addresses, it just ends the loop.  I have tried and failed to add the extra loop step that loops in the "Set Variable [$first; Contacts::Address] step.

              • 4. Re: Finding and omitting matching records
                Steve Wright
                  

                Ill make a quick mock up sample file to test it, I wrote it rather late last night :-)

                 

                 

                • 5. Re: Finding and omitting matching records
                  Steve Wright
                    

                  A better way would be to do this via a relationship

                   

                  Set up a 2nd occurence of your table and link it to the first using

                   

                  Contacts::CompanyID = ContactsDup::CompanyID

                  Contacts::Address = ContactsDup::Address 

                   

                  Add 2 fields

                   

                  1. ContactSerial and 

                  2. DupCheck

                   

                  In the dupcheck field, use a calculation containing :

                   

                  Contacts::Duplicate = ContactsDup::Duplicate

                   

                  Now Replace the contents of ContactSerial with auto entered serial numbers.

                   

                  What you will then end up with, is either a 1 for unique records or a 0 for duplicate records, which you can then perform a search on and work with much easier than what could end up being a very long running series of loops.

                   

                  See the sample file here

                   

                   

                  • 6. Re: Finding and omitting matching records
                    trialuser1111
                      

                    I actually was able to solve it 90% with just a simple script tweak:

                     

                    The If logic now is as follows:

                     

                    If [$first = Contacts::Address]

                       Omit Record

                    Else

                       Go to Record [Next]

                       Set Variable [$first = Contacts::Address]

                       Go to Record [Next; Exit after last]

                    End If

                     

                    Also, before the Loop, I added a Sort Records step to sort by Address, which helps results too.  I still get one duplicate on occasion at the bottom of the results, but it's a lot better now.