3 Replies Latest reply on Sep 10, 2010 1:38 PM by philmodjunk

    How can I manage duplicate records in Filemaker



      How can I manage duplicate records in Filemaker


      I went to the knowledge base and found the following script but I can't get it to perform what I need.  It returns errors.  Primarily in Manage Scripts, how do I make it set the Mark field with an X?  Also, How does it know to go to the previous records and compare that Global field to the ID of the current or next field? 

      Any help on how to  manage and mark duplicates is appreciated. 

      Show All Records
      Sort Records [Restore, No Dialog] Sort by Client ID in Ascending order
      Go to Record/Request/Page [First]
      Replace Field Contents [No dialog, 'Mark', " "]
      Set Field['Global','ClientID']
      Go to Record/Request/Page [Next, Exit after last]
      If ['Global = ClientID']
      Set Field ['Mark', '"X"']
      Set Field ['Global','ClientID']
      End If
      End Loop
      Perform Find [Restore] Find records when Mark = X

        • 1. Re: How can I manage duplicate records in Filemaker

          Set Field steps should look like this in your script editor:

          Set Field [YourTable::YourField ; "X"]

          You have two specify buttons with set field, use the first button to select the field where you want to enter the "x". Use the lower button to enter the expression of the value you want to set, the "X" in your case.

          • 2. Re: How can I manage duplicate records in Filemaker

            I appreciate it and was able to get the script to set the Global field as the ID field. It went through the entire script but didn't place an X in any field under the Mark column showing any duplicates. 

            I can't figure out how it knows to check the ID field of one record against the Global field in the previous record so it can note it as  a duplicate. 

            Somethings off somewhere.  I'm new to Filemaker but believe I'm doing it properly.   There has to be something missing in the script that is posted on their knowledge base.


            • 3. Re: How can I manage duplicate records in Filemaker

              There are other approaches for working with duplicate values. Entering Find mode, putting an ! in the field that might store duplicates, performing the find and then sorting your records by this field so that duplicate values are in a group is another way to see which records might store a duplicate value instead of doing Show All Records.

              This script works if you specify a sort order that sorts on the field, ClientID, that may have duplicate values. The sort groups all records with the same client ID next to each other. The global field is being used to compare the ID value of the previous record to the ID value of the current one. That won't work unless you've specified global storage for the global field. With today's FileMaker, you have a slightly faster/simpler option of using a variable instead of a global field for this, but the global field should still work for you if you have everything defined correctly.