Open Data Protocol (OData) is a data access protocol initially defined by Microsoft.  In NAV 2013 Microsoft first added a support for OData.  On the Developer Network Microsoft has a few walk-throughs aimed to get us started in using both SOAP and OData web services.

To get started with OData first make sure that the OData service is enabled on your developement server.

ODataEnabled

As you can see on the image above I also enable the use of NTLM Authentication.  In a production environment OData should use SSL as described in this walk-through from Microsoft.

I want to share with you a few points that I have found out and are not obvious in the walk-throughs.

When an external application makes a OData request NAV will behave the same way as a Windows Client would do.  The server will do the login routine by executing trigger 1 in Codeunit 1, CompanyOpen and when the request is finishing the server executes the CompanyClose trigger.  If the request if for a Page object then all the page triggers are executed, OnInit, OnOpenPage and so on.

The OData will only show the fields added to the Page or to the Query with the table primary key fields added.  Lets take a closer look at how the Page object works with OData.

In the OnOpenPage trigger and in the SourceTableView property it is possible to add filters.  These filters will apply to the OData stream and you will not be able to insert data into NAV outside of these filters – same functionality as if you where using the Page in the Windows Client.  The properties; Editable, InsertAllowed, ModifyAllowed and DeleteAllowed all work with OData.  Lets for example look at Page 39 – General Journal.  In the OnOpenPage trigger the code is filtering the table data with a Template Name and a Batch Name.

Page39

From OData, the variable OpenedFromBatch will always be False so the first template and batch for the PAGE::”General Journal” will always be selected and new entries will always be inserted into that journal.  This also means that it is not possible to use Page 39 to read the journal lines from any other journal then the first one.  Fields that are not visible in a Page are visible and usable in OData.

When creating a new record the code in the OnNewRecord trigger is executed.

OnNewRecord

This will all work fine for the first template and batch.  The AutoSplitKey property is also active so as long as you are fine with inserting in to the default journal then you can use this Page for your OData web service.

The easiest way is still to create a new page dedicated to the web service functionality, show the primary key fields in the page and skip the OnOpenPage and the OnNewRecord code.  I use the OnNewRecord code to put in default values for the table I am inserting into.

On the Microsoft web site walk-through it is shown how to create a new customer, look for a customer and modify a customer.

I have found that I want to add one line to that example

[code] NAV nav = new NAV(new Uri("http://localhost:7048/DynamicsNAV/OData/Company(‘CRONUS%20International%20Ltd.’)"));
nav.Credentials = CredentialCache.DefaultNetworkCredentials;
nav.IgnoreResourceNotFoundException = true;[/code]

Without IgnoreResourceNotFoundException the following code example will return an exception if the customer is not found within the given filter.

[code lang=”csharp”] private static Customer GetCustomer(NAV nav, string customerNo)
{
var customers = (from c in nav.Customer
where c.No == customerNo
select c);
foreach (Customer customer in customers)
return customer;
return null;
}[/code]

By combining a Get function like this with a New or Modify function it is easy to update the existing value for any given table in the database.

[code lang=”csharp”]private static Boolean AddNewDefaultDimensionCodeValue(NAV nav, int tableNo, string no, string dimensionCode, string dimensionCodeValue)
{
DefaultDimensions existingDefaultDimension = GetDefaultDimension(nav, tableNo, no, dimensionCode);
if (existingDefaultDimension == null)
{
DefaultDimensions newDefaultDimension = new DefaultDimensions();
newDefaultDimension.Table_ID = tableNo;
newDefaultDimension.No = no;
newDefaultDimension.Dimension_Code = dimensionCode;
newDefaultDimension.Dimension_Value_Code = dimensionCodeValue;
nav.AddToDefaultDimensions(newDefaultDimension);
nav.SaveChanges();
return true;
}
else
{
existingDefaultDimension.Dimension_Value_Code = dimensionCodeValue;
nav.UpdateObject(existingDefaultDimension);
nav.SaveChanges();
return false;
}
}
private static DefaultDimensions GetDefaultDimension(NAV nav, int tableNo, string no, string dimensionCode)
{
var dimensionValues = (from d in nav.DefaultDimensions
where d.Table_ID == tableNo && d.No == no && d.Dimension_Code == dimensionCode
select d);
foreach (DefaultDimensions dimensionValue in dimensionValues)
return dimensionValue;
return null;
}
[/code]

Remember, that without the SaveChanges nothing will be updated in NAV.

Now go ahead and use OData to integrate NAV with all your external systems and devices.  Good luck.

16 thoughts on “OData in NAV 2013 R2

  1. Dave Machanick says:

    I am getting an error – ‘CredentialCache’ does not exist in the current context (for nav.Credentials)
    Is there a missing step or a feature I need to turn on?

    Dave

  2. Hi Dave
    Did you create the Service Reference correctly and add a Using line for the reference ?

  3. Dave Machanick says:

    I followed the example as best as I could. I have the using line right above the class program line.
    The service reference – I changed it to match the Odata service field in the web services table, From the web service table I can press the icon and it brings up info about the service in Internet Explorer.
    I noticed the Odata field in web service table does not default to the service tier name. If I start off with a mismatch, could that be the problem? Should I delete and recreate the service reference? (I cannot find a way to change the address I used for the service reference.)

  4. I use “http://NAV2013R2DEV.Kappi.local:7068/DynamicsNAV71W1/OData/Company(‘CRONUS%20International%20Ltd.’)” in my Uri code but “http://NAV2013R2DEV.Kappi.local:7068/DynamicsNAV71W1/OData” in the Service Reference.

    You can select in Visual Studio Solution Explorer to Show All Files. After that you will see Reference.datasvcmap with the address that you should be able to change.

  5. Dave Machanick says:

    I have been using localhost. In web service my OData URL is: http://localhost:8148/HAS/OData/Company('Hasco%20-%20Live‘)/CustomerCard.
    In C# my reference address is: “http://localhost:8148/HAS/OData/”
    Do I need to change localhost to a different format, and if yes what? Do I need to set up IIS?
    I don’t understand what the 3 parts of “NAV2013R2DEV.Kappi.local” are referring to.

    1. “NAV2013R2DEV.Kappi.local” is the full DNS name of my NAV 2013 R2 developement machine.

      1. David Machanick says:

        Hi,
        I am at Directions, so I asked one of the Microsoft people to help me.
        I have a missing line (that is not in the example either).

        using System.Net;
        That fixed my problems running the code.

  6. Neeraj says:

    Is it work for multi tenant environment also?

  7. Hi Neeraj, this also works in multi tenancy. Just add ?tenant=default to the URL and if you use NAVUserPassword authentication you use tenant\username for authentication.

  8. Marcelo says:

    Hi Gunnar, I get a system.argumentexception when adding ?tenant=default to the Nav nav = new uri.

    The error generates from the web services referece,cs and it says “Expected an absolute, well formed http URL without a query or fragment.”

    If I remove the ? sign then it does not gives me any error but it does not display the data.

    Any idea of why I cannot use the ? sign which is necessary to indicate a tenant?

    1. Marcelo says:

      Found out how to do it. Actually you do not have to add the ?tenant=default directly to the new uri but have to addqueryoption to the nav.Customer function. I used the following code for future references:

      string baseUrl =”http://localhost:7048/DynamicsNAV71/OData/Company(‘TIGO%20HN’)/”;
      Uri baseUri = new Uri(baseUrl);

      NavOData.NAV nav = new NavOData.NAV(baseUri);
      nav.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials;

      //
      string tenantID = “tigo”;
      var customers = from c in nav.Customer.AddQueryOption(“tenant”,tenantID) select c;
      dataGridView1.DataSource = customers.ToList();

      1. JB says:

        hello, it’s working great to query but how would you apply this change when trying to POST (http POST) a line?

        1. The you need OData on top of a page.

  9. Peter says:

    Hi Gunnar,

    are there any advantages of using the Odata Services compared to the “old” Soap Services? My main concerns are message size and general performance. A simple performance test for a large page has shown no remarkable differences between these two.

  10. Hi

    The fastest methods are using OData for a Query and SOAP on top of a XMLPort.

Leave a Reply

%d bloggers like this: