Looping through parent and then child records

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.

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