8 Replies Latest reply on Jan 29, 2010 10:56 AM by philmodjunk

    Duplicating a record and its children



      Duplicating a record and its children




      I'm looking for a way to duplicate a record and its children.  Do I have to traverse all the children with GTRR steps or there is an easiest method?  Maybe a generic recursive script?  In my case, there are like 10 different related child tables.


      It would not be a problem with a regular programming language to create a recursive method but in FM, no idea since GTRR is not generic.


      Thanks for any help. 

        • 1. Re: Duplicating a record and its children

          It might be a good idea to describe what you're trying to do (beyond "duplicate records"). On first glance it seems unnecessary to create duplicate records since the originals can be referenced by relationships, but I don't know your structure so I can't say for sure.



          • 2. Re: Duplicating a record and its children

            Howdy el pablo,


            I figure you already saw this post, and probably already knew how to do this before asking your question:  But just in case...




            If there is an easier way than the looping script in that post, I'm eager to learn it as well.  I don't think there is an easier method native to FMP.

            • 3. Re: Duplicating a record and its children

              In some case the need to duplicate record is there.  In our case, we need to write some report for appraisal.  Sometime, we need to create 2 appraisals for the same house.  The second one is usually a modified version of the first one.  Eg : Two reports one for an actual value of the house and another for potential value.


              The user writes its first report and just need to duplicate the second and do the necessary modification.  Now he got 2 distinct report link to the same assignment.  Each report has its own set of children record and these need to follow the report, since we want to keep each report independent.


              I hope my example helps to understand the use of duplication. 

              • 4. Re: Duplicating a record and its children

                Thanks for the link, I'm already using something similar.  This method only works for 1-level relationship.  I need to go up to 4-level down.  So I'm looking for a method that is generic and recursive.  Since I want to duplicate a "branch" of the table "tree".


                A simpler view of the relationships in my case would be


                Reports -= Buildings -= Appartments -= "some leaf tables".


                The way I'm doing it right now is using 4 nested loops with a similar method to what you gave me.  In other programming language I would have done a few line method, but in FM 75+ lines which seems to me cumbersome.


                I guess what's limiting my development is the GTRR.  Since it's not possible to set the related table by calculation. 


                • 5. Re: Duplicating a record and its children

                  Don't know if this is practical for your situation, but you can "chain togther" a series of GTRR calls to get from one Reports record to all the records in one leaf table.


                  Starting on a Reports layout,

                  GTRR (specify the appropriate Buildings TO and layout)

                  GTRR(Specify the approprate Apartments TO and layout, this time use the "match found set option" to match all related apartments records for all the buildings records you brought up)

                  GTRR(specify a leaf table, again using the match found set option).


                  Then, for each successive "leaf" table, go back to the Apartments layout and do another GTRR to find those related records.


                  You have to trap for cases where there are no matching records at each step and you have to take into account that "match found set" can carry a significant performance penalty.


                  Go To Related Records is a very useful tool, but which is very poorly documented. To learn more about GTRR, click the following link:

                  The Complete Go To Related Record

                  • 6. Re: Duplicating a record and its children

                    Yep, that's what I've done.  Chained GTRR with loops, but only for the current record.  Since we need to know which child goes with which parent.  I was hoping to find a kind of recursive script that could be used for any "branch". I guess it's not possible due to the fact that there is no simple way to check the existence of child tables.


                    Here is an algorithm of what I was thinking possible in FM :



                    script DuplicateRecordTree (parentID as string)begin     Duplicate Record     pk = newRecord.pk          if (parentID <> "")          Set newRecord.fk = parentID     end if          omit record          if (currentTable.hasChildTables)          foreach (childT in currentTable.childTables)               if (childT.records.related.count > 0)                    foreach (r in childT.records.related)                         Goto Related Record(r)                         DuplicateRecordTree (pk)                    next               end if          next     end ifend script





                    For data security reason, I always use a separation model for scripting.  Eg : ui_DuplicateRecord calls duplicateRecord.  All my Custom Dialogs are in the "ui_" script.


                    They were tedious script to develop, but the job is done and I'm okay with the result.  But I'm always looking for a better solution than nested loops and GTRRs.


                    • 7. Re: Duplicating a record and its children
                         Hmmm... the code tag doesn't seem to work well.  You can copy paste the code in a text editor and add a line return at each first tabulation.
                      • 8. Re: Duplicating a record and its children

                        Good point. I hadn't considered that the resulting mass of found "leaf" records will need to be assigned different Parent keys during duplication.


                        You could build a list of number pairs  in a variable where value 1 is the old parent ID and value 2 is the new parent ID while you loop through the parent keys and duplicate them. Then you can use the resulting list to assign correct keys to the duplicated children. This does add a level of complexity to your looping duplicate script so I'm not sure if the result is worth it or not...