Tuesday, March 2, 2010

Retrieving the total number of records for a given object

I'm currently working on a CRM 4.0 live deployment. I needed to figure out how many of a given object existed in the CRM database. On an on-premise version of CRM this is a simple problem to solve. You can use SSRS or just query the database directly. In CRM 4.0 live this isn't so easy. Query Expression will only return 5000 results with a single query. I had to use fetch xml with the 'aggregate' attribute. Here's the needed fetch xml:

String fetchXml = @"<fetch version='1.0' output-format='xml-platform'
mapping='logical' aggregate='true'>
<entity name='@ENTITY_NAME'>
<attribute name='@KEY' aggregate='count' alias='total' />
@FILTER
</entity>
</fetch>";

Here's the code:
/*Loop through objects and retreive totals*/
foreach (String entityName in objects)
{
System.Console.Write("Retreiving " + entityName);
String fetchXml = @"<fetch version='1.0' output-format='xml-platform'
mapping='logical' aggregate='true'>
<entity name='@ENTITY_NAME'>
<attribute name='@KEY' aggregate='count' alias='total' />
@FILTER
</entity>
</fetch>";

fetchXml = fetchXml.Replace("@ENTITY_NAME", entityName);
String filter = String.Empty;
if (entityName.Equals("task"))
{
fetchXml = fetchXml.Replace("@KEY", "activityid");
}
else
{
fetchXml = fetchXml.Replace("@KEY", entityName + "id");
}
if (entityName.Equals("customeraddress"))
{
filter = "<filter type='and'><condition
attribute='name' operator='eq'
value='Historical'/></filter>";
}

fetchXml = fetchXml.Replace("@FILTER", filter);

String xml = String.Empty;

xml = crmService.Fetch(fetchXml);
XmlDocument xmlDoc = new XmlDocument();
xmlDoc.LoadXml(xml);
//Retrieve count
int total = Convert.ToInt32(xmlDoc.GetElementsByTagName("total")[0].InnerText);
System.Console.Write(": total: " + total + "\n");
}

See this Microsoft link for more information.

Bits, bits, they encircle and enslave... I can't escape!

C

No comments:

Post a Comment