8 Replies Latest reply on Dec 10, 2010 11:19 AM by rlinsurf

    Delete duplicates when only one field has data

    rlinsurf

      Title

      Delete duplicates when only one field has data

      Post

      So I've got this db that has only a single field, and I can already see that it has a bunch of duplicate records. I've seen the delelte duplicates script posted everywhere, but that requires a calc_field joing two fileds, i.e., last name, and address. But here's there's only the single field. Is there a way to get rid of all the dups?

      This is FMP Advanced 11.

        • 1. Re: Delete duplicates when only one field has data
          philmodjunk

          The only reason for the calc field is when duplicate values have to be present in more than one field for the record to be considered a duplicate. The same method can be used with a single field, you just use that one field in place of the calculation field.

          You may find that all you need is to:

          1. Enter find mode
          2. Put an ! in the field
          3. Perform the find
          4. Sort your records by this one field to group the duplicate values together.
          5. Delete the duplicates.

          Once you've removed the duplicates, figure out what design change or validation rule you need to prevent the duplicates from being entered in the first place.

          • 2. Re: Delete duplicates when only one field has data
            rlinsurf

            I'm not sure I understand what the ! means. For example, if there are two records with the same information does it find both records or is the found set only the duplicate record?

            • 3. Re: Delete duplicates when only one field has data
              philmodjunk

              ! is the find duplicate values operator. It will find any record where there is at least one other record with the same value in the same field. Try it and see what happens.

              • 4. Re: Delete duplicates when only one field has data
                rlinsurf

                Ah, thanks :)

                Ok, so what I'm seeing is it';s getting the earlier entries rather than the later ones. Is there a way to get the later duplicates instead?

                • 5. Re: Delete duplicates when only one field has data
                  philmodjunk

                  It gets both, but you then need to sort your records so that the duplicates are grouped together.

                  • 6. Re: Delete duplicates when only one field has data
                    rlinsurf

                    Hi, Phil--

                    Well, I'm really confused.

                    So I created the following script:

                    Show All Records

                    Sort Records [Restore, No Dialog] Sort by Notes::Notes in Ascending order, Notes::Set Number in Ascending order

                    Go to Record/Request/Page [First]

                    Replace Field Contents [No dialog, Notes::Mark, ""]

                    Loop

                    Set Field[Notes::Global,Notes::Notes]

                    Go to Record/Request/Page [Next, Exit after last]

                    If [Notes::Global = Notes::Notes]

                    Set Field [Notes::Mark, "X"]

                    Else

                    Set Field [Notes::Global, Notes::Notes]

                    End If

                    End Loop

                    Perform Find [Restore] Find records when Mark = X

                    In this script, I'm using the Notes field, which is really the only content, and a field I called Set Number which denotes the first, second, and so on set of notes at the time of import. This means for example, the first 50 notes are denoted as 1, the next 30 are denoted as 2, and so on. This way, I can also tell what set of notes any individual note belongs to.

                    When I run the script, it seems to find (i.e., Mark) the duplicates in the lower numbered sets first, and then the higher numbered sets. I've tried setting the sort order to both Notes Descending and Number Descending and finally to both, to no avail.

                    Can you tell me what I'm doing wrong?

                    • 7. Re: Delete duplicates when only one field has data
                      philmodjunk

                      What is the data type of your "Set Number" field? Make sure that it is of type Number or you won't be able to sort correctly on it.

                      Then specify this sort order:

                      Notes   (ascending)// any order will do here
                      Set Number (ascending)

                      You would want ascending order here, not descending so the script will marke the 2nd and subsequent duplicates, leaving the lowest numberd field unmarked.

                      • 8. Re: Delete duplicates when only one field has data
                        rlinsurf

                        Yes, Set Number is a number.

                        That looks like it did it. Thanks again!