For me it is great to be able to have fun with two environments I very much love: on a daily basis I work with Microsoft Dynamics NAV and consult and develop for customers for a market leading Microsoft partner. In my spare time and for a occasional customer I love my development tool Xojo. It enables me to make cross-platform applications, web applications and services and even iOS apps. Xojo just gets stuff done and Dynamics NAV as well:-)
Combining these two environments gives new opportunities, but also some difficulties to overcome. Today I wanted to make a ‘proof of concept’ that I would be able to consume data from Dynamics NAV 2015, with my NAV API Web service (build in Xojo), and pass the data to another application or process. In this particular case, I pass the data to a common listbox which runs on a web page in my web service. In the next graphic however I mention iOS as a target, for showing the data in an iOS app as well, but that is the subject for my next post.
Web Service in Dynamics NAV
One of the ways to expose data from NAV is by using web services. This is as easy as making a new custom page, put a few fields on it and publish the page as web service from the NAV client:
This page is a page of type ‘List’, no filters or special coding are applied, the page just shows all customers and their current balance like this if you would run it from the Dynamics NAV client:
The page is published as a web service on the “Web Services” page:
For running web services from Dynamics NAV some configuration has to be done on server level but that is not covered right now.
In this post I make use of the option to request JSON data from Dynamics NAV. I find JSON data easier to handle than plain XML and has, in my opinion, less overhead. JSON is also very similar to a iOS dictionary and is therefore much easier to handle in iOS and Java. I will cover iOS in my next post.
NAV API Web Service in Xojo
I need a web service that acts as middleware or API (Application Programming Interface) between Dynamics NAV and third party applications like iOS/Android/Desktop: my API needs to listen and respond to the requests of third party applications and has to query data from the Dynamics NAV web service in a secure way.
With Xojo I can make web applications that can act as an API: a web application build with Xojo can run as standalone web server or as CGI on a specific port, using http and/or https for secure communications. For authentication with Dynamics NAV, my API needs to give its credentials. I use NTLM authentication for that reason. This is also a server setting for Microsoft Dynamics NAV but must also be supported by the API I plan to build.
My API will get various requests from third party apps and needs to translate the request to the appropriate actions and return the requested data. Thanks to the Event Handler “HandleSpecialURL” I am able to ‘catch’ the URL that is requested from the API. Based on that request my API will execute the correct query and return the requested data, or send back a 401 or 500 error in case my API cannot handle the request. I will cover URL handling in my next post.
Proof of concept
Before starting to build an API I want to know first what difficulties I might encounter and decide what the API must be able to do and who would use it. For now I think that my API only should return information and should give no opportunity to enter data. It could be used by salespeople on the road to query the balance of their customers for example and give directions to the customer with the highest and oldest balance:-)
Building a web application in Xojo is easy. You need a page, add some controls to it, and add code where this is needed:
This design was done in about 10 minutes. Notice the button “Get Customer Balance List”, a click on this button should:
- execute code to send a request to the Dynamics NAV web service,
- give the proper credentials to Dynamics NAV,
- receive the data,
- put the data in the textfield,
- and finally populate the listbox with normalized data.
Clicking on the button in the Xojo IDE gets us to the code (Please notice that this code is experimental and in no way to be used in production environments. Error handling, security, proper UTF8 encoding and text formatting is missing.)
Dim c As New CURLSMBS Dim cresult As Integer Dim URL As String Dim UrlPart(3) As String // Get URL parts together, we need URL encoding UrlPart(0) = "http://hp-jacco:7048/DynamicsNAV80/OData" UrlPart(1) = "Company('CRONUS Nederland BV')" UrlPart(2) = "CustomerBalanceList" //UrlPart(3) = "No eq '20000'" // optional filter on Customer '20000' // Compose the URL form the Url parts en URL encode the parts URL = UrlPart(0) + "/" + EncodeURLComponent(UrlPart(1)) + "/" + EncodeURLComponent(UrlPart(2)) + "/?$format=json" // Add filter to URL if any If UrlPart(3) <> "" Then URL = URL + "&$filter=" + EncodeURLComponent(UrlPart(3)) End If // Do request with CURL c.OptionVerbose = True c.OptionURL = URL c.CollectOutputData = True c.OptionHTTPAuth = 8 c.OptionUsername = "[domain\user]" c.OptionPassword = "[password]" cresult = c.Perform // Put result in text area txtResult.Text = c.OutputData lblResult.Text = Url // Parse json Dim jsonData As String = c.OutputData Dim results As New JSONItem(jsonData) Dim navisionData As JSONItem = results.Value("value") // Put data in the listbox Dim i As Integer For i = 0 To navisionData.count-1 Dim navisionItem As JSONItem = navisionData.child(i) Listbox1.AddRow(navisionItem.value("No").StringValue,navisionItem.value("Name").StringValue,navisionItem.value("Address").StringValue,navisionItem.value("City").StringValue,str(navisionItem.value("Balance").CurrencyValue)) Next
The code to use was some figuring out for me. Xojo supports the http socket but I could not find a way to get NTML authentication to work with the httpsocket, tips anyone? So I ended up using a third party CURL plugin from MBS.
Running the API and clicking the button in the API gives the following result:
After clicking the button, the textfield is filled with raw JSON data. The listbox shows the normalized data. I have highlighted one specific record. No filters are applied on the data in this example. Data is received from 68 customers and takes less than a second on a remote machine.
Conclusion and some thoughts
My API enables me to consume data from Microsoft Dynamics NAV and expose the data to third party apps. Well, in my next blogpost I will add that extra dimension: I will build an iOS app that requests the data from my API. My API will request the data from Dynamics NAV and return the data to iOS. How cool would that be?
More fun to share and stuff to write about. Thank you for reading so far:-)