Did you know that the views you and your users define in Microsoft Power Apps (including Microsoft Dynamics) can also be used from the Dataverse Web API?
The syntax is slightly different depending on whether the view is a system view (a.k.a. a public view; typically defined via a development tool such as make.powerapps.com or provided in a solution) or a user view (created by an end user—for example, in a model-drive app, by using the “Create view” menu option when viewing a grid page).
Get Its GUID
To use either type of view from the Dataverse Web API, you’ll need its GUID. You can query this out of entity savedquery
(for system views) or userquery
(for user views).
GET {{webApiUrl}}savedqueries?$select=savedqueryid&$filter=name eq 'My Active Contacts'
(include the "always include" headers)
GET {{webApiUrl}}userqueries?$select=userqueryid&$filter=name eq 'My Active Contacts w/Associated Account'
(include the "always include" headers)
Alternately, if you’re in a model-drive app, looking at a grid with the view of interest selected, the view’s GUID may be in the page’s URL.
Apply the View
To apply the view, use the savedQuery
(for system/public views) or userQuery
(for user views) query option. Set this option’s value to the view’s GUID and append it to a URL that references an entity collection of the appropriate type (e.g. if the view is for contacts, the entity collection should be a collection of contacts). Note that, unlike most query options, these option names do not start with a dollar sign.
GET {{webApiUrl}}contacts?savedQuery=00000000-0000-0000-00aa-000010001004
(include the "always include" headers)
GET {{webApiUrl}}contacts?userQuery=5165c88f-33a1-eb11-b1ac-000d3a3b7f8d
(include the "always include" headers)
And there you have it—at least, the basics of applying a view via the Dataverse Web API!
But there’s more….
Any Collection, Not Just Base
A view can be applied to any collection of the appropriate entity type, not just to the entity’s base table (a.k.a. main entity set).
Above, two views defined for contacts were directly applied to the contacts table. However, these views can also be applied to other collections of contacts—such as the below collection-valued relationship property on account:
GET {{webApiUrl}}accounts/my_associated_contacts?savedQuery=5165c88f-33a1-eb11-b1ac-000d3a3b7f8d
(include the "always include" headers)
Building Blocks
An applied view is just a starting point.
Additional filtering can be added using $filter
. The quantity of rows returned can be controlled via $top
.
Several other familiar query options may be available, depending on the particulars of the view (like whether or not it aggregates—more query options are available with non-aggregating views):
$select
$expand
—but only on the many side of a many-to-one relationship (expanded results will be returned in flattened format, like you’d receive back from a Fetch XML join, vs. in a nested JSON object).$orderby
$count
$filter
and (if available) $select
, $expand
and $count
will be applied on top of the referenced view. A $filter
will add additional filtering to the view’s already-defined filters (vs. replacing the existing filtering defined in the view). Similarly, $select
and $expand
can add additional columns to the returned dataset, but can’t be used to keep the columns already specified in the view from being returned. $count=true
can be added to return a count but $count=false
won’t remove the count if the view already specifies that it should be included.
On the other hand, $top
and $orderby
override what is defined in the view. If the view is defined to return the top 2 records but your query contains $top=3
, three records (if that many are available) will be returned. Similarly, if the view orders by column A but the request specifies $orderby=my_b
, the $orderby
in the request, not the ordering defined in the view, will be followed.
The View Advantage
The ability to use views like this allows your API calls to pull the same that a user in Power Apps/Dynamics would see without the API client needing to know the particulars of the relevant filters, joins, etc.. A user in Power Apps/Dynamics and a user of your API client can see the same data fetched using the same criteria with the query used defined in just one place. This DRYness is good!
Suppose your client returns a list of top purchasing customers. Business just changed the definition for top purchasers from customers with 3+ orders to those with 5+ orders. If your API client has a hard-coded “top purchasers” request with 3+ written into its filter criteria, you’ll need to update your client to align it with this business change. However, if instead your client uses a Dataverse view to fetch this data, all that’s needed is for that view to be updated in Power Apps. Once that happens, your API client will automatically start fetching data matching the revised filtering criteria. No need to release a new version of your client!
Above, not only did a Dataverse view eliminate duplication of logic, it also helped maintain a separation of concerns—separating the business criteria defining the dataset (specified in the view) from the technical implementation of fetching data (e.g. the code in your API client). Using views to achieve a separation of concerns can be valuable even if the view will only ever be used from the API (never directly in the Power Apps/Dynamics UI).
Note
Don’t forget to include the “always include” headers with every request sent to the Web API.