Developing a Navision API Web Service in Xojo

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.

navaip

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:

page design

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:

Customer Balance Page

The page is published as a web service on the “Web Services” page:

Web services in Dynamics NAV

For running web services from Dynamics NAV some configuration has to be done on server level but that is not covered right now.

JSON

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.

HandleSpecialURL

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:

Xojo Integrated Development Environment

 

appdesign

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:

resultAPI

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:-)

 

 
Comments
 
Comments

No comments yet.