TOPIC: Join SQL Tables

I have been looking for a way to pull data from multiple tables in one database and manipulate those into a series of text files..

For example I have 4 tables that contain information that together comprises the contents of a single invoice that I need to create a text file for, in a specific format, to import into a text bridge software..

Will flowheater allow me to take data from 4 unique table put it together based on a common value, invoice number, and then create a text file with that invoice number and then repeat the process to create all the invoices in those tables creating unique files for each invoice from those 4 tables?

Thanks,
Herb
Request via Email

Hi Herb,

You can do this by 2 ways.

Create a view in your database engine and select this view in FlowHeater on the READ side, or joins these tables together by a SQL query and use this SQL query. See the follow example
Put this SQL statement into the SQL textbox (READ side Configurator) and click the load schema button. Now you can export these field form these 4 tables into one text file.

If you want to export one text file per invoice, please have a look to the following forum post.
Export each row into a separate flat file

Thanks Robert..

Just some background that might further develop this topic.. Tables 1,2,3 all have a matching number of records in common and all have invoice number in common.. However Table 4 has articles, or items but still has invoice number in common.. Since table 4 may have more rows specific to the single row, invoice number, being related by tables 1, 2, and 3.. Will that change this query?

Also.. With respect to the output.. I do have a particular output format that must be met.. My tables closely resemble that format, in terms of sequence or order, but to what extent can output into single files be manipulated? For example.. Can invoice number contained in the tables be removed before being placed into the single output file? Primarily because invoice number is only important to relate the data from the various tables and is not necessary in the final product text file.. Also.. Could each text file that is created be labeled, or named by the invoice number? I realize my sequence might be off here.. Surely you'd want to use the invoice number to name the file before removing it before writing the individual text file.. Point made?

Thanks for your time.

Herb

Hi Herbert,

With SQL joins you can do all what you want with your data even 1:n relations and so on. This is not a FlowHeater functionality this is provided by the database engine. For more information please have a look to the following tutorial. SQL Join Tutorial

Your second question, this is the power of FlowHeater
Please have a look to the general examples and you will see that’s very easy. Your question about - set the export text file name to the invoice number I think it´s answered by this topic.

export each row into a separate flat file

If not I´ll could make a little example for you. Please post again if you need some example.