4 Replies Latest reply on Oct 23, 2014 11:08 AM by sportyguy209

    Removing Duplicate Records Script

    sportyguy209

      Title

      Removing Duplicate Records Script

      Post

      I found the following script for finding duplicate records on the FileMaker website, but when I get to the if Statement after the Loop, I keep getting an error message that the database can't be found. Any help would be appreciated. TIA

      Show All Records
      Sort Records [Restore; No dialog] <--- Sort by the UniqueID field in ascending order
      Go to Record/Request/Page [First]
      Set Variable [$id; Value:SampleTable::UniqueID] <-- Sets "$id" variable to be equal to UniqueID field

      Replace Field Contents [No dialog; SampleTable::Mark; " "] <--- Replace the contents of the "Mark" field with a blank (" ")
      Loop
           Go to Record/Request/Page [Next, Exit after last]
           If [$id = SampleTable::UniqueID]
                Set Field [SampleTable::Mark; "X"] <--- Marks record as a duplicate by placing an "X" in the "Mark" field
           Else
                Set Variable [$id; Value:SampleTable::UniqueID]
           End If
      End Loop
      Perform Find [Restore] <-- Finds all records where Mark = X.  You will need to define the find criteria for this script step
      I found the following info for creating a script to find duplicate records in FileMaker.

       

        • 1. Re: Removing Duplicate Records Script
          philmodjunk

          Your if statement must refer to the same table and field specified in Sort Records. What you show still uses the "placeholder" value used in the example script.

          It's also possible to use Import Records to filter out duplicates without using any looping script at all.

          • 2. Re: Removing Duplicate Records Script
            sportyguy209

            For the Variable in the script I found on the FileMaker website, I used "$id"

            For the Value in Set Variable I used "Overall_World_Champion_Calculator::UniqueID"

            For the If Statement after Loop in my script I tried:

             If [$id = Overall_World_Champion_Calculator::UniqueID]
                      Set Field [Overall_World_Champion_Calculator::Mark; "X"]
                 Else
                      Set Variable [$id; Value:Overall_World_Champion_Calculator::UniqueID]
                 End If

            That's when I get the error message The specified table cannot be found.

            • 3. Re: Removing Duplicate Records Script
              philmodjunk

              What you show is actually 5 different scrip steps. Are you, perhaps trying to type all of this into a specify calculation dialog? That would not be correct. Only the expression inside the [   ]    would be entered into the specify calculation dialog. The set field, else and other steps would be added to your script by double clicking those options from the list on the left hand side of the script editor.

              • 4. Re: Removing Duplicate Records Script
                sportyguy209

                Thank you, that fixed it. Yes, I was confused as to where a step ended and what needed to be included in the step.