13 Replies Latest reply on Dec 1, 2013 9:28 AM by philmodjunk

    How to merge similar records in FM

    TvdK

      Title

      How to merge similar records in FM

      Post

           I've been reading and looking into scripting but i'm afraid i have to admit i'm not much of a programmer and i don't know where else to go with my question. I hope someone is here who can help me. 

           Thing is: i have this database with lots of similar records in it. I'd like to compress it so it only leaves records with some totals (instead of individual earnings). So basically what i need to get done is: if a record contains the same 'Track' and the same 'Katalognummer' (this can be easily be 500 records), all these 'Account total' fields need to be summed, a new record should be created with all previous data in it and the summed 'Account total'. After that, all records that were summed can be deleted so it leaves me in the end with a 'compressed' database. 

           If you look at the screenshots i attached, you'll understand what i mean i hope. 

           I can get the desired result by using 'sub summery' and print it as a pdf file but thats only an 'optical' solution. It still leaves me with the same amount of records. 

           So basically it would like to be able to reduce my database to what i can achieve when using sub summery techniques (as you can see on the images). 

           My best guess is to achieve this result with some scripting, comparing records in a loop, creating a new record, pasting all data in with the new 'Account total' and then deleting all previously looked up records and repeat this process until there are no 'doubles' anymore. But easier said than done, for me

           Anyone out there that can help me ? 

           Would be very much appreciated!

           Thanks

           ton

            

      Schermafbeelding_2013-11-27_om_23.10.57.png

        • 1. Re: How to merge similar records in FM
          TvdK

               This is what it looks likes when using sub summery... however, in this case the database stays the way it is, containing all the records instead of just 2 which is what i'm looking for

          • 2. Re: How to merge similar records in FM
            philmodjunk

                 The fact that you know how to set up a summary report, however, helps in explaining a script that will do what you want.

                 Add a number field to this table named Flag.

                 Run this script:

            #This script should not be run at a time when other users might be accessing/editing this data
                 #Clear flag values set when script was previously performed

                 Enter Find Mode[]
                 Set Field [YourTable::Flag ; 1 ]
                 Set Error Capture [on]
                 Perform Find[]
                 If [ Get ( FoundCount ) // flagged records where found ]
                    Replace Field Contents [ No Dialog ; YourTable::Flag ; "" ]
                 End If
            #Condense records
                 Loop
                    #Look for unflagged records
                    Enter Find Mode []
                    Set Field [YourTable::Flag ; 1 ]
                    Omit Records
                    Set Error Capture [on]
                    Perform Find []
                    Exit Loop if [ Not Get ( FoundCount ) //exit when no unflagged records are found ]
                    #Find all records of the same track and catalog number
                    Set Variable[$Track ; Value: YourTable::Track ]
                    Set Variable [$Catalog ; value; YourTable::Katalognummer ]
                    Enter Find Mode []
                    Set Field [YourTable::Track ; $Track ]
                    Set Field [YourTable::Katalognummer; $Catalog ]
                    Perform Find []
                    #update record with total from summary field
                    Set Field [ YourTable::Account Total ; YourTable::Total //I am guessing at the field names here. Total should be the name of your summary field.]
                    Set Field [YourTable::Flag ; 1 ]
                    #Keep record with new combined value and delete the others
                    Omit Records
                    Delete All Records [No dialog] ---> this step does not really delete all records. It deletes all records in the current found set
                 End Loop

            • 3. Re: How to merge similar records in FM
              TvdK

                   Thanks for the fast reply, i'll now look into it, can't paste the script so have to 'translate' it... will let you know if it works!

              • 4. Re: How to merge similar records in FM
                TvdK

                     Unfortunately i can't get it to work. It now looks like this but not sure if you understand Dutch ;-) Strange i can't paste it in, have to use another screenshot

                      

                      

                • 5. Re: How to merge similar records in FM
                  philmodjunk

                       I can't read Dutch but comparing line by line, I would guess that Veld instellen is Dutch for Set Field. If so, your script step is missing a parameter. It should be: Veld installen ; YourTable::Flag ; 1 ]

                       And "YourTable" was my Place holder for the name of your table. It really shouldn't read "your table" but should be the name of the actual table shown in your original screen shot.

                       When Setting up Set Field, there are two Specify buttons that must be clicked. To get Set Field [Table::Field ; Expression], add set field to your script and click the first button (specify target field). Select Table::Field from the list of fields. Do not click the specify button next to the repetition box. Click OK to close this dialog box. Now click the lower specify button (calculated result) and create the expression to the right of the semicolon (;). Do not try to type in the semicolon.

                  • 6. Re: How to merge similar records in FM
                    TvdK

                          Hi Phil, sorry to bother you once more. I've looked into it today and i think i understand the script you wrote, seems logical (in a way). However, it's still not working they way it should be. I notice a couple of things i'll try to describe:

                         1) for some reason, almost all albums (containing more than 1 track) are brought back to 1 remaining track ?

                         2) i noticed some albums are missing a track title, i don't know what happens with these records.. they don't show up in the end result

                         3) speaking about the end result, the script won't stop... i have to stop it manually and than select 'show all records' to see the records still in the database

                         Not quite sure if i did correctly but i indeed made a field called Total (resume field) in the table which should contain a total of 'Account Total". -> what should be the state of this 'repeating' option? all at once or seperately ?

                         Last but not least, i'm curious why you seem to able to paste in the script, i can only do it by attaching a screenshot ;-(

                         Hope to hear from you

                         Thanks

                    • 7. Re: How to merge similar records in FM
                      philmodjunk

                           Are you on a layout for Royalties or a different table? This script should be run on a layout based on Royalties judging by the field references that I see in your copy of this script.

                      • 8. Re: How to merge similar records in FM
                        TvdK

                             there's only one table so that can not be the case. I notice something else... some album titles are - in the end - completely missing. Looks like if a certain track appears on different albums, they are all merged together. 

                        • 9. Re: How to merge similar records in FM
                          philmodjunk

                               A record with no text in the Track field makes no sense to me so I didn't consider the possibility that you might have tracks with no name. There must be at least some kind of identifier in the Track field that, when combined with the catalog number, uniquely identifies each track or this script will not work.

                               If it's possible to have a single track with no track name for a given catalog number, it would be possible to modify this script to correctly handle that situation, but if you have two tracks with no name and the same catalog number, then there is no way to get this to work correctly.

                          • 10. Re: How to merge similar records in FM
                            philmodjunk

                                 Seeing the script in English helped me spot a key error in your script.

                                 You have:

                                 Exit Loop if [ Not Get ( RequestCount ) ]

                                 It should be:

                                 Exit Loop If [ Not Get ( FoundCount ) ]

                                 That solved the problem where you loop was never exiting

                                 I then made one change to better deal with "no name" tracks:

                                 Set Variable [$Track ; value: If ( IsEmpty ( Royalties::Track ) ; "=" ; Royalties::track ) ]

                                 That turns the find into a find for all records with the specified catalog number and no data at all in the Track field.

                                 Running the script then produced a set of 30 "condensed" records that appear to be correct.

                                 But I wouldn't actually do such a thing with data in one of my databases. I'd create a set of related records, one record for each track/catalog number and use a calculation field to compute the needed value as a sum of the related records in the original table.

                            • 11. Re: How to merge similar records in FM
                              TvdK

                                   I'm gonna be studying on your solution but adjusting the script here made it run exactly like it should, thanks so much!

                              • 12. Re: How to merge similar records in FM
                                TvdK

                                     I have one more question: is it possible to export the remaining records as an excel sheet (i know how to do that) but with a new name based on the initial excel sheet i imported ? So for instance the original excel sheet i import is called: 'ton statement' and then the output should be something like "condensed ton statement" ?

                                     I tried to get that done by Get ( filename) but that results in an ouput using the name of the database instead of the original imported excel sheet. 

                                      

                                • 13. Re: How to merge similar records in FM
                                  philmodjunk

                                       That can be done manually or in a script by setting up a $path variable for the export records step. And you can produce such a file for export from your original set of records without needing to run this script and physically condense the data. There's a group by option that basically produces your original summary report of the data exporting one row of data to the excel file for each Track/catalog number.

                                       You may find this thread on $Path variables to be helpful: Exploring the use of a $Path Variable in Scripts