Thinkcrm

A Matt Yates blog about all things CRM!

Tag Archives: SQL

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.