16) Data Entity Filtering in Dynamics 365 for Finance & Operations

Data entities were introduced in D365FO to provide a way for external services to interact with business processes within D365FO without having to know or understand how the inner workings of that data was stored. It was built on top of the OData protocol which provides a standard by which we can consume those objects through RESTful APIs. But what I found while using these was that some of the syntax for these commands was not straightforward so I decided to let others know what I had found.

In each of the next sections when I use the term <D365Url> this is the base URL you use to access your D365FO instance so for example: https://fp-d365fo.cloudax.dynamics.com. This will help simply some of the URL paths we are going to use.

How to view data entities?

While you can view data entities within the AOT, another easy way to view them is from an Internet browser by visiting <D365Url>/data where you will find a list of every data entity within an environment. Results for this metadata call will be returned in XML format.

You can access any of these by adding the data entity name to the end of your URL, for example to return users you would use <D365Url>/data/SystemUsers. Results for the actual data entity are returned in JSON format.

Cross Company

By default data entities will return results within the DAT company, for entities that are non-company specific (for example any entity that are system based like SystemUsers) this is not an issue.

For entities that are on a per company basis (for example Customers/Vendors) if you want to return all results across all legal entities you can add cross-company=true to the query

Key Values

Every data entity within D365FO has what are called ‘key values’, they are the set of data that is unique to the object and can be used to retrieve a single record. This can be found on the data entity record in the AOT under Keys -> Entity Key.

For entities that are non-company specific, this can be a single value, for entities that are company specific the company Id is also needed.

For example the key value of a System User is the UserId, so to query for a particular user we can do something like this:

<D365Url>/data/SystemUsers(UserId='Alicia')

For the Vendor data entity it is the VendorAccountNumber, but since vendors are company specific we need to add the company Id to this as well:

<D365Url>/data/Vendors(dataAreaId='ussi',VendorAccountNumber='US_SI_000006')?cross-company=true

Filtering

You can also add additional filtering to your data entity calls beyond what Key Values are configured by using the filtering functionality built in to OData. There are all sorts of commands and I’ve found this reference guide to be extremely helpful: OData Uri Conventions

A couple things to keep in mind:

• If you have multiple OData commands in a query, they are always separated by the character ‘&’ and an OData operator is always prefaced by the character ‘$’
• Spaces are not valid URL characters but are required for some OData commands, any spaces in a URL will be automatically encoded once submitted so your URL may change slightly which is expected

Earlier we showed how you can query for a particular vendor by using the key values:

<D365Url>/data/Vendors(dataAreaId='ussi',VendorAccountNumber='US_SI_000006')?cross-company=true

This can also be written as a query filter by changing the route slightly:

<D365Url>/data/Vendors?cross-company=true&$filter=dataAreaId eq 'ussi' and VendorAccountNumber eq 'US_SI_000006'

Both of the above queries return the exact same result.

You will notice that after you submit the URL with the query filter in it that the URL changes slightly, this is because of the spaces in the URL not being valid characters so they are encoded to ‘%20’ so your URL changes to this:

<D365Url>/data/Vendors?cross-company=true&$filter=dataAreaId%20eq%20'ussi'%20and%20VendorAccountNumber%20eq%20'US_SI_000006'

You can find out more about URL encodings here: HTML URL Encoding Reference

The nice thing about using the filter command on data entities is that you are not restricted to just the key values, you can use any parameter on the object. For example, if you wanted to find all vendors in the USSI company that have a Vendor Group Id of 20 you could do the following:

<D365Url>/data/Vendors?cross-company=true&$filter=dataAreaId eq 'ussi' and VendorGroupId eq '20'

Paging

By default, data entities return the top 10,000 results. If there are more results in the data entity you will have to use paging to ensure all results are returned. You can do this by employing the ‘skip’ and ‘top’ commands.

Your first call to the data entity would only include the top command which ensures only a certain number of results are returned. Then all subsequent calls would use the skip command to offset the data returned and return the next batch. From some testing, I’ve found that 1000 results seems to be the most stable when returning large result sets. Below is some psudo-code on how to do this via .NET:

using (var client = new HttpClient())
{
    int batch = 1;
    int batchSize = 1000;

    var response = await client.GetStringAsync($"data/{dataEntity}?cross-company=true&$top=" + batchSize).ConfigureAwait(false);
    var odata = JsonConvert.DeserializeObject<ODataResponse<T>>(response);
    IEnumerable<T> result = odata.Value;

    while (odata.Value.Count() == 1000)
    {
         response = await client.GetStringAsync($"data/{dataEntity}?cross-company=true&$skip=" + batch * batchSize + "&$top=1000").ConfigureAwait(false);
         odata = JsonConvert.DeserializeObject<ODataResponse<T>>(response);
         result = result.Concat(odata.Value);
         batch++;
     }
     return result;
}

Conclusion

Hopefully this helps take away some of the confusion around data entities and how to access them. As always, if you have any questions about this please feel free to reach out.