--- title: "Custom query for internal documents" slug: "custom-query-for-internal-documents" description: "Enhance your D365 F&SCM experience with Custom queries for advanced data retrieval, cross-company joins, and precise control over data selection." tags: ["Concept topic"] status: "update" updated: 2026-10-05T12:13:40Z published: 2026-10-05T12:13:40Z canonical: "docs.staedean.com/custom-query-for-internal-documents" --- > ## Documentation Index > Fetch the complete documentation index at: https://docs.staedean.com/llms.txt > Use this file to discover all available pages before exploring further. # Custom query for internal documents 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 with 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. > [!NOTE] > **General advice** > > Try to use the standard query unless the standard query does not give the desired results. So, only use a 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] > **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] > **Note:** > > - Keep the automatically created relation with the parent record. > - Only select a real ancestor document record. Example: ![](https://cdn.document360.io/da662430-6a41-47b1-8c7e-7e4ac37a9756/Images/Documentation/Images/File%20imageA6DA0512-6E14-4BAC-B286-C948CD72815E) ### Performance considerations - In general, a **Custom query** runs slower than a standard query. Reason, a 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. ## Related - [Add document record fields - Internal documents](/add-document-record-fields-internal-documents.md) - [Check relations](/check-relations.md) - [Add document records - Internal document](/add-document-records-internal-document.md) - [Set up document - D365 F&SCM](/set-up-document-d365-fscm.md) - [Set up document - Inventory journal](/set-up-document-inventory-journal.md) - [Set up document - Ledger journal](/set-up-document-ledger-journal.md) - [Set up document - Trade agreement journal](/set-up-document-trade-agreement-journal.md) - [Set up document - Other journals](/set-up-document-other-journals.md) - [Set up document - Staging](/set-up-document-staging.md)