Merging related tables into a single flat table for export

[Ziphius]

I posted about this before (see attached link) but was never really able to apply the solution successfully. This time I'm adding a file showing three related tables (parent, child, grandchild). The client wants one spreadsheet with all the data in it. In other words, the parent records will repeat for each child record and so on. When I export by simply grabbing the fields I need, I need to drag down the resulting export table to fill in those repeating fields, which is time consuming. If there is a way to script the process to have a one button solution, that would be superb.

 

 

 

Merging_to_Flat_Table.fmp12

[Ziphius]

So I revisited the previous solution posted above, instead of importing key fields from the parent table first (Surveys table), I imported the primary and foreign keys from the sightings table first (grandchild table), then imported it's parent table by matching the primary key of the Environmental table with the foreign key of the sightings table and adding new records. Then did the same when importing the Survey table by matching its primary key with the Environmental table foreign key and adding additional records. With the flat table populated with all the primary and foreign keys, I could add whatever fields from any of the tables to the Flat Table Layout for export. This works but does require importing each table on its own. If there is a more automated solution, that would be great but this certainly gets at what I need.

Merging_to_Flat_Table2.fmp12