Jump to content

Exporting data from related tables


Recommended Posts

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.

Link to post
Share on other sites

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

 

Link to post
Share on other sites

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

Link to post
Share on other sites

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.

 

Link to post
Share on other sites

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.

 

 

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

 

Link to post
Share on other sites

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.