Archive

Posts Tagged ‘FetchXML’

Using ‘No Lock’ in FetchXML CRM

October 16, 2014 Leave a comment

In one of our requirement we are retrieving records using FetchXML and we observed fetch taking long time when there are more concurrent user access.

Reason:

  • Records are being updated by other users when I perform Fetch

Fix :

  • Set “no-lock=’true'” in the FetxhXML as below

<fetch mapping=’logical’ no-lock=’true’>

</fetch>

🙂

Categories: CRM Tags: , ,

Aggregate Query Record Limit Exceeded – Exception in CRM

October 16, 2014 Leave a comment

I was getting below exception when using FetchXML aggregation to compute Average of one of my entities field.

AggregateQueryRecordLimit exceeded.  Cannot perform this operation.

My Fetch XML :

<fetch distinct=’false’ mapping=’logical’ aggregate=’true’>

<entity name=’new-expenses’>

<attribute name=’new_gross’ alias=’gross_avg’ aggregate=’avg’ />

</entity>

</fetch>”;

Reason :

  • FetchXML Aggregation has a limitation, by default its limited to performing aggregate queries on less than 50,000 records.

Fix :

  • We cannot change this limit on CRM online
  • On onpremise, we can increase the limit by changing ‘AggregateQueryRecordLimit’ column value of ‘MSCRM_CONFIG.Deployment Properties’ table.
  • Refer this link on how to change.

Aggregation using Fetch XML

One of the major differentiator between Query Expression Vs Fetch Xml is that you can perform Aggregation on result set using Fetch XML.

The following aggregate functions are supported:

  • sum
  • avg
  • min
  • max
  • count(*)
  • count(attribute name)

Refer this link

🙂

Retrieve records with Fetchxml using Jscript – CRM 2011

June 20, 2013 1 comment

Retrieve the records using FetchXML is a convenient way as we can form the FetchXML easily using the ‘Advanced Find’ .

Download Fetch XML

Download Fetch XML

Once you have the FetchXML ready, below is the JScript to execute and read the result set.

  • In this sample, I am fetching records from custom entity “Bikes” which has relationship with ‘Contact’ entity. (i.e., Fetch all the bikes of a Contact whose full name contains ‘Raj’)
  • Note – I am using “xrmservicetoolkit” helper Jscript which is available in CodePlex. You just need to download the Jscript and refer to your CRM form.

function getBikesByContactName() {

var fetchXml =

“<fetch mapping=’logical’>” +

“<entity name=’raj_bike’>” +

“<attribute name=’raj_name’ />” +

“<attribute name=’createdon’ />” +

“<link-entity name=’contact’ from=’contactid’ to=’raj_customerid’ alias=’ad‘>” +

“<attribute name=’fullname’ />” +

“<filter type=’and’>” +

“<condition attribute=’fullname’ operator=’like’ value=’%raj%’ />” +

“</filter>” +

“</link-entity>” +

“</entity>” +

“</fetch>”;

// Execute the fetch

var bikes = XrmServiceToolkit.Soap.Fetch(fetchXml);

// Get the results by loop through ‘bikes’

for (var indxBikes = 0; indxBikes < bikes.length; indxBikes++) {

alert(“Bike Name – “+bikes[indxBikes].attributes.raj_name.value);

alert(“Created On – “+bikes[indxBikes].attributes.createdon.value);

// Get the related entity (i.e., Contact) attributes using LinkEntity alias)

alert(“Related entity attribute (Contact Name) – ” + bikes[indxBikes].attributes.ad.fullname.value);

}

}

🙂