Export data from several tables to Excel

[LisetteG]

Hi,

I have several tables that I work with. I'm currently trying to export batches of data from these different tables in one xls file.

I work for an international fellowship grant program and we keep records of our alumni (table 1), their US academic information, meaning degrees; universities; etc (table 2), their grant(s) information, meaning program year; grant type; academic area (table 3). Of course this information is linked through other tables in between.

So essentially one alumni member can have more than one grant and more than one academic affiliation.

I want to produce an xls that lists grantee information along with US affiliation and grant information. It is able to export the data but of course when I export with the alumni table as the source, information from table 2 and 3 shifts and doesn't produce the correct information. Please see attached, the top part is the excel and the bottom is FMP.

I've tried changing the source but that results in repeated information of data from other tables.

Would anyone happen to have any tips or tricks to help me export data from multiple tables that would stay true to the data.

I look forward to hearing any of your ideas.

[brian rich]

Hi Lisette

How about setting up a separate 'Work' table which contains the fields that you need on the Excel spreadsheet, gathering the data you want from the other tables into the 'work' table, and export the work table as an Excel spreadsheet.

The simplest way would be to export the data you want from each table into a temporary file on your desktop, then import each temporary file into your Work table, adding the imported records each time. You can now save the work table which has data from all your tables to your Excel file.

When you're done, delete all the records in the work table for next time.

Brian

[LisetteG]

Hi Brian,

This sounds like a good solution. I just wanted to clarify --

You mean export each table which contains the data I ultimately want to go into the final excel file, then merge all data from the tables (by importing) into one new table which would list data for each alumni in separate fields (horizontally), ex: university 1, university 2, university 3 ?

Thanks.

Lisette

[brian rich]

Not quite - export each table into a temporary file - then import/add these files back into your 'work table'. Then export the work table to make the final Excel file.

The temporary files could be Filemaker files, probably easier to manage than Excel. Personally, I'd save these to the temporary folder ( the path to this is obtained from the Get(TemporaryPath) function) so they will be automatically deleted when you close Filemaker and I didn't have to tidy up. ;-)

Brian