Count records in a table

 

It’s been a known issue since forever that you don’t know how many rows (records) there is in a table (entity) once the count is larger than 5000 since Microsoft in it’s infinite wisdom chose not to implement the counting of rows larger than 5000.

The usual solution from way back was to push the number of rows exported to Excel to half a million or so and then export the relevant records from advanced find and see how many rows were present in the excel file.

Jump to 2021, or maybe 2020 I’m not really sure. In the Webapi there is now a function that, supposedly, return the recordcount, note that it doesn’t return the number of rows.

https://docs.microsoft.com/en-us/dynamics365/customer-engagement/web-api/retrievetotalrecordcount?view=dynamics-ce-odata-9

That’s the docs page of this function and you pretty much do a call like “https://contosocrm.crm.dynamics.com/data/api/v9.1/RetrieveTotalRecordCount(EntityNames=['account'])” if you want to see the number of rows in the account table.

According to the documentation, this is supposed to give you the number of rows “from a snapshot within last 24 hours.”. This is maybe the only issue, but probably not. In quite a lot of cases I’ve seen the data is quite static but the return number if WAY off.

This is a shame, really, since the function is really nice to have and very practical since you don’t have to do something else like doing some magic in PowerBI, creating you own console app or using another tool like https://www.xrmtoolbox.com/plugins/Fic.XTB.FastRecordCounter/.

I wonder why the chosen implantation is set to work “on a snapshot from within last 24 hours” when you can use the xrmtoolbox tool to count the live data, or PowerBI even if will chew away at your entitled precious API calls. Too bad it isn’t implemented in a way that is usable for real.
I'm not even sure that the snapshot-part is the issue but I tested this on a customer system that's about to go live to see how the migration was going and the answer for account was 65k and when a colleague did the check using PowerBI the amount was 105k so I wonder what user scenarios this can be usable at all in.

 

Rickard Norström
Developer at CRM-Konsulterna
www.crmkonsulterna.se