This documentation is archived and is not being maintained.

Retrieving Data from Related Records

Dynamics
banner art

[Applies to: Microsoft Dynamics CRM 4.0]

Find the latest SDK documentation: CRM 2015 SDK

Use the Microsoft Dynamics CRM Web Service RetrieveMultiple message to retrieve data from related records using JScript. This sample shows how to retrieve CustomerAddress records for a related customer (account or contact) record.

Microsoft Dynamics CRM Quote, Order, and Invoice entity forms include a Look Up Address... button that will retrieve any available addresses for the customer defined in the record. The user can select which address to add from any available addresses.

Microsoft Dynamics CRM Look Up Address dialog box

The data from the customer address record will then be copied into the Quote, Order, or Invoice record form. This sample replicates some of this feature using the case entity form. This same functionality could be applied to any other entity with a customizable form and a lookup field to a customer record. The same code works regardless of when the lookup field only references an account or contact record.

The custom Look up Address dialog in this sample will resemble this:

Custom Look Up Address Dialog

Note    It is important to understand how Microsoft Dynamics CRM stores customer address information. By default, Account and Contact forms display address fields for one address. Data for two separate addresses can be displayed in the form for these entities. Data for these addresses is stored with the respective customer record. However, when customer records are created, two corresponding customer address records are also created and are kept synchronized with the data in the customer record. This lets users add addresses using the More Addresses area under details in the customer record form.

Important    The account or contact entity Address Name field must contain data. To support this functionality, set the requirement level for the Address Name field to Business Required.

Case Entity

This sample uses the case entity with the following custom attributes defined and present in the published case entity form:

NameDisplay NameTypeMaximum Length
new_addressnameAddress Namenvarchar200
new_cityCitynvarchar50
new_countryregionCountry/Regionnvarchar50
new_stateprovinceState/Provincenvarchar50
new_street1Street 1nvarchar50
new_street2Street 2nvarchar50
new_zippostalcodeZIP/Postal Codenvarchar20

Note   For more information about how to create and use custom attributes, see the Help topics "Create or edit attributes for an entity" and "Edit the main form for an entity." These topics are available at: rc.crm.dynamics.com/rc/regcont/en_us/op/help/ui_cust_Entity.htm.

Custom Address Lookup page

The page that will be displayed when the Look Up Address button is clicked should be put in the ISV folder of the Microsoft Dynamics CRM Web site. Add a uniquely named folder to separate the page from other solutions that may be in the ISV folder. For this sample the page will be available at /ISV/AWC_AddressLookup/AWC_AddressLookup.htm.

Note    Because this page depends on the window.opener object, it will not work unless the page exists on the same domain as the Microsoft Dynamics CRM Web application. This sample has only been tested as a page in the /ISV folder of the Microsoft Dynamics CRM Web application.

AWC_AddressLookup.htm

This HTML page has a script element that defines one variable and two functions.

objectdescription
var crmFormCaptures a reference to the Case record form using the window.opener object
function getAddressesCalled when the page loads, this function uses an XMLHttp object to access the Microsoft Dynamics CRM Web service. It uses the RetrieveMultiple message to request all the customeraddress records that have a parentid value that matches the customerid value of the Case record form.

This function also generates a table to display the results within the page.

function setAddressCalled when the user selects one of the customeraddress records retrieved from the results table. It copies the values from the table to the Case record form.

<html>
<head>
<title>
AWC Custom Look Up Address -- Web Page Dialog Box
</title>

<script language="javascript">
// Use the window.opener to obtain a reference to the crmForm.
var crmForm = window.opener.document.crmForm;

function getAddresses()
{
// Get a reference to the Customer associated with the Case record.
 var customerid = crmForm.all.customerid.DataValue[0].id;
 // Use the GenerateAuthenticationHeader() method 
 // available from the CRM form to generate the Soap header text.
 var authenticationHeader = window.opener.GenerateAuthenticationHeader();

 // Define the SOAP XML to access Microsoft Dynamics CRM Web service.
 var xml = "<?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>" + 
 // Specify the RetrieveMultiple message.
 "<RetrieveMultiple xmlns="+
 "\"http://schemas.microsoft.com/crm/2007/WebServices\">" + 
 // Specify that this is a QueryByAttribute query.
 "<query xmlns:q1="+
 "\"http://schemas.microsoft.com/crm/2006/Query\" "+
 "xsi:type=\"q1:QueryByAttribute\">" + 
 // Query the customeraddress entity.
 "<q1:EntityName>customeraddress</q1:EntityName>" + 
 // Set the columns you want to return.
 "<q1:ColumnSet xsi:type=\"q1:ColumnSet\">" + 
 "<q1:Attributes>" + 
 "<q1:Attribute>customeraddressid</q1:Attribute>" + 
 "<q1:Attribute>name</q1:Attribute>" + 
 "<q1:Attribute>addresstypecode</q1:Attribute>" + 
 "<q1:Attribute>line1</q1:Attribute>" + 
 "<q1:Attribute>line2</q1:Attribute>" + 
 "<q1:Attribute>line3</q1:Attribute>" + 
 "<q1:Attribute>city</q1:Attribute>" + 
 "<q1:Attribute>stateorprovince</q1:Attribute>" + 
 "<q1:Attribute>postalcode</q1:Attribute>" + 
 "<q1:Attribute>country</q1:Attribute>" + 
 "</q1:Attributes>" + 
 "</q1:ColumnSet>" + 
 // Specify the attribute that you are querying on.
 "<q1:Attributes>" + 
 "<q1:Attribute>parentid</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\">"+
 customerid+
 "</q1:Value>" + 
 "</q1:Values>" + 
 "</query>" + 
 "</RetrieveMultiple>" + 
 "</soap:Body>" + 
 "</soap:Envelope>";
// 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", xml.length
  );
// Send the XMLHttp request.
 xmlHttpRequest.send(xml);
// Capture the XMLHttp response in XML format.
 var resultXml = 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(resultXml.xml);
 // Get only the BusinessEntity results.
 var businessEntities = oXmlDoc.getElementsByTagName('BusinessEntity');
 // Start to define the table and column headers where 
 // the results will be displayed.
 var resultsTable = "<table><tr><th nowrap><nobr />"+
 "</th><th nowrap>Name</th><th nowrap>Type</th>"+
 "<th nowrap>Street 1</th><th nowrap>City</th nowrap>"+
 "<th>Country</th><th nowrap>State</th>"+
 "<th nowrap>Postal Code</th></tr>";
 
 var addressCount = 0;
// Loop through the BusinessEntity elements.
 for (i=0;i < businessEntities.length;i++)
 {
// There can be some customeraddress records that do not represent a 
// real address. Those records will not have a value for the address name.
// Ignore those records where the address name is null.
  if (businessEntities[i].selectSingleNode('./q1:name') != null)
   {
    // Start a new row for the results table.
    resultsTable += "<tr>";

    var NameElement = businessEntities[i].selectSingleNode('./q1:name');
    // We already know it is not null.
    var NameValue = NameElement.text;
          
    var TypeElement = businessEntities[i].selectSingleNode(
     './q1:addresstypecode/@name'
      );
    // Check whether the value is null before setting it.
    var TypeValue =(TypeElement == null)? " ": TypeElement.text;
          
    var Street1Element = businessEntities[i].selectSingleNode('./q1:line1');
    // Check whether the value is null before setting it.
    var Street1Value = (Street1Element == null)? " " : Street1Element.text;
          
    var Street2Element = businessEntities[i].selectSingleNode('./q1:line2');
    // Check whether the value is null before setting it.
    var Street2Value = (Street2Element == null)? " " : Street2Element.text;
          
    var Street3Element = businessEntities[i].selectSingleNode('./q1:line3');
    // Check whether the value is null before setting it.
    var Street3Value = (Street3Element == null)? " " : Street3Element.text;
          
    var CityElement = businessEntities[i].selectSingleNode('./q1:city');
    // Check whether the value is null before setting it.
    var CityValue = (CityElement == null)? " " : CityElement.text;
          
    var CountryElement = businessEntities[i].selectSingleNode('./q1:country');
    // Check whether the value is null before setting it.
    var CountryValue = (CountryElement == null)? " " : CountryElement.text;
          
    var SPElement = businessEntities[i].selectSingleNode('./q1:stateorprovince');
    // Check whether the value is null before setting it.
    var SPValue = (SPElement == null)? " " : SPElement.text;
          
    var PCElement = businessEntities[i].selectSingleNode('./q1:postalcode');
    // Check whether the value is null before setting it.
    var PCValue = (PCElement == null)? " " : PCElement.text;
          
    // Set the value of the cells in the row for the record.
          
    // The first column includes a button that calls the setAddress function.
    resultsTable += "<td nowrap><input type='button' value='OK'";
    resultsTable += " onclick='setAddress();'/></td>";
    resultsTable += "<td nowrap>"+NameValue+"</td>";
    resultsTable += "<td nowrap>"+TypeValue+"</td>";
    resultsTable += "<td nowrap><span>"+Street1Value+"</span>";
    // The grid will not display these columns but we want to be able
    // to access them when we update the fields in the case form.
    resultsTable += "<span style='display:none;'>"+Street2Value+"</span>";
    resultsTable += "<span style='display:none;'>"+Street3Value+"</span></td>";
    resultsTable += "<td nowrap>"+CityValue+"</td>";
    resultsTable += "<td nowrap>"+CountryValue+"</td>";
    resultsTable += "<td nowrap>"+SPValue+"</td>";
    resultsTable += "<td nowrap>"+PCValue+"</td>";

    // End the row for the results table.
    resultsTable += "</tr>";
    // Increment the count of addresses retrieved.
    addressCount++;
   }
 }
// Close the results table.
 resultsTable += "</table>";

 // Get a reference to the existing results DIV in the document.
 var results = document.getElementById("results");

 // Check whether any results were returned.
 if (addressCount > 0)
 {
  // Set the message within the results DIV element.
  results.innerHTML = resultsTable;
  // Adjust the height of the window so all the results are visible.
  var addedLines = addressCount * 25;
  window.resizeTo(600,100+addedLines);
 }
else
 {
  // When there are no results, display an appropriate message
  // and a button to close the window.
  var msg = "<div style='text-align:center;'>"+
  "<p>This Customer does not have any Addresses.</p>"+
  "<input type='button' value='Close' "+
  "onclick='window.close();' /></div>";
  results.innerHTML = msg;
 }
}

function setAddress()
{
 // This function is called when the user selects
 // one of the addresses returned.

 // Get a reference to the TD element parent of the button.
 var addressRowButtonColumn = event.srcElement.parentElement;
 // Get a reference to the TR element parent of the TD element.
 var addressRow = addressRowButtonColumn.parentElement;

 // Set variables based on the cells of the TR element.
 var addressName = addressRow.cells(1).innerText;
 var streetsCell = addressRow.cells(3);
 // These values are in SPAN elements within the TD element for this column.
 // Only the first item is visible in the column.
 var Street1 = streetsCell.children(0).innerText;
 var Street2 = streetsCell.children(1).innerText;
 var Street3 = streetsCell.children(2).innerText;
 var City = addressRow.cells(4).innerText;
 var StateOrProvince = addressRow.cells(5).innerText;
 var Country = addressRow.cells(6).innerText;
 var PostalCode = addressRow.cells(7).innerText;

 // Use the reference to the crmForm to set the values.
 crmForm.all.new_addressname.DataValue = addressName;
 crmForm.all.new_street1.DataValue = Street1;
 crmForm.all.new_street2.DataValue = Street2;
 crmForm.all.new_street3.DataValue = Street3;
 crmForm.all.new_city.DataValue = City;
 crmForm.all.new_stateprovince.DataValue = StateOrProvince;
 crmForm.all.new_countryregion.DataValue = Country;
 crmForm.all.new_zippostalcode.DataValue = PostalCode;
 // Close the window.
 window.close();
}
</script>
<style>
body 
{
 background-color:#eaf3ff;
 font-family: Tahoma, Verdana, Arial;
 margin:0px;
}
div
{
 width:100%;
}
table
{
 border-collapse:collapse;
 border:solid 1px #6699CC;
 width:100%;
}
th
{
 background-color:#f0f0f0;
 border-bottom:solid 1px #6699CC;
 font-size:11px;
 padding-left:3px;
 padding-right:3px;
 border-right:solid 1px #6699CC;
}
td
{
 background-color:white;
 border-bottom:solid 1px #C4DDFF;
 font-size:11px;
 padding-left:3px;
 padding-right:3px;
 OVERFLOW: hidden;   
}
input
{
 cursor: hand;
 border:solid 1px black;
}
</style>

</head>
<body onload="getAddresses();">
 <div id="results" />
</body>
</html>

Look Up Address Button

Look Up Address Button on the Case Entity Form

To define the Look Up Address button for the Case entity form, export the ISV.Config and edit the incident element within the /ImportExportXml/IsvConfig/configuration/Entities node.

Note   For more information about how to import and export customizations see the Help topics "Export Customizations and Configurations" and "Import Customizations and Configurations." These topics are available at rc.crm.dynamics.com/rc/regcont/en_us/op/help/ui_cust_export.htm and rc.crm.dynamics.com/rc/regcont/en_us/op/help/ui_cust_import.htm.

The incident element will define a Button within the toolbar for the Case entity form. After you have configured the Button, import the ISV.Config to enable the button.

Note   Before you can see this button, you must enable the clients that will display custom menus and toolbars. For more information, see Enabling Your Customizations.

The button is configured to be valid for both Create and Update forms so that it is available before and after the record is saved for the first time. This sample also restricts the button from being available while the user is working offline with Microsoft Dynamics CRM for Outlook because, without access to the server, the user would be unable to access the custom address lookup page in the ISV folder.

The only requirement is that the Case record must be related to a Customer. The Button uses the JavaScript attribute to set the script to be executed when the button is clicked. The JavaScript validates that a Customer is associated with the record before you try to open the AWC_AddressLookup.htm page.

Note   For more information about how to configure button elements by using ISV.Config, see Editing the ISV.Config.

This is the XML that defines the Look Up Address Button:

<Entity name="incident">
 <ToolBar>
  <Button 
   AvailableOffline="false"
   ValidForCreate="1" 
   ValidForUpdate="1" 
   JavaScript="
   if (crmForm.all.customerid.DataValue != null)
   {
    window.open(
     '/ISV/AWC_AddressLookup/AWC_AddressLookup.htm',
     '_blank',
     'height=100 width=600 left=100 top=100 resizable','false'
     );
   }
   else
   {
    alert(
     'You must set an Customer before you can look up an Address.'
     );
   }"
   >
   <Titles>
    <Title LCID="1033" Text="Look Up Address..." />
   </Titles>
   <ToolTips>
    <ToolTip 
     LCID="1033" 
     Text="Select an address from the existing addresses for this customer." 
     />
   </ToolTips>
  </Button>
 </ToolBar>
</Entity>

Testing the Script

  1. Create the seven custom address attributes described earlier for the case entity, add them to the form, and publish the case entity.
  2. Create a folder named AWC_AddressLookup in the ISV folder of your Microsoft Dynamics CRM Web site
  3. Create a HTML page titled AWC_AddressLookup.htm in the AWC_AddressLookup folder that you created. Paste the HTML shown earlier into the page.
  4. Export the ISV.Config and add the toolbar configuration to the incident element as described earlier
  5. Import the ISV.Config to add the toolbar. Open an existing Case record to confirm that the Look Up Address button is displayed.
  6. Open a new Case record in Microsoft Dynamics CRM. Do not specify a customer.
  7. Click the Look Up Address button. Confirm that the message "You must set a Customer before you can lookup an Address." is displayed.
  8. Look up a customer record to set the value of the Customer field. Select a customer who has no addresses.
  9. Click the Lookup Address button. Confirm that the AWC_AddressLookup.htm page is displayed and says "This Customer does not have any addresses".
  10. Look up a Customer record to set the value of the Customer field. Select a customer who has one or more addresses.
  11. Click the Lookup Address button. Confirm that the AWC_AddressLookup.htm page displays a list of available addresses.
  12. Click OK in one of the rows in the page to select an address. The window will close. Confirm that the address information has been added to the address fields that you created in step 1.

© 2010 Microsoft Corporation. All rights reserved.


Show: