4 Replies Latest reply on Feb 17, 2016 11:15 AM by Christic

    Duplicate address

    Christic

      I have a imported database, it has 6723 records. There are 1202 records with one or more duplicate addresses.  We are send out letters, but only 1 per address, what is the best way to remove all but one of each address.

       

       

      Clarence

        • 1. Re: Duplicate address
          mikebeargie

          Are all of the address fields in separate fields? If so you can create a match key to identify dupes with a script as such

          Sort Records [ matchKey ; ASC ]

          Go to record [first]

          Loop

             Set Variable [ $check ; matchKey ]

             If [ $original = $check ]

               Set Field [ dupeFlag ; 1 ]

             Else

               Set Variable [ $original ; matchKey ]

             End If

             Go to record [ next ; exit after last ]

          End Loop

          This will populate a "dupeFlag" field with a value of one, so you can search and omit any dupeFlag = 1 records.

           

          Your match key should be something like the address & zip stripped of spaces and puts it in the same case:

          Lower ( filter( address1; "qazwsxedcrfvtgbyhnujmikolpQAZWSXEDCRFVTGBYHNUJMIKOLP0123456789 ) & left( zip ; 5 ) )

          • 2. Re: Duplicate address
            BruceRobertson

            Missed a quote:

             

            Lower ( filter( address1; "qazwsxedcrfvtgbyhnujmikolpQAZWSXEDCRFVTGBYHNUJMIKOLP0123456789" ) & left( zip ; 5 ) )

            • 3. Re: Duplicate address
              mikebeargie

              lol thanks. Filemaker needs a built in constant for that long alpha/number combo.

              • 4. Re: Duplicate address
                Christic

                Soooo cool,  Thank you much, worked GREAT!!!!

                 

                 

                Clarence