Skip to main content
Business Central forum
Answered

API query with Odata date filter with encoding

editSubscribe (0) ShareShare
ReportReport
Posted on by UG Leader

Hi

When calling an API endpoint on api.businesscentral.dynamics.com from BizTalk, we are required to include what looks like an Odata filter on the uri:

e.g:

&$filter=(documentDate ge 2020-07-01) and (documentDate le 2020-07-31)

In PostMan this returns records, but when the spaces are "encoded" it accepts the request but returns no records

e.g:

&$filter=(documentDate%20ge%202020-07-01)%20and%20(documentDate%20le%202020-07-31)

or

&$filter=(documentDate+ge+2020-07-01)+and+(documentDate+le+2020-07-31)

At the moment its a real struggle to change the BizTalk send behaviour to stop the encoding, so I was wondering if this is a API config issue?

Regards

Categories:
  • steveendow Profile Picture
    steveendow 2,281 on at
    RE: API query with Odata date filter with encoding

    Excellent!  Thanks for the update!

  • Verified answer
    Community Member Profile Picture
    Community Member UG Leader on at
    RE: API query with Odata date filter with encoding

    Ok, sorry, a quick update to say that the fully encoded url did in fact yield results.

    This turned out to be an issue with the actual date parameters we were asked to test with!

    The API quite happily returns results if you encode spaces with "%20" or "+" (even within the OData filter).

    Many thanks

  • Community Member Profile Picture
    Community Member UG Leader on at
    RE: API query with Odata date filter with encoding

    Thanks Steve for these suggestions.

    What you suggested is what we've already tried.

    The only difference being that we have additional parameters on the call.

    Full example:

    v1.0/salesCreditMemos?$expand=*&company=TEST%20UK%20Ltd.&$filter=(documentDate%20ge%202020-07-01)%20and%20(documentDate%20le%202020-07-31)

  • steveendow Profile Picture
    steveendow 2,281 on at
    RE: API query with Odata date filter with encoding

    Hi Andrew,

    Very interesting question.  I just did some tests and reviewed the OData V4 specifications, and here's my observation.

    It appears that the filter "values" can be encoded, but the OData filter syntax itself cannot be encoded.

    See section 2.2 URL Syntax on this page:

    http://docs.oasis-open.org/odata/odata/v4.01/odata-v4.01-part2-url-conventions.html

    The examples provided are not great, but notice that the parentheses are not encoded--only the filter "values" inside the parentheses are encoded.

    The OData standard appears to have some optional conventions, so I have found that Business Central doesn't support all of the specifications in that documentation, but seems to support most.

    I did a test with the BC Web API, and here's what I found:

    Unencoded filter works properly:

         items?$filter=number eq '1896-S'

    Fully encoded filter does not work (filter ignored):

        items?$filter%3Dnumber%20eq%20%271896-S%27

    Equals sign is NOT encoded, but everything after equals sign is encoded works:

        items?$filter=number%20eq%20%271896-S%27

    Based on this last test, encoding spaces AND apostrophes works for me

    I then tested with date ranges, which also worked:

         purchaseInvoices?$filter=(invoiceDate%20ge%202021-01-01)%20and%20(invoiceDate%20le%202021-02-01)

    This above example appears to be essentially the same as what you are using.

    The one thing I notice is that you have & before your "$filter" keyword.

    Can you try ? before the $filter?  In my example above, I'm using purchaseInvoices?$filter...

    Here's a full URL example:

    https://api.businesscentral.dynamics .com/v2.0 / mycompany.com/ Sandbox1/ api/v2.0/ companies(f11e1111-3d8a-eb11-bb5f-000d3a398a56)/ purchaseInvoices?$filter=(invoiceDate%20ge%202021-01-01)%20and%20(invoiceDate%20le%202021-02-01)

    .

    Let me know if the & vs ? makes a difference.

Helpful resources

Quick Links

What Motivates a Super User?

We know many of you visit the Dynamics 365 Community and Power Platform…

Demystifying Copilot with Sundar…

Industry experts answer burning questions directly from our amazing Community…

Enabling Copilot Case and Conversation…

Agents can easily recap an ongoing chat, transcribe a voice conversation…

Leaderboard

#1
Andre Arnaud de Calavon Profile Picture

Andre Arnaud de Cal... 282,978 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 222,562 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,138

Product updates

Dynamics 365 release plans