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.
One option is to write the console app having the required QueryExpression or FetchXML condition using the Dynamics 365 SDK to get the count.
Or use the Record Counts tool of CDS.Tools
https://xrm.tools/RecordCounts
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.
The result
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
https://nishantrana.me/2020/05/21/setting-up-using-sql-to-query-data-in-dynamics-365-preview/
We can also use the CDS T-SQL endpoint within the SQL 4 CDS Plugin.
https://markcarrington.dev/2020/05/24/sql-4-cds-2-1-0-the-t-sql-edition/
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
https://www.xrmtoolbox.com/plugins/AndyPopkin.RecordCounter/
- or use RetrieveTotalRecordCountRequest
https://dreamingincrm.com/2019/07/22/getting-entity-record-counts/
- or use Count aggregrate function
https://docs.microsoft.com/en-us/previous-versions/dynamicscrm-2016/developers-guide/gg309565(v=crm.8)?redirectedfrom=MSDN#count
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..
Nishant Rana's Weblog
Thank you so much for sharing this article. Will it be possible in FetchXML to check & find only the records that have more than 1 line item in a table for a specific Order ID? For example in SQL we could do something like this:
Order ID = 1, Order Name = Food
Order ID = 1, Order Name = Drink
Order ID =2, Order Name = XYZ
SELECT OrderID, COUNT(OrderID)
FROM Orders
GROUP BY OrderID
HAVING COUNT(OrderID)>1
Order ID=2 should not be returned because it is not referenced more than once in the query result.
How can we achieve the same in FetchXML? Would highly appreciate it if you can provide an example.
LikeLiked by 1 person
Understood your requirement Julien. Interesting one. I think it might be too complex for fetch xml – https://docs.microsoft.com/en-us/powerapps/developer/data-platform/use-fetchxml-aggregation#group-by
Give https://www.xrmtoolbox.com/plugins/MarkMpn.SQL4CDS/ a try here Julien. I’d also check.
LikeLike
Thank you for your prompt reply and for providing the above documentation. Using SQL4CDS tool it is possible to transform SQL statements to a fetch XML query so we can create a complex/advanced system view that we want or maybe alter an existing system view with complex queries?
LikeLike
I found the FetchXML Builder plugin and the FetchXML / View Record Counter plugin extremely helpful in obtaining record counts in Dynamics 365. The FetchXML Builder makes it easy to construct complex queries, and the Record Counter plugin provides a convenient way to execute the query and get the count. It’s a great combination of tools for anyone working with large data migrations.
LikeLike
I have created a browser-based tool for the same. Check it here https://AshishVishwakarma.com/DataverseRowsCounter/ In case if below 50K rows fetchXml aggregate option can is used, for more RetrieveTotalRecordCountis used.
LikeLike