1. To-Increase Dynamics 365 for Finance & Operations
  2. Connectivity studio
  3. Design
  4. Set up documents
  5. Set up external documents - ODBC

Manage document record setup - ODBC documents

You have several options to manage the document record setup for ODBC documents.

You can:
  • Change the sequence of the records.
  • View where a record is used.
  • Check or change the table relations.
  • Define the data querying order.
  • Define the range of data to be queried.
  • Validate the record setup.


Application Consultant Application Consultant Start Start Define record sequence Define record sequence You can organize the business document records in these ways: Define a parent-child relation with another record to define the record structure.Change the sequence of the records.You can only change the sequence of records if these are on the same level in the record structure. So, for example:If you move a parent record, you cannot move it to a position below its child records. The child records stay as child records and are moved as well.You can change the sequence of child records with the same parent. Procedure 1. Click Connectivity studio Integration Design. 2. Click the Documents tab. 3. In the list, find and select the desired document. 4. Click Edit. 5. Sub-task: Change sequence. 6. In the Record section, in the list, find and select the desired record. 7. Click Move down. 8. In the Record section, in the list, find and select the desired record. 9. Click Move up. 10. Sub-task: Define or change parent. 11. In the Record section, in the list, find and select the desired record. 12. In the Parent record field, enter or select a value. 13. Close the page. View where document record is used View where document record is used Sometimes, if you want to clean-up your document setup, you cannot delete an element. In such a case, you can view where the element is used in the connectivity setup.You can do so for a:DocumentDocument recordDocument record field Procedure 1. Click Connectivity studio Integration Design. 2. Click the Documents tab. 3. In the list, find and select the desired document. 4. Click Edit. 5. Sub-task: View where the document is used. 6. On the ActionPane, on the Deploy tab, click Where-used. 7. Close the page. 8. Sub-task: View where a document record is used. 9. In the Record section, in the list, find and select the desired record. 10. In the Record section, click Where-used. 11. Close the page. 12. Sub-task: View where a document record field is used. 13. In the Record section, in the list, find and select the desired record. 14. On the Fields tab, in the list, find and select the desired field. 15. On the Fields tab, click Where-used. 16. Close the page. 17. Close the page. Notes For an ODBC document, you can have fields defined that are not used in the connectivity setup, but that are required for the Database connection. Check relations Check relations For internal documents and ODBC documents, the relation between the tables of a parent record and a child record are important. The relation is required to be able to query from parent to child records in the record tree.Check or define the relation between the table for the current record and the table of the parent record.Note: If you define the parent record for an internal document and a table relation exists with the current records table, it automatically adds the first-found relation to the current record. If the relation fields are not yet added to the parent record or the current record, these are automatically added. For an ODBC document, you must set the relations manually. Procedure 1. Click Connectivity studio Integration Design. 2. Click the Documents tab. 3. In the list, find and select the desired internal or ODBC document. 4. Click Edit. 5. In the Record section, in the Record list, find and select the desired record. 6. Click the Relations tab. 7. Click New. 8. In the Record field field, enter or select a value. 9. In the Parent field field, enter or select a value. 10. Close the page. Define sorting Define sorting For internal documents and ODBC documents, you can define the order in which the data in the record is queried and processed during export or import. For example, to export sales orders, you want the sales order to be queried by customer and for each customer by delivery date. To do so, on the Sorting tab, add a record for both the CustAccount field and the DeliveryDate field, in this sequence. Procedure 1. Click Connectivity studio Integration Design. 2. Click the Documents tab. 3. In the list, find and select the desired internal or ODBC document. 4. Click Edit. 5. In the Record section, in the Record list, find and select the desired record. 6. Click the Sorting tab. 7. Click New. 8. Set the record field based on which you want to sort the data. In the Record field field, enter or select a value. Note: You can only enter a sorting for a field that is also defined in the record fields. 9. In the Order field, select an option. 10. Close the page. Notes If you face performance issues when processing large numbers of records, it can be that the record field of the sorting is not part of the index of the record table. To solve the performance issues, you can either remove the sorting or add the field to the index of the table (if you have control over the database). Define range Define range For each record, you can define the range of data that is queried for export or import. For example, you only want to export sales orders for a specific customer group. To do so, on the Range tab, add a record for the CustGroup field.For more information on how to define ranges in the Range field, refer to Advanced filtering and query syntax. Procedure 1. Click Connectivity studio Integration Design. 2. Click the Documents tab. 3. In the list, find and select the desired internal or ODBC document. 4. Click Edit. 5. In the Record section, in the Record list, find and select the desired record. 6. Click the Range tab. 7. Click New. 8. Select the Test check box. 9. In the Record field field, enter or select a value. Note: You can only enter a range for a field that is also defined in the record fields. 10. In the Range type field, select an option. 11. In the Range field, type a value. 12. Close the page. Notes By default, the entity name field is used as an extra range. If on import, the data source name is known, it creates an entity with the correct name. For EDIFACT, you cannot always use this. For example, for the dates (DTM) segment, no easy condition exists to find the right date. Therefore, you can add your own range. This range skips the range for the entity name. A range to the createdTransactionid is used to make sure that the current set is imported. For each transaction scope a new ID is created.If you have defined a range of type Custom, you can test if the expression gives the expected result. To do so, on the Action Pane, on the Development tab, click Custom range. On the dialog, in the Expression field, enter the expression, and press Tab. The expression result is shown in the Result field. Validate setup

Validate connectivity setup

If you open a form or save (changes to) the setup of a key element in Connectivity studio, the setup is validated automatically. If errors are found in the setup, an error icon   is shown. You can click the icon to show the related error messages.

You can also manually start an automated test to check for errors in the setup. As a result, the found errors are shown. Also, the error icons are shown where applicable.
When errors are found, you can try to fix these errors automatically.
You can check and auto-fix errors for these key elements in Connectivity studio:
 

Key element

Check

Projects

When automatically checked, only the project setup is checked, and not the related setup like messages and connectors. When started manually, the full project setup is checked.

Documents

The document setup is checked, including the document records setup.

Document records

Only the document records setup is checked.

Messages

The message setup is checked, including the data synchronization setup and message mapping.

Message - Data synchronization setup

Only the data synchronization setup is checked.

Message mapping

Only the message mapping is checked.

Message business events

A check is done if a business event is created for the message business event. Also, a check is done if the target fields and source fields match with the related document setup.

Connectors

Only the connector setup is checked.

Web services

The web service setup is checked, including the data synchronization setup.

Web service - Data synchronization setup

Only the data synchronization setup is checked.
 
In this flow, in the activity steps, as an example, the validation is done for documents.

 End End

Activities

Name Responsible Description

Define record sequence

Application Consultant

You can organize the business document records in these ways:

  • Define a parent-child relation with another record to define the record structure.
  • Change the sequence of the records.
You can only change the sequence of records if these are on the same level in the record structure. So, for example:
  • If you move a parent record, you cannot move it to a position below its child records. The child records stay as child records and are moved as well.
  • You can change the sequence of child records with the same parent.

View where document record is used

Application Consultant
Sometimes, if you want to clean-up your document setup, you cannot delete an element. In such a case, you can view where the element is used in the connectivity setup.
You can do so for a:
  • Document
  • Document record
  • Document record field

Check relations

Application Consultant

For internal documents and ODBC documents, the relation between the tables of a parent record and a child record are important. The relation is required to be able to query from parent to child records in the record tree.

Check or define the relation between the table for the current record and the table of the parent record.

Note: If you define the parent record for an internal document and a table relation exists with the current records table, it automatically adds the first-found relation to the current record. If the relation fields are not yet added to the parent record or the current record, these are automatically added. For an ODBC document, you must set the relations manually.

Define sorting

Application Consultant

For internal documents and ODBC documents, you can define the order in which the data in the record is queried and processed during export or import.

For example, to export sales orders, you want the sales order to be queried by customer and for each customer by delivery date. To do so, on the Sorting tab, add a record for both the CustAccount field and the DeliveryDate field, in this sequence.

Define range

Application Consultant

For each record, you can define the range of data that is queried for export or import.

For example, you only want to export sales orders for a specific customer group. To do so, on the Range tab, add a record for the CustGroup field.

For more information on how to define ranges in the Range field, refer to Advanced filtering and query syntax.

Validate setup

Application Consultant

If you open a form or save (changes to) the setup of a key element in Connectivity studio, the setup is validated automatically. If errors are found in the setup, an error icon   is shown. You can click the icon to show the related error messages.

You can also manually start an automated test to check for errors in the setup. As a result, the found errors are shown. Also, the error icons are shown where applicable.
When errors are found, you can try to fix these errors automatically.
You can check and auto-fix errors for these key elements in Connectivity studio:
 

Key element

Check

Projects

When automatically checked, only the project setup is checked, and not the related setup like messages and connectors. When started manually, the full project setup is checked.

Documents

The document setup is checked, including the document records setup.

Document records

Only the document records setup is checked.

Messages

The message setup is checked, including the data synchronization setup and message mapping.

Message - Data synchronization setup

Only the data synchronization setup is checked.

Message mapping

Only the message mapping is checked.

Message business events

A check is done if a business event is created for the message business event. Also, a check is done if the target fields and source fields match with the related document setup.

Connectors

Only the connector setup is checked.

Web services

The web service setup is checked, including the data synchronization setup.

Web service - Data synchronization setup

Only the data synchronization setup is checked.
 
In this flow, in the activity steps, as an example, the validation is done for documents.

Provide feedback