web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics NAV (Archived) / OData Query applying o...
Microsoft Dynamics NAV (Archived)

OData Query applying order

(0) ShareShare
ReportReport
Posted on by Pavel Penkava 115

Hello,
correct me if I am wrong, but I believe there is a bug in the Query implementation of OData.

I have a Web Service that returns Items from Dynamics NAV 2013 R2. There are plenty of products (Items) which has VAT_Prod_Posting_Group equal to empty string '', but when I send the request below, it returns just one item. It seems like the filter is applied after the applying of skip and top instead of the reversed order (one of the first 5 items really has the string value of '').
http://localhost:7048/DynamicsNAV71/OData/ItemsList?$skip=0&$top=5&$filter=VAT_Prod_Posting_Group eq ''

I've tried a similar request on the OData testing site (as seen below) and there it works as expected (skip and top are applied after the filter pattern).
http://services.odata.org/Northwind/Northwind.svc/Customers?$skip=0&$top=5&$filter=Fax eq null

*This post is locked for comments

I have the same question (0)
  • keoma Profile Picture
    keoma 32,729 on at

    hi

    try "...&filter=VAT_Prod_Posting_Group eq null" in your query instead.

    best regards

  • Pavel Penkava Profile Picture
    Pavel Penkava 115 on at

    Hello, I can't test it because I no longer work on the project and my licence expired already. But I guess I've tried this before and didn't work either.

  • Suggested answer
    keoma Profile Picture
    keoma 32,729 on at

    hi,

    after a greater number of tests, it seems that in nav odata services the filter tag and the top tag in the uri to not go together. skip is no problem. in general the filter call works.

    so

    localhost/.../ItemsList$skip=0&$filter=VAT_Prod_Posting_Group eq ''

    works.

    best regards

  • Pavel Penkava Profile Picture
    Pavel Penkava 115 on at

    Hello,

    it's good to know, but how can I bypass the need for the "top" tag? I wanted to retrieve only a few elements in each request so I could use it in lazy loaded list on mobile platforms.

  • Verified answer
    keoma Profile Picture
    keoma 32,729 on at

    hi,

    i tried

    http://localhost:7048/DynamicsNAV70/OData/Company('CRONUS%20AG')/ItemsList?$filter=Product_Group_Code%20eq%20%27%27&$top=3

    that works fine. 

    the field "Product Group Code" is in odata translated to Product_Group_Code.

    the field "VAT Prod. Posting Group" is translated to VAT_Prod_Posting_Group. with VAT_Prod_Posting_Group it does not work. Vat_Item_No does also not work. but only if the comparative value is '' (empty). if you use a concrete, valid value, e.g. 'VAT16', the top tag works too.

    strange enough.


    maybe that helps too:

    msdn.microsoft.com/.../jj551780(v=nav.71).aspx

    an alternative way:

    you are using an odata webservice for developing an application in c#?

    if so, filter the data with linq in c#. sample code:

    var client = new ServiceReference1.NAV(new Uri("http://localhost:7048/DynamicsNAV71/OData/"));
    client.Credentials = System.Net.CredentialCache.DefaultCredentials;

    var items = (from item in client.ItemsList
    where item.VAT_Prod_Posting_Group.Equals("")
    select item).ToList();
    Console.WriteLine("Result");
    int i=0;
    while (i < 5) {
      Console.WriteLine(items[i].No + " " + items[i].Description);
      i++; }
    Console.WriteLine("---------------------------");
    Console.ReadLine();

    for java check:

    https://code.google.com/p/odata4j/

    best regards

    if you find one of the answers helpful to find a solution please set it to verified. thx.

  • Pavel Penkava Profile Picture
    Pavel Penkava 115 on at

    Unfortunately it was application written in Java (for Android), so I couldn't use Linq. Everything would be much easier if it would be written in C#, which I prefer too :)

    But I guess the suggested page msdn.microsoft.com/.../jj551780.aspx could help, so we can close it because I can't test it any more ;)

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics NAV (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans