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

Leave a comment

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.

Dynamics 365 Field NameWeb API Field NameField Type
NamenameSingle Line of Text (Text)
Phonetelephone1Single Line of Text (Phone)
Annual RevenuerevenueCurrency
IndustryindustrycodeOption Set
Currency_transactioncurrencyid_valueLookup

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

 

THANKS FOR READING. BEFORE YOU LEAVE, I NEED YOUR HELP.
 

I AM SPENDING MORE TIME THESE DAYS CREATING YOUTUBE VIDEOS TO HELP PEOPLE LEARN THE MICROSOFT POWER PLATFORM.

IF YOU WOULD LIKE TO SEE HOW I BUILD APPS, OR FIND SOMETHING USEFUL READING MY BLOG, I WOULD REALLY APPRECIATE YOU SUBSCRIBING TO MY YOUTUBE CHANNEL.

THANK YOU, AND LET'S KEEP LEARNING TOGETHER.

CARL

https://www.youtube.com/carldesouza

 

ABOUT CARL DE SOUZA

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

carldesouza.comLinkedIn Twitter | YouTube

 

Leave a Reply

Your email address will not be published. Required fields are marked *