1. To-Increase Dynamics 365 for Finance & Operations
  2. Connectivity studio
  3. Concepts

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

A query in D365 FO can have specific features that are different from a standard SQL query. Connectivity studio completely relies on the queries in D365 FO.

Some frequently asked query-related questions are:
  • I am missing data in my export. What can be the problem?
  • I am importing data, but I need to combine multiple records to create the sales order header. How can I do this?
On a document record, two important query-related fields are available that can be used to answer these questions:
  • Join mode
  • Combine with parent record

Join mode

The join mode works similar as in SQL queries. The join mode is only applicable to parent records. So, if a record has no child records, the join mode is not applicable.
You can select one of these join modes:
  • Inner join
  • Outer join
  • Exists join
  • NoExistsJoin

Example

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

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.
Example:
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.

Outer join

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.
Example:
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.

Exists join

To process a parent record, a child record must exist. So, only the parent records are processed that have a child record.
Example:
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.

NoExistsJoin

Only parent records are processed that do no have child records.
Example:
A sales order is only processed if it does not have a sales line.
In the example, sales order S002 is processed.

Combine with parent record

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.

Example

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.
Example:
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

Process parent-child records together

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.
Example:
In the example, the query takes these record combinations:
  1. Sales order S001 + Sales line Line1
  2. Sales order S001 + Sales line Line2
  3. Sales order S001 + Sales line Line3

General example:

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

Related to Notes

Add document records - XML

 		 

Add document records - JSON

 		 

Add document records - Text

 		 

Add document records - Fixed text

 		 

Add document records - EDI

 		 

Add document records - Microsoft Excel

 		 

Add document records - Microsoft Word

 		 

Add document records - ODBC

 		 

Add document records - Internal document

 		 

Provide feedback