SmartConnect 22.214.171.124 and later include the NAV/D365 Business Central OData connector, which uses the Odata services instead of the SOAP services used by the standard NAV/D365 Connector. This gives SmartConnect greater flexibility when using NAV/D365 BC as both a source and destination. In this week’s Tech Tuesday, I will go into detail about using the new OData Connector as a data source.
If you are using an older version of SmartConnect, the new version is available here.
Here is a link with instructions for configuring the NAV/D365 Odata connector.
Adding a Service
- First, make sure the web service you want to connect to is published in NAV/D365 BC.
- Select your company, and click the plus(+) icon to add a new service.
- Select the service you want from the list.
- Repeat the steps above to add as many as desired.
Adding Service Filters
If desired, a filter can be added to the individual services selected.
- Click the ellipsis(…) to open the filtering window.
- In the Filter Window select the column to be used in the filter from the left side.
- Select your operator and enter your variable. In my example I am using the below query.
- Clicking preview in this window will display the results from only the selected service.
*note: white space does not matter in the Filter Window
Here is a link to Microsoft Documentation about the available Odata filters.
Adding Service Filter using a Global Variable
The Odata connector allows the use of Global Variables in Service Filters.
- Create a new Global Variable to be used in the Service Filter
- Navigate to SmartConnect > Maintenance > Global Variables
- Create a new variable and click the (Add) button
- Assign a default value; the default value will be used when previewing the source
- In the map, open the Filter Window and select your global variable from the right side
- Build the query same as you would with hard coded values, but use global variables in their place
Here is how the query used earlier would look when using global variables. Notice that there are single quotes around the vendor global. This is because it contains a string value and quotes are required for string values.
Expected_Receipt_Date gt GBL_DATE
Buy_from_Vendor_Name eq ‘GBL_VENDOR’
Joining Multiple Services
In my example I am pulling Purchase Orders and Purchase Order lines. Before I am done I need to join the services together so SmartConnect can process them.
- Select the Modify button from the Data Source section.
- In the Modify Query Window, select all the tables from the right side of the screen
- Join the tables together by dragging a key field from one table onto another
- Select the fields you need from all tables
- If necessary, rename a field using the Alias field at the bottom of the window
- Click the Data tab at the top right to verify the query returns the correct results
Common Error Messages
‘Unable to call service: A binary operator with incompatible types was detected.’
This error message means the value used in the variable was not a valid data type. Make sure your filters have single quotes around strings and not around other data types
‘Please enter all fields for the data Source.’
First click the Modify button and join your services together.
‘You can only modify data sources that have more than one service linked.’
First add at least two services to the map before modifying the join.