1. Data Management
  2. Connectivity Studio
  3. Design
  4. Set up documents
  5. Set up external documents - ODBC

Set up ODBC document

Use an ODBC document to define the data model of the external source or target with which you want to exchange data. You can exchange data with an external database via ODBC or with an external Azure SQL database.


Application Consultant Application Consultant Start Start Set up document - ODBC Set up document - ODBC Use an ODBC document to directly read data from or write data to an external database. You can exchange data with an external database via ODBC or with an external Azure SQL database. You can also exchange data with another database using CData or DB2. Procedure 1. Click Connectivity studio Integration Design. 2. Click the Documents tab. 3. Click New. 4. Define a meaningful name for the document. Example: If the document is used for a sales integration, you can use names like 'Sales - Order' or 'Sales - Invoice'. In the Document field, type a value. Note: Best practice: In the document name, do not use the: - Application: Use the Application field to define the applicable application. - Document type: Use the Document type field to define the applicable document type. 5. In the Project field, enter or select a value. 6. Define the applicable application for the document. For an ODBC document, for example, select a 'ODBC' application. In the Application field, enter or select a value. Note: You can only select an application that is defined for the applicable project. You can define project applications on the Projects page. 7. In the Document types field, select 'ODBC'. 8. Sub-task: Set properties. 9. Expand the Properties section. 10. To improve performance when processing a lot of records, you can use paging. For paging, the records are split over several threads which run these records in parallel batch tasks. Define the number of records to be processed by one batch task. In the Page size field, enter a number. Note: You can use this calculation to define the number to be entered: Page size = Total number of records / Number of available threads. 11. In the Process type field, select an option. 12. Select Yes in the Allow space field. 13. Select Yes in the Adjust date time field. 14. Select Yes in the Disable Unicode field. 15. Sub-task: Set (custom) handler. 16. Expand the Custom section. 17. In the Handler field, enter or select the desired handler class. 18. Sub-task: Set ODBC commands. 19. Expand the ODBC command section. 20. Click New. 21. In the ODBC command field, type a value. 22. In the Reference field, enter or select a value. 23. Select the Execute before check box. 24. Close the page. Add document records - ODBC Add document records - ODBC To each document, add the data records to be exchanged. For ODBC documents, set up the records in line with how the data is structured and named in the external database.This topic explains how to add records to an ODBC document. Procedure 1. Click Connectivity studio Integration Design. 2. Click the Documents tab. 3. In the list, find and select the desired ODBC document. 4. Click Edit. 5. In the Record section, click Add line. 6. In the Record field, type a value. 7. In the Record table field, keep the default value 'BisBufferTable'. 8. In the Parent record field, enter or select a value. 9. Select Yes in the Combine with parent record field. 10. In the Join mode field, select an option. 11. Select Yes in the History field. 12. Sub-task: Set record details. 13. Select Yes in the Exclude company where clause field. 14. In the SQL owner schema field, type a value. 15. In the External table field, enter or select a value. 16. Close the page. Notes Generic external document record details fields:External reference: You can indicate which field of the record contains the unique ID of the record in the external database. In this way, you link the external ID to the D365 FO record ID. Note: Usually, you only use this for an ODBC connection with CRM or if you use web services.Revision field: You can indicate which of the records fields contains the revision of the record in the external database. In this way, you find and link the latest revision to the record ID. Note: You can only use this field in combination with the External reference field. Manage document record setup - ODBC documents

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.

 Initialize document record fields - ODBC Initialize document record fields - ODBC For documents of type ODBC, you can initialize the fields for a record. To initialize record fields for an ODBC document, use a connector of type Database to connect to the applicable external database. Make sure, the name in the Record table field is exactly the same name as the relevant table in the external database. The fields are added to the record based on the fields of the external table. When the initialization is finished, review and complete the properties of the initialized fields. Usually, during review, you do not add fields. However, you can remove the not needed fields. Procedure 1. Click Connectivity studio Integration Design. 2. Click the Documents tab. 3. In the list, find and select the desired document of type ODBC. 4. Click Edit. 5. In the Records section, in the Record list, find and select the desired record. 6. On the Fields tab, click Initialize. 7. In the Connector field, enter or select a value. 8. Click OK. 9. Close the page. Select fields - ODBC Select fields - ODBC You can add a selection of table fields to a record. You can select fields from an external table via ODBC. To connect to the external environment, the default connector of type Database. You can set up this default connector for the applicable project. To find the external table name, the name that is defined in the Record table field is used. When the field selection is added to the record, review and complete the properties of the added fields. Procedure 1. Click Connectivity studio Integration Design. 2. Click the Documents tab. 3. In the list, find and select the desired document of type ODBC. 4. Click Edit. 5. In the Record section, in the Record list, find and select the desired record. 6. On the Fields tab, click Select fields. 7. Click Select all. 8. Click Deselect all fields. 9. You can select mandatory fields only. Click Select mandatory. Note: You can also select fields manually. 10. Click OK. 11. Close the page. Copy fields Copy fields You can copy fields from a record of another document. You can use this, for example, to save setup time if you use a specific record in several documents.You can only copy fields:From a record with the Record table defined.To a record with no fields.As a result, the full field setup is copied from the selected record to the current record. 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. In the Record section, in the Record list, find and select the desired record. 6. Click Copy fields. 7. In the Record field, select the desired record from another document. Note: You can only select a record with the same Record table defined as the current record. 8. Click OK. 9. Close the page. Add document records fields - ODBC Add document records fields - ODBC To each document record, add the data fields which values must be exchanged. For ODBC documents, set up the fields in line with naming in the external database.For ODBC documents, make sure the fields have the same type as in the external database.This topic explains how to add record fields to an ODBC document.If fields are already initialized for, selected for, or copied to the record, you can review and complete the setup for these fields. To do so, skip step 6. Procedure 1. Click Connectivity studio Integration Design. 2. Click the Documents tab. 3. In the list, find and select the desired ODBC document. 4. Click Edit. 5. In the Records section, in the Record list, find and select the desired record. 6. On the Fields tab, click New. 7. For the applicable project, you can define a default ODBC connector to an external database. If the Database connection is active, and you selected an external table in the Record field, you can select fields from the external table. If no Database connection is active, enter a free-definable name. Usually, this is a field name as used in the external database. In the Field name field, type a value. Note: The value of this field cannot have spaces. 8. For the applicable project, you can define a default Database connector to an external database. If the Database connection is active, and you selected an external table in the Record field, you can select fields from the external table. The value, as entered in the Field name field, is the default value. In the Record table field field, type a value. Note: The value of this field cannot have spaces. 9. Select the Key field check box. 10. In the Mandatory field, select an option. 11. In the Length field, enter a number. 12. In the Type field, select an option. 13. In the Dimension field, select an option. 14. In the Dimension field field, enter or select a value. 15. Define the name of the dimension to be read from Microsoft Dynamics AX 2012. In the Dimension name field, type a value. Note: Only use the dimension fields for migrations from Microsoft Dynamics AX 2012 to D365 FO. 16. Sub-task: Set external references for record. 17. In the Line details section, set the external references for the record. You can indicate which of the records fields contains the unique ID of the record in the external database. In this way, you link the external ID to the record ID. In the External reference field, enter or select a value. Note: Usually, you only use this for an ODBC connection with CRM or if you use web services. 18. You can indicate which of the records fields contains the revision of the record in the external database. In this way, you find and link the latest revision to the record ID. In the Revision field field, enter or select a value. Note: You can only use this field in combination with the External reference field. Manage document record field setup

Manage document record field setup - Internal documents

You have several options to manage the document record field setup for internal documents.
You can:

  • Change the sequence of the fields.
  • View where a field is used.
  • Clean up unused fields.
  • Create a related record.

 Validate connectivity 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

Set up document - ODBC

Application Consultant

Use an ODBC document to directly read data from or write data to an external database. You can exchange data with an external database via ODBC or with an external Azure SQL database.

You can also exchange data with another database using CData or DB2.

Add document records - ODBC

Application Consultant

To each document, add the data records to be exchanged. For ODBC documents, set up the records in line with how the data is structured and named in the external database.

This topic explains how to add records to an ODBC document.

Manage document record setup - ODBC documents

Application Consultant

 		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.

Initialize document record fields - ODBC

Application Consultant

For documents of type ODBC, you can initialize the fields for a record. To initialize record fields for an ODBC document, use a connector of type Database to connect to the applicable external database.

Make sure, the name in the Record table field is exactly the same name as the relevant table in the external database.
The fields are added to the record based on the fields of the external table.
When the initialization is finished, review and complete the properties of the initialized fields. Usually, during review, you do not add fields. However, you can remove the not needed fields.

Select fields - ODBC

Application Consultant

You can add a selection of table fields to a record.

You can select fields from an external table via ODBC. To connect to the external environment, the default connector of type Database. You can set up this default connector for the applicable project.

To find the external table name, the name that is defined in the Record table field is used.

When the field selection is added to the record, review and complete the properties of the added fields.

Copy fields

Application Consultant

 		You can copy fields from a record of another document. You can use this, for example, to save setup time if you use a specific record in several documents.
You can only copy fields:
  • From a record with the Record table defined.
  • To a record with no fields.
As a result, the full field setup is copied from the selected record to the current record.

Add document records fields - ODBC

Application Consultant

 		To each document record, add the data fields which values must be exchanged. For ODBC documents, set up the fields in line with naming in the external database.
For ODBC documents, make sure the fields have the same type as in the external database.
This topic explains how to add record fields to an ODBC document.
If fields are already initialized for, selected for, or copied to the record, you can review and complete the setup for these fields. To do so, skip step 6.

Manage document record field setup

Application Consultant

 		You have several options to manage the document record field setup for internal documents.
You can:
  • Change the sequence of the fields.
  • View where a field is used.
  • Clean up unused fields.
  • Create a related record.

Validate connectivity 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