5 Replies Latest reply on Oct 18, 2015 6:19 PM by kjcohio

    How do I eliminate duplicate records?


      I have list of phone numbers and would like to eliminate the duplicates, leaving just one of each unique phone number.  Is there a magic command or script?  Thanks.

        • 1. Re: How do I eliminate duplicate records?

          The standard search for duplicates is to insert a ! (exclamation) in your search field. Note, however, this will only find exact duplicates. For example, if a phone no was entered in one record as 1234 5678 and in another record as 1234-5678, these will not be treated as duplicates. Once you have used the above to remove exact duplicates, a useful technique for identifying near matches such as this example is to look at the field Index, and work your way through any likely candidates you see.

          Screen Shot 2015-10-19 at 8.46.58 am.png

          Screen Shot 2015-10-19 at 8.50.15 am.png

          • 2. Re: How do I eliminate duplicate records?

            Thanks for that.  At that point, any idea how to delete duplicates without manually doing it record-by-record?

            • 3. Re: How do I eliminate duplicate records?

              First, I would calculate a "numberonly" field as Filter ( phoneNumberField ; "1234567890" ) to address Keywords' (excellent) point about what constitutes a "duplicate" in a field where you can have dashes, periods, parentheses, spaces, etc.


              Next, search for non-unique values in that numberonly field with an exclamation point wildcard. (if scripting, Perform Find step)


              Sort by the numberonly field so that like phone numbers are together in the list (if scripting, Sort step)


              Then loop through omitting the first of each set of duplicate records. If scripting, this part'll look something like this:


              Go to record/request/page [first]


                   If( numberonly <> $lastnumber )

                        # this is the first of the set, so we want to omit it so it doesn't get deleted

                        set variable [ $lastnumber ; numberonly ]

                        omit record

                        # Note: when you omit a record, the next record becomes the current record, so you don't need to go to next separately


                        # this is a duplicate. It's the same as the last number we saw

                       go to record/request/page [ next , exit after last ]

                   End If

              End Loop


              Then delete the remaining found set. (if scripting, Delete All Records (which is really "delete found set")




              Chris Cain


              • 4. Re: How do I eliminate duplicate records?
                1. create a number field called 'dup'
                2. find all records
                3. sort records by the phone number field
                4. in the new dup field 'replace field content…' with this calc.
                  If ( GetNthRecord ( yourField ; Get ( RecordNumber )-1 ) = yourField ; 1 ; "" )
                5. then find:  dup = 1
                6. delete found set
                • 5. Re: How do I eliminate duplicate records?

                  That worked!