I want to get a list of distinct countries with a Web API call from a Dataverse table with 60.000+ records for the purposes of populating a <select>
with options on a web page.
In a production environment, I would use a Fetch API request to a Dataverse table with a JWT token obtained with Microsoft Authentication Library MSAL. I will show examples of this implementation in as series of posts Power Pages SPA later here.
In this post I show examples which can be easily be tested in a browser by pasting the queries in the URL bar, provided that you have a dataverse environment and tables with data.
Example values
Environment URL: https://your-env.api.crm4.dynamics.com/
Table name: lfa1
with 60.000+ records
Field name: countryname
Web API aggregate query
To get a list of values I could make this aggregate query:
https://your-env.api.crm4.dynamics.com/api/data/v9.2/lfa1s?$apply=groupby((countryname))&$count=true
But it fails since the aggregate functions are limited to a collection of 50,000 records.
Result:
{
"error": {
"code": "0x8004e023",
"message": "AggregateQueryRecordLimit exceeded. Cannot perform this operation."
}
}
I will try fetchXml query instead.
Web API fetchXml query
https://your-env.api.crm4.dynamics.com/api/data/v9.2/lfa1s?fetchXml=<fetch distinct='true' returntotalrecordcount='true'><entity name='lfa1'><attribute name='countryname'/></entity></fetch>
Result:
{
"@odata.context": "https://your-env.api.crm4.dynamics.com/api/data/v9.2/$metadata#lfa1s(countryname)",
"@odata.count": 111,
"value": [
{
"countryname": "Puerto Rico"
},
{
"countryname": "South Korea"
},
{
"countryname": "Bahrain"
},
{
"countryname": "Vietnam"
},
{
"countryname": "USA"
},
<other countries removed>
]
}
These values can now be used to populate <select>
with options.
Top comments (0)