Fetch all records

Have you ever tried to write a code which will get you all records from a specific entity? It's harder then you think it is! Everybody who is a bit aware of the CRM SDK thinks it should be a fetch statement like this:

<fetch mapping='logical'><entity name='account'><attribute name='accountid'/></entity>

WRONG!
This would only give you the first 5000 records in the database! It is written down in the SDK with small letters, but it could drive you crazy..

There are two solutions for this issue.
1) Add a registery setting to specify not to implement MaxRowsPerPage
2) Modify the fetch statement and merge several results

Here are the details for each solution
1st solution
Search in the SDK for the word "TurnOffFetchThrottling". You should add this as DWORD registery setting to HKLM\Software\Microsoft\MSCRM. Set the value to 1. You will now not have the 5000 records limit.

2nd solution
Modify your fetch statement to include paging and count numbers. Store all the data in an DataSet and perform that series of code over and over again as long as there is data coming.

Here's the script you should use to get all accountid's (for clarity and the ease of use I have added a function called "FetchDataSet").


private DataSet FetchAllAccountIds(){
int i=1;
bool bFinished = false;
DataSet dsAllData = new DataSet();
while (bFinished == false)
{
StringBuilder sbFetch = new StringBuilder();
sbFetch.AppendFormat("<fetch mapping='logical' page='{0}' count='5000'>", i);
sbFetch.Append("<entity name='account'>");
sbFetch.Append("<attribute name='accountid'/>");
sbFetch.Append("<attribute name='new_12_accountid'/>");
sbFetch.Append("</entity>");
sbFetch.Append("</fetch>");
DataSet dsTempResult = FetchDataSet(sbFetch.ToString());
dsAllData.Merge(dsTempResult);
if (dsTempResult.Tables[0].Rows[0]["morerecords"].ToString() == "0")
{
bFinished = true;
}
else
{
i++;
}
}
return dsAllData;
}

private DataSet FetchDataSet(string fetchXml)
{
string strResult = service.Fetch(fetchXml);
DataSet ds = new DataSet();
System.IO.StringReader reader = new System.IO.StringReader(strResult);
ds.ReadXml(reader);
return ds;
}


I hope this saves you some time!

Thanks to Andrew Krivosheyenko for the Regedit solution!

26 comments:

Andrew Krivosheyenko said...


WRONG!
This would only give you the first 5000 records in the database! It is written down in the SDK with small letters, but it could drive you crazy...

paging are good, but why not just turn off fetch throttling? that will remove the 5000 limit.

Ronald Lemmen said...

Interesting reply. I'll test this tomorrow!

Andrew Krivosheyenko said...

just check SDK chm, "registry settings" section...

Ronald Lemmen said...

You are indeed right. I've updated the blog. Thanks!

Anonymous said...

Hello Ronald

I am new to CRM and I am not getting the meaning for _dataHandler30.What does it mean exactly.

Plz Let me know ASAP.

Regards
Someswar

someswar.m@gmail.com

Ronald Lemmen said...

Hi Someswar,

The _dataHandler30. was a piece of code I forgot to remove. It's updated now and should work.

Kind regards

Anonymous said...

Hello Ronald
I am new to CRM and have a Confusion regarding to create a SalesOrder Programmatically.I tried a lot but not able to get a result.I got solution for another query so far and Thankful to you.
At this moment,I am expecting the same Response from you as early as possible.I need it very urgent.

So Could you please give me solution step by step to create SalesOrder Progrmmatically.

waiting for your kind Response.

Regards
Chanti

Ronald Lemmen said...

Hi Chanti,

The creation of a salesorder is fairly equal to the creation of any other entity. When creating a SalesOrder, then keep in mind that you use the field CustomerId for the relationship with an account.

If this doesn't solve your issue, then either post the error message you're receiving to the newsgroup or mail me the issue: ronaldl at avanade dot com.

Kind regards

Anonymous said...

Hi Ronald,

Thakns for your Response and I have one more confusion.

For Example I am Creating SalesOrder(whatever the entity is),
I want to pass the CustomerID(Or some Parameter which Returns LookUp/PriceList/Guid)then what can i do.

Actually what i am writing is

SalesOrder objSalesorder=new SalesOrder();
so.name="CRM";
so.CustomerId=new Customer();
so.CustomerId.Value=?(How can i get this Value);

I found some code in CRM SDK i.e
so.CustomerId.Value=new Guid("f31bb38a-0ec0-403f-99a6-3af469d7d76f");

what does it mean actually i dont know.And not only for this customerid,but also have Confusion for Return types of Key,Lookup,PriceList(Eg:PriceLevelId,PriceList etc).

So Could You please Send me a Sample Code to create SalesOrder.

Thanks in Advance.

Waiting for your kind Response.

Regards
Chanti

Ronald Lemmen said...

Hi Chanti,

You're heading the good way. For the value of the customerid you should fill in the guid (unique identifier) of the account or contact you want to attach this to. You should have that guid in your code, or you can use the Fetch webmethod to find it. For testing purposes you can just enter a hardcoded guid just like you posted. You should then take the guid of an account/contact from your crm system. Open the account/contact and press ctrl+n and you'll see the guid in the address bar.

Next to providing the value, you should also assign the type. it can be either account or contact. The code for this is: EntityName.contact.ToString();

For more info regarding the other types look at this address:
http://msdn.microsoft.com/library/default.asp? url=/library/en-us/CrmSdk3_0/htm/v3d0microsoftcrmv3d0sdk.asp

Kind regards

Anonymous said...

Hi Ronald,

Thanks for Youe Reply.
I have tried with this code.

salesorder so = new salesorder();

//Set the properties
so.ordernumber = "98489";
so.name = "HP PC";
so.customerid = new Customer();
so.customerid.Value = new Guid("556E49E6-581B-4B61-91E4-6A7E7E8E6A9E");
so.customerid.type = "account";
so.totalamount = new CrmMoney();
so.totalamount.formattedvalue = "$123.00";
so.pricelevelid = new Lookup();
so.pricelevelid.Value = new Guid("f31bb38a-0ec0-403f-99a6-3af469d7d76f");
Guid ordercreated = service.Create(so);
MessageBox.Show("Sales Order Created");

Ronald Plz Observe the code mentioned above.

Please Help me How to Retrieve CustomerId and PriceLevelId Instead of Passing Hardcodeed Value.

And Is it enough for creating SalesOrder.

I understood that some dependencies are there to create SalesOrder.In the sense,For example we should create account or Contact before creating SalesOrder to get CustomerID.its ok.But How can retrieve CustomerID while I am creating SalesOrder as given code.

Coming to PriceLevel,I should create Product to retrieve PriceLevelId before creating SalesOrder.

What I want now is How to retrieve these type of dependencies when i am creating SalesOrder or something else.

So Could you Plz Respond Programmatically next time.

waiting for Response.

I have one more confusion I will ask u later.

Regards
Chanti

Ronald Lemmen said...

Hi Chanti,

Please let me correct the url of my last message. The correct url is:
http://msdn.microsoft.com/library/en-us/CrmSdk3_0/htm/v3d0usingcrmtypes.asp
This url explains how to work with the crm types. When you look to that page, then you'll notice some differences with your code.
so.customerid.type = "account" should be so.customerid.type = EntityName.account.ToString();
so.totalamount.formattedvalue = "$123.00" should be: so.totalamount.Value = (decimal)123.00;
so.pricelevelid.Value = EntityName.pricelevel.ToString(); should be added as well.

The dynamically way to get the guids is to work with the fetch method. See this page for info:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/CrmSdk3_0/htm/v3d0commonmethodsfetch.asp

You'll end up with something like:

string fetch = @"<fetch mapping=""logical"">
<entity name=""account"">
<attribute name=""accountid""/>
<filter type=""and"">
<condition attribute=""accountnumber"" operator=""eq"" value=""156""/>
</filter>
</entity>
</fetch>";

// Fetch the results.
String result = service.Fetch(fetch);

You should understand that I love to help people, but that I can't create the exact codes for everybody. Please take the time to use my hints and links to improve your knowledge regarding CRM.

With kind regards

Anonymous said...

Hi Ronald

Remember me? I am chanti.Got Useful information from you so far regarding creating SalesOrder and etc.

Now I am Completely entering into Our Domain(CRM).Means New Project will be strated by next week.I need Your help as i am new to CRM and to gain and learn good knowledge in MS CRM.I hope that you should.

Anyhow I will be in touch with you if i have any doubts.

Thanks in Advance

Kind Regards
Chanti

Ronald Lemmen said...

Hi Chanti,

Good luck with your CRM project! If you have any questions, just ask the public newsgroups or post your question to the crm sandbox site: http://codegallery.gotdotnet.com/crm

Kind regards,

Ronald

Anonymous said...

Hello Ronald,
I Need some help from you regarding how to assign activities to Lead Programmatically.

So Could you please help me in this regard.

Regards
Alex

Anonymous said...

Hi Ronald

How to create Activities(PhoneCall,Fax and etc) in MS CRM 3.0.I have lot of confusion while creating Phonecall where passing the values of properties "from" and "to".

So Could u plz help me.

Thanks in Advance.

Regards
John

Ronald Lemmen said...

Hi John,

Searching on google (keywords: crm phonecall from to) found me this page:

http://groups.google.nl/group/microsoft.public.crm.developer/browse_thread/thread/fe8955286b49cafa/dea7c337c855095a?lnk=st&q=&rnum=2&hl=nl#dea7c337c855095a

The code described there is:
public Guid CreatePhoneCallActivity(string subject, string description,
Guid to, string toType, Guid regardingId, string regardingType,
CrmDateTime dateStart)
{
phonecall phonecallCreate = new phonecall();
phonecallCreate.scheduledstart = dateStart;
phonecallCreate.subject = subject;
phonecallCreate.description = description;
phonecallCreate.regardingobjectid = new Lookup();
phonecallCreate.regardingobjectid.type = regardingType;
phonecallCreate.regardingobjectid.Value = regardingId;


activityparty to = new activityparty();
to.partyid = new Lookup();
to.partyid.type = toType;
to.partyid.Value = to;


activityparty from = new activityparty();
from.partyid = new Lookup();
from.partyid.type = EntityName.systemuser.ToString();
from.partyid.Value = User.UserId; // any user id


phonecallCreate.to = new activityparty[] { to };
phonecallCreate.from = new activityparty[] { from };


return CrmServiceInstance.Create(phonecallCreate);


That should work.

Ronald

Anonymous said...

Hi Ronald,

Is there a way to capture the fetchXML for a users current view.

I checked out your script to grab the fetch in advanced find

javascript:prompt("my query:", resultRender.FetchXml.value);

just cant seem to get the fetchXML for a currently selected view. I want to grab it and send it off to an server side function that processes records for bulk email shot

Anonymous said...

Ronald;

We stumbled upon this very problem when trying to bypass another CRM "quirk" so this has helped us a lot.

The original problem we were trying to workaround was the way CRM counts records.

We are trying to get a count of distinct contacts for some criteria.

The FetchXml aggregate=count option work really well; until we include criteria for a one-many relationship. The the count goes wrong.

For instance, we have one contact who has two loyalty cards (stored in a custom entity with a 1-n relationship to contact). If the contact has one card and we do a count we get 1. correct.

If the contact has two cards and we do a count, we get 2. incorrect as we want the count of contacts, not of cards.

A few Google searches seem to confirm that CRM in fact does a SELECT DISTINCT COUNT(contactid) FROM Contact WHERE ....

instead of the expected
SELECT COUNT(DISTINCT contactid) FROM Contact WHERE ....

Do you know of anyway around this problem still using FetchXml?

I really don't like the idea of modifying the registry to bypass the threshold ans bringing back all the xml just to count the result nodes.

Any ideas would be hugely appreciated.

Thanks
Ryan

Anonymous said...

Hi Ronald,

I would like to get Records count only like SELECT COUNT(*) recordsCount FROM account. How can I do it?

I am using RetrieveMultiple to retrieve records. And I faced one problem when I am using Paging. It hits the error if there is NO records (There is no page number 1). It is NO problem if there is records. What is the way to avoid that problem?

Regards,
aung

Vineeta said...

Can you tell me how to passa value to FetchXML, than hardcoded value for filter condition?

Ian said...

hi,

am using your FetchXML code which is excellent.
Am getting the following error
"'column' argument cannot be null. Parameter name: column"

when returning the data from a total of 5211 from the Account entity.
The 1st 5000 records are ok, the error occurs when returning the 2nd set of 211 records.
This is the xml...

"










"

Cheers for any assistance.
Ian

Ian said...
This comment has been removed by the author.
Ian said...

another attempt to display the FetchXML

"fetch version=\"1.0\" output-format=\"xml-platform\" mapping=\"logical\" page=\"2\" count=\"5000\" distinct=\"false\"
entity name=\"account\"
attribute name=\"name\"
attribute name=\"primarycontactid\"
attribute name=\"telephone1\"
attribute name=\"accountid\"
order attribute=\"name\" descending=\"false\"
filter type=\"and\"><condition attribute=\"statecode\" operator=\"eq\" value=\"0\"
/filter
/entity
/fetch"

tabi said...

You can refer to this post on how to fetch mutliple records and schedule workflows.
http://raotayyabali.wordpress.com/2010/07/21/scheduling-workflows-in-crm-4-0/

Weifeng said...

I think the readers of this post might interested in writing FetchXML, here is a web tool convert from SQL to FetchXML, available at http://www.sql2fetchxml.com