Dynamics CRM–Retrieving optionset values from the database

SELECT o.name,
        a.Value,
        l.label
FROM AttributePicklistValueView a
      JOIN OptionSetView o ON o.OptionSetId = a.OptionSetId
      JOIN LocalizedLabelView l ON l.ObjectId = a.AttributePicklistValueId
WHERE o.Name = ‘optionSetName';

Dynamics CRM–Where is Status transition data stored in the DB

I was curious as to how status transition data is stored in the database for CRM and here is what I found:

The data is stored in the AttributePicklistValue table in the field “TransitionData” as string that represents xml

The XML looks like this:

<allowedtransitions xmlns="http://schemas.microsoft.com/crm/2009/WebServices">
     <allowedtransition sourcestatusid="803000000" tostatusid="803000001" />
     <allowedtransition sourcestatusid="803000000" tostatusid="803000002" />
     <allowedtransition sourcestatusid="803000000" tostatusid="2" />
</allowedtransitions>

Here is some SQL that allows you to pull the data.

SELECT DISTINCT
        e.LogicalName,
        a.LogicalName,
        sm.Value,
        apv.State_Status_Value,
        apv.TransitionData
FROM Entity e
      JOIN Attribute a ON a.EntityId = e.EntityId
      JOIN AttributePicklistValue apv ON apv.OptionSetId = a.OptionSetId
      JOIN StringMap sm ON sm.ObjectTypeCode = e.ObjectTypeCode
                           AND sm.AttributeValue = apv.value
WHERE LEN(apv.TransitionData) > 0
ORDER BY e.LogicalName,
          a.LogicalName,
          sm.Value,
          apv.State_Status_Value,
          apv.TransitionData;

Here is a query that can pull back the OptionSets and their corresponding values

SELECT o.Name,
        v.Value,
        l.Label
FROM AttributePicklistValueAsIfPublishedView v
      INNER JOIN LocalizedLabelAsIfPublishedView l ON v.AttributePicklistValueId = l.ObjectId
                                                      AND l.ObjectColumnName = 'DisplayName'
      INNER JOIN OptionSetAsIfPublishedView o ON v.OptionSetId = o.OptionSetId
WHERE o.name LIKE 'opportunity_%'
ORDER BY o.Name;

Finally, the correct way to get at this data is via the service and here is the sample code: Retrieve valid status transitions https://msdn.microsoft.com/en-us/library/dn689060.aspx

Debugging dynamic scripts injected into CRM Ribbon

If you need to debug javascript code thats invoked via the ribbon and the javascript is getting injected dynamically into the page, then you need a way to stop at a breakpoint.

One thing you can do is add a line at the top of your script with the following code:
debbugger;

This will cause the debugger to stop at that line and you can then work your way through the file.

But an even cooler way to do it (when using Google Chrome) is to add the following line at the very bottom:

//# sourceURL=dynamicScript.js

Now, when the code gets injected into Chrome, you will find it in the “Sources” tab.

Notes:
You can name the file anything, and so if you have multiple points of injection, you can name each one differently.
Your breakpoints are preserved across reloads of the page (which is unlike using the debugger statement).
You need to look under the (no domain) header for the file.

And when using the log, use this function:

function logToConsole(message) { if (typeof console != 'undefined') { console.log(message); } }

Note: the reason I dont override log function is because I want to play nice with other JS scripts that maybe getting loaded within CRM. You need to use this especially in IE, as when the debugger is not shown, the console object can be null.

Taking apart the Ryobi Expand-it string trimmer head

For some reason my Ryobi Expand-it electric trimmer head got stuck and would no longer spool out its line. I tried to take apart the head, but unfortunately, the instructions were not very clear.

It turns out that you turn the red knob right (in the same direction as the “Wind” arrows), when you have the bottom of the trimmer head facing you.

And obviously to tighten it, you turn it to the left. (Its the opposite of the righty tighty and lefty loosey rule!)

Also, I found that its a lot easier to refill the reel, once you take apart the head.

Setting up CRM certificate on an environment restored from CRM Online

Download the certificate from your online instance (Settings >> Customizations >> Developer Resources)

Install the certificate into the user certificates:

Search for: Manager User Certificates

Right click on Personal >> Certificates and choose Import.

Browse and select the certificate you downloaded from CRM online

Right click on the certificate “*.crm.dynamics.com” and choose All Tasks >> Export

Choose “Base-64 encoded X.509 (.cer) as the format and click next and export it to a file.

Attach the certificate to CRM.

Open a powershell command window in Admin mode.

Add the CRM powershell snamp in by running: Add-PSSnapin Microsoft.Crm.PowerShell

Next install the certificate by running (replacing the path to the data file with the file from the step where you exported the base-64 file above:

Set-CrmCertificate -certificatetype appfabricissuer -StoreName My -StoreLocation LocalMachine -StoreFindType FindBySubjectDistinguishedName -DataFile C:\base64-crm.dynamics.com.cer

Finally validate that it worked by running: Get-CrmCertificate

More info:

https://msdn.microsoft.com/en-us/library/gg328249.aspx

DupFinder from JetBrains–XSLT

Jetbrains has a cool tool to find duplicates in your code-base called DupFinder.exe

Here is a slightly modified xsl, that also outputs the line numbers

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
     <xsl:output method="html" indent="yes" />
     <xsl:template match="/">
         <html>
             <body>
                 <h1>Statistics</h1>
                 <p>Total codebase size: <xsl:value-of select="//CodebaseCost"/></p>
                 <p>Code to analyze: <xsl:value-of select="//TotalDuplicatesCost"/></p>
                 <p>Total size of duplicated fragments: <xsl:value-of select="//TotalFragmentsCost" /></p>
                 <h1>Detected Duplicates</h1>
                 <xsl:for-each select="//Duplicates/Duplicate">
                     <h2>Duplicated Code. Cost: <xsl:value-of  select="@Cost"/></h2>
                     <h3>Duplicated Fragments:</h3>
                     <xsl:for-each select="Fragment">
                         <xsl:variable name="i" select="position()"/>
                         <p>Fragment <xsl:value-of select="$i"/>  in file <xsl:value-of select="FileName"/></p>
                         <p>Line Range Start: <xsl:value-of select="LineRange/@Start"/> End: <xsl:value-of select="LineRange/@End"/></p>
                         <pre><xsl:value-of select="Text"/></pre>
                         <br />
                     </xsl:for-each>
                 </xsl:for-each>
             </body>
         </html>
     </xsl:template>
</xsl:stylesheet>

Create an offline installer for Visual Studio 2017

Its as simple as:

1. Download the appropriate installer: https://www.visualstudio.com/downloads/

2. Run the following command on the installer (eg: if you downloaded the enterprise edition and the file was called “vs_enterprise__1432384505.1483734108.exe”

vs_enterprise__1432384505.1483734108.exe --layout vs2017_offline

Where vs2017_offline is the folder where the offline installer will be downloaded.

For more information see: https://docs.microsoft.com/en-us/visualstudio/install/create-a-network-installation-of-visual-studio.

Note: these steps can be applied to any Visual Studio installer (since 2015). Also, if you rerun the command, it will automatically update the folder with the latest release.

Cannot open Sql Encryption Symmetric Key because Symmetric Key password does not exist in Config DB

We were getting this error when we were trying to set up service endpoint in CRM. Though you could get a similar error when you are trying other things in CRM too.

To change the key, you need to view the “Data Encryption” settings (under “Data Management”). Trying to open this page will give you the following error:

The HTTPS protocol is required for this type of request. Enable the HTTPS protocol and try again. For more information, see the Post-Installation and Configuration instructions.

To get around this error, (and ONLY do this in a dev environment. NEVER EVER in prod!), you can run this script:

UPDATE [MSCRM_CONFIG].[dbo].[DeploymentProperties]
SET [BitColumn]=1
WHERE ColumnName='DisableSSLCheckForEncryption' and BitColumn <> 1

Reference: https://technet.microsoft.com/en-us/library/dn531199.aspx?f=255&MSPPError=-2147217396

Extracting CRM solution using the solutionPackager tool

Here is how one can use the SolutionPackager to extract the solution file into individual files. (more info: https://msdn.microsoft.com/en-us/library/jj602987.aspx)

solutionpackager /action:Extract /zipfile:NCM-SB1\Default_1_0.zip /folder:NCM-SB1\Solution

Uses:

1. You can extract the files to make it easier to put them into source control

2. You can extract the files from similar solutions from 2 different organizations and then compare them for differences.

CRM–Default solution fails to export

Whenever I tried to export the default solution from our on-premise 2016 instance, it would error out after about 6 minutes with a generic error of: “Unexpected error occurred”

On the webserver running CRM, I found the following error message:

- <Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event"> - <System>   <Provider Name="MSCRMWebService" />   <EventID Qualifiers="49152">18176</EventID>   <Level>2</Level>   <Task>0</Task>   <Keywords>0x80000000000000</Keywords>   <TimeCreated SystemTime="2017-05-03T16:41:46.000000000Z" />   <EventRecordID>168537</EventRecordID>   <Channel>Application</Channel>   <Computer>xxxxxxxxxxxx</Computer>   <Security />   </System> - <EventData>   <Data>xxxxxxx</Data>   <Data>xxxxxxxx</Data>   <Data>none</Data>   <Data>30</Data>   <Data>ExportSolution</Data>   <Data>Microsoft.Crm.Extensibility.InternalOperationPlugin, Microsoft.Crm.ObjectModel, Version=8.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35</Data>   <Data>Microsoft.Crm.Extensibility.InternalOperationPlugin</Data>   <Data>Unhandled Exception: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor) at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments) at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) at System.Web.Services.Protocols.LogicalMethodInfo.Invoke(Object target, Object[] values) at Microsoft.Crm.Extensibility.InternalOperationPlugin.Execute(IServiceProvider serviceProvider) at Microsoft.Crm.Extensibility.V5PluginProxyStep.ExecuteInternal(PipelineExecutionContext context) at Microsoft.Crm.Extensibility.VersionedPluginProxyStepBase.Execute(PipelineExecutionContext context) Inner Exception: System.NullReferenceException: Object reference not set to an instance of an object. at Microsoft.Crm.Tools.ImportExportPublish.WorkflowHandler.RaiseExceptionIfNotActivatedBPF(WorkflowEntity workflow) at Microsoft.Crm.Tools.ImportExportPublish.WorkflowHandler.ExportItem(XmlDocument XDoc, XmlNode Node) at Microsoft.Crm.Tools.ImportExportPublish.ExportHandler.Export(XmlDocument XDoc) at Microsoft.Crm.Tools.ImportExportPublish.RootExportHandler.RunExport() at Microsoft.Crm.WebServices.ExportXmlService.ExportSolutionWithTargetVersion(String solutionName, Boolean managed, String targetVersion, Boolean exportAutoNumberingSettings, Boolean exportCalendarSettings, Boolean exportCustomizationSettings, Boolean exportEmailTrackingSettings, Boolean exportGeneralSettings, Boolean exportMarketingSettings, Boolean exportOutlookSynchronizationSettings, Boolean exportRelationshipRoles, Boolean exportIsvConfig, Boolean exportSales, Boolean exportExternalApplications, ExecutionContext context)</Data>   </EventData>   </Event>

The fact that it was happening on “RaiseExceptionIfNotActivatedBPF”, told me it had something to do with workflows.

Looking for ways to fix it on the interwebs brought me to the following post:https://community.dynamics.com/crm/f/117/t/232669, which points out the fact that Workflow records somehow got messed up with the upgrade. Simply, running the following statement worked for me:

IMPORTANT: Running sql directly against your CRM database is not supported and could void your warranty. So do so, at your own peril!

Please note: I did this on a test CRM instance that was not going to be used in production. If you plan on using this fix, it is probably better for you to engage Microsoft Support and have them look at your organization database.

UPDATE WorkflowBase
  SET
      BusinessProcessType = 0
WHERE Category = 4
      AND BusinessProcessType IS NULL;

But in case that doesn't, run the following script in its entirety:

BEGIN TRAN; SELECT name,        Workflowid,        WorkflowIdUnique,        CreatedOn,        overwritetime FROM workflowbase WHERE name = 'Opportunity Sales Process'; UPDATE WorkflowBase   SET       UniqueName = 'opportunitysalesprocess' WHERE WorkflowId = '3E8EBEE6-A2BC-4451-9C5F-B146B085413A'; SELECT Businessprocesstype,        UniqueName,        statecode,        statuscode,        workflowid FROM workflowbase WHERE category = 4; SELECT UniqueName,        statecode,        statuscode,        BusinessProcessType,        SupportingSolutionId,        * FROM workflowbase WHERE WorkflowId = '3E8EBEE6-A2BC-4451-9C5F-B146B085413A'; UPDATE WorkflowBase   SET       SupportingSolutionId = NULL WHERE WorkflowId = '3E8EBEE6-A2BC-4451-9C5F-B146B085413A'; UPDATE WorkflowBase   SET       BusinessProcessType = 0 WHERE Category = 4       AND BusinessProcessType IS NULL; UPDATE WorkflowBase   SET       UniqueName = 'phonetocaseprocess' WHERE WorkflowId = '0FFBCDE4-61C1-4355-AA89-AA1D7B2B8792'; UPDATE WorkflowBase   SET       UniqueName = 'leadtoopportunitysalesprocess' WHERE WorkflowId = '919E14D1-6489-4852-ABD0-A63A6ECAAC5D'; COMMIT TRAN;

IE Developer tools crashes on Dynamics CRM

Unhandled exception in Iexplorer.exe

I found that it was related to “Learning Path” and opting out of “learning path” allows developer toolbar to work correctly.

Dynamics CRM–Status to Status Reason Mapping (statecode to statuscode)

SELECT distinct e.LogicalName as entity,       smState.Value AS [State/StateCode],        smstate.AttributeValue as stateCode,        smStatus.Value AS [statusReason/statusCode],        smStatus.AttributeValue as statusCode FROM StatusMap sm      JOIN Entity e ON sm.ObjectTypeCode = e.ObjectTypeCode      JOIN StringMap smState ON smState.AttributeValue = sm.State                                AND smState.ObjectTypeCode = e.ObjectTypeCode                                AND smState.AttributeName = 'StateCode'                              JOIN StringMap smStatus ON smStatus.AttributeValue = sm.Status                                 AND smStatus.ObjectTypeCode = e.ObjectTypeCode                                 AND smStatus.AttributeName = 'StatusCode'                         WHERE  1=1 and e.IsCustomEntity = 0 and e.LogicalName in ('lead','account','contact','opportunity') ORDER BY e.LogicalName,        smState.AttributeValue,          smStatus.AttributeValue;

https://docs.google.com/spreadsheets/d/1PnfHNqUdKQ54rJyeRolOeCZVk73zcvo-0qmtN3MF-sc/pubhtml

Useful bookmarklets for working with CRM

Here are some useful bookmarkets that I have collected for working with Dyanmics CRM (tested with CRM 2016)

1. Download the files from: https://github.com/rajrao/CRM-Tools (you need just the folder CrmDev).
2. Open the following location via Windows Explorer: %userprofile%\Favorites\Links
3. Copy the folder CrmDev into the folder opened in (2).
4. You will now have access to the bookmarks in IE.

Alternatively, you can use the following html and use the import functionality in your favorite browser to get these links.
https://rawgit.com/rajrao/CRM-Tools/master/Bookmarklets/bookmark.htm (source: https://github.com/rajrao/CRM-Tools/blob/master/Bookmarklets/bookmark.htm)

Headless Authentication against CRM 365 WebApi

Or how to authenticate against the CRM 365 web-api, without a user-name and password.
Background: We had to write a web-service that communicated with CRM. And because it was going to be a web-service that was communicating with CRM web-api, we didnt want to use a user-name and password and instead, we wanted to just use . And hence the name  “headless authentication”.
Create an Azure App Registration:

1. Login to Azure portal: https://portal.azure.com
2. Navigate to the “App Registrations” blade, and add an app
1. Click on “Add”
2. 
3. Enter a value for name, set the application type to “Web App/API” and enter a sign-on URL (any value will do). Click Create
   
4. Return to the “App Registrations” blade and select the new app you created in step 3.
5. You should now see the essential settings of the app:
   
   You will need the Application ID later.
6. Click on All Settings and then Choose “Required Permissions”. Click on Add
   
   In “Select an API”, select the “Dynamics CRM Online” API and click Select.
   Next under “Select Permissions”, select “Access CRM Online as organization users” and then click Select.
   
   Finally, click Done. The result should look like this:
   
7. Next, click on “Keys” and add a new row, where you set the Description value to “key” (this can be any value), Expires: Never and then click “Save”.
   
   The value field will update. Copy the value and save it. Once you leave this view, you will not be able to retrieve this key again. This is the shared secret your application will use to authenticate.

Setup a CRM user for the application

1. Go to the “Security” options
   
2. Choose the “Application Users” view
   
3. Click New (make sure the User type is set to “Application User”)
4. Set the application id to the value you from step 5 of Create an Azure App Registration.
5. Enter an email and a name for the application user.
6. Click Save.
7. Click on “Manage Roles” and assign a role to the user (note: you cannot use a system role and you will need to use a custom role).

Create a console app to test the code

1. Test the code using the repo: https://github.com/rajrao/Crm365HeadlessAuthentication
2. I have new code that shows how to use Microsoft Authentication Library (MSAL) to connect to CRM here: https://github.com/rajrao/Crm365HeadlessAuthentication/blob/master/MsalBasedCrmAuthenticationHeadless/Program.cs

MSDOC: https://docs.microsoft.com/en-us/powerapps/developer/common-data-service/authenticate-oauth#requirements-to-connect-as-an-app