Skip to content
+1-888-319-3663

EONE BLOG

Tech Tuesday: Using the NAV/D365 Business Central Odata Query Builder


SmartConnect 20.18.1.11 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

  1. First, make sure the web service you want to connect to is published in NAV/D365 BC.
  2. Select your company, and click the plus(+) icon to add a new service.
  3. Select the service you want from the list.
  4. 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.

  1. Click the ellipsis(…) to open the filtering window.
  2. In the Filter Window select the column to be used in the filter from the left side.
  3. Select your operator and enter your variable. In my example I am using the below query.
  4. 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.

  1. 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
  2. In the map, open the Filter Window and select your global variable from the right side
  3. 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
and
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.

  1. Select the Modify button from the Data Source section.
  2. In the Modify Query Window, select all the tables from the right side of the screen
  3. Join the tables together by dragging a key field from one table onto another
  4. Select the fields you need from all tables
  5. If necessary, rename a field using the Alias field at the bottom of the window
  6. 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.

Leave a Comment





RECENT POSTS


2021 Q4 Promotions
Connect with us at Directions EMEA 2021 in Milan!
eOne at Summit North America 2021
Employee Spotlight: Megan Tripp, Business Development Representative
eOne is now compatible with Microsoft Dynamics GP October 2021 Release

POPULAR POSTS


2016 SmartConnect Integration Bootcamps
Tech Tues: Automatically refreshing Pivot Tables in Excel Refreshable Reports
Happy Thanksgiving from the eOne team!
2017 SmartConnect Integration Bootcamps
New Releases of Extender and SmartList Builder

CATEGORIES

TAGS

Business Central CRM D365 Business Central Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News error Error Message Events Excel Excel Report Builder Extender Flexicoder GP integration Map Meet the Team Microsoft dynamics crm Microsoft Dynamics GP Navigation List Builder Office Relationships partners Popdock release Reporting SalesForce SalesForce.com SmartConnect SmartConnect.com SmartConnect Bootcamp SmartConnect Maps SmartConnect Office Hours SmartList SmartList Builder SmartPost SmartView SQL Tech Tuesday Templates training

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.