1 2 3 Previous Next 30 Replies Latest reply on Jan 14, 2013 10:21 PM by philmodjunk

    Merge Duplicate Records?  FMPro 12

    JefferisPeterson

      Title

      Merge Duplicate Records?  FMPro 12

      Post

      Hi guys, 

      I have imported a bunch of images and because of the inconsistencies of the text index, some of the images were imported separately from the text record file.   

       

      Is there a way to merge records that have the same File Name , e.g.   like bat&ball.png   where one file has the image and other other has the description?     

       

      TIA, 

      Jeff 

        • 1. Re: Merge Duplicate Records?  FMPro 12
          philmodjunk

          A looping script could be set up.

          Perform a find that puts a lone ! in the file name field to find all records with duplicate file names.

          Sort the found records on this same field to group the records with duplicate names.

          Then use a looping script that steps through the records, merges the data and Marks the record you want to delete by having it set a value in a field in that record.

          After the merge is complete, find all the "marked" records and delete them.

          • 2. Re: Merge Duplicate Records?  FMPro 12
            JefferisPeterson

            Got any tutorials oar examples of looping scripts I can look at to teach me how to do this? I've programmed in php a lot but not really much in FM... I guess I'm newbie status, but I'm a quick study. 

             

            TIA,

            Jeff 

            • 3. Re: Merge Duplicate Records?  FMPro 12
              philmodjunk

              Enter FInd mode [] //clear the pause check box
              Set field [YourTable::fileNamefield ; "!"]
              Set Error capture [on]
              perform find[]
              If [ get (FoundCount ) //duplicates were found ]
                 Sort [restore ; no dialog ] //sort on the filenamefield.
                 Go to record/request/page [first]
                 Loop
                    #what you do here depends on what needs to be "merged" and whether it is possible to have more than two records with duplicate values in the file name field. You may need a second loop inside the first if it's possible to get more than two records with duplicate filenames.

                 Go to record/request/page [next ; exit after last]
                End Loop
              End If

              • 4. Re: Merge Duplicate Records?  FMPro 12
                JefferisPeterson

                I'm only seeing 2 identical files at a time.  This brings back memories!  A bit of Applescript and old FM work. It has been years!  

                So you are doing the whole thing in a script and not doing the duplicate find manually?  I was trying to do a script from a found set of duplicates, manually sorted by Item field name... 

                • 5. Re: Merge Duplicate Records?  FMPro 12
                  philmodjunk

                  The first four lines of the script example I posted should find all records in the table where there is at least one other record with the same text in the filename file.

                  Then the script sorts the records by this same field so that records with duplicate values are adjacent to each other in the found set.

                  • 6. Re: Merge Duplicate Records?  FMPro 12
                    JefferisPeterson

                    Okay, so I don't want to merge the file name which would give me two instances of the same name?  I want in the merge the other values to merge like description , image, and file path.  So if one record has file path and the other does not, they merge, but if they both have file path, but one has description and the other does not, I assume a merge becomes more complicated, like  If FieldName is Empty then  merge, else if Fieldname is Not empty, then do not merge.  Yes? 

                     Or can I use a replace function instead of merge to prevent duplication of content? 

                    'BTW, thanks  for  the help! 

                    • 7. Re: Merge Duplicate Records?  FMPro 12
                      philmodjunk

                      That's what I meant by "it depends on what you need to do to merge the data".

                      Except for empty fields, will the data in both records in the duplicate pair be identical?

                      If so, the script inside the loop might look like this:

                      Set Variable [$Field1 ; value: YourTable::field1 ]
                      Set Variable [$Field2 ; value: YourTable::field2 ]
                      Set Variable [$Field3 ; value: YourTable::field3 ]
                      Set Variable [$Field4 ; value: YourTable::field4 ]
                      Set Field [YourTable::MarkForDeletion ; 1 ]
                      Go to record/requst/page [next]
                      Set field [Yourtable::Field1 ; $Field1 ]
                      Set field [Yourtable::Field2 ; $Field2 ]
                      Set field [Yourtable::Field3 ; $Field3 ]
                      Set field [Yourtable::Field4 ; $Field4 ]

                      • 8. Re: Merge Duplicate Records?  FMPro 12
                        JefferisPeterson

                        Yes, the data should be identical except for the empty fields.  Here the images match, but one has the description and one does not.  The file paths are / should be the same for both records. 

                        • 9. Re: Merge Duplicate Records?  FMPro 12
                          philmodjunk

                          Hmmm, the only field I see that has data in one record and not the other is the Notes field. Is this a consistent pattern throughout all your records or are there exceptions?

                          My last post was a bit too "brute force" as it could overwrite data with null values--clearing data instead of keeping it.

                          But you could rewrite the last set of steps as:

                          Set field [Yourtable::Field1 ; If ( Not IsEmpty ( $Field1 ) ; $Field1 ; Yourtable::Field1 ) ]
                          Set field [Yourtable::Field2 ; If ( Not IsEmpty ( $Field2 ) ; $Field2 ; Yourtable::Field2 ) ]
                          Set field [Yourtable::Field3 ; If ( Not IsEmpty ( $Field3 ) ; $Field3 ; Yourtable::Field3 ) ]
                          Set field [Yourtable::Field4 ; If ( Not IsEmpty ( $Field4 ) ; $Field4 ; Yourtable::Field4 ) ]

                          To avoid that problem.

                          with all such "batch update" operations, it's a good idea to make a back up copy of your file so that you can toss your file and switch to the back up if it doesn't work like you expected.

                          • 10. Re: Merge Duplicate Records?  FMPro 12
                            JefferisPeterson

                            Yes that makes sense: if not empty.   There are some of these that are missing the actual image in one of the duplicates. 

                            I'll definitely make a backup  before running my script.  I'll post it here before I run it so you can see if I did anything stupid :-) 

                            • 11. Re: Merge Duplicate Records?  FMPro 12
                              JefferisPeterson

                              Phil,  I am not sure what are variables in your example with the $ and which are just  placeholders.  I have 4 fields I need to merge: Item   = Item   (key field), Images in the image | Container field,  file location in  the Location field, and  Notes in the Note field (descriptions).  Can you tell me if this script looks correct? 

                              Thanks, 

                              Jeff 

                               

                              So far I have this: 

                              Enter FInd mode [] //clear the pause check box
                              Set field [Assets::Item ; "!"]
                              Set Error capture [on]
                              perform find[]
                              If [ get (FoundCount ) //duplicates were found ]
                                 Sort [restore ; no dialog ] //sort on the filenamefield.
                                 Go to record/request/page [first]
                                 Loop

                                 Set field [Assets::Item ; If ( Not IsEmpty ( $Item ) ; $Item ; Assets::Item ) ]
                              Set field [Assets::Notes ; If ( Not IsEmpty ( $Notes ) ; $Notes ; Assets::Notes ) ]
                              Set field [Assets::Location ; If ( Not IsEmpty ( $Location ) ; $Location ; Assets::Location ) ]
                              Set field [Assets::Image | Container ; If ( Not IsEmpty ( $Image | Container ) ; $Image | Container ; Assets::Image | Container ) ]
                              Set Field [Assets::MarkForDeletion ; 1 ]
                              Go to record/request/page [next ; exit after last]
                                End Loop
                              End If
                              • 12. Re: Merge Duplicate Records?  FMPro 12
                                philmodjunk

                                I see that the piecemeal nature of my posts has caused some confustion.

                                Anything with a $ in my example was a variable. You'd set up such a variable for each field where you need to merge data. The way this works is that on the first record of the pair, you copy data from fields. Once you have all the data copied, you go to the second record of the pair and copy data from the variable into the second record's corresponding field, but only if the data in the variable is not empty.

                                Your script should look like this:

                                Enter FInd mode [] //clear the pause check box
                                Set field [Assets::Item ; "!"]
                                Set Error capture [on]
                                perform find[]
                                If [ get (FoundCount ) //duplicates were found ]
                                   Sort [restore ; no dialog ] //sort on the filenamefield.
                                   Go to record/request/page [first]
                                   Loop

                                     #Load variables with values
                                     Set Variable [$Item ; value: Assets::item ]
                                     Set Variable [$Notes ; value: assets::Notes ]
                                     Set Variable [$Location ; value : assets::location ]
                                     Set Variable [$Image ; value : assets::Image | container ]
                                     Set Field [Assets::MarkForDeletion ; 1 ]

                                     #Merge data into record with duplicate file name
                                     Go to record/request/page [next ]    
                                     Set field [Assets::Item ; If ( Not IsEmpty ( $Item ) ; $Item ; Assets::Item ) ]
                                     Set field [Assets::Notes ; If ( Not IsEmpty ( $Notes ) ; $Notes ; Assets::Notes ) ]
                                     Set field [Assets::Location ; If ( Not IsEmpty ( $Location ) ; $Location ; Assets::Location ) ]
                                     Set field [Assets::Image | Container ; If ( Not IsEmpty ( $Image | Container ) ; $Image | Container ; Assets::Image | Container ) ]

                                     Go to record/request/page [next ; exit after last]
                                  End Loop
                                End If

                                I've also figured out a way to use GetNthRecord for this--it makes for a smaller number of steps in the loop and does not use any variables--but
                                I've hesitated to post it after going so far down the path with this script.
                                • 13. Re: Merge Duplicate Records?  FMPro 12
                                  JefferisPeterson

                                  Why can't you paste in FM into new scripts? ??   

                                  • 14. Re: Merge Duplicate Records?  FMPro 12
                                    JefferisPeterson

                                    Running into a few problems creating the script: 

                                     

                                    Set field [Assets::Item ; "!"]   The closest I can get to this is :  Set field [Assets::Item ; [ "!"] ]   There is no ! in the operators of the calculation set. 

                                       Sort [restore ; no dialog ] //  CAN'T Seem to find a way to add Restore to this section using the script editor

                                         Set Field [Assets::MarkForDeletion ; 1 ]   /// All I could do is choose my own field Find Empty and put in a value : 222
                                         Set Field [Assets::Find Empty ; 222]  ///   don't know if that is what you mean, but mark for deletion isn't an operation in the script editor
                                    Finally
                                      Set field [Assets::Item ; If ( Not IsEmpty ( $Item ) ; $Item ; Assets::Item ) ]    When I try to create that, it gives me an error than an arithmetic operatior is expected, like a + 
                                    All I can get to is   Set field [Assets::Item ]
                                    1 2 3 Previous Next