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:
Retrieve Entity Records
Now let’s retrieve a specific entity, accounts. This displays a list of accounts in the system:
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 Name||Web API Field Name||Field Type|
|Name||name||Single Line of Text (Text)|
|Phone||telephone1||Single Line of Text (Phone)|
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:
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:
This retrieves only 10 rows:
Note how we add & to the end of $select. This query returns 10 accounts.
We can also add $orderby with asc (ascending) or desc (descending) to order the results:
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:
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