2 Replies Latest reply on Jul 2, 2013 12:30 PM by RBowering

    Looping through parent and then child records

    RBowering

      Title

      Looping through parent and then child records

      Post

           Hi

           I have a lovely Filemaker report (containg Parent records followed by multiple related Child records) but, in order to share it with others I have to send an output to Excel, run soime VBA and then copy THAT table into Word

           The output from FM (Filemaker Pro 12) to Excel lists the the child records to the right of the parents (i.e. Parent data is in columns A- H and child records in I - T). In the final out put I want to see

           Parent field 1 ... Parent Field 2 ... Parent Field 3 .... etc

      Parent field 1 ... Child Field 1 ......Child field 2 etc

      Parent field 1 ... Child Field 1 ......Child field 2 etc

           (not easy to do this without columns of tabs)

           I was thinking I could create a Dummy table with enough columns in Filemaker for the Export to Excel function

           Then I could

           a) Create a header row (Titles for the first record)

           b) Loop through Parents in ID order

           c) Enter Parent data as next recrd

           d) Enter Child Header row as next record

           e) Loop through Child data where ID = Parent ID

           f) Enter Child data (many times)

           g) End Child Loop

           h) End Parent loop

           As a novice to programming how would I script this? I assume I'd need to create another table with a 'Parent Header Row' and 'Child Header Row'?

           I'd like the output Filemaker table to be like the layout in the picture (minus the formatting and colours). The formatting is to illustrate what I want. Pale blue for the Parent Header row (once only). Yellow represents the parent records. Grey represents the Child header row which repeates for each new set. White are the child records.

           Not asking for much am I?

            

      Paret_and_Children.PNG

        • 1. Re: Looping through parent and then child records
          philmodjunk

               The basic skeleton of your script might look like this:

               The script changes layouts when it needs to refer to records from a different table. Note that scripts can trip script triggers when changing layouts so be careful to make sure no such triggers are tripped by the script.

               //start on a layout for the parent table
               //Find, sort records to get what you need for your exported data
               Go to Record/REquest/page [first]
               Loop
                  // use set variable steps to copy data for parent header into variables
                  Go to Layout [New temp table]
                  New Record/Request
                  // use set field steps to copy data from variables into fields in the new record
                  Go to layout [parent table layout]
                  If [ Not IsEmpty ( ChildTable::ForeignKey ) // make sure that related records exist. ]
                      Go To Related Record [Show only related records; From table: Child ; Using layout: "Child" (Child) ]
                       Loop
                          // use set variable steps to copy data for child data row into variables
                          Go to Layout [New temp table]
                          New Record/Request
                          // use set field steps to copy data from variables into fields in the new record
                          Go to Record/Request/Page [ next ; exit after last ]
                        End Loop
                        Go to layout [Parent table layout ]
                  End If
                  Go to Record/Request/Page [ next ; exit after last ]
               End Loop

          • 2. Re: Looping through parent and then child records
            RBowering

                 thank you, I'll give it a go