10 Replies Latest reply on May 22, 2013 10:53 AM by JustenBeasley

    Merging Duplicate Record Data

    JustenBeasley

      Title

      Merging Duplicate Record Data

      Post

           I am trying to find a way that I can merge duplicate fields data.

                                 Address                 Time Visited

           Example:    123 test lane                      3

                               345 Amaco Dr.                 2

                               123 test lane                     5

           Run the script and the datbase now looks like this

            

            

           Example:    123 test lane                      8

                               345 Amaco Dr.                 2

            

           If any way you can help that would be greatly apprecaited. 

            

           thanks,

            

           -Justen

        • 1. Re: Merging Duplicate Record Data
          philmodjunk

               Maybe that's a typo, but why would you merge 123 test lane with 124 test lane?

               There are two basic approaches:

               Permanent change to your data:

               FInd all duplicates using ! in the address field. Sort by this field to group duplicate records, then loop through the records to update the first instance with the total of Time Visited and mark the other instances for deletion. After loop is finished, find all marked records and delete them.

               Temporary "merge" just for reporting purposes:

               Define a summary field that computs the total of Time Visited. Design a list view type summary report layout with a sub summary layout part "when sorted by Address". Put the Address and summary field in the sub summary part. Delete the body layout part. Find the records you want for your report. Sort them by Address field and you have a list of addresses with no duplicates and the correct subtotals in your Time Visited column.

          • 2. Re: Merging Duplicate Record Data
            JustenBeasley

                 Phil, 

                 Sorry, that was a  typo

                 I see what you are saying, but i have about 6,000 records currently and growing so this would be a very intense hands on project. is there not something like two step script. that merges and sums the two items?

                  

                 Thanks,

            • 3. Re: Merging Duplicate Record Data
              philmodjunk

                   Both methods that I have described would be scripted, not manual processes.

                   Which works for you?

              • 4. Re: Merging Duplicate Record Data
                JustenBeasley

                     I think the first solution would be the best, because they want to keep that data in the merged format. I will start to look more into scripting it that way.

                • 5. Re: Merging Duplicate Record Data
                  philmodjunk

                       Here's how to compute the subtotal needed for each address after you have found and sorted your records to group them by identical address:

                       Define a summary field that computes the total of the Time Visited field.

                       Then this expression will return the total for the current group of records with the same address:

                       GetSummary ( SummaryField ; Address )

                  • 6. Re: Merging Duplicate Record Data
                    JustenBeasley

                         Phil, 

                          

                         If anyway possible you could go more indepth with your solution. I am following your idea, but I am having trouble executing it?

                          

                         thanks,

                    • 7. Re: Merging Duplicate Record Data
                      philmodjunk

                           What have you managed to set up so far? Perhaps I can start from that and help you through the next step or two...

                      • 8. Re: Merging Duplicate Record Data
                        JustenBeasley

                             So i can do the easy part, sort and find the duplicates, but how to i define a summary field for 6,000 records to I have to them one by one? I just an not sure where I am supposed to go next

                        • 9. Re: Merging Duplicate Record Data
                          philmodjunk

                               You need a single summary field to compute the total fo Time Visited.

                               Your script would loop through the records after the find and sort using the GetSummary function to get the sub total for each group of records with a common address.

                               Hmmm, Is this a one time fix or something that you will need to do repeatedly?

                               I just thought of a simpler way to do this, but it is best suited for a one time fix of the issue.

                               If you have FileMaker Advanced, make a copy of your table in Manage | Database | Tables. If you do not, use Import Records with the New Table option for the target table to make a copy of it.

                               In this copy of the original table, set up the following validation options on the address field:

                               Unique Values
                               Validate Always.

                               If you used import records to copy the table, delete all records from this copy of the table.

                               Now use Import Records again to import your data from the original table into the new table. During import, all the duplicates will be filtered out importing just one instance of each address.

                               Now define a relationship between the two tables that match just by the Address fields.

                               On the layout for your new table, Show All Records, put the cursor in the Time Visited field and use Replace Field Contents with this calculation:

                               Sum ( OriginalTableNameHere::TimeVisited )

                               You now have the results specified, but in a new table. You can then delete all records from your original table and import your data from the new table back into the original.

                               This throws a lot of data around, but is simpler than a scripted approach to set up. Make lots of back up copies of your file while you do this so that you can try again if you don't get the results expected.

                          • 10. Re: Merging Duplicate Record Data
                            JustenBeasley

                                 Phil would you be willing to contact me I would pay you to create a couple of these functions quickly?

                                 jbeasley@joltstl.com