Jump to content

exporting related tables into single flat excel file


This topic is 3425 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

• 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"

Link to comment
Share on other sites

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
Link to comment
Share on other sites

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 by Lee Smith
update info added
Link to comment
Share on other sites

• 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.

 

Link to comment
Share on other sites

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.

post-100516-0-32364700-1415734768_thumb.

Export_Related_Keys.zip

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 3425 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.