One of the must-have tools for every Power Platform developer is XRMToolBox. Within it is a tool called FetchXML Builder, which lets us connect to our Dataverse environment, browse and query data easily, and export the results in various formats. Don’t let the name ‘FetchXML’ fool you—it does much more than just fetch XML! We can also convert the query output to OData (Web API) format for use with our scripts.
This post is part of my ongoing series on using the Dataverse Web API with PowerShell. Make sure you have read through my post on Obtaining an Access Token first, as you’ll need this in order to perform the Web Requests in the scripts.
Configure XMLToolBox and FetchXRM Builder
I’m going to assume you don’t already have the Application installed. If you have, feel free to skip on ahead.
- Download XRMToolBox and follow the prompts to install
- On the Start Page, go to “Open Tool Library” and search for Fetch XML Builder. Click Install.
- Once installed, back on the tools tab. You should see it listed.
Connect to Dataverse Environment
Connect to an environment by selecting the ‘Microsoft Logon Control’ option. Choose ‘Office 365’ as the deployment type and log in with your O365 username and password. Select the environment you want to connect to, and after testing the connection, you should be successfully connected
Querying with Fetch XML Builder
Select and Filter
I have two custom tables in my Dataverse: ‘Manufacturer’ and ‘Country.’ Many manufacturers can belong to a country (for example, Honda and Toyota are manufacturers from Japan).
Next, let’s query all manufacturers whose country is Japan. To do this, we simply need to filter by the countryid, which is a lookup to the Country table. FetchXML Builder opens a dialog box with the table views to easily select the country record. I’ll also configure the query to return only the manufacturer names by using the select attribute. Finally, click the ‘Execute’ button (or press F5) to run the query.
With our results, all we need to do now is go to convert and select OData (WebAPI). Then, copy the URI by right-clicking and select “Copy URL”.
Now, we can incorporate this into our PowerShell script. For better readability and modularity, I’ve placed Select, Filter, and Top parameters into an ordered dictionary variable called $ODataParams.
$EnvironmentURL = "https://YOURENVIRONMENTHERE.crm.dynamics.com"L
$CountryLookUpValue = "9f84eede-9212-f011-998a-002248096cf4" # This is the guid for Japan
$Header = @{
"Authorization" = "$($authResponseObject.token_type) $($authResponseObject.access_token)"
"Accept" = "application/json"
"OData-MaxVersion" = "4.0"
"OData-Version" = "4.0"
"Content-Type" = "application/json"
"Prefer" = "return=representation"
}
$ODataParams = [ordered]@{
select = "spdy_manufacturername"
filter = "(_spdy_country_value eq $CountryLookUpValue) and (spdy_Country/spdy_countryid ne null)"
top = "50"
}
$URI = "$($EnvironmentURL)/api/data/v9.2/spdy_manufacturers?`$select=$($ODataParams.select)&`$filter=$($ODataParams.filter)&`$top=$($ODataParams.top)"
$Response = (Invoke-RestMethod -Uri $URI -Method Get -Headers $Header).value
$Response
Link Related Entity
Building on the previous example, let’s say we also want to include the actual country name in the results. We’ll use the link-entity to join the tables (think: SQL join). The relationship dropdown under link-entity makes it easy to view all the Many-to-One and One-to-Many relationships. After selecting the relationship, the ‘Entity Name’, ‘From’, and ‘To’ fields will automatically populate.
You may also notice I aliased the table as country under the “Alias” property. It’s best to alias your linked entities, as it ensures your columns will have unique names, otherwise you may get unexpected results.
The PowerShell code will need some tweaks to include the $expand parameter usage in the URI. Further, to fetch just the Country Name in our response, I’ll use a calculated property to access the pscustomobject and extract the country name:
$EnvironmentURL = "https://YOURENVIRONMENTHERE.crm.dynamics.com"L
$CountryLookUpValue = "9f84eede-9212-f011-998a-002248096cf4" # This is the guid for Japan
$Header = @{
"Authorization" = "$($authResponseObject.token_type) $($authResponseObject.access_token)"
"Accept" = "application/json"
"OData-MaxVersion" = "4.0"
"OData-Version" = "4.0"
"Content-Type" = "application/json"
"Prefer" = "return=representation"
}
$ODataParams = [ordered]@{
select = "spdy_manufacturername"
filter = "(_spdy_country_value eq $CountryLookUpValue) and (spdy_Country/spdy_countryid ne null)"
expand = "spdy_Country(`$select=spdy_name)"
top = "50"
}
$URI = "$($EnvironmentURL)/api/data/v9.2/spdy_manufacturers?`$select=$($ODataParams.select)&`$expand=$($ODataParams.expand)&`$filter=$($ODataParams.filter)&`$top=$($ODataParams.top)"
$Response = (Invoke-RestMethod -Uri $URI -Method Get -Headers $Header).value
$Response | Select-Object @{l='Manufacturer';e={$_.spdy_manufacturername}}, @{l='Country';e={$_.spdy_Country.spdy_name}}
Group by and Aggregate
Let’s try retrieving a list of all countries and counting the number of manufacturers by country. Make sure the ‘Aggregate’ option is selected! We’ll start with the Country table, link it to the Manufacturer table, group by country name, and then apply a count aggregation on the manufacturer name
Alias your columns when using group by and ensure you group all columns except for the one you want to aggregate!
In our PowerShell script, we would need to use a calculated property to determine the count of manufacturers per country by measuring the length of the pscustomobject array. Without this, the OData query returns an array of manufacturers associated with the country, which isn’t what we want — we only want the count.
$EnvironmentURL = "https://YOURENVIRONMENTHERE.crm.dynamics.com"
$Header = @{
"Authorization" = "$($authResponseObject.token_type) $($authResponseObject.access_token)"
"Accept" = "application/json"
"OData-MaxVersion" = "4.0"
"OData-Version" = "4.0"
"Content-Type" = "application/json"
"Prefer" = "return=representation"
}
$ODataParams = [ordered]@{
select = "spdy_name"
filter = "(spdy_manufacturer_Country_spdy_country/any(o1:(o1/spdy_manufacturerid ne null)))"
expand = "spdy_manufacturer_Country_spdy_country(`$select=spdy_manufacturername)"
top = "50"
}
$URI = "$($EnvironmentURL)/api/data/v9.2/spdy_countries?`$select=$($ODataParams.select)&`$expand=$($ODataParams.expand)&`$filter=$($ODataParams.filter)&`$top=$($ODataParams.top)"
$Response = (Invoke-RestMethod -Uri $URI -Method Get -Headers $Header).value
$Response | Select-Object @{l='CountryName';e={$_.spdy_name}}, @{l='ManfacturerCount';e={$($_.spdy_manufacturer_Country_spdy_country).Length}}
And that’s about it! As you can see, the FetchXML tool is really powerful to construct queries quickly for all kinds of use-cases.
