Dynamics 365 Web API Retrieve, Select, Filter, Order By and Top

In this post, we will look at how to use the Dynamics 365 Power Apps Web API to retrieve data.

Find the Web API URL

First, find the Web Api URL. You can get this by going to your org and going to Settings->Customizations->Developer Resources. It is under Service Root URL:

Note you will have a different URL, which you can swap with my URL below.

Now if we go to this URL in a web browser, we can see the Web API is returning data:

https://crm374549.api.crm.dynamics.com/api/data/v9.1/

Retrieve Entity Records

Now let’s retrieve a specific entity, accounts. This displays a list of accounts in the system:

https://crm374549.api.crm.dynamics.com/api/data/v9.1/accounts

NOTE: There is a limit of 5000 records returned in each call. If there are over 5000 records, a paging link will provide the next 5000 records. This is also returning all fields of each account.

Retrieve Selected Fields

Now let’s say instead of returning every field, we want to limit the fields returned. We may do this because we don’t need all of the fields within the account entity and limiting fields returned will be faster, therefore improving performance. We can do this by using a $select statement in the URL, then add each field we want to select after the = sign. 

Let’s say we want to select the fields from the table below.

Note the field types as well, we will select a variety of field types, including text fields, currency, option set and lookup. We can get these fields by selecting all fields using the Web API. Note the Currency lookup field is requested by modifying its original name of transactioncurrencyid to _transactioncurrencyid_value.

The URL with the $select is now. Note the ? after accounts. We need this after the entity name if we are performing additional filtering:

https://crm374549.api.crm.dynamics.com/api/data/v9.1/accounts?$select=name,telephone1,revenue,industrycode,_transactioncurrencyid_value

We can see we are now returning only the selected fields:

The Id of the record is returned as well. Note the industrycode is returned as the option set value not the label, and the _transactioncurrencyid_value returns it’s Id, not the label. We will come back to how to retrieve the labels in another post.

Top N Rows

Next, let’s say we want to return only the top number of rows to our query. We can add $top. For example:

https://crm374549.api.crm.dynamics.com/api/data/v9.1/accounts?$select=name,telephone1,revenue,industrycode,_transactioncurrencyid_value&$top=10

This retrieves only 10 rows:

Note how we add & to the end of $select. This query returns 10 accounts.

Order By

We can also add $orderby with asc (ascending) or desc (descending) to order the results:

https://crm374549.api.crm.dynamics.com/api/data/v9.1/accounts?$select=name&$top=10&$orderby=name%20desc

Filter

Another useful operation is $filter. Let’s say we want to limit the data returned to only show accounts where the revenue is greater than $1 million. We can do this with the query. Again, we divide each operator with the & symbol. This produces:

https://crm374549.api.crm.dynamics.com/api/data/v9.1/accounts?$select=name,telephone1,revenue,industrycode,_transactioncurrencyid_value&$top=10&$orderby=name%20asc&$filter=revenue%20gt%2010000000

TIP: A list of operators additional can be found at https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/webapi/query-data-web-api

ABOUT CARL DE SOUZA

Carl de Souza is a developer and architect focusing on Microsoft Dynamics 365, Power BI, Azure, and AI.

carldesouza.com | LinkedIn | Twitter | YouTube

Facebook

Twitter

Linkedin