When exporting data from multiple Sage tables in a single job it is possible to link two tables on a common field, this has the effect of cascading any data filters defined on the parent table to the child table.
For example in the Fig 1 screenshot you can see that Invoices & Invoice Items are being exported from the Sage demo data, there is nothing special about this until you apply a filter to the Invoices table.
Fig 1
If we filter the Invoices table to only export records where the customer account references equals A1D001 we get 3 records returned as shown in Fig 2.
Fig 2
If we run this job as it is we would get the 3 invoice records as shown, for the Invoice Items table we would get all the records, if we are only exporting 3 invoice records its reasonable to assume we may not want all the records from the invoice items table, ideally we only want the items that belong to the exported invoices.
This is where the Relationships come in, we can define a relation between the 2 tables and link them on the INVOICE_NUMBER field as shown in Fig 3. Now if we export the job we will only get the relevant invoice items exported.
Fig 3
You can define relations as deep as you like and a table can have multiple tables related to it, the only limitation is to be found in the filter editor preview data screen, the filter editors previewed data on a child table is only aware of the relation linking to its immediate parent tables filter, if the parent table is also a child of another and its parent table that has a filter defined that filter will not be taken into account within the filter editor preview screen, you need to use the master preview date button found on the table selection screen to see a more accurate preview that takes all relations and filters into account.
This is a design time limitation within the filter/preview screen only.
Only include records with children
You may only want to export date from the parent table that has child data, for example you may not want to export customers that have no invoices, checking the box will filter out parent records with no child records.
Merging Multiple Export tables into a single table/result set
You can merge all the tables of exported data into a single table, for example the above scenario will by default export two tables, one for invoices and one for invoice items, a typical data structure would be:
Invoices
INVOICE_NUMBER |
ACCOUNT_REF |
1 |
Cust1 |
2 |
Cust2 |
Invoice Items
INVOICE_NUMBER |
STOCK_CODE |
SALES_PRICE |
1 |
Item 1 |
1 |
1 |
Item 2 |
2 |
1 |
Item 3 |
3 |
2 |
Item 1 |
1 |
2 |
Item 2 |
2 |
2 |
Item 3 |
3 |
2 |
Item 4 |
4 |
2 |
Item 5 |
5 |
If the merge tables checkbox is selected the above tables would be merge into a single table looking something like the below example.
Merged Table
ACCOUNT_REF |
INVOICE_NUMBER |
STOCK_CODE |
SALES_PRICE |
Cust1 |
1 |
Item 1 |
1 |
Cust1 |
1 |
Item 2 |
2 |
Cust1 |
1 |
Item 3 |
3 |
Cust2 |
2 |
Item 1 |
1 |
Cust2 |
2 |
Item 2 |
2 |
Cust2 |
2 |
Item 3 |
3 |
Cust2 |
2 |
Item 4 |
4 |
Cust2 |
2 |
Item 5 |
5 |