3 Replies Latest reply on Dec 10, 2008 9:29 AM by TSGal

    Merging Duplicate Records



      Merging Duplicate Records


      I need some help!!  I have serached the forum and help database but I must be using the wrong search words.


      I have a database that was set-up many years ago.  Each time a consumer was issued some special equipment a new record was created.  This has resulted in many records for "John Doe" with different serial numbers. 


      We would like to combine all of John Doe's records into one record with a list of the Serial Numbers in the one record utilizing a "repeating field". 


      How do I find all of John Doe's records and merge them together keeping some of his information (i.e. address) the same but list the different serial numbers in a repeating field?





        • 1. Re: Merging Duplicate Records


          Hi "dwhylanjr",


          A quick note...


          I would STRONGLY recommend that you utilize a "Relationship" for listing all of "John Doe's" 'serial numbers'!!!



          I'm not sure what version of FMP you are using, but FMP 7, 8 & 9 all include 'multiple tables' for creating simple relationships.



          In simplistic terms (assuming you are using FMP 7, 8 or 9)...


           - create a new field "ConsumerID" in your 'existing' table

           - search for duplicates and assign each set of duplicates the same 'ConsumerID' number 

           - create a new table "Serial_Numbers" with "ConsumerID" and "SerialNumber" fields

           - map a relationship from...

               <<  'existing' table/"ConsumerID" field  =   "Serial_Numbers" table/"ConsumerID" field  >>

           - import the "ConsumerID" and "SerialNumber" data from 'existing table' into "Serial_Numbers" table

           - create a portal using the previously mapped relationship in your 'existing' 'consumer layout' with the related "SerialNumber" field

           - NOW you should see the list of 'serial numbers' listed for each 'consumer'

           - now you can delete all the extra 'duplicate' consumer records  --  BE CAREFUL NOT TO DELETE THE MOST CURRENT CONSUMER RECORD WITH THE MOST CURRENT DATA...  ie, 'address', 'phone numbers', etc. 



          I hope this helps... Good Luck!!!



          • 2. Re: Merging Duplicate Records
               Ok, I followed your instructions and I am able to see each consumer and the various serial numbers.  NOW, how do I merge all of John Doe's records into ONE single record?  I am using repeating fields to hold the data...  In some cases I have 20 occurances for a consumer or 25 different serial numbers.  So, I want to merge all of john doe's single records into one so I can have a list of serial numbers in one john doe record.
            • 3. Re: Merging Duplicate Records



              Have you thought about having the serial numbers in a separate table and linking the two tables together?  It would probably be easier.


              In any case, you can write a script that uses the GetRepetition() function to put the serial numbers into the repeating field.  For each instance, you would increase the repetition number by one.  For example,


              Sort Records [Restore] 

              Go to Record/Request/Page [First]

              Set Variable [$consumer; "" ]


                 If [Consumer ≠ $consumer ]

                    Set Variable [$consumer; Consumer ]

                    Set Variable [$repetition; 1 ]

                    Go to Layout [<layout with summary table> ]

                    New Record/Request

                    Set Field [ <second consumer field>; $consumer ]

                    Go to Layout [<first layout> ] 

                 End If

                 Set Variable [$serialnumber; SerialNumber ] 

                 Go to Layout [<layout with summary table> ]

                 Set Field [SerialRepeat[$repetition]; $serialnumber ]

                 Set Variable [$repetition; $repetition + 1 ]

                 Go to Layout [<first layout> ] 

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

              End Loop




              This script sorts the table by the Consumer (ID or Name - depending on your key), goes to the first record and initializes the variables $consumer to nil.


              We enter the loop and evaluate to see if the Consumer has changed (which it has for the first record).  If so, we want to reinitialize everything and add a new record to the table where you want to put your serial numbers into a repeating field.


              We then grab the Serial Number, switch to the other layout and put that serial number into the next repetition.  After doing so, we increase the repetition number by one, return to the original layout and go to the next record.  When the end has been reached, the script ends.


              If you need clarification for any of the above steps, please let me know.



              FileMaker, Inc.