Let us take a scenario, where a data migration package is running, and it either creates or updates (or deletes) a large number of records into Dynamics 365, and we want to get the count of records created/updated/deleted in the last x hour or so.
With views, we are limited to just 5000 records.
Or use the Record Counts tool of CDS.Tools
We can also make use of SSRS reports here.
Create a report using the report wizard, specify the criteria
In the LayOut fields window, specify Count as the summary type for grouping.
Run the report to get the count.
Another option that we have used the most is to use the FetchXML
Builder plugin to build the query, copy it.
And use it in the FetchXML / View Record Counter plugin of XrmToolBox.
Select the entity, contact, in this case, paste the FetchXML query and click Execute Count.
We will get the count.
Along with FetchXML / View Record Counter we can also use
SQL 4 CDS plugin.
Within the FetchXML builder click on Edit in SQL 4 CDS button.
And now with CDS T-SQL endpoint (preview), we can use SQL Server Management Studio as well to directly write the T-SQL instead of fetch xml
We can also use the CDS T-SQL endpoint within the SQL 4 CDS Plugin.
What if we want total record count for an Entity?
Apart from all the methods above,
- we can use Record Counter XrmToolBox Plugin for that
- or use RetrieveTotalRecordCountRequest
- or use Count aggregrate function
however, it will fail in case of more than 50000 records
‘AggregateQueryRecordLimit exceeded. Cannot perform this operation.’
To increase the limit (on-premise)- https://nishantrana.me/2012/09/06/aggregatequeryrecordlimit-exceeded-cannot-perform-this-operation/
- Will returntotalrecordcount property of FetchXML help ?
It will be helpful if total records are less than 5000.
Hope it helps..