4 Replies Latest reply on Nov 25, 2008 10:51 AM by LeoCA

    Find Duplicate Records in a table

    LeoCA

      Title

      Find Duplicate Records in a table

      Post

      I try to find duplicate records in a table and have hard time of using either layouts or scripts. I know I can accomplish this in a SQL statement:

       

      Select UniqueKeys

      From Table1

      Group By UniqueKeys Having Count(*)>1

       

      How can I accomplish this using FileMaker?

       

      Thanks.

       

      LeoCA

        • 1. Re: Find Duplicate Records in a table
          raybaudi
            

          Hi LeoCa

           

          this is a starting point:

           

          Set Error Capture [ On ]
          Perform Find [ Find Records criteria = yourTable::yourField symbol: ! ]
          If [ Get ( LastError ) ]
          Show Custom Dialog [ "No dupes !" ]
          Show All Records
          End If
          Sort Records [ no dialog ; yourTable::yourField ; ascending ]


          • 2. Re: Find Duplicate Records in a table
            LeoCA
              

            Hi Daniele,

             

            Thank you for your response.

             

            I still can't see how the Perform Find will get ONLY duplicate records.  There are no specific values for fields (uniqueKeys which may have 3 fields) that I can specify in the Find Records criteria.

             

            The "If [ Get ( LastError ) ]" seems not a valid statement since "Get ( LastError )" will return number NOT Boolean.

             

            Could you elaborate more on your solution?

             

            Thanks.

             

            LeoCA

            • 3. Re: Find Duplicate Records in a table
              raybaudi
                

              "I still can't see how the Perform Find will get ONLY duplicate records.  There are no specific values for fields "

               

              The specific value is the symbol: !

               

              When in Find Mode, entering that symbol into a field, will find all the records that have duplicate values in that field.

               

              "The "If [ Get ( LastError ) ]" seems not a valid statement since "Get ( LastError )" will return number NOT Boolean."

               

              Every not empty or not 0 values are considered "true".

              So, if Get( LastError ) returns 401 ( No records match the request ), it's the same as say: true.

              • 4. Re: Find Duplicate Records in a table
                LeoCA
                  

                Daniele,

                 

                Thanks a million!  I tried your way and it works like a charm.

                 

                LeoCA