Microsoft Dynamics CRM – Page 65 – Nishant Rana's Weblog

Newly Created Entity not appearing in Stunnware’s Tool (Fetch XML Wizard)

Hi,

I created two new entities in my CRM 2011 development server and thought of writing a fetch xml query using the Fetch XML Wizard tool of Stunnware.

But much to my surprise while trying to add Entities for the query, I was not able to find them in the list. This happens because of the Metadata Cache not getting refreshed. The tool maintains the cache of the metadata. This post helped me in resolving the issue.

http://luckyabhishek.blogspot.in/2011/05/stunnware-fetch-xml-doesnt-show.html

Bye.

Sample JavaScript Code to retrieve all the members of a Static Marketing List in CRM 2011

Hi,

We recently had a requirement to retrieve all the members of a particular static marketing list through Jscript. The difficult part was to fetch all the records in case of more than 5000 records. Here we had to repeatedly check the soap result to see if there are any more records and if so create the fetch xml query in runtime and again execute it till we get all the members.

This particular post was of immense help

http://blog.customereffective.com/blog/2011/05/execute-fetch-from-javascript-in-crm-2011.html


// call this function and pass the id of the static marketing list to it
 function GetAllMembers(listGuid)
 {

 _oService='';
 _sOrgName = "";
 memberArray = new Array();
 morePages = 'true';
 pageNumber = 1;
 pageCookie = '';
 recordGuid='';
 XMLHTTPSUCCESS = 200;
 XMLHTTPREADY = 4;

 context = typeof (Xrm) == "undefined" ? GetGlobalContext() : Xrm.Page.context;
 var _sServerUrl = context.getServerUrl();
 if (_sServerUrl.match(/\/$/)) {
 _sServerUrl= _sServerUrl.substring(0, serverUrl.length - 1);
 }

 GetMarketingListMembers(listGuid);

}

function GetMarketingListMembers(listGuid) {

 fetchOnLoad(pageNumber, pageCookie, listGuid);

for (var i = 0; i < memberArray.length; i++) {

alert(memberArray[i]);
 // Iterates over numeric indexes from 0 to 5, as everyone expects
 }
 }

function fetchOnLoad(pageNumber, pageCookie, listGuid) {

 recordGuid = listGuid;

var sFetch = "<fetch mapping='logical' page='" + pageNumber + "' count='5000' paging-cookie='" + pageCookie + "' version='1.0'>" +
 "<entity name='listmember'>" +
 "<attribute name='entityid' />" +
 "<filter>" +
 "<condition attribute='listid' operator='eq' value='" + recordGuid + "' />" +
 "</filter>" +
 "</entity>" +
 "</fetch>";

_oService = new FetchUtil(_sOrgName, _sServerUrl);
 _oService.Fetch(sFetch, myCallBack);

}
 function myCallBack(results) {

// add all the member in the array

for (i in results) {
 memberArray.push(results[i].attributes.entityid.guid);
 }

if (morePages == 'true') {
 fetchOnLoad(pageNumber, pageCookie, recordGuid);
 }

}

function FetchUtil(sOrg, sServer) {
 this.org = sOrg;
 this.server = sServer;
 if (sOrg == null) {
 if (typeof(ORG_UNIQUE_NAME) != "undefined") {
 this.org = ORG_UNIQUE_NAME;
 }
 }
 if (sServer == null) {
 this.server = window.location.protocol + "//" + window.location.host;
 }
 }

FetchUtil.prototype._ExecuteRequest = function(sXml, sMessage, fInternalCallback, fUserCallback) {
 var xmlhttp = new XMLHttpRequest();
 xmlhttp.open("POST", this.server + "/XRMServices/2011/Organization.svc/web", false);
 xmlhttp.setRequestHeader("Accept", "application/xml, text/xml, */*");
 xmlhttp.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
 xmlhttp.setRequestHeader("SOAPAction", "http://schemas.microsoft.com/xrm/2011/Contracts/Services/IOrganizationService/Execute");
 if (fUserCallback != null) {
 //asynchronous: register callback function, then send the request.
 var crmServiceObject = this;
 xmlhttp.onreadystatechange = function() {
 fInternalCallback.call(crmServiceObject, xmlhttp, fUserCallback)
 };
 xmlhttp.send(sXml);
 } else {
 //synchronous: send request, then call the callback function directly
 xmlhttp.send(sXml);
 return fInternalCallback.call(this, xmlhttp, null);
 }
 }
 FetchUtil.prototype._HandleErrors = function(xmlhttp) {
 /// <summary>(private) Handles xmlhttp errors</summary>
 if (xmlhttp.status != XMLHTTPSUCCESS) {
 var sError = "Error: " + xmlhttp.responseText + " " + xmlhttp.statusText;
 alert(sError);
 return true;
 } else {
 return false;
 }
 }
 FetchUtil.prototype.Fetch = function(sFetchXml, fCallback) {
 /// <summary>Execute a FetchXml request. (result is the response XML)</summary>
 /// <param name="sFetchXml">fetchxml string</param>
 /// <param name="fCallback" optional="true" type="function">(Optional) Async callback function if specified. If left null, function is synchronous </param>
 var request = "<s:Envelope xmlns:s=\"http://schemas.xmlsoap.org/soap/envelope/\">";
 request += "<s:Body>";
 request += "<Execute xmlns='http://schemas.microsoft.com/xrm/2011/Contracts/Services'>" + "<request i:type=\"b:RetrieveMultipleRequest\" " + ' xmlns:b="http://schemas.microsoft.com/xrm/2011/Contracts" ' + ' xmlns:i="http://www.w3.org/2001/XMLSchema-instance">' + '<b:Parameters xmlns:c="http://schemas.datacontract.org/2004/07/System.Collections.Generic">' + '<b:KeyValuePairOfstringanyType>' + '<c:key>Query</c:key>' + '<c:value i:type="b:FetchExpression">' + '<b:Query>';
 request += CrmEncodeDecode.CrmXmlEncode(sFetchXml);
 request += '</b:Query>' + '</c:value>' + '</b:KeyValuePairOfstringanyType>' + '</b:Parameters>' + '<b:RequestId i:nil="true"/>' + '<b:RequestName>RetrieveMultiple</b:RequestName>' + '</request>' + '</Execute>';
 request += "</s:Body></s:Envelope>";
 return this._ExecuteRequest(request, "Fetch", this._FetchCallback, fCallback);
 }

FetchUtil.prototype._FetchCallback = function(xmlhttp, callback) {
 ///<summary>(private) Fetch message callback.</summary>
 //xmlhttp must be completed
 if (xmlhttp.readyState != XMLHTTPREADY) {
 return;
 }
 //check for server errors
 if (this._HandleErrors(xmlhttp)) {
 return;
 }
 var moreRecordsXML = xmlhttp.responseXML.selectSingleNode("//a:MoreRecords").text;
 if (moreRecordsXML == 'true') {
 morePages = 'true';
 pageCookie = xmlhttp.responseXML.selectSingleNode("//a:PagingCookie").text;
 pageCookie = pageCookie.replace( /\"/g , '\'');
 pageCookie = pageCookie.replace( /</g , '&lt;');
 pageCookie = pageCookie.replace( />/g , '&gt;');
 pageCookie = pageCookie.replace( /'/g , '&quot;');
 pageNumber = pageNumber + 1;
 } else {
 morePages = 'false';
 }

// get the value of the morerecords

var sFetchResult = xmlhttp.responseXML.selectSingleNode("//a:Entities").xml;
 var resultDoc = new ActiveXObject("Microsoft.XMLDOM");
 resultDoc.async = false;
 resultDoc.loadXML(sFetchResult);
 //parse result xml into array of jsDynamicEntity objects
 var results = new Array(resultDoc.firstChild.childNodes.length);
 for (var i = 0; i < resultDoc.firstChild.childNodes.length; i++) {
 var oResultNode = resultDoc.firstChild.childNodes[i];
 var jDE = new jsDynamicEntity();
 var obj = new Object();
 for (var j = 0; j < oResultNode.childNodes.length; j++) {
 switch (oResultNode.childNodes[j].baseName) {
 case "Attributes":
 var attr = oResultNode.childNodes[j];
 for (var k = 0; k < attr.childNodes.length; k++) {
 // Establish the Key for the Attribute
 var sKey = attr.childNodes[k].firstChild.text;
 var sType = "";
 // Determine the Type of Attribute value we should expect
 for (var l = 0; l < attr.childNodes[k].childNodes[1].attributes.length; l++) {
 if (attr.childNodes[k].childNodes[1].attributes[l].baseName == 'type') {
 sType = attr.childNodes[k].childNodes[1].attributes[l].text;
 }
 }
 switch (sType) {
 case "a:OptionSetValue":
 var entOSV = new jsOptionSetValue();
 entOSV.type = sType;
 entOSV.value = attr.childNodes[k].childNodes[1].text;
 obj[sKey] = entOSV;
 break;
 case "a:EntityReference":
 var entRef = new jsEntityReference();
 entRef.type = sType;
 entRef.guid = attr.childNodes[k].childNodes[1].childNodes[0].text;
 entRef.logicalName = attr.childNodes[k].childNodes[1].childNodes[1].text;
 entRef.name = attr.childNodes[k].childNodes[1].childNodes[2].text;
 obj[sKey] = entRef;
 break;
 default:
 var entCV = new jsCrmValue();
 entCV.type = sType;
 entCV.value = attr.childNodes[k].childNodes[1].text;
 obj[sKey] = entCV;
 break;
 }
 }
 jDE.attributes = obj;
 break;
 case "Id":
 jDE.guid = oResultNode.childNodes[j].text;
 break;
 case "LogicalName":
 jDE.logicalName = oResultNode.childNodes[j].text;
 break;
 case "FormattedValues":
 var foVal = oResultNode.childNodes[j];
 for (var k = 0; k < foVal.childNodes.length; k++) {
 // Establish the Key, we are going to fill in the formatted value of the already found attribute
 var sKey = foVal.childNodes[k].firstChild.text;
 jDE.attributes[sKey].formattedValue = foVal.childNodes[k].childNodes[1].text;
 }
 break;
 }
 }
 results[i] = jDE;
 }
 //return entities
 if (callback != null) callback(results);
 else return results;
 }

function jsDynamicEntity(gID, sLogicalName) {
 this.guid = gID;
 this.logicalName = sLogicalName;
 this.attributes = new Object();
 }

function jsCrmValue(sType, sValue) {
 this.type = sType;
 this.value = sValue;
 }

function jsEntityReference(gID, sLogicalName, sName) {
 this.guid = gID;
 this.logicalName = sLogicalName;
 this.name = sName;
 this.type = 'EntityReference';
 }

function jsOptionSetValue(iValue, sFormattedValue) {
 this.value = iValue;
 this.formattedValue = sFormattedValue;
 this.type = 'OptionSetValue';
 }

Hope it helps.

Solving “Unable to access the MSCRM_CONFIG database. SQL Server does not exist or access denied” in CRM 2011.

Hi,

For one of the users, I was getting the below error while trying to open Deployment Manager Tool.

The following posts and article helped in resolving the issue.

http://hinamehta14.wordpress.com/2012/08/22/fixed-unable-to-access-the-mscrm_config-database-sql-server-does-not-exist-or-access-denied-in-crm-2011-2/

http://social.microsoft.com/Forums/uk/crmdeployment/thread/6515d40c-d8fd-4090-9980-9088860cd6b5

http://technet.microsoft.com/en-us/library/gg197631.aspx

Bye.

Updated SoapLogger tool to generate JavaScript in CRM 2011.

Hi,

There is a SOAPLogger tool that ships with CRM 2011 SDK which can be used to capture HTTP request and response.

http://msdn.microsoft.com/en-us/library/gg594434.aspx

Using that tool we can write our code in C# and and it captures the corresponding SOAP Request and Response for it in a file named Output.txt. I have updated the code so that it formats the SoapRequest in JavaScript which than can be used as web resource in our solution. (It works with Office 365 as well).

The C# code for creating a contact record

And the corresponding output.txt file

Dowload it here :- SoapLogger (rename .doc to .zip)

Hope it helps.

Get the total members count of a Dynamic Marketing List in CRM 2011.

Hi,

For a Static Marketing List, we can get the total member count from the attribute “Members Count”.

However this attribute doesn’t’ work for Dynamic Marketing List as this value can only be derived at run time, when the query associated with the list is executed.

One way we can get the total count for the list is by following the below steps.

We can get the fetch xml query associated with the Dynamic Marketing List.
Remove the attribute and order tag from it.
Modify it to include aggregate function.

Sample Code in C#

 private void btnGetMembersDynamicML_Click(object sender, EventArgs e)
 {
 IOrganizationService orgService = GetOrganizationService();

ColumnSet cols= new ColumnSet(new string[] { "query" });

// GUID of the Dynamic Marketing List
 var entity = orgService.Retrieve("list", new Guid("03C4E473-F7DA-E111-B988-00155D886334"), cols);
 var dynamicQuery = entity.Attributes["query"].ToString();

var countQuery = ModifyFetchXML(dynamicQuery);

var memberCountResult = orgService.RetrieveMultiple(new FetchExpression(countQuery));

DataCollection<Entity> dataCollection = memberCountResult.Entities;

int totalMembers = default(int);
 if(dataCollection != null && dataCollection.Count > 0)
 {
 foreach(Entity entityVal in dataCollection)
 {
 AliasedValue value = (entityVal.Attributes["member_count"] as AliasedValue);
 totalMembers = (int)value.Value;
 }
 }

MessageBox.Show("Total Members : " + totalMembers.ToString());

}

private static string ModifyFetchXML(string dynamicQuery)
 {
 var doc = new XmlDocument();
 doc.LoadXml(dynamicQuery);

var entitynode = doc.GetElementsByTagName("entity")[0];

int childCount = entitynode.ChildNodes.Count;

// Remove all the attribute and order tag
 for (int i = 0; i <= childCount; i++)
 {
 var attributenode = entitynode.SelectSingleNode("//attribute");
 if (attributenode != null) entitynode.RemoveChild(attributenode);
 else
 {
 var ordernode = entitynode.SelectSingleNode("//order");
 if (ordernode != null) entitynode.RemoveChild(ordernode);
 }
 }

// add a new attribute tag
 // <attribute name="fullname" alias="member_count" aggregate="count" />
 XmlElement xmlNodeCustomSettings = doc.CreateElement("attribute");

xmlNodeCustomSettings.SetAttribute("name", "contactid");
 xmlNodeCustomSettings.SetAttribute("alias", "member_count");
 xmlNodeCustomSettings.SetAttribute("aggregate", "count");

entitynode.AppendChild(xmlNodeCustomSettings);

// Add aggregate= true attribute
 // <fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false" aggregate="true">
 var root = doc.DocumentElement;
 if (root != null) root.SetAttribute("aggregate", "true");

return doc.InnerXml;
 }

public IOrganizationService GetOrganizationService()
 {
 Uri organizationUri = new Uri("http://servername/orgname/XRMServices/2011/Organization.svc");
 Uri homeRealmUri = null;
 ClientCredentials credentials = new ClientCredentials();
 credentials.Windows.ClientCredential = System.Net.CredentialCache.DefaultNetworkCredentials;
 OrganizationServiceProxy orgProxy = new OrganizationServiceProxy(organizationUri, homeRealmUri, credentials, null);
 IOrganizationService _service = (IOrganizationService)orgProxy;
 return _service;
 }

Hope it helps.