Retrieve Fields from Related Entities to Populate Fields on a Form in Microsoft Dynamics CRMv4
By David Pritchett in Dynamics CRM, Script and Code on Tuesday, February 24th, 2009
I have been working with a client that I had implemented last year and one of their original requirements was to populate fields on a form with values from fields already filled in on related entities. We all know that if two entities are related, as in Account and Contact, you can map fields from one to the other and the values will be transferred from the parent to the child if you create the child record under the parent record. However, what if you still want certain values to be inherited from other records no matter how you create the child record or if the relationship is not parent/child? For example, I want the Contact record to inherit values from the Account record I select in the Account lookup on the Contact form when I create a new Contact. This can be done by implementing some code via an onChange event on the Account lookup field. Since you need to actually send a query to retrieve these values, this requires some advanced java script techniques such as building an XML query, creating an Active X object and parsing the object to retrieve the values you need for your form. There are several examples on how to do this type of functionality floating around on various places on the web, but I always have a hard time finding them when I need to go back for reference, so I thought I would post a comprehensive example here. You can use this and apply it to any entity that is related to another entity. I will over-comment the code to explain what is going on, but you can remove later if you wish. This example will retrieve the Telephone and Owner fields from the Account and set them to the respective fields on the Contact.
/* ————————————————————————————————
Begin.
Retrieves the Telephone and Owner field values from the Account record
based on the Account selected
————————————————————————————————–*/
// Get the data value of the Account lookup after the Account is selected
var AccountLookUp = crmForm.all.parentcustomerid.DataValue;
if (AccountLookUp != null && AccountLookUp != “undefined”)
{
// Get the value for the accountid from the array and set it to a variable
var AccountID = AccountLookUp[0].id;
//alert(AccountID);
/*————————————————————————————————
Build your query XML that you will use to create the ActiveX object. This is using standard SDK protocol. In this example I just retrieve all of the fields from the Account. You can vary your query to only retrieve certain fields or to pass other parameters. Check with the SDK to see examples of how to build your query in this way. Here, I am just retrieving all Account fields based on the AccountID of the Account selected
————————————————————————————————–*/
var Accountxml = “” +
“<?xml version=”1.0″ encoding=”utf-8″?>” +
“<soap:Envelope xmlns:soap=”http://schemas.xmlsoap.org/soap/envelope/” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:xsd=”http://www.w3.org/2001/XMLSchema”>” +
GenerateAuthenticationHeader() +
” <soap:Body>” +
” <RetrieveMultiple xmlns=”http://schemas.microsoft.com/crm/2007/WebServices”>” +
” <query xmlns:q1=”http://schemas.microsoft.com/crm/2006/Query” xsi:type=”q1:QueryExpression”>” +
” <q1:EntityName>account</q1:EntityName>” +
” <q1:ColumnSet xsi:type=”q1:AllColumns” />” +
” <q1:Distinct>false</q1:Distinct>” +
” <q1:Criteria>” +
” <q1:FilterOperator>And</q1:FilterOperator>” +
” <q1:Conditions>” +
” <q1:Condition>” +
” <q1:AttributeName>accountid</q1:AttributeName>” +
” <q1:Operator>Like</q1:Operator>” +
” <q1:Values>” +
” <q1:Value xsi:type=”xsd:string”>” + AccountID + “</q1:Value>” +
” </q1:Values>” +
” </q1:Condition>” +
” </q1:Conditions>” +
” </q1:Criteria>” +
” </query>” +
” </RetrieveMultiple>” +
” </soap:Body>” +
“</soap:Envelope>” +
“”;
//alert(Accountxml);
/*————————————————————————————————
Create the ActiveX object. Once again, this is standard protocol. Notice the
last two lines that pass our Query XML that we created above.
————————————————————————————————*/
var xmlHttpRequest = new ActiveXObject(“Msxml2.XMLHTTP”);
xmlHttpRequest.Open(“POST”, “/mscrmservices/2007/CrmService.asmx”, false);
xmlHttpRequest.setRequestHeader(“SOAPAction”,”http://schemas.microsoft.com/crm/2007/WebServices/RetrieveMultiple”);
xmlHttpRequest.setRequestHeader(“Content-Type”, “text/xml; charset=utf-8″);
xmlHttpRequest.setRequestHeader(“Content-Length”, Accountxml.length);
xmlHttpRequest.send(Accountxml);
// Set the XML response to a variable that can be parsed later.
var resultAccountXml = xmlHttpRequest.responseXML;
//alert(resultAccountXml);
/*————————————————————————————————
Create a container for the “nodes” retrieved. Essentially, these are just the
fields that are returned in the XML result.
————————————————————————————————*/
var entityNodes = resultAccountXml.selectNodes(“//RetrieveMultipleResult/BusinessEntities/BusinessEntity”);
// Loop through the collection of returned entities.
// Note that the query above limits the result to a single entity, so you will only find one
// node. To be more specific, it could be 0 nodes as well, if you don’t have access to accounts
// or your system is empty.
for (var i = 0; i < entityNodes.length; i++) {
// Access the current array element
var entityNode = entityNodes[i];
// Attributes are child nodes of the BusinessEntity node. Use selectSingleNode to return
// the first occurrence of a named node. The selectNodes method we used before returns all
// matching nodes, but as an attribute name only occurs once, selectSingleNode is easier to use.
// Use the same namespace alias (q1) you have specified in the query.
var TelephoneNode = entityNode.selectSingleNode(“q1:telephone1″);
var OwnerNode = entityNode.selectSingleNode(“q1:ownerid”);
// Always check for null values. If an attribute is set to null, it is not contained in the
// resulting XML. And accessing accountidNode.text when accountidNode is null leads to
// a runtime error.
var Telephone = (TelephoneNode == null) ? null : TelephoneNode.text;
var Owner = (OwnerNode == null) ? null : OwnerNode.text;
// alert(Telephone);
// alert(Owner);
}
// Set the values retrieved from the Account to the corresponding fields on your Contact form.
crmForm.all.telephone1.DataValue = Telephone;
crmForm.all.ownerid.DataValue = Owner;
}
/* ————————————————————————————————
End.
————————————————————————————————–*/
If you are not a hardcore code writer like me, then keep these things in mind when building your own code:
1) Most of the code above is standard, and you are simply plugging in variables along the way. So, follow the variables as they are created and see where they are plugged in.
2) The syntax for the Query XML, ActiveX Object and Node parsing is all standard protocol. There is no need to tweak this (although calling the web service could change over time and versions), except to pass your specific variables.
3) Debug using alert() along the way to track your variables and make sure they are being assigned properly. I left the ones I typically use in my example above.
Note: Certain parts of the code above have been copied from other sources and I left the whole comments intact. I would like to give credit to the original author(s), but I don’t know who they are. So, thanks to whoever posted the original examples!
About David Pritchett
Tags: ActiveX Object, JavaScript, OnChange Event Code, XML Query
Posted in Dynamics CRM, Script and Code
Need Expert Help With Microsoft CRM?
Get the support you need to effectively manage your business.
Learn more about our Microsoft CRM support options
Hey Guys,
Thanks for the XML soap lookup query, however, I wanted to let you know that your wordpress blog messed up the characters for copy/paste. The ” (quote) used in the above code is invalid for Microsoft CRM, and must be replaced with proper open and close quotes.
Just in case anyone else was attempting to use this code… those changes need to be made. Thanks again!
-Paul
Good catch. I have had that problem with copying from blogs before as well. I will check to see if we can change it to be copyable from the blog without any modifications.
“Note: Certain parts of the code above have been copied from other sources and I left the whole comments intact. I would like to give credit to the original author(s), but I don’t know who they are. So, thanks to whoever posted the original examples!”
Two of the original authors that you might want to credit are Adi Katz, and David Fronk
It’s great to hear the names of some of the original authors. Thank you for giving them a shout out, Jack!
This is great code, it worked very well. But, I have a minor issue. How do we tweek this to use with Lookup fields? For example, I copied over the billing address of a contact to my opportunity based on the Attention field. I was able to move everything except the state and country fields. Is there a way to bring that over as well?
Thanks!
Shams
Shams,
Great question! We’ll have to do some research to see what’s possible in regards to Lookup fields. We’ll post another comment as soon as we know our findings, and hopefully we’ll be able to help you.
Thanks!
Kristen
Great! I’m looking forward to your reply. It’ll solve 3-4 of my issues.
Thanks!
Kristen,
So, how’s the research going on this request?
Thanks!
Shams
David should be getting in contact with you soon about your question, if he hasn’t already – he’s the author of the particular post, and the consultant who is doing the research. We’ve been pretty busy lately, but hopefully he will have some information for you soon.
I think this code might solve an issue I have. We are an outplacement firm. I created a custom entity called “candidate” to differentiate them from contacts. I think I set up the entity relationships correctly but if you try to schedule a phone call to a candidate the telephone number does not populate. Although i was able to design the system, I do not write code of any kind. Would a variation of the above work in an OnChange event for the telephone number in the custom entity…or would it cause a problem with native accounts and contacts? Could it look for the name of two different fields (telephone1 and new_telephone1 which is the field for the custom entity)and pick whichever one has data? Any help you could give with this nagging issue would be hugely appreciated. Thank you.
This is really useful!
Did you ever solve the lookup-request?
I have solved the lookup-issue. You can disregard my previous comment.
It’s paste on the lookup onchange or form onload. Thanks.