You’re offline. This is a read only version of the page.
Hi, I am new to Power Platform. I have an entity with over 30,000 records and I need a way to find the total number of items in that entity. I tried the following methods:
- using PowerApps: create a canvas app and using the CountRows function, I keep getting 'data returned by service was invalid' error.
- using PowerAutomate: read all items in entities and use Length() function to calculate the number of items. since get items return a maximum of 5000 records, the total is always 5000.
- using the 'Edit data in Excel' feature of CDS: the spreadsheet always return a maximum of 17,0000 records.
- using a 'model-driven app': using the 'export to excel' feature from the app will return all my records
so the only reliable method to get total number of items is appear to be exporting a list of all items from a Model-driven app, is there a more efficient way to to do this? ideally, I need this from a Power App or a Power Automate to make business decisions.
Thanks
That's works. thanks
Ignoring Eric's attempts
To get the total number of records in an entity you can use RetrieveTotalRecordCount
https://crmtipoftheday.com/1375/get-record-count-for-entities/
If you want to know the number of filtered records in an entity you can use ODATA aggregation see http://docs.oasis-open.org/odata/odata-data-aggregation-ext/v4.0/cs01/odata-data-aggregation-ext-v4.0-cs01.html#_Toc378326295
Share
Report
15
All responses (
Answers (https://{{my_cool_org}}.crm.dynamics.com/api/data/v9.1/{{my_cool_entity}}?$filter=modifiedon gt 2020-07-31T00:05:00-07:00&$apply=aggregate($count as Count)
note the $apply=aggregate($count as Count) bit at the end as that's the important bit
The result will look like this
{
"@odata.context": "https://{{my_cool_org}}.crm.dynamics.com/api/data/v9.1/$metadata#{{my_cool_entity}}",
"value": [
{
"Count": 49689
}
]
}
2 things to note though:
1) aggregation starts to fail once you have 50,000 records
2) the result may not be 100% accurate as caching can occur.
You can use a fetchXml query to get this - see below for one to count the contacts. You can use a tool like Xrm Toolbox to run the query, or create a Reporting Services report to run the query
<fetch distinct='false' mapping='logical' aggregate='true'> <entity name='contact'> <attribute name='contactid' alias='contact_count' aggregate='count'/> </entity> </fetch>
You can also get the count via the SDK with Query Expression or Fetch XML by using the paging cookie. Here's more info:
Hi @Matt-Majedi,
In Power Automate, I never tried but you can try to increase the default size of the List records action (steps below), but not sure it will work because the max record count in CDS Web API is 5000.
If that doesn't work then the method to retrieve all your records is checking if @odata.nextLink property is returned and if so, get the Skip token from the @odata.nextLink and pass it to the List Records action. Here's the pseudo flow to get you going
More info on paging: https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/webapi/query-data-web-api#limits-on-number-of-entities-returned
Hope this helps...
