Wednesday, December 1, 2010

Map Custom Attributes from Opportunity Product to Quote Product to Order Product and so on in Microsoft Dynamics CRM Online 2011 and 4.0

The process of mapping custom attributes in Microsoft Dynamics CRM Online from opportunity product to quote product to order product to invoice product is very similiar in CRM Online as it is in on-premise CRM.  There is just one problem that needs to be overcome, and that is getting the GUID for the entitymap entities from the CRM backend to plug into the URL that is required to add custom attributes to the mappings.  

If you want to know how this works in on-premise using SQL management studio please review my older  blogpost at: http://mileyja.blogspot.com/2008_09_01_archive.html.

There are some issues that arise out of using CRM Online as you cannot access the SQL Server your instance resides on, this means you cannot easily get the GUIDs you need from the backend unless you use web services to retrieve them.  I know the metadata browser in 4.0 does not show the entitymap entity in the menu as entity you can drill down on, but believe me you can access this entity using a retrieve or retrievemultiple call using SOAP in jscript through the 2007 web services (which were also conveniently left in place for backward compatibility in CRM 2011).  The other issue is that there is a minor change to the URL you need to use to access the custom mapping as CRM Online follows the IFD url structure for where the orgname is located.

1. First, you will need this jscript that you can attach to any entity form event in CRM to get the guid when that event is executed in order to retrieve the unique entitymapid for your organization to map from salesorderdetail (Order Product) to invoicedetail (Invoice Product).  Since this only needs to be done once, you can just attach it to an event and use the preview form function to retrieve the GUIDs and then remove the jscript without ever publishing it.

First in 4.0 Online or On-Premise:

//create SOAP envelope
var xmlSoapHeader= "" +
"<?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\">";
var xmlAuthHeader = GenerateAuthenticationHeader();
var xmlSoapBody = "<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\" xmlns=\"http://schemas.microsoft.com/crm/2007/WebServices\">" +
"      <q1:EntityName>entitymap</q1:EntityName>" +
"      <q1:ColumnSet xsi:type=\"q1:ColumnSet\">" +
"        <q1:Attributes>" +
"          <q1:Attribute>entitymapid</q1:Attribute>" +
"          <q1:Attribute>sourceentityname</q1:Attribute>" +
"          <q1:Attribute>targetentityname</q1:Attribute>" +
"        </q1:Attributes>" +
"      </q1:ColumnSet>" +
" <q1:Attributes>" +
" <q1:Attribute>sourceentityname</q1:Attribute>" +
" </q1:Attributes>" +
" <q1:Values>"+
" <q1:Value xsi:type=\"xsd:string\">salesorderdetail</q1:Value>" +
" </q1:Values>" +
"    </query>" +
" </RetrieveMultiple>" +
"  </soap:Body>" +
"</soap:Envelope>" +
"";


var xmlt = xmlSoapHeader + xmlAuthHeader + xmlSoapBody;
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", xmlt.length);
xmlHttpRequest.send(xmlt);
var resultXml = xmlHttpRequest.responseXML.xml;

alert(resultXml);

Now in CRM 2011 Online or On-Premise you can use the same script, but it must be encapsulated in a jscript library as a function that you call from the event:

function testfunc()
{
//create SOAP envelope
var xmlSoapHeader= "" +
"<?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\">";
var xmlAuthHeader = GenerateAuthenticationHeader();
var xmlSoapBody = "<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\" xmlns=\"http://schemas.microsoft.com/crm/2007/WebServices\">" +
"      <q1:EntityName>entitymap</q1:EntityName>" +
"      <q1:ColumnSet xsi:type=\"q1:ColumnSet\">" +
"        <q1:Attributes>" +
"          <q1:Attribute>entitymapid</q1:Attribute>" +
"          <q1:Attribute>sourceentityname</q1:Attribute>" +
"          <q1:Attribute>targetentityname</q1:Attribute>" +
"        </q1:Attributes>" +
"      </q1:ColumnSet>" +
" <q1:Attributes>" +
" <q1:Attribute>sourceentityname</q1:Attribute>" +
" </q1:Attributes>" +
" <q1:Values>"+
" <q1:Value xsi:type=\"xsd:string\">salesorderdetail</q1:Value>" +
" </q1:Values>" +
"    </query>" +
" </RetrieveMultiple>" +
"  </soap:Body>" +
"</soap:Envelope>" +
"";


var xmlt = xmlSoapHeader + xmlAuthHeader + xmlSoapBody;
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", xmlt.length);
xmlHttpRequest.send(xmlt);
var resultXml = xmlHttpRequest.responseXML.xml;

alert(resultXml);
}


2. Now run the form event you attached the jscript to.

The alert message is a little bit difficult to look at when the script executes, but if you use fiddler to capture your sessions, it will show you the needed entitymapid GUID quite nicely:



3.  Now you can piece together the URL for the mapping interface for the custom attribute mapping.  The syntax to use is:

 https://myorgsubdomain.crm.dynamics.com/Tools/SystemCustomization/Relationships/Mappings/mappingList.aspx?mappingId=entitymapidGUID

Here is a syntax example using the guid above:

https://myspecialcrminstance.crm.dynamics.com/Tools/SystemCustomization/Relationships/Mappings/mappingList.aspx?mappingId=EB562CC6-991B-DF11-87A9-02BF0A0679DE

NOTE:  Since the GUIDs are unique to each organization of CRM, the GUID in the URL won't actually work except in my own personal organization.

4. Navigate to the URL using your browser and below is the resulting screen.  You should see a similiar mapping dialog when you click "New" on the screen.  I chose to show you the end result from 2011 in the following screenshot for the mapping screen, it looks only a bit different in 4.0 Online and functions the same way.



Thats all there is to it, just add you mappings as needed.  If you need to get the other GUIDs for the other detail (product) entities in the sales process chain you just change the entity types in the script and re-run it.


This post is provided as-is and implies no warranty, Jamie Miley does not assume any responsibility for problems arising from the use of this information.

10 comments:

  1. Are the custom mappings retained with an export of the entities (4.0) or the solution (2011)?

    ReplyDelete
  2. I have heard of issues pertaining to this for 2011 with solutions. I would remove the mappings prior to export and then re-add them after import for safety.

    ReplyDelete
  3. Jamie
    Awesomely clever as always!
    I want to adapt this to do something similar for opportunity > quote but in the script I only see the source entity name, not the target. Is this because there is always only one map for each entity in this (usually)linear process of opp > quote > order > invoice so we don't need to specify both in order to find the mapping GUID?

    ReplyDelete
  4. Very useful Jamie, thanks. Adam, all you need to do is change 'salesorderdetail' here:
    " salesorderdetail" +

    to 'opportunityproduct' to see the 3 relationships from opportunity product to quote product / sales order product / invoice product

    or 'quotedetail' to see the relationship to sales order product

    ReplyDelete
  5. All,

    Thanks Jamie for the direction to go on this. I think I've found an easier way to get this data (at least for me). Awhile back I installed the CRM2011 OData Query Designer solution (http://crm2011odatatool.codeplex.com/) in our online instance. I used that to build a query for an EntityMapSet where "Source Entity" = quotedetail. That returned an XML document containing the EntityMapID. From there I used Jamie's URL and plugged in our org and the EntityMapID and that got me where I needed to go.

    The OData Query Designer is a great tool for designing queries to use in your plugins or javascript.

    Good luck all!

    Howard

    ReplyDelete
  6. Hello
    When testing your client, do you need to be on the same server that hosts the web service ?

    ReplyDelete
    Replies
    1. No, otherwise it would never work for the CRM Online scenario, this method works for CRM Online also.

      Delete
  7. No, otherwise it would never work for the CRM Online scenario, this method works for CRM Online also.

    ReplyDelete
  8. Jamie,

    But it doesn't work with CRM 2015 on premises, from Opportunity Product to Quote Product.
    Can you please help me ?

    ReplyDelete
  9. Hello, how can I use this solution to map Product to Order Product?

    ReplyDelete