Thinkcrm

A Matt Yates blog about all things CRM!

CRM 2011 – Access hidden mappings via SQL or REST

About a year ago I ran into a little bit of trouble whilst building sales demo regarding setting mappings between Opportunity Product, Quote Product, Order Product and Invoice Product. These mappings do not exist within the Microsoft Dynamics CRM interface and can only be navigated directly through a URL. This is very strange as the page from the resulting URL looks exactly the same as the out of the box mapping pages!

To access these mappings you will need to obtain the relevant mapping ID from the database, you can choose from the following SQL Queries or use REST (explained at the end of this post):

Access to mappings via SQL:

Step 1:

Select * from entitymapbase where targetentityname =

‘opportunityproduct’ (opportunity)
‘quotedetail’ (quotes)
‘salesorderdetail’ (orders)
‘invoicedetail’ (invoices)

Step 2:

From the results, find the relevant mapping between SourceEntityName and TargetEntityName and copy the GUID value of the EntityMapId for that row.

Step 3:

Append the EntityMapId to the URL below:

http://CRMSERVERNAME/Tools/SystemCustomization/Relationships/Mappings/mappingList.aspx?mappingId=
Now the resolution to accessing hidden mapping via SQL queries works brilliantly for on-premise versions of both Microsoft Dynamics CRM v4 and v2011 but not so much for Microsoft Dynamics CRM Online. As you all know, the ability to run these queries on a Microsoft Dynamics CRM Online implementation is practically impossible.

I thought I had hit a stumbling block until my colleague Chris Barnard pointed out that we can access all of the information we need using the CRM REST endpoint.

Access to mappings via REST:

Step 1:

Navigate to the REST endpoint (CRM –> Customisations –> Developer Resources)

Step 2:

Append ‘EntityMapSet’ to the end of the URL

(e.g. https://EXAMPLEORG.crm.dynamics.com/XRMServices/2011/OrganizationData.svc/EntityMapSet)

Step 3:

You may notice that the page appears as an RSS feed rather than displaying the XML we are after. If this is the case then you can change this by navigating to:

Internet Options –> Content à Feeds and Web Slices –> Settings –> Untick ‘Turn on feedreading view’

Feed View:


XML View:

Note: You may need to re-launch the page for this change to take effect.

Step 4: (same as SQL)

From the results, find the relevant mapping between SourceEntityName and TargetEntityName and copy the GUID value of the EntityMapId for that row.

Step 5:

Append the EntityMapId to the URL below:

http://CRMSERVERNAME/Tools/SystemCustomization/Relationships/Mappings/mappingList.aspx?mappingId=
This will gives you access to the hidden mappings for these areas of CRM and is particularly helpful if you are customising one or more of these screens as part of building a sales process.

Advertisements

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

%d bloggers like this: