Lookup – Arvind's CRM Blog

Retrieve Data Using Web API and FetchXml

Sharing sample code to retrieve data using Web API and using FetchXml to build the query instead of oData. In this example, I am retrieving contact based on email/mobile no and calling function form an entity’s ribbon button to validate duplicate record.

function ValidateContact() {
var mobilePhone = Xrm.Page.getAttribute(“mc_contactnumber”).getValue();
var email = Xrm.Page.getAttribute(“emailaddress”).getValue();
var clientURL = Xrm.Page.context.getClientUrl();
var fetchContact = “<fetch version=’1.0′ output-format=’xml-platform’ mapping=’logical’ distinct=’false’>” +
” <entity name=’contact’>” +
” <attribute name=’fullname’ />” +
” <attribute name=’emailaddress1′ />” +
” <attribute name=’mc_mobilephone’ />” +
” <attribute name=’createdon’ />” +
” <attribute name=’ownerid’ />” +
” <order attribute=’fullname’ descending=’false’ />” +
” <filter type=’and’>” +
” <filter type=’or’>” +
” <condition attribute=’mc_mobilephone’ operator=’eq’ value='” + mobilePhone + “‘ />” +
” <condition attribute=’emailaddress1′ operator=’eq’ value='” + email + “‘ />” +
” </filter>” +
” <condition attribute=’statecode’ operator=’eq’ value=’0′ />” +
” </filter>” +
” </entity>” +
“</fetch>”;

To use FetchXml, we need to format FetchXml in usable Web API service endpoint format. We do this by storing the FetchXML in a variable and encoding the string with the encodeURI function native to JavaScrip as below.
var encodedFetchXml = encodeURI(fetchContact);

Another point, if you want formatted values to be included, then you need to set request header like below(This is mainly used to get Lookup, OptionSet, DateTime specific formatted value like lookup text value).
req.setRequestHeader(“Prefer”, “odata.include-annotations=\”*\””);
OR
req.setRequestHeader(“Prefer”, “odata.include-annotations=\”OData.Community.Display.V1.FormattedValue\””);

Please refer the below URL for details.
https://community.dynamics.com/crm/b/mscrmcustomization/archive/2016/10/18/ms-crm-2016-web-api-operations-retrieve-single-or-multiple-records

http://himbap.com/blog/?p=2012

Set Custom View as Default View for Lookup Control

Let’s say we have to set Custom View as Default View of a particular Lookup. Below are the steps to set Custom View as default View.

1. Create a Custom View as per your requirement and Save it.(In my case, Created a custom view of Account which will be shown on contact entity for Primary Customer Field)

2. Get View Id from Browser. 3. Download Fetch XML for your custom view as shown below. In my case, below is the FetchXML query..

4. Format the FetchXML as string variable as below.

    var fetchXml = “<fetch version=’1.0′ “ +
“output-format=’xml-platform’ “ +
” mapping=’logical’ “ +
” distinct=’false’>” +
“<entity name=’account’>” +
    “<attribute name=’name’ />” +
    “<attribute name=’primarycontactid’ />” +
    “<attribute name=’telephone1′ />” +
    “<attribute name=’accountid’ />” +
    “<attribute name=’accountratingcode’ />” +
    “<order attribute=’name’ descending=’false’ />” +
    “<filter type=’and’>” +
     ” <condition attribute=’telephone1′ operator=’eq’ value=’555-0135′ />” +
    “</filter>” +
“</entity>” +
“</fetch>”;
5. Create layoutXml to set Grid properties to display the view as below.
var layoutXml = “<grid name=’resultset’ “ +
“object=’1′ “ +
“jump=’name’ “ +
“select=’1′ “ +
“icon=’1′ “ +
“preview=’1′>” +
“<row name=’result’ “ +
“id=’accountid’>” +
“<cell name=’name’ “ +
“width=’300′ />” +
“<cell name=’primarycontactid’ “ +
“width=’150′ />” +
“<cell name=’telephone1′ “ +
“width=’100′ />” +
“<cell name=’accountratingcode’ “ +
“width=’100′ />” +
“disableSorting=’1′ />” +
“</row>” +
“</grid>”;

6. Call the addCustomView() method of Lookup to set custom view as Default View.

Sets the viewId, viewDisplayName, fetchXml, and layoutXml variables to pass as arguments so that a custom view is added as the default view to the control for the Parent Customer lookup field.

7. Add above steps in a custom function and call it on OnLoad or OnChange as per requirement
function setDefaultCustomView() {
    var viewId = “{00000000-0000-0000-00AA-000010001001}”;
    var viewDisplayName = “Custom Account View”;
    var fetchXml = “<fetch version=’1.0′ ” +
“output-format=’xml-platform’ ” +
” mapping=’logical’ ” +
” distinct=’false’>” +
“<entity name=’account’>” +
“<attribute name=’name’ />” +
“<attribute name=’primarycontactid’ />” +
“<attribute name=’telephone1′ />” +
“<attribute name=’accountid’ />” +
“<attribute name=’accountratingcode’ />” +
“<order attribute=’name’ descending=’false’ />” +
“<filter type=’and’>” +
” <condition attribute=’telephone1′ operator=’eq’ value=’555-0135′ />” +
“</filter>” +
“</entity>” +
“</fetch>”;

var layoutXml = “<grid name=’resultset’ ” +
“object=’1′ ” +
“jump=’name’ ” +
“select=’1′ ” +
“icon=’1′ ” +
“preview=’1′>” +
“<row name=’result’ ” +
“id=’accountid’>” +
“<cell name=’name’ ” +
“width=’300′ />” +
“<cell name=’primarycontactid’ ” +
“width=’150′ />” +
“<cell name=’telephone1′ ” +
“width=’100′ />” +
“<cell name=’accountratingcode’ ” +
“width=’100′ />” +
“disableSorting=’1′ />” +
“</row>” +
“</grid>”;

Xrm.Page.getControl(“parentcustomerid”).addCustomView(viewId, “account”, viewDisplayName, fetchXml, layoutXml, true);

}

Note: Pls change the double quote(“) manually before testing the code.

Populate Lookup value on selection of another Lookup

Step 1.

Step 2.

Step 3.

Let me explain the scenario…

Suppose we have City Lookup on Lead entity and we want to populate Region (Lookup), State (Lookup) and Zone (Lookup) on selection of particular City from City Lookup. You need to write below code on OnChange event of City Lookup field.

if(crmForm.all.new_cityid.DataValue !=null)

{

var citylookupValues = crmForm.all.new_cityid.DataValue;

if(citylookupValues[0] !=null)

{

var cityID = citylookupValues[0].id;

var regionID = GetAttributeValueFromID(“new_city”, cityID , “new_regionid”);

var stateID = GetAttributeValueFromID(“new_city”, cityID , “new_stateid”);

var zoneID = GetAttributeValueFromID(“new_region”, regionID , “new_zoneid”);

if(regionID != null)

{

//Create an array to set as the DataValue for the lookup control.

var regionlookupData = new Array();

//Create an Object add to the array.

var regionlookupItem= new Object();

//Set the id, typename, and name properties to the object.

regionlookupItem.id = regionID;

regionlookupItem.typename = ‘new_region’;

regionlookupItem.name = regionName;

// Add the object to the array.

regionlookupData[0] = regionlookupItem;

// Set the value of the lookup field to the value of the array.

crmForm.all.new_regionid.DataValue = regionlookupData;

crmForm.all.new_regionid.ForceSubmit = true;

}

if(stateID != null)

{

//Create an array to set as the DataValue for the lookup control.

var statelookupData = new Array();

//Create an Object add to the array.

var statelookupItem = new Object();

//Set the id, typename, and name properties to the object.

statelookupItem.id = stateID;

statelookupItem.typename = ‘new_state’;

statelookupItem.name = stateName;

// Add the object to the array.

statelookupData[0] = statelookupItem;

// Set the value of the lookup field to the value of the array.

crmForm.all.new_stateid.DataValue = statelookupData;

crmForm.all.new_stateid.ForceSubmit = true;

}

if(zoneID != null)

{

var zoneName = GetAttributeValueFromID(“new_zone”, zoneID, “new_name”);

//Create an array to set as the DataValue for the lookup control.

var zonelookupData = new Array();

//Create an Object add to the array.

var zonelookupItem = new Object();

//Set the id, typename, and name properties to the object.

zonelookupItem.id = zoneID;

zonelookupItem.typename = ‘new_zone’;

zonelookupItem.name = zoneName;

// Add the object to the array.

zonelookupData[0] = zonelookupItem;

// Set the value of the lookup field to the value of the array.

crmForm.all.new_zoneid.DataValue = zonelookupData;

crmForm.all.new_zoneid.ForceSubmit = true;

}

else

{

crmForm.all.new_regionid.DataValue = null;

crmForm.all.new_stateid.DataValue = null;

crmForm.all.new_zoneid.DataValue = null;

}

function GetAttributeValueFromID(sEntityName, sGUID, sAttributeName)

{

* sEntityName: the name of the CRM entity (account, etc.)

* whose attribute value wish to look up

* sGUID: string representation of the unique identifier of the specific object whose attrbuite value we wish to look up

* sAttributeName – the schema name of the attribute whose value we wish returned

var sXml = “”;

//var oXmlHttp = new ActiveXObject(“Msxml2.XMLHTTP”);

var oXmlHttp = new ActiveXObject(“Msxml2.XMLHTTP.6.0”);

//var serverurl = “http://10.10.40.50:5555”;

var serverurl = “”;

//set up the SOAP message

sXml += “<?xml version=\”1.0\” encoding=\”utf-8\” ?>”;

sXml += “<soap:Envelope xmlns:soap=\”http://schemas.xmlsoap.org/soap/envelope/\””

sXml += ” xmlns:xsi=\”http://www.w3.org/2001/XMLSchema-instance\””

sXml += ” xmlns:xsd=\”http://www.w3.org/2001/XMLSchema\”>”;

sXml += “<soap:Body>”;

sXml += “<entityName xmlns=\”http://schemas.microsoft.com/crm/2006/WebServices\”>” +

sEntityName + “</entityName>”;

sXml += “<id xmlns=\”http://schemas.microsoft.com/crm/2006/WebServices\”>” +

sGUID + “</id>”;

sXml += “<columnSet xmlns=\”http://schemas.microsoft.com/crm/2006/WebServices\””

sXml += ” xmlns:q=\”http://schemas.microsoft.com/crm/2006/Query\””

sXml += ” xsi:type=\”q:ColumnSet\”><q:Attributes><q:Attribute>” +

sAttributeName + “</q:Attribute></q:Attributes></columnSet>”;

sXml += “</soap:Body>”;

sXml += “</soap:Envelope>”;

// send the message to the CRM Web service

oXmlHttp.Open(“POST”, serverurl +

“/MsCrmServices/2006/CrmService.asmx”,false);

oXmlHttp.setRequestHeader(“SOAPAction”,

“http://schemas.microsoft.com/crm/2006/WebServices/Retrieve”);

oXmlHttp.setRequestHeader(“Content-Type”, “text/xml; charset=utf-8”);

oXmlHttp.setRequestHeader(“Content-Length”, sXml.length);

oXmlHttp.send(sXml);

// retrieve response and find attribute value

//var result = oXmlHttp.responseXML.selectSingleNode(“//” + sAttributeName);

var result = oXmlHttp.responseXML.selectSingleNode(“//*[local-name()=\””+ sAttributeName +”\”]”);

if (result == null)

{

return “”;

}

else

return result.text;

}

Populate lookup value on selection of another lookup

I had client requirement that on the selection of City (lookup), Region and Sub Region should be populated automatically which is read only field. To achieve this we mapped the city with region (lookup) and sub region (lookup) on the city entity. After writing below code I was getting following error.

There was an error with this field’s customized event.

Field:new_cityid

Event:onchange

Error:’ new_subregionid.value ‘ is null or not an object

I solved the issue with help of this link and answer posted by Adi Katz .

“The keyValues are only available when you use the lookup dialog. The form assistant does not contain the columns that are retrieved by the lookup and this is why the items array is empty.”

For details please visit below link.

http://social.microsoft.com/Forums/en-US/crmdevelopment/thread/ad9b5b22-da6b-4f20-9fd4-b7e46d9a7556

After this solution I added Sub Region field to lookup dialog (lookup view) and this solve my issue.

Put below code on OnChange of City Lookup field.

/*****Auto Populate SubRegion on Selection of City field******************/

if (crmForm.all.new_cityid.DataValue==null)

{
crmForm.all.new_subregionid.DataValue = null;
}

else

{

var lookup_guid;

var lookup_name;

var lookup_type;

var lookup_typename;

var lookupItem = new Array;

lookupItem = crmForm.all.new_cityid.DataValue;

lookup_guid=lookupItem[0].id;

lookup_name=lookupItem[0].name;

lookup_type=lookupItem[0].type;

lookup_typename=lookupItem[0].typename;

var subRegionName;

if(crmForm.all.new_cityid.items != null)

{
var lookupValues = crmForm.all.new_cityid.items[0].keyValues;
subRegionName = lookupValues.new_subregionid.value? lookupValues.new_subregionid.value : null;

}
else
{
subRegionName = null;

}

var subregionidvalue = GetAttributeValueFromID(lookup_typename, lookup_guid, ‘new_subregionid’);

if(subregionidvalue != null)

{
var subregionlookupData = new Array();

//Create an Object add to the array.

var subregionlookupItem = new Object();

//Set the id, typename, and name properties to the object.

subregionlookupItem.id = subregionidvalue;

regionlookupItem.typename = ‘new_subregion’;

regionlookupItem.name = subRegionName ;

// Add the object to the array.

subregionlookupData[0] = subregionlookupItem;

// Set the value of the lookup field to the value of the array.

crmForm.all.new_subregionid.DataValue = subregionlookupData;

crmForm.all.new_subregionid.ForceSubmit = true;

}

function GetAttributeValueFromID(sEntityName, sGUID, sAttributeName)

{

var sXml = “”;

var oXmlHttp = new ActiveXObject(“Msxml2.XMLHTTP.6.0”);

var serverurl = “”;

//set up the SOAP message

sXml += “<?xml version=\”1.0\” encoding=\”utf-8\” ?>”;

sXml += “<soap:Envelope xmlns:soap=\”http://schemas.xmlsoap.org/soap/envelope/\””;

sXml += ” xmlns:xsi=\”http://www.w3.org/2001/XMLSchema-instance\””;

sXml += ” xmlns:xsd=\”http://www.w3.org/2001/XMLSchema\”>”;

sXml += “<soap:Body>”;

sXml += “<entityName xmlns=\”http://schemas.microsoft.com/crm/2006/WebServices\”>” + sEntityName +

“</entityName>”;

sXml += “<id xmlns=\”http://schemas.microsoft.com/crm/2006/WebServices\”>” + sGUID + “</id>”;

sXml += “<columnSet xmlns=\”http://schemas.microsoft.com/crm/2006/WebServices\””;

sXml += ” xmlns:q=\”http://schemas.microsoft.com/crm/2006/Query\””;

sXml += ” xsi:type=\”q:ColumnSet\”><q:Attributes><q:Attribute>” + sAttributeName +

“</q:Attribute></q:Attributes></columnSet>”;

sXml += “</soap:Body>”;

sXml += “</soap:Envelope>”;

// send the message to the CRM Web service

oXmlHttp.open(“POST”, serverurl + “/MsCrmServices/2006/CrmService.asmx”,false);

oXmlHttp.setRequestHeader(“SOAPAction”,”http://schemas.microsoft.com/crm/2006/WebServices/Retrieve”);

oXmlHttp.setRequestHeader(“Content-Type”, “text/xml;charset=utf-8”);

oXmlHttp.setRequestHeader(“Content-Length”, sXml.length);

oXmlHttp.send(sXml);

// retrieve response and find attribute value

// retrieve the given attribute name in any XML namespace

var result = oXmlHttp.responseXML.selectSingleNode(“//*[local-name()=\”” + sAttributeName +”\”]”);

if (result == null)

{

return “”;

}

else

{

return result.text;

}

Show both active and inactive records in the lookup view

CRM MVP Batistuta Cai already had a post about a plug-in solution.

If the lookup entity is a system entity, you can also use this technique:

Let’s start from an example: you have a custom entity call: MyEntity, you have setup a N:1 relationship between MyEntity and Opportunity, so the user can see an opportunity lookup field on the MyEntity form. Now you want to show users both active and inactive opportunities from that lookup field, all you need to do is put the below code into MyEntity.OnLoad() event:

crmForm.all.new_opportunityid.lookupclass = “alllookups”;

The lookup class are controlled via xml files in %ProgramFiles%\Microsoft CRM\Server\ApplicationFiles\
If you take a look at the file: opportunity.xml, you may find a condition like: <condition attribute=”statecode” operator=”eq” value=”0″/>

you can remove the condition, and then use this class, e.g: crmForm.all.new_opportunityid.lookupclass=”opportunity”; However it’s very much unsupported way(by changing files)! But if you open the file: alllookups.xml, you may find that the opportunity(object type=”3″) entity doesn’t have such condition, so we can use this class to get all opportunities.