For internal documents in Connectivity studio, the Custom query feature enhances the standard query capabilities of D365 F&SCM. It provides you with an advanced method to query data. Compared to the standard D365 F&SCM query, you can create more complex queries with greater flexibility and control over data selection. The Custom query is compatible with existing Connectivity studio and D365 F&SCM processes.

The Custom query feature offers an alternative approach to data retrieval. Unlike the standard query, with the Custom query, you can:

• Join data across different companies.

• Control queries more precisely wit the Group by function.

• Limit the selection to the first record only for complex queries.

• Use fields from ancestor records in record relations.

The standard D365 F&SCM query remains available.

If you use the:

• Standard query, to select the data, only one D365 F&SCM query is created for the document.
• Custom query, to select the data, a separate query is created for each document record.

General advice: Try to use the standard query unless the standard query does not give the desired results. So, only use the custom query if required to get a specific query result.

Document setup

For internal documents, you can choose between these query types:

• Standard (default behavior)

• Custom (advanced option)

You can do so, on the document header, in the Advanced section, in the Query type field.

Cross-company data joins

With the Custom query, you can join tables across companies in the same D365 F&SCM environment.

To get table data from another company, for a document record:

1. Set the Join mode of the record to OuterJoin.
2. Add the 'dataAreaId' field.
3. Define a range of type Value for the 'dataAreaId' field. In the Range field, you can enter:
   • One company.
   • Several companies, separated with a comma.
   • An asterisk (*) to select all companies.
   • Note: If you do not define a range, no data is selected from another company.

Select first record only

A document record can result in a selection of several matching records. In some case, you only want to select one record for a document record. With the Custom query, you can use the Select first record function. Selecting only one record improves the performance.

Example: Several addresses are stored for a customer. However, you only want to select the first address.

To only select the first matching record:

1. On the Document page, on the Lines view, in the Record section, select the desired document record.
2. In the Line details section, set the First record only field to Yes.

Group by

With the Custom query, for internal documents, you can use Group by to summarize, condense, and analyze data during export.

Group by has several types of functions:

• Group by function: Groups records based on the value of one or more fields.
• Aggregate functions: An aggregate is done based on all records in the group.
  • Avg: Calculates the average value of the field.
  • Min: Gets the lowest value of the field.
  • Max: Gets the highest value of the field.
  • Sum: Totals the field values.
  • Count: Counts the records in the group.
• None: The field is ignored by the Group by function.

To apply the Group by function:

1. Activate the Group by function:
   1. On the Document page, on the Lines view, in the Record section, select the desired document record.
   2. In the Line details section, set the Group by field to Yes.
2. For at least one of the document record fields, in the Group by field, select Group by.
3. If desired, for the other fields, you can set any of the Group by aggregation options.
4. For the document record fields that must be ignored for the Group by function, in the Group by field, select None.

Ancestral relations

With the Custom query, you can set up a field relation with a field of an ancestor document record.

If the Custom query feature is active for a document, for a document record, on the relations tab, the Parent record ID field becomes available. You can use this field to define a relation to an ancestor document record of the current  document record.

Note:

• Keep the automatically created relation with the parent record.
• Only select a real ancestor document record. Example:
  

Performance considerations

• In general, a Custom query runs slower than a standard query. Reason, the custom query runs several separate smaller queries, one for each document record.

• Cross-company data joins can reduce performance. So, only use it when necessary.

• Use the group by and related aggregation functions carefully. Excessive grouping can reduce performance.

• Test your custom query and its performance before you deploy it in a live D365 F&SCM environment.