Business Central Web API Error When Using $expand

 By Steve Endow

I recently responded to a Business Central Community Forum post here:

https://community.dynamics.com/business/f/dynamics-365-business-central-forum/448915/api-query-with-odata-date-filter-with-encoding

While researching that question, I learned something from that forum post:  $expand=*

Huh, I didn't know that was possible!

If you aren't familiar with the $expand OData query option, check out my blog post on the topic:

https://blog.steveendow.com/2021/09/using-metadata-and-expand-with-business.html

So it seems that you can use a wildcard with $expand to automatically expand all Navigation Properties available in a Business Central Web API endpoint. Very interesting!

I personally don't think I would ever recommend using such a query option in a real world API integration, but I was intrigued by the possibility!

So I fired up Postman and did some testing with my new found query option.

Query Option $expand=*

To my surprise I was greeted by this error:

Error: Application_FilterErrorException Message

"The filter \"US\" is not valid for the System ID field on the Country/Region table. Invalid format of GUID string. The correct format of the GUID string is: CDEF7890-ABCD-0123-1234-567890ABCDEF where 0-9, A-F symbolizes hexadecimal digits."

Huh. Well, that's interesting.

My first inclination was to assume that there was some bad data in the sample CRONUS company that I was testing with.  I've run into a few situations where the demo data was incomplete or invalid, resulting in validation errors.

But after reviewing all of the customers in CRONUS, the Country/Region value looked okay.

And then I read the error message a few more times.  US is not valid for the System ID field, which is a GUID?  That sounds like a data type mismatch, and I couldn't imagine how the sample customer records in CRONUS could be causing this error.

So I posted my results on Twitter, and Jeremy, AJ, and Erik all confirmed that it looked like a bug in the Business Central Web API.

So that was indeed interesting.  But the geek inside of me was actually less interested in that, and more interested in yet another thing I learned while researching the error.

I realized that I did not know how the Navigation Properties are added to a Business Central Web API endpoint.  I thought I knew, and thought I had researched it previously, but after reviewing the AL API page code for the Sales Credit Memos, I couldn't find the code that created the Navigation Properties.

It may be there, and it may have been staring right at me, but for some reason I was unable to identify exactly how the ELEVEN different Navigation Properties are declared or defined in the API page code.  I swear that I've worked on a custom API page that had a Navigation Property, but for some reason this one eluded me.

Here is the AL code for the Sales Credit Memos API page:

https://github.com/microsoft/ALAppExtensions/blob/main/Apps/W1/APIV2/app/src/pages/APIV2SalesCreditMemos.Page.al

AJ Kauffmann provided me with some info on how the Navigation Properties are defined, so that's my next learning assignment.

Steve Endow is a Microsoft MVP in Los Angeles.  He works with Dynamics 365 Business Central and related technologies.

You can also find him on Twitter and YouTube