Using 'Join mode' and 'Combine with parent record' on document records
Concept

Join mode

To explain the join modes, as an example, a sales order document with header and lines is used:

 

The example data set is:

Sales order	Customer	Item	Quantity	Sales price
S001	C001	1001	10	11.65
		1002	20	9.87
S002	C001	-	-	-
S003	C001 C001	1003	5	7.25
		1004	20	10.37

Inner join

To process the parent record, the child record must exist. If a child record exists, both the parent record and the child record are processed.

A sales order is only processed if it has a sales line. If no sales line exists, the sales order is not processed. If a sales line exists, both the sales order and the sales line are processed.
In the example, sales orders S001 and S003 are processed with the related lines. Because sales order S002 has no lines, it is not processed.

To process the parent record, it is not required that a child record exists. All parent records are processed. If child records exist, these are processed as well.

Sales orders with sales lines and sales orders without sales lines are processed.
In the example, sales orders S001, S002, and S003 are processed. For sales orders S001 and S003, the lines are processed as well.

To process a parent record, a child record must exist. So, only the parent records are processed that have a child record.

If a sales order has one or more sales line, only the sales order is processed. If the sales order does not have a sales line, the sales order is not processed.

In the example, sales orders S001 and S003 are processed without the related lines.

Only parent records are processed that do no have child records.

A sales order is only processed if it does not have a sales line.

In the example, sales order S002 is processed.

By default, if you have a parent-child relation, the parent and child record are processed separately. However, you can indicate that parent and child record are processed together. The 'Combine with parent record' option is only applicable to child records.

Best practice: Wen you design a document, first keep 'Combine with parent record' set to No. When all document records are defined, you can finetune the record setup, and set 'Combine with parent record' to Yes where required.

To explain the 'Combine with parent record' options, as an example, a sales order document with header and lines is used:

 

The example data set is:

Sales order	Customer	Line
S001	C001	Line1
		Line2
		Line3

Process parent-child records separately

Set the 'Combine with parent record' field to 'No'. The parent and child record are processed separately. The query first takes the parent record, and then the parent record with the first child record, the parent record with the second child record, and so on.

In the example, the query takes these records and record combinations:

1. Sales order S001
2. Sales order S001 + Sales line Line1
3. Sales order S001 + Sales line Line2
4. Sales order S001 + Sales line Line3

Set the 'Combine with parent record' field to 'Yes'. The parent and child record are processed together. The query first takes the parent record with the first child record, then the parent record with the second child record, and so on.
Best practices:

• In documents with a more complex record structure, only combine a child record with a parent record if, in your data, the parent-child relation is always 1:1. For example, a sales order always has only one customer. So, you can combine the customer record with the parent sales order record.
• In the document record structure, if several records have the same parent record, these are shown on the same level. Move a child record that is combined with the parent record as far down as possible for the level.

In the example, the query takes these record combinations:

In this example, the 'Combine with parent record' options are applied both to a document with a parent record that has several child records:

Parent record	Child record	Combine with parent record
SalesOrder	SalesLine	No
	Address	No
	Customer*	Yes

* Note that the 'Customer' record is positioned as last record of the level that has the sales order as a parent. See best practices.

 

The example data set is:

Sales order	Line	Address	Customer
S001	Line1	Address1	C001
	Line2	Address2
	Line3

In the example, the query takes these record combinations:

1. Sales order S001 + Customer C001
2. Sales order S001 + Customer C001 + Sales line Line1
3. Sales order S001 + Customer C001 + Sales line Line2
4. Sales order S001 + Customer C001 + Sales line Line3
5. Sales order S001 + Customer C001 + Address1
6. Sales order S001 + Customer C001 + Address2