Thursday, October 21, 2010

Mapping MSCRM fields from Opportunity Product to Quote Product

For one of our customer we added some custom attributes in Opportunity product and like to add its mappings to quote product and same with quote product to order product so that Opportunity-Quote-Order process can work as expected and the values of the custom attributes in opportunity product could transfer automatically in Quote product.

Now the Big Question :

MSCRM does not directly list the relationship between a Quote Product and Opportunity Product within the available Customization User Interface.

Answer: a little googling and we got this. A very beautiful and simple Four step process:

STEP 1 –

Run a SQL Query against the MSCRM Database (typically Organization_MSCRM where Organization is the Name for the MSCRM Deployment) to determine the GUID Id of the Relationship between the Quote Product Entity and the Order Product Entity.

SELECT    EntityMapId
FROM EntityMapBase
WHERE TargetEntityName='quotedetail' AND SourceEntityName='opportunityproduct'




STEP 2 – Take the output of this SQL Query and insert this into the following URL:



http://[x]/Tools/SystemCustomization/Relationships/Mappings/mappingList.aspx?mappingId=[y]


Where [x] is the URL address of the MSCRM Deployment in question and [y] is the GUID Id output of the SQL Query



STEP 3 – This will present the Relationship Mapping screen for mapping attributes from the Opportunity Product entity to the Quote Product entity.



From here, any number of mappings can be added to define how the Products attached to an Opportunity are mapped across when a Quote is added to the Opportunity. (obviously any changes or additional mappings will need to be published in usual MSCRM fashion)



STEP 4 –  Enjoy



The same steps can also be used for controlling how fields are mapped across from Quotes to Orders in a similar fashion.



SELECT    EntityMapId
FROM EntityMapBase
WHERE TargetEntityName='salesorderdetail' AND SourceEntityName='quotedetail'



And similarly how fields are mapped from Order Products to Invoice Products.



SELECT    EntityMapId
FROM EntityMapBase
WHERE TargetEntityName='invoicedetail' AND SourceEntityName='salesorderdetail'


This is a useful technique when working with any custom fields in a Opportunity-Quote-Order process in MSCRM.