6. Mapping MSCRM fields from Opportunity Product to Quote Product

MSCRM has an oversight in the default Opportunity-Quote-Order process in taking fields entered at the Opportunity Product stage and having these mapped to any resulting Quote Product or Order Product records. 

This means that any fields (such as the field we added to the Opportunity Product screen to track the Product Number of the Product entered from main post in this category) are not mapped over to the resulting Quote Products on our Quote.

Opportunity Products

List of Opportunity Products

Quote Products

List of Quote Products mapped from an Opportunity

This obviously would be confusing to the end-user! 

However whilst MSCRM does not directly list the relationship between a Quote Product and Opportunity Product within the available Customisation User Interface – this relationship (and crucially it’s Mappings) are available to us. 

The following steps allow us to add or remove mappings to this relationship, and ensure that our custom Product Number field is mapped across. 

STEP 1 – Run a SQL Query against the MSCRM Database (typically X_MSCRM where X is your Organisation 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:

Relationship Mappings from Opportunity Product to Quote Product

Relationship Mappings from Opportunity Product to Quote Product

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)

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.

16 Responses to 6. Mapping MSCRM fields from Opportunity Product to Quote Product

  1. Daniel Turtledove says:

    Hi.
    This help was Brilliant! It worked perfectly for what I needed to do. Thank You!
    I now want to do exactly the same but I want to pull in 6 custom fields from the product Entity in to the opportunityproduct Entity.
    I took your 3 SQL examples and changed the source and target names to the names to be :
    TargetEntityName=’opportunityproduct AND SourceEntityName=’product’
    and
    TargetEntityName=’quotedetail’ AND SourceEntityName=’product’
    but the result returned nothing in both instances.
    Please advise where I have gone wrong.
    Alternatively, are you able to send me the GUIDs of these two relationships between the (1) product and the quotedetail and (2) product and opportunityproduct.
    I am in desperate need of an answer.
    Thanks
    Daniel.

  2. Hi there Daniel,

    Unfortunately MSCRM does not give us the option to edit the mappings between Product and the ‘line type’ entities for Opportunity Product, Quote Product, Order Product and Invoice Product – and so we cannot bring down fields from the Product screen by the normal Customisation methods.

    We can however do this via Javascript at the point of entry for your solution (typically Quote Product or Opportunity Product, but in some cases Order Product), such that when the user specifies the Product, the client-side Javascript retrieves certain fields from the Product Record via a call to the MSCRM Web Service and then populates certain fields on the Quote Product or Opportunity Product screen accordingly.

    Once populated, we can use the mappings technique outlined in this post to preserve the values from the initial point of entry to the Quote Product then to Order Product and to Invoice Product depending on the business process.

    Best bet is to look at the earlier post for ‘2. Adding Opportunity Products’ – this gives an example of how to pull down the default Unit of Measure and Product Number field into the Opportunity Product screen upon the user selecting a particular Product for the Opportunity.

    Hope that helps, Paul.

  3. Thank you for this post. This helped me a lot as we have created new fields in CRM for Leads, Accounts, Contacts and Opportunities.

    I edited the default mappings that came with the system and added the mappings for my custom attributes. Now when a Lead is qualified, the proper values are passed over to Contact, Account and Opportunity records.

  4. Ralf Ramackers says:

    Would it be possible to map the Order Products to a new custom entity e.g. Entity Products?

  5. Alex says:

    Hi,

    This is exactly what I need for what I am trying to do. However when I use the Id returned by the query into the URL as instructed i get an error saying the entitymap with that Id does not exist. Any ideas will be greatly appreciated. Thanks.

  6. Mouhanad Alkhaldi says:

    Thank you very much , you make my day !

  7. Anderson Alves says:

    It works, thank you!

  8. laytagon says:

    HI,

    I have tested the quote detail to sales order detail mapping. and it works. Thank a lot.
    I would be really appreciated if you answer my question.
    The company i am working for normally does not use “unrecommended” practices. So what i want to know is whether this is a recommended practice.

    Thanks

  9. Eugenia says:

    Thanks a million, that’s very usefull!

  10. Eric says:

    Hello,
    This tool is great and easy to list and modify EXISTING mapping (visible and hidden).
    BUT how to initiate a mapping betwen Salesorderdetail and a new entity detail ?

    I had to create a new entity which is mapped to salesorder entity. This is working fine. The problem is that I don’t know how to create the mapping of the détails of these 2 entities, in that case Products.

    Any idea about how to initiate this mapping at detail level ?

    Thanks

  11. Pingback: Berechung Mehrwertsteuersatz 2011 im CRM | Gate4

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s