By Steve Endow
In June 2021, I discussed my circuitous journey to learn about and understand the $metadata and $expand OData "query options" and how to use them with the Business Central web API.
 |
| BC Web API OData Entity Model |
Here is that video:
Today I had to research the $expand option for a BC API, but completely forgot how to use $metadata and find the related record "multiplicities" or "NavigationProperties" available for BC records exposed by APIs.
Since I had forgotten everything I discussed in my video from June, I figured it was time to write down a few notes in a blog post so that I can come back to this post in the future to refresh my memory.
The $metadata Option
When working with the Business Central web API, you can add $metadata to the end of the base API URL to get information about all of the available endpoints as well as related data elements that can be accessed using the $expand option.
For example, when working with my BC Docker Container named "dev18", I would use this URL:
http://dev18-default:7048/BC/api/v2.0/$metadata
When working with a BC SaaS environment: (substitute your tenant and environment)
https://api.businesscentral.dynamics.com/v2.0/mycompany.com/PRODUCTION/api/v2.0/$metadata
You can read all the gory details in the OData documentation here:
If you access the above Business Central API URL with the $metadata option, the request should return a massive pile of XML that looks something like this:
|
| BC Web API OData Entity Model |
Okay, so what are you supposed to do with those 4,000 lines of XML?
First, find the BC API object that you are working with. For example, let's suppose we want to know which additional data elements can be expanded on the Customer object.
If you search in this XML for "customer", you'll find lots of entries. Look for the line that says EntityType Name="customer".
This shows the customer object and the properties (fields) that are returned by the API.
If you scroll down past the Properties, you will start to see "NavigationProperty" items.
These are the "child records" or "related records" that you can include with the Customer records using the $expand option.
The $expand Option
Now that we know the NavigationProperty objects that can be retrieved for a Customer, we can use the $expand option in our API request.
You can read all the gory OData details about $expand here:
For example, if I want to retrieve a specific customer, and I want to also retrieve the Contacts associated with the customer, my request would look like this:
Sample Docker container URL:
http://dev18-default:7048/BC/api/v2.0/companies(aaaa3def-4c1f-ec11-bb7c-000d3a2bffff)/customers(11113516-4d1f-ec11-bb7c-000d3a2b9999)?$expand=contactsInformation
Sample BC SaaS URL:
https://api.businesscentral.dynamics.com/v2.0/mycompany.com/PRODUCTION/api/v2.0/companies(aaa73282-83ff-ea11-aa61-0022481e3fff)/customers(1111895e-84ff-ea11-aa61-0022481e9999)?$expand=contactsInformation
Using this $expand option includes contactsInformation in the customer data:
If I repeat this process for BC items, I go back to my $metadata XML and search for EntityType Name="item". I can then see the NavigationProperty values that are available for BC items.
In the case of items, there are only 5 navigation properties available for use with $expand. Which is a bummer, because my customer would like to retrieve item quantities by location from the BC API. Based on this metadata, it looks like quantities by location are not available, and it will likely require a custom API.
Steve Endow is a Microsoft MVP in Los Angeles. He works with Dynamics 365 Business Central, Microsoft Power Automate, Power Apps, Azure, and .NET.
