Answered

number of items in a Common Data Service entity

(0) Share

Report

Posted on by Matt-Majedi

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

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (5)

Answers (2)

Verified answer

EricRegnier 8,720 Most Valuable Professional on at

Like (1)

Report
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.
Click on the ellipses then Settings
Enable Pagination and set the Threshold. The max is 100,000

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...

Was this reply helpful? Yes No
EricRegnier 8,720 Most Valuable Professional on at

Like (0)

Report

You can also get the count via the SDK with Query Expression or Fetch XML by using the paging cookie. Here's more info:
https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/org-service/page-large-result-sets-with-queryexpression
https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/org-service/page-large-result-sets-with-fetchxml

Was this reply helpful? Yes No
David Jennaway 716 on at

Like (0)

Report

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>

Was this reply helpful? Yes No
Verified answer

Ben Thompson 1,400 on at

Like (1)

Report
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

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.

Was this reply helpful? Yes No
Matt-Majedi 15 on at

Like (0)

Report

That's works. thanks

Was this reply helpful? Yes No