Tuesday, March 16, 2010

FetchXml and the Paging Cookie

So the other day I was trying to extract some data from a CRM live instance. I needed to get more than 5000 records and I was trying to query a many to many relationship table. So I had to use fetchXml as the QueryExpression class can't query a many to many table. To get more than 5000 records I had to enable paging via the paging cookie. Here's how it's done:



// Build fetchXml string with the placeholders.
String fetchXml = @"<fetch version='1.0' mapping='logical'
page='{0}' count='100' paging-cookie='{1}'>
<entity name='neu_neu_settlement_payees'>
<attribute name='neu_neu_settlement_payeesid' />
<attribute name='neu_settlementid' />
<attribute name='contactid' />
</entity>
</fetch>";

fetchXml = String.Format(fetchXml, pageNumber, pagingCookie);

// Excute the fetch query and get the xml result.
String fetchResult = crmService.Fetch(fetchXml);
// Load the fetch result into XMLDocument to parse its cotents.
XmlDocument doc = new XmlDocument();
doc.LoadXml(fetchResult);
// The morerecords attribute will return 1 if there are more records.
String moreRecords = doc.DocumentElement.Attributes["morerecords"].InnerText;

// The paging-cookie attribute holds the paging cookie to pass in the next query.
XmlAttribute at = doc.DocumentElement.Attributes["paging-cookie"];
pagingCookie = (at != null) ? at.InnerXml : string.Empty;

// Retrieve the result nodes.
XmlNodeList resultNodes = doc.DocumentElement.SelectNodes("result");

// Check for morerecords, if it returns 1.
if (moreRecords != null && moreRecords == "1")
{
// Increment the page number to retrieve the next page.
pageNumber++;
}
else
{
// If no more records in the result nodes, exit the loop.
break;
}

The big stumbling block for me was using .InnerXml to get the pagingCookie value; not .InnerText. This code all sits in a while(true) loop.

Have fun...

Java and Regular Expressions

So the other day I was working away with some open source bits and was needing to do some string parsing. I am a hugh fan of regular expressions. They are vary efficient; and once understood, make it vary easy to perform complex string parsing with very few lines of code. I was trying to perform matching on special regular expressions characters; namely '?.' I was trying to escape the ? with black slashes. In the java world you have to use two black slashes for every one that you want. So '\\' turns into '\' and '\\\\' translate into '\\.' After spending a while trying to escape the ? I found a very simple solution. Just add the "Pattern.LITERAL" flag to the Pattern constructor. Here's an example:


Pattern pattern = Pattern.compile(name,Pattern.CASE_INSENSITIVE | Pattern.LITERAL);



Happy coding...

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

Monday, March 1, 2010

Hide left hand nav links

Hello Gang,

This post is about hiding left hand nav links. Every once in a while I get asked to hide certain navigation links based on user interaction or values on a form. Here's the process:

  1. Identify the DOM element that we need to hide
  2. Create function to toggle DOM element
  3. Fire function when the form loads
To find the element id that we need to hide you can do one of two things:
  1. Find the relationship name in the relationship section of the customizations editor. You will have to prefix "nav_" to this relationship name
  2. Open the form in question, press Ctrl-n, and dig around in the source until you find the appropriate DOM element id.
Here's the code:



crmForm.Neudesic_HideNavObligor = function() {

/*get value of checkbox*/
var value = crmForm.all.neu_obligor.DataValue;
var display = "none";
if(value) {
display = "block";
}
try {
/*this will fail on preview as the left hand menu is not generated*/
var element = document.getElementById("nav_neu_obligor_neu_settlement");
element.style.display = display;
}catch(error) {

}
}
/*attach event*/
if(crmForm.FormType == 1 || crmForm.FormType == 2) {

crmForm.all.neu_obligor.attachEvent("onclick", crmForm.Neudesic_HideNavObligor);
/*fire event when the form opens*/
crmForm.Neudesic_HideNavObligor();
}