3 Replies Latest reply on Aug 31, 2011 10:33 AM by GuyStevens

    Delete duplicates problem

    GuyStevens

      Title

      Delete duplicates problem

      Post

      I've been trying out several different scripts to try and delete duplicate records in a table. But I don't seem to be able to get them to work like they should.

      The idea is that I import data from different tables into one table so I can create a report from that table. But that table can't have duplicate data.
      The data comes from a global field and a sum total. (I'm Dutch, so some of the words might seem odd to you)

       

      The closest one I've gotten is the fillowing script:

       

      That turns the long list into the short list. (See images furher down)

      But if you look closely you see that the short list is missing the "Telefoonkosten"
      They seem to have gotten entirely deleted.

      I tried to use "!" in Find mode to look for duplicates, but that simply returns all my records.

      Any ideas?

      Before the Script:

      Before script

      After the script:

      After Script

        • 1. Re: Delete duplicates problem
          Sorbsbuster

          I think the problem is that when the script comes to the first Telefoonkosten it recognises that it is different from the previous record and keeps it.

          It resets the $Variable and moves to the next Telefoonkosten.

          It checks it against the variable and finds it to be a duplicate, so it deletes it.

          This automatically moves it on to the next record, and around the loop again.

          It checks what is now the (original) 3rd Telefoonkosten, compares it, and deletes the record.

          It now has no 'Next' record to go to, so the active record is back to being your original 1st Telefoonkosten .

          It goes round the loop again and compares this record to the $Variable and finds it is the same, so it deletes it.

          You will always lose the complete last group this way, I believe.

          (BTW: I would include a Sort script step at the very start of your script, just to be safe.)

           

          In the 'Else' section of the script you could check - before it is deleted -  if the record you are on is the last record in the found set.  (Although I'm sure there is a cleverer way than that.)

          • 2. Re: Delete duplicates problem
            philmodjunk

            Since you are importing this data, you can also completely eliminate this scripted loop. (Though I believe sorbsbuster is dead on for why you are losing the last group).

            Set a unique values, validate always validation rule on this field and records with duplicate values will be automatically excluded during import.

            • 3. Re: Delete duplicates problem
              GuyStevens

              And the winner is!!  "PhilModJunk"  :)

              "Set a unique values, validate always validation rule on this field and records with duplicate values will be automatically excluded during import."

              Actually I found tthis solution about a half an hour after made the original post.
              But I was still very interested to see an explanation or possible suggestions so that's why I waited a little bit before posting the solution.

               

              Thanks for your contribution guys!