Skip to main content

Notifications

Community site session details

Community site session details

Session Id : VhqA5coCHI9SpGWQfqXBRB
Power Apps - Microsoft Dataverse
Answered

number of items in a Common Data Service entity

Like (0) ShareShare
ReportReport
Posted on 7 Sep 2020 09:05:02 by 15

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

  • Matt-Majedi Profile Picture
    15 on 07 Sep 2020 at 18:44:25
    Re: number of items in a Common Data Service entity

    That's works. thanks

  • Verified answer
    Ben Thompson Profile Picture
    1,400 on 07 Sep 2020 at 12:22:06
    Re: number of items in a Common Data Service entity

    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.

     

  • David Jennaway Profile Picture
    716 on 07 Sep 2020 at 11:21:32
    Re: number of items in a Common Data Service entity

    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>

  • EricRegnier Profile Picture
    8,714 Most Valuable Professional on 07 Sep 2020 at 11:21:11
    Re: number of items in a Common Data Service entity

    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

  • Verified answer
    EricRegnier Profile Picture
    8,714 Most Valuable Professional on 07 Sep 2020 at 11:17:46
    Re: number of items in a Common Data Service entity

    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. 

    1. Click on the ellipses then Settings
      2020-09-07_22-45-03.png
    2. Enable Pagination and set the Threshold. The max is 100,0002020-09-07_22-44-25.png

     

    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

    2020-09-07_23-13-43.png

     

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

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Understanding Microsoft Agents - Introductory Session

Confused about how agents work across the Microsoft ecosystem? Register today!

Warren Belz – Community Spotlight

We are honored to recognize Warren Belz as our May 2025 Community…

Congratulations to the April Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard > Power Apps - Microsoft Dataverse

#1
stampcoin Profile Picture

stampcoin 15

#2
ankit_singhal Profile Picture

ankit_singhal 11 Super User 2025 Season 1

#3
mmbr1606 Profile Picture

mmbr1606 9 Super User 2025 Season 1

Overall leaderboard

Featured topics

Loading started