Retrieve related data field via CRM lookup field

Hello Experts,

Environment:  Microsoft Dynamics CRM 4.0
Entity:  Contracts

When opening an existing Contract, I want to display in an ALERT, the state where the Customer is located.  So, I open a existing Contract and bam, "FL" shows up in an ALERT statement for Clients where the CustomerId lookup field's corresponding Account record has "FL" for the value in the "address1_stateorprovince" field.  

What's the best way to do this?  I've been able to successfully alert the CustomerID, Name, & Typename as shown in the link below, under the lookup value section for example #1.

http://sites.google.com/site/ranjitclub/working-with-datatypes-in-javascript
2_under_parAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Feridun KadirPrincipal ConsultantCommented:
To get the address1_stateorprovince field you will need to call the CRM webservices to retrieve the record for the customer.

In the contract form you can use JavaScript (as you have found) to alert the contents of any field that is shown on the form. However, the state is not on the form but is in the record for the customer.

The CRM 4.0 SDK has some information "Accessing Microsoft Dynamics CRM Web Services".

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Feridun KadirPrincipal ConsultantCommented:
This article may help you get started: http://go4answers.webhost4life.com/Example/access-lookup-fields-data-javascript-117618.aspx

You'll need to amend to suit your instance; the structure is as follows:

an authentication section
a query that specifies the columns to return (in your case just the state)
construct and send the request to the web services
processing the result
2_under_parAuthor Commented:
Thanks!  I got busy and forgot to follow up with this thread.  There is a little bit extra posted below, in that I retrieved more fields than I needed, but this got it done.  Var named 'Mess' displays in an alert.

if(crmForm.FormType == 4 ) 
 {    // Create Form = 1

var Mess ='';
  var AccountLookUp = crmForm.all.customerid.DataValue;
 var authenticationHeader = window.opener.GenerateAuthenticationHeader();

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;
var Name=AccountLookUp [0].name;

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'>",  
       authenticationHeader,
        "<soap:Body>",  
        "   <RetrieveMultiple xmlns='http://schemas.microsoft.com/crm/2007/WebServices'>",  
       "<query xmlns:q1='http://schemas.microsoft.com/crm/2006/Query' xsi:type='q1:QueryByAttribute\'>",
// Query the customeraddress entity.
 "<q1:EntityName>account</q1:EntityName>",
 // Set the columns you want to return.
"<q1:ColumnSet xsi:type='q1:ColumnSet'>",
 "<q1:Attributes>",
 "<q1:Attribute>address1_name</q1:Attribute>",
 "<q1:Attribute>address1_line1</q1:Attribute>",
"<q1:Attribute>address1_line2</q1:Attribute>",
 "<q1:Attribute>address1_line3</q1:Attribute>",
"<q1:Attribute>address1_city</q1:Attribute>",
 "<q1:Attribute>address1_stateorprovince</q1:Attribute>",
"<q1:Attribute>address1_postalcode</q1:Attribute>",
 "<q1:Attribute>address1_country</q1:Attribute>",
"<q1:Attribute>address1_telephone1</q1:Attribute>",
"<q1:Attribute>address1_addresstypecode</q1:Attribute>",
 "<q1:Attribute>address1_shippingmethodcode</q1:Attribute>",
"<q1:Attribute>address1_freighttermscode</q1:Attribute>",
 "<q1:Attribute>telephone1</q1:Attribute>",
 "</q1:Attributes>",
 "</q1:ColumnSet>", 
 // Specify the attribute that you are querying on.
 "<q1:Attributes>", 
 "<q1:Attribute>name</q1:Attribute>",
 "</q1:Attributes>", 
 // Set the value of the attribute using the customerid 
 // value of the case record.
 "<q1:Values>",
 "<q1:Value xsi:type='xsd:string'>",
Name ,
 "</q1:Value>",
 "</q1:Values>",
 "</query>" , 
"   </RetrieveMultiple>",  
        "</soap:Body>",  
        "</soap:Envelope>" 
        ].join("");  

// Create an instance of an XMLHTTP object.
 var xmlHttpRequest = new ActiveXObject("Msxml2.XMLHTTP");
// Configure the XMLHttp object for the 
// Microsoft CRM Web services.
 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
  );
// Send the XMLHttp request.
 xmlHttpRequest.send(Accountxml );
// Capture the XMLHttp response in XML format.
 var resultAccountxml  = xmlHttpRequest.responseXML;

// Create an XML document that you can parse.
 var oXmlDoc = new ActiveXObject("Microsoft.XMLDOM");
 oXmlDoc.async = false; 
// Load the document that has the results.
 oXmlDoc.loadXML(resultAccountxml.xml);
 // Get only the BusinessEntity results.

 var businessEntities = oXmlDoc.getElementsByTagName('BusinessEntity');

  var errorCount = resultAccountxml.selectNodes('//error').length; 
    if (errorCount != 0) 
    { 
     var msg = resultAccountxml.selectSingleNode('//description').nodeTypedValue; 
     alert(msg); 
    }  


for (i=0;i < businessEntities.length;i++)
 {
if (businessEntities[i].selectSingleNode('./q1:address1_name') != null)
{
var name =businessEntities[i].selectSingleNode('./q1:address1_name').nodeTypedValue;
crmForm.all.address1_name.DataValue=name;
}

if(businessEntities[i].selectSingleNode('./q1:address1_stateorprovince')!=null)
{
var Mess =businessEntities[i].selectSingleNode('./q1:address1_stateorprovince').nodeTypedValue;
alert(Mess);
}



}
}
}

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Enterprise Software

From novice to tech pro — start learning today.