Dataverse: get distinct values with Web API

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.