exporting related tables into single flat excel file

[Ziphius]

Is there a way to export related records such that you can get all records for each table (let's say 4 related tables - parent to great grand child table) so that each record for each table is shown in the exported excel file. In other words, parent fields will get repeated in one to many child records and parent records with no child will show blanks under the child variables. This seems to work properly only with direct parent and child records but once you get more than one table away, it doesn't seem to hold its hierarchy. Seems there should be an easy way to do this. I've attached a file as an example.

 

Any suggestions?

 

 

RelatedTablesExport.fmp12.zip

[pixi]

• create a new table called "Export"

• copy all key fields from all tables you want to export

• make sure all sub tables have the id of the parent table

• export the primary and foreign keys of all record from all tables

• import them back into the export table, from parent downwards

• create relationships from the export table to all of the other tables

• export all data of all fields using the relationships from the export to the other tables.

 

the idea here is to have all related records in one single table.

when importing back in use "Update matching records in found set" and "Add remaining data as new record" and use the parent id of the current table imported as "Match records based on this field"

[Ziphius]

Thank you.

 

1. I created a new table called "Export"

2. I copied all primary and foreign key fields from my tables and pasted them into the Export table (copy and paste only available in "Advanced" versions of FM Pro for those reading this and having trouble)

3. All sub tables have a foreign key field that contain the same value as its related parent table primary key

4. I exported all the primary and foreign keys of all the tables from FM Pro into a single Excel spreadsheet

5. I imported the Excel spreadsheet back into the Export table by matching the variable names and adding the new records

6. This is where I am having trouble - when I try to create relationships from my Export table by relating primary keys it tells me that there cannot be more than one relational path between any two tables in the graph and another occurrence of the table is need. When I create the occurrence, Filemaker crashes on me:

 

Exception Type:        EXC_BAD_ACCESS (SIGSEGV)

Exception Codes:       KERN_INVALID_ADDRESS at 0x0000000000000000

 

I'm a little confused with your last two steps. Any chance you could attach your relationship schema. Also, would you mind explaining your last point about export all of the fields "using the relationships from the export to the other tables". I apologize if this should be simple to follow for most.

 

Thanks,

Mark

[Lee Smith]

Why are you posting your question to the version 13 topic instead of 12?

 

If you are using 13 now, here is a quick link to update your profile information My Profile

 

If you are still using 12, let me know and I’ll move it to the proper topic area.

 

Lee

Edited November 11, 2014 by Lee Smith
update info added

[Ziphius]

My bad, I'm using Filemaker 13 advanced. I updated my profile.

[pixi]

• pt. 6. use the butten down left (see screenshot)

 

• kernel panic: you have a serious problem with your computer and/or filemaker installation.

restart holding shift until you see a dark grey progress bar on a light grey background. after you see the login window with a red "Secure System start" (or whatever it is called in english) restart immediately. this will clear all caches on the computer and do some housekeeping.

additionally check for plugins and disable them if crashing doesn't go away.

 

[Ziphius]

Thank you, I switched to a different computer and I'm able to create the table occurrences without crashing.

 

So now I have my Export table with all my primary and foreign keys linked by one to one relationships with the primary and foreign keys from related tables using new tables of occurrence (see attached).

 

When I export these primary and foreign keys, along with the fields that I am interested in, I still don't get a true, single, flat database (see attached). I am under the assumption that when I export, I export all the primary and secondary keys form the Export table and then go to each individual table and export all the fields within it that I want. Any ideas where I am going wrong?

Thanks,

 

Mark

 

Edit: I changed the attached Excel file to keep it simple so it shows only the parent and foreign keys and how they get exported. You can see that the one to many (primary key to secondary key) work fine for the Survey keys but then it breaks down for the nested Environmental, Sightings, and Photo keys.

Export_Related_Keys.zip

[Ziphius]

Basically I have 4 tables that are all nested (grandparent to grandchild), each table has a one to many relationship with its child table. Some records may not always have child or grandchild nested information. I want to create a new table that merges all 4 tables into one table with parent fields repeating. I realize this defeats the purpose of a relational database but its a format that my client wants. Intuitively it seems that as long as the primary and foreign keys are properly linked in each table, it should be easy enough to drill up or down to grab the related data, but I can't seem to make it work.

 

MergeFourRelatedTablesIntoOne (3).zip