Exporting data from related tables

[Darren S]

Hi,

I have an FM solution that manages the loans we arrange for our clients.  To do this we have 2 main data tables - Contacts and Deals.

We also have several other tables that contain related data.  These include Loan Accounts, Security, Comments, Documents, and Notes.  There are also other join tables that allow us to record other relationships (eg. Contacts - Contacts to reflect employer and employee relationships).

All deal related tables have a field called 'ID_Deal' which I use to link all the related data together.

I need to be able to export a Deal, which would include all the related data from the other tables.  I am happy to export this data as a CSV file, or to a new FM file.

I am looking for a simple way of scripting this.  I would search the Deals table, and then export the found records and all related data from other tables.

I think I can work through the 'logic' and write a script that may include delimited fields, performing finds and possibly using the loop command, but this seems to be a tedious process and maybe not the best way of achieving the result.

Can anyone assist with a smarter and more simple method of exporting the data?

Thanks in advance.

[comment]

Speaking in general, fields from related tables can be included in the export field order. It's hard to tell it this will fit your purpose, because we don't know how exactly your tables are inter-related, what data you want to include in the export and - most importantly - what is the purpose of the export (in particular, what would be the purpose of exporting to a new FM file - usually you export data to make it available to other applications).

 

[Darren S]

Thanks for the reply.

We have several consultants and we may need to send them data for all the loans they arrange.  In this case a flat data structure is OK.  I can do an export to any file format and send this.

In other cases, I would like to send an FM file that preserves the related tables.  In effect, it would be a copy of the database but only containing the desired records.

Thank you

[comment]

Those are two very different requirements. Although you can include data from related tables, an export will always result in a single table. In order to create "an FM file that preserves the related tables" you would need to either save a copy of your file and delete the unwanted records, or export the data (if necessary, in several steps, one for each table) and import it into an empty clone of your file. This would be a considerable scripting endeavor.

 

[Darren S]

Thanks very much for the reply.

I was hoping there might be a solution to create a copy of an FM file containing only a subset of data (based on a found set).  This way I can more easily hand over data to a third party, so they can use it without going through an import process.

I'll reconsider how I plan to do this.  Thanks for your input, as always.

 

 

[comment]

26 minutes ago, Darren S said:

so they can use it without going through an import process.

I didn't mean them to go through an import process. I meant that you (or rather your script) should do the import for them, before sending them the file. 

The main problem I see here is getting the empty clone to import the file/s exported from the original solution. A possible solution could be a startup script that tests the status of an "Imported" flag; if it returns false, import the files and set the flag to true.

 

[Darren S]

OK.  I'll write a script to handle the export and import processes.

Thanks.