3 Replies Latest reply on Apr 23, 2017 5:09 PM by bigtom

    Show Only Duplicate Records in Portal

    mprythero

      Hi All - out of curiosity, is it at all possible to show records that are duplicates in a portal view? Or is there a way to do something like that in a different fashion?

       

      I can't limit one of the unique fields to just unique values because I need the user to be able to come back and see the duplicates that were imported into the database against the records that are already in the database.

       

      I'd appreciate any help you can provide.

       

      Thanks! Matt

        • 1. Re: Show Only Duplicate Records in Portal
          beverly

          there should still always be a unique Primary key to each record.

          that being said, you can create a "self-join" (link the table to an alias of itself) in the RG (relationship graph).

          This allows you to look at records and have portal of those that are duplicate.

          NOT always a good way!

           

          OR...

           

          You can FIND on duplicates with the "!" operator in the field in Find Mode. Then sort on that field to group the duplicates together. perhaps making a sub-summary part on that sorted field. (the last just helps you visually see the duplicates grouped together).

          from there, it's up to you to determine how to keep (or delete) a record that might have duplicate data.

           

          beverly

          • 2. Re: Show Only Duplicate Records in Portal
            philmodjunk

            Note also that with import using the "matching records of found set" option, you can import without getting such duplication, yet update existing records with new info.

            • 3. Re: Show Only Duplicate Records in Portal
              bigtom

              Here are links to the processes beverly described.

              FileMaker Pro 15 Help

               

              FileMaker Pro 15 Help

               

              You can also identify duplicates with ExecuteSQL function. I have done this using a Global unstored calc field in the past and generally only as a temporary tool. Maybe not the best way but it works. Good for identifying multiple duplicates. Good to have an identifier like a creation date to determine which records to keep. Sometimes you want the new records, sometimes you may want the originals. This keeps from having to add a Unique/Duplicate field to every record. Needs another TO joining the ID and the Global field. The portal will show you the original record and all duplicates.

               

              ExecuteSQL ( "SELECT IDpk FROM table WHERE field in (SELECT field FROM table GROUP BY field HAVING COUNT(field) > 1)"; ""; "" )