5 Replies Latest reply on Nov 1, 2014 1:37 PM by AjEGfmTech

    Duplicating Multiple Linked Portals

    AjEGfmTech

      Title

      Duplicating Multiple Linked Portals

      Post

      I am referring to an excellent post by PhilModJunk regarding duplicating portal records.
      http://forums.filemaker.com/posts/ac6afe2913

      This is working great for me and I understand it. What I'm trying to figure out is how to expand the script to apply to multiple linked portals.

      I am creating an store visit form. The form has a hierarchy of elements, each with their own table.

      Forms >---< Categories >---< Sections >---< Measures >---< Items

      The initial layout starts with a Form and a portal to create Categories. I have used the script above to successfully duplicate a Form and its related portal of Categories. But now each Category will have several Sections. Each Section several Measures. Each Measure several Items. Where do I insert the additional duplicate record loop steps? Below is the 'final' script based on the post referenced above.

      #Script to duplicate the entire hierarchy of records for a selected Form.

      #Script is intended to be started from the 'Forms List' layout. IF started from 'FormCreate' layout, skip the 'Goto Related Record' step.

      Go to Related Record [ From table: “FormCreate”; Using layout: “FormCreate” (FormCreate) ]

      Freeze Window

      Set Variable [ $RecordNumber; Value:Get ( RecordNumber ) ]

      #Presume that 'DuplicateRecord' will grab a new UUID?

      #FORM

      Duplicate Record/Request

      Set Field [ FormCreate::Title; "New Untitled Form" ]

      #Grab the new UUID.

      Set Variable [ $FormID; Value:FormCreate::__pk_FormID ]

      #After 'DuplicateRecord' the original record is not necessarily in a predictable place. The GotoRecord with $RecordNumber variable compensates for that.

      Go to Record/Request/Page [ $RecordNumber ] [ No dialog ]

      #Check to make sure there is at least one FormID foreign key in the Category before proceeding.

      #CATEGORY

      If [ not IsEmpty ( FormCreate_CATEGORY::_fk_FormID ) // Category records from portal to duplicate. ]

      #Goto the related portal record in the layout where it resides.

      Go to Related Record [ From table: “FormCreate_CATEGORY”; Using layout: “FormCreate_Category” (FormCreate_CATEGORY) ] [ Show only related records ]

      Go to Record/Request/Page [ First ]

      Loop

      #Duplicate related portal record.

      Duplicate Record/Request

      Set Field [ FormCreate_CATEGORY::_fk_FormID; $FormID ]

      #Remove duplicate record.

      Omit Record

      Go to Record/Request/Page [ First ]

      #Remove original portal record that was duplicated.

      Omit Record

      ExitLoopIf [Get(FoundCount) =0] End Loop

      Go to Layout [ original layout ] Go to Record/Request/Page [ Last ]

      End If
      Go to Layout
      [ original layout ] 

      I presume that inside the 'Categories' Loop there would need to be a new 'If' group for 'Sections'? I presume it would be right after the 'DuplicateRecord' and 'SetField'? This method would end up with quite a set of nested Loops and If statements. Is this the best/proper way to do something like this?

      I am interested in any advice. Thanks!

        • 1. Re: Duplicating Multiple Linked Portals
          AjEGfmTech

          I must not do a great job of creating posts because I seem to not get many responses. I think I put too much detail and description into the post. It IS a lot to read thru, comprehend and respond to, it would take a lot of persistence to read through all of it. Unfortunately, I am still learning and am not adept enough yet to describe my issue in more simple, abstract terms. That would be my goal.

          In either case, I think I solved my own question. I'm including the entire script for one more level deeper (down to Sections). I will continue to expand it to cover all child tables. As I thought, it IS a lot of nesting, but I couldn't see another way? I'm very interested in any other, simpler ideas.

          PS. I also see other people post their scripts in nice, compact, indented ways. I can't see how to do that? I just print the script to PDF in FileMaker, open the PDF in Preview, copy/paste it into the post in a Block Quote. Another method?

          PSS. Scratch that. I found PhilModJunk's link to his 'FM_Bugs.fmp12' file that has 'Script Pretty' text area that I pasted the script PDF text into and checked the 'HTML' box and pasted into the post here.

          #Script to duplicate the entire hierarchy of records for a selected Form.
          Set Error Capture [ Off ]
          #Script is intended to be started from the 'Forms List' layout. IF started from 'FormCreate'
          layout, skip the 'Goto Related Record' step.
          Go to Related Record [ From table: “FormCreate”; Using layout: “FormCreate” (FormCreate) ] Freeze Window
          # === FORM ===
          #Grab FORM RecordNumber to find original record again later.
          Set Variable [ $FormRecordNumber; Value:Get ( RecordNumber ) ] #Presume that 'DuplicateRecord' will grab a new UUID?
          Duplicate Record/Request
          Set Field [ FormCreate::Title; "New Untitled Form" ]
          #Grab the new UUID.
          Set Variable [ $FormID; Value:FormCreate::__pk_FormID ]
          #Goto original FORM record, thanks to RecordNumber variable from above.
          Go to Record/Request/Page [ $FormRecordNumber ] [ No dialog ]
          # === CATEGORY ===
          #Check CATEGORY portal to make sure there is at least one Category record with a FormID
          foreign key before proceeding.
          If [ not IsEmpty ( FormCreate_CATEGORY::_fk_FormID ) ]
                 #Goto the related portal record in the CATEGORY layout where it resides.
                 Go to Related Record [ From table: “FormCreate_CATEGORY”; Using layout: “FormCreate_Category” (FormCreate_CATEGORY) ]
                 [ Show only related records ]
                 Go to Record/Request/Page
                 [ First ]
                 Loop
                         #Grab CATEGORY RecordNumber to find original record again later.
                         Set Variable [ $CategoryRecordNumber; Value:Get ( RecordNumber ) ] #Duplicate related portal record.
                         Duplicate Record/Request
                         Set Field [ FormCreate_CATEGORY::_fk_FormID; $FormID ]
                         #Grab new UUID for nested 'If' below.
                         Set Variable [ $CategoryID; Value:FormCreate_CATEGORY::__pk_CategoryID ]
                         #Goto original CATEGORY record, thanks to RecordNumber variable from above.
                         Go to Record/Request/Page [ $CategoryRecordNumber ] [ No dialog ]
                         # === SECTION ===
                         #Check SECTION portal to make sure there is at least one Section record with a
                         CategoryID foreign key before proceeding.
                         If [ not IsEmpty ( FormCreate_SECTION::_fk_CategoryID ) ]
                                 #Goto the related portal record in the SECTION layout where it resides.
                                 Go to Related Record [ From table: “FormCreate_SECTION”; Using layout: “FormCreate_Section” (FormCreate_SECTION) ]
                                 [ Show only related records ]
                                 Go to Record/Request/Page
                                 [ First ]
                                 Loop
                                         #Grab SECTION RecordNumber to find original record again later.
                                         Set Variable [ $SectionRecordNumber; Value:Get ( RecordNumber ) ] #Duplicate related portal record.
                                         Duplicate Record/Request
                                         Set Field [ FormCreate_SECTION::_fk_CategoryID; $CategoryID ]
                                         #Remove duplicate record.
                                         Omit Record
                                         Go to Record/Request/Page
                                         [ First ]
                                         #Remove original portal record that was duplicated.
                                         Omit Record
                                         ExitLoopIf[Get(FoundCount) =0] End Loop
                                         #Go back to the CATEGORY layout now that the SECTION loop is done.
                                         Go to Layout [ “FormCreate_Category” (FormCreate_CATEGORY) ] Go to Record/Request/Page
                                         [ Last ]
                                 End If
                                 # === SECTION ===
                                 #Remove duplicate record.
                                 Omit Record
                                 Go to Record/Request/Page
                                 [ First ]
                                 #Remove original portal record that was duplicated.
                                 Omit Record
                                 ExitLoopIf[Get(FoundCount) =0] End Loop
                                 #Go back to the FORM layout now that the CATEGORY loop is done.
                                 Go to Layout [ “FormCreate” (FormCreate) ] Go to Record/Request/Page
                                 [ Last ]
                         End If
                         # === CATEGORY ===
                         # === FORM ===
                         Go to Layout [ original layout ]

          • 2. Re: Duplicating Multiple Linked Portals
            philmodjunk

            Yes, the above method would require nesting the loops as you have started to do here. You can add each loop one at a time, test on a copy of your file and then add the next loop once you can confirm that the previous modification is working 100% correctly.

            But this is a potentially huge number of records to duplicate all at once. It may be unavoidable, but if I were in your shoes, I'd be very carefully analyzing this process to see if there is any way to get this job done without duplicating all of these records.

            • 3. Re: Duplicating Multiple Linked Portals
              AjEGfmTech

              Thanks for your input Phil, I greatly appreciate it. I had started doing what you suggested, adding each new nested loop one at a time and then testing. I got to the third nest and ran into an error where the same record would duplicate 2 or 3 times then skip a record and start again. So if the expected result of duplicated records would be 1, 2, 3, 4, the actual result would be 1, 1, 1, 3. I think (hope) that I must have designated the wrong table or layout somewhere in the process. After looking last night, I couldn't find anything, so I'm hoping I can with fresh eyes this morning. I hope there isn't some inherent problem with multiple nesting that causes some cascading issue.

              The total number of hierarchical records for a typical Form is about 360. I can totally appreciate your statement about whether there is another way of doing this. As I have been developing what seems like a 'simple form', I keep asking myself if there is a better, simpler way to do this. The hierarchical nature of the form leads me to keep coming back to the set of six tables (the sixth is the Visits table).

              Last night as I hit the problem with the third nested loop I started wondering if it would be simpler and better to just flatten the whole thing out into a single table. Each record would have Visit, Form, Category, Section, Measure and Item fields. The whole record would be like one concatenated string. The obvious issue would be what happens when a Form, or Category, or Section, etc. data changes. Every flattened record would need to have that change applied. For example, if someone wanted to change the name of a Category. I presume that could be done with a script that would popup an entry field, take the new name, search all the records for all that match the existing name and do a 'ReplaceContents' with the new name. That sounds messy too, and I have a feeling it could get dicey when trying to create reports of the Visit results.

              Then I thought about a compromise situation where there were two tables, one for Measures (with fields for Visits, Forms, Categories, Sections) and another table for Items. Since each item is a pass/fail that results in a Measure score, those are the two that really would be best to separated. I had started down that path before as I was trying to figure out how to allow the creation of different Form versions and how to select a specific Form version to use on a Visit. As I started developing that, it just seemed 'simpler' to split every table into one-to-many relationships. They link together neatly in a single chain.

              Is there a simpler way? Yes, I would certainly think there is, but with my level of experience, I just haven't found it.

              The process I'm trying to work out now, where I am duplicating records in multiple related (portal) tables will need to occur rarely (couple times a year) for new Form versions. However, that nearly identical process will need to occur almost daily for Visits which make a duplicate of a selected Form version to start the Visit. On top of that, this needs to be an FM Go solution because the visits are conducted by staff onsite with iPads.

              I think this is a very doable solution, and I often feel on the cusp of getting it all working, then little hiccups and doubts enter in. Sorry for the long-winded answer, I greatly appreciate the feedback Phil.

              • 4. Re: Duplicating Multiple Linked Portals
                philmodjunk

                be careful of any specified sort orders on any of the layouts where this script duplicates records. If you sort records with the "keep records in sorted order" specified, the new records won't stay put as the last record in the found set and this can keep the script from working. The script assumes that your set of records will be unsorted on each layout where records are duplicated.

                Another possible "gotcha" is that the change in layouts can trip any number of script triggers on either the layout being left or the new layout being entered. This pauses the duplication script, runs the triggered script and then returns control back to the paused script, but now your found set, current record, current layout, etc may be completely different from what you need for your duplication script. You may need to create some "utility layouts" kept hidden from the user where you don't have any specified script triggers.

                I also cannot recommend too strongly that complex scripts like this scream for FileMaker advanced where you can use the script debugger to step through the script in order to see where/why it is going off the rails on you.

                • 5. Re: Duplicating Multiple Linked Portals
                  AjEGfmTech

                  Thanks for the tips Phil. I will keep that in mind, especially the sort order issues - I don't really use script triggers at this point. I also DO use FileMaker Advanced. I TOTALLY agree, I cannot imagine developing any kind of FM database without it! I was able to find the issue after an extended time of searching and troubleshooting. I would NOT have been able to find the issue without Script Debugger and Data Viewer in FM Advanced. They are what makes that even possible. My problem ended up being a combination of an extra step I had added to grab and use the record number in the nested statements and that I had the omit record steps out of order in comparison to the nested statements. Like I said, it took me awhile to test out different scenarios with Script Debugger and find the order that was successful for all four levels of the hierarchy.

                  It's rather lengthy, but in case it helps anyone else with a similar issue, below is the final successful script. I have it extensively commented.

                  #Script to duplicate the entire hierarchy of records for a selected Form.
                  Set Error Capture [ Off ]
                  #Script is intended to be started from the 'Forms List' layout. IF started from 'FormCreate'
                  layout, skip the 'Goto Related Record' step.
                  Go to Related Record [ From table: “FormCreate”; Using layout: “FormCreate” (FormCreate) ] Freeze Window
                  # === FORM ===
                  #Grab FORM RecordNumber to find original record again later.
                  Set Variable [ $FormRecordNumber; Value:Get ( RecordNumber ) ] #Presume that 'DuplicateRecord' will grab a new UUID?
                  Duplicate Record/Request
                  Set Field [ FormCreate::Title; "New Untitled Form" ]
                  #Grab the new UUID.
                  Set Variable [ $FormID; Value:FormCreate::__pk_FormID ]
                  #Goto original FORM record, thanks to RecordNumber variable from above.
                  Go to Record/Request/Page [ $FormRecordNumber ] [ No dialog ]
                  # === CATEGORY ===
                  #Check CATEGORY portal to make sure there is at least one Category record with a FormID
                  foreign key before proceeding.
                  If [ not IsEmpty ( FormCreate_CATEGORY::_fk_FormID ) ]
                         #Goto the related portal record in the CATEGORY layout where it resides.
                         Go to Related Record [ From table: “FormCreate_CATEGORY”; Using layout: “FormCreate_Category” (FormCreate_CATEGORY) ]
                         [ Show only related records ]
                         Go to Record/Request/Page
                         [ First ]
                         Loop
                                 #Duplicate related portal record.
                                 Duplicate Record/Request
                                 #Relate new child Category record to parent Form record.
                                 Set Field [ FormCreate_CATEGORY::_fk_FormID; $FormID ]
                                 #Grab new UUID for nested 'If' below.
                                 Set Variable [ $CategoryID; Value:FormCreate_CATEGORY::__pk_CategoryID ]
                                 #Remove duplicate Category record.
                                 Omit Record
                                 #Goto original Category portal record that was duplicated.
                                 Go to Record/Request/Page
                                 [ First ]
                                 # === SECTION ===
                                 #Check SECTION portal to make sure there is at least one Section record with a
                                 CategoryID foreign key before proceeding.
                                 If [ not IsEmpty ( FormCreate_SECTION::_fk_CategoryID ) ]
                                         #Goto the related portal record in the SECTION layout where it resides.
                                         Go to Related Record [ From table: “FormCreate_SECTION”; Using layout: “FormCreate_Section” (FormCreate_SECTION) ]
                                         [ Show only related records ]
                                         Go to Record/Request/Page
                                         [ First ]
                                         Loop
                                                 #Duplicate related portal record.
                                                 Duplicate Record/Request
                                                 #Relate new child Section record to parent Category record.
                                                 Set Field [ FormCreate_SECTION::_fk_CategoryID; $CategoryID ]
                                                 #Grab new UUID for nested 'If' below.
                                                 Set Variable [ $SectionID; Value:FormCreate_SECTION:: __pk_SectionID ]
                                                 #Remove duplicate Section record.
                                                 Omit Record
                                                 #Goto original Section portal record that was duplicated.
                                                 Go to Record/Request/Page
                                                 [ First ]
                                                 # === MEASURE ===
                                                 #Check MEASURE portal to make sure there is at least one Measure
                                                 record with a SectionID foreign key before proceeding.
                                                 If [ not IsEmpty ( FormCreate_MEASURE::_fk_SectionID ) ]
                                                         #Goto the related portal record in the MEASURE layout where it
                                                         resides.
                                                         Go to Related Record [ From table: “FormCreate_MEASURE”; Using layout: “FormCreate_Measure” (FormCreate_MEASURE) ]
                                                         [ Show only related records ]
                                                         Go to Record/Request/Page
                                                         [ First ]
                                                         Loop
                                                                 #Duplicate related portal record.
                                                                 Duplicate Record/Request
                                                                 #Relate new child Measure record to parent Section record.
                                                                 Set Field [ FormCreate_MEASURE::_fk_SectionID; $SectionID ]
                                                                 #Grab new UUID for nested 'If' below.
                                                                 Set Variable [ $MeasureID; Value:FormCreate_MEASURE:: __pk_MeasureID ]
                                                                 #Remove duplicate record.
                                                                 Omit Record
                                                                 #Goto original Measure portal record that was duplicated.
                                                                 Go to Record/Request/Page
                                                                 [ First ]
                                                                 #INSERT NEST here.
                                                                 # === ITEM ===
                                                                 #Check ITEM portal to make sure there is at least one Item
                                                                 record with a MeasureID foreign key before proceeding.
                                                                 If [ not IsEmpty ( FormCreate_ITEM::_fk_MeasureID ) ]
                                                                         #Goto the related portal record in the ITEM layout where it
                                                                         resides.
                                                                         Go to Related Record [ From table: “FormCreate_ITEM”; Using layout: “FormCreate_Item” (FormCreate_ITEM) ] [ Show only related records ]
                                                                         Go to Record/Request/Page
                                                                         [ First ]
                                                                         Loop
                                                                                 #Duplicate related portal record.
                                                                                 Duplicate Record/Request
                                                                                 #Relate new child Item record to parent Measure
                                                                                 record.
                                                                                 Set Field [ FormCreate_ITEM::_fk_MeasureID; $MeasureID ]
                                                                                 Set Variable [ $ItemID; Value:FormCreate_ITEM:: __pk_ItemID ]
                                                                                 #Remove duplicate record.
                                                                                 Omit Record
                                                                                 #Goto original Item portal record that was duplicated.
                                                                                 Go to Record/Request/Page
                                                                                 [ First ]
                                                                                 #INSERT NEST here.
                                                                                 #Omit original Item portal record that was duplicated. Omit Record
                                                                                 ExitLoopIf[Get(FoundCount) =0]
                                                                         End Loop
                                                                         #Go back to the MEASURE layout now that the ITEM loop
                                                                         is done.
                                                                         Go to Layout [ “FormCreate_Measure” (FormCreate_MEASURE) ]
                                                                         #Go back to the first Measure record so it can be omitted
                                                                         for the next time thru the loop.
                                                                         Go to Record/Request/Page
                                                                         [ First ]
                                                                 End If
                                                                 # === ITEM ===
                                                                 #Omit original Measure portal record that was duplicated. Omit Record
                                                                 ExitLoopIf[Get(FoundCount) =0]
                                                         End Loop
                                                         #Go back to the SECTION layout now that the MEASURE loop is
                                                         done.
                                                         Go to Layout [ “FormCreate_Section” (FormCreate_SECTION) ] #Go back to the first Section record so it can be omitted for the next
                                                         time thru the loop.
                                                         Go to Record/Request/Page
                                                         [ First ]
                                                 End If
                                                 # === MEASURE ===
                                                 #Omit original Section portal record that was duplicated. Omit Record
                                                 ExitLoopIf[Get(FoundCount) =0]
                                         End Loop
                                         #Go back to the CATEGORY layout now that the SECTION loop is done.
                                         Go to Layout [ “FormCreate_Category” (FormCreate_CATEGORY) ]
                                         #Go back to the first Category record so it can be omitted for the next time thru
                                         the loop.
                                         Go to Record/Request/Page
                                         [ First ]
                                 End If
                                 # === SECTION ===
                                 #Omit original Category portal record that was duplicated. Omit Record
                                 ExitLoopIf[Get(FoundCount) =0]
                         End Loop
                         #Go back to the FORM layout now that the CATEGORY loop is done.