PowerShell - MS Dynamics CRM CRUD Operations

Please refer Read App.Config -
Please refer Connect MS Dynamics CRM

$crm_service = Invoke-CRMConn -OrganizationUrl $crm_organization_url -UserName $crm_username -Password $crm_password

## 1. To test the connection, check who you are:

function WhoAMI() {
    $request = new-object Microsoft.Crm.Sdk.Messages.WhoAmIRequest
    $crm_service.Execute($request)

    <#
    UserId         : 0dc96a70-952d-e611-80f0-0050568c6c1c
    BusinessUnitId : a6099f28-952d-e611-80ec-0050568c6c1c
    OrganizationId : 0d67a31e-952d-e611-80ec-0050568c6c1c
    ResponseName   : WhoAmI
    Results        : {[UserId, 0dc96a70-952d-e611-80f0-0050568c6c1c], [BusinessUnitId, a6099f28-0caf-e611-80ec-0050568c6c1c],
                     [OrganizationId, 0d67a31e-0caf-e611-80ec-0050568c6c1c]}
    ExtensionData  : System.Runtime.Serialization.ExtensionDataObject
    #>
}

PowerShell - Connect MS Dynamics CRM

## create crm connction function, call this function with arguments  OrganizationURL, UserName, Password
function Invoke-CRMConn {
    ## pass arguments OrganizationURL, UserName, Password
    [CmdletBinding()]
    param(
        [Parameter(Position=0, Mandatory=$true)] [string]$OrganizationURL,
        [Parameter(Position=1, Mandatory=$true)] [string]$UserName,
        [Parameter(Position=2, Mandatory=$true)] [string]$Password
    )

    ## get directory path
    $current_directory =  $(Get-Item ($MyInvocation.ScriptName)).DirectoryName
   
    ## load crm & xrm dll
    $xrm_dll = $current_directory + "/microsoft.xrm.sdk.dll"
    $crm_dll = $current_directory + "/microsoft.crm.sdk.proxy.dll"
    [void][System.Reflection.Assembly]::LoadFile($xrm_dll)
    [void][System.Reflection.Assembly]::LoadFile($crm_dll)
    [void][System.Reflection.Assembly]::LoadWithPartialName("system.servicemodel")

    ## create credentials
    $clientCredentials = new-object System.ServiceModel.Description.ClientCredentials
    $clientCredentials.UserName.UserName = $UserName
    $clientCredentials.UserName.Password = $Password
   
    ## create  organization service proxy 
    $service = new-object Microsoft.Xrm.Sdk.Client.OrganizationServiceProxy($OrganizationURL, $null, $clientCredentials, $null)
   
    ## use default crm time out or set 5 minutes of timeout.
    #$service.Timeout = new-object System.Timespan(0, 5, 0)

    ## return service
    return $service
}

## test the service using who i am request
$service1 = Invoke-CRMConn -OrganizationURL "https://dns.com/XRMServices/2011/Organization.svc" -UserName 'test@dns.com' -Password ABC123!@#'
$request = new-object Microsoft.Crm.Sdk.Messages.WhoAmIRequest
$service1.Execute($request)

<#
UserId             : 123ddfa0-9534-f234-8780-78900568c2fc0
BusinessUnitId : 456a043cf-0c34-e234-878c-78900568c6c1c
OrganizationId : 7895b3c2-0c34f-e234-878c-7890568c6c1c
ResponseName   : WhoAmI
Results        : {[UserId, 123ddfa0-952d-f234-8780-7890568c6c1c], [BusinessUnitId, 456a043cf-0caf-e234-8780-7890568c6c1c],
                 [OrganizationId, 7895b3c2-0caf-e234-8780-7890568c6c1c]}
ExtensionData  : System.Runtime.Serialization.ExtensionDataObject
#>

MSCRMMonitoringTest Logging 'Monitoring test failed: Test Title: Help Content Server Tests.: Machine: DNSSES1234: ServerRole: HelpServer'

MSCRMMonitoringTest Logging 'Monitoring test failed: Test Title: Help Content Server Tests.: Machine: DNSSES1234: ServerRole: HelpServer'

Every 15 minutes, CRM Logging the below error

Event 18732, MSCRMMonitoringTest

Monitoring test failed: Test Title: Help Content Server Tests.: Machine: <Server_Name>: ServerRole: HelpServer
Test Log:
The remote server returned an error: (404) Not Found.
   at System.Net.HttpWebRequest.GetResponse()
   at Microsoft.Crm.Monitoring.WebHelpers.GetWebResponse(String webpage, Boolean setCrmAuth, Boolean useProxy, Byte[] postData, Boolean setUserAgent, Int64 dmzPort)
   at Microsoft.Crm.Monitoring.Tests.HelpContent.VerifyHelpContent.TestDefaultHelp(TestResult result)
   at Microsoft.Crm.Monitoring.Tests.HelpContent.VerifyHelpContent.Execute()
   at Microsoft.Crm.Monitoring.Engine.MonitoringRuntime.ExecuteMonitoringTest(MonitoringTestBase test, Int32 attempts)

InnerException Type:System.Net.WebException
.

Workaround solution to fix the above logging error

1. Create TestsToExclude.xml file,
<MonitoringTestsToExclude>
    <Test isDisabled="true">VerifyHelpContent</Test>
</MonitoringTestsToExclude>
2. Place it on %ProgramFiles%\Microsoft Dynamics CRM\Monitoring folder (all Servers).

3. After 15 minutes, you see the log with succeded message

All monitoring tests succeeded: Machine: APWMAD0A2398: ServerRole: HelpServer.

MSCRMMonitoringTest Logging 'A certificate registered for use by Microsoft Dynamics CRM has expired'

Every 15 minutes, CRM Logging the below errors

Event 25089, MSCRMMonitoringTest

A certificate registered for use by Microsoft Dynamics CRM has expired.  Certificate type: TrustedIssuer Certificate Name: http://<URL>/adfs/services/trust Expiration Date: 1/11/2016 6:00:00 AM Store Location:  Store Name:

Event 18797, MSCRMMonitoringTest

Monitoring test failed: Test Title: Trusted Issuer Certificate test: Machine: <MACHINE_NAME>: ServerRole: DiscoveryService, Portal, ApiServer
Test Log:
Retrieving certificate data from config DBVerifying TrustedIssuer certificate.  Name=http://<URL>/adfs/services/trustTrustedIssuer certificate is not stored in local store:  It is contained in the config DB.  Name=http://<URL>/adfs/services/trustCertificate Lifespan:  Valid from 09/14/2014 19:00:00 to 01/11/2016 06:00:00Failure: Certificate has expiredVerifying TrustedIssuer certificate.  Name=http://<URL>/adfs/services/trustTrustedIssuer certificate is not stored in local store:  It is contained in the config DB.  Name=http://<URL>/adfs/services/trustCertificate Lifespan:  Valid from 10/12/2015 19:00:00 to 02/15/2017 06:00:00Remaining certificate lifespan 21.7 % is greater than the configured threshold of 10.0 %Certificate is not nearing expiration.

I found 2 expired (invalid) certificates on the servers (Deployment & all UI Servers).

Workaround solution to fix the above logging error

1. Delete older one (yellow color tag) certificate from all servers
A) Run cmd -> type mmc -> Console Root window -> File menu -> select Add/Remove Snap-ins -> Add Cerificate -> Select(Computer account) -> Finish -> OK.

B) Console Root -> Certificates (Local Computer) -> Personal -> Certificates
   -> Select old cerificate (date ended with '02/15/2017' ) -> Delete.

2. Re run ‘Configure Claims-Based Authentication’ tool from CRM Deployment Manager.

3. After 15 minutes, you see the log with succeded message

Monitoring test succeeded: Test Title: Trusted Issuer Certificate test: Machine: <MACHINE_NAME>: ServerRole: DiscoveryService, Portal, ApiServer
Test Log:
Retrieving certificate data from config DBVerifying TrustedIssuer certificate.  Name=http://<URL>/adfs/services/trustTrustedIssuer certificate is not stored in local store:  It is contained in the config DB.  Name=http://<URL>/adfs/services/trustCertificate Lifespan:  Valid from 10/12/2015 19:00:00 to 02/15/2017 06:00:00Remaining certificate lifespan 21.3 % is greater than the configured threshold of 10.0 %Certificate is not nearing expiration.

Convert JSON to Object using Json.NET

sample json file
[{"empNumber":"123456","primaryName":{"firstName":"FN","lastName":"LN"},"disability":false,"otherNames":[{"firstName":"FN","lastName":"LN1"},{"firstName":"FN","lastName":"LN2"}],"homeAddress":{"addressLine1":"1234 Python Java Rd","city":"Bellevue","state":"WI","postalCode":"628204"}},{"empNumber":"7890","primaryName":{"firstName":"1FN","lastName":"1LN"},"disability":true,"otherNames":[{"firstName":"1FN","lastName":"1LN1"},{"firstName":"1FN","lastName":"1LN2"}],"homeAddress":{"addressLine1":"5869 Dotnet CRM St","city":"Chicago","state":"NE","postalCode":"567567"}}]

Download Json.NET dll from http://www.newtonsoft.com/json

MS Dynamics CRM - Display Unique/Auto Number on the form before save.

My client requirement is display the unique reg number for new contact before the save record.
My Previous blog <a href="http://makdns.blogspot.com/2016/03/ms-dynamics-crm-generate-uniqueauto.html">MS Dynamics CRM - Generate Unique/Auto Number</a> will show the reg number on form after save the new contact. if the user clicks save and close, they can't see the number.

So i come up with the web service concept to generate unique number for new contact and display on the contact screen.
Also i am using SQL Server sys.sequences for generating new number.

Web Service Code

MS Dynamics CRM - Generate Unique/Auto Number

MS Dynamics CRM doesn't contain auto increment number attribute. My client was assigned each unique registration number for contact entity.
There is lot options available in the market, but i used very easy code to achieve this task. I used pre create plugin operation to create new registration unique/auto number. Number will be create on while saving the record.

i created one custom entity (dns_global), it will keep last reg number.

Dynamics CRM - Update Contact Fullname

My client want the fullname with the suffix and custom order like LastName, FirstName Middle Name Suffix. Dynamics CRM System Settings doesn't have the option to set the custom order fullname. Contact entity fullname is readonly attribute, it won't allow to update the fullname directly.

You can update through only pre create or update operation plugin.

MS Dynamics CRM - Login user's security role using javascript

I have one situation, login user has multiple security roles, if the login user has permission for particular role, show some buttons/fields otherwise the hide buttons/fields.

It is not stright forward to find out the security role, So i wrote one javascript to find out the security role.

Here i am checking the user has "Content Manager" security role or not.

var CMRole = CheckUserRole("Content Manager");
if (CMRole) {
    //true to show buttons
} else {
    //false to hide buttons
}

This function will get all the user's security role Id, passing security role id to get the security role name. If it match, it is true otherwise false.

function CheckUserRole(roleName) {
    var currentUserRoles = Xrm.Page.context.getUserRoles();
    for (var i = 0; i < currentUserRoles.length; i++) {
        var userRoleId = currentUserRoles[i];
         var userRoleName = GetRoleName(userRoleId);
         if (userRoleName == roleName) {
            return true;
         }
    }
    return false;
}

This function calling odata web service and get the each security role name.

 function GetRoleName(userRoleId) {
     var selectQuery = "RoleSet?$top=1&$filter=RoleId eq guid'" + userRoleId + "'&$select=Name";
     var odataSelect = GetServerUrl() + selectQuery;
     //alert(odataSelect);
     var roleName = null;
     $.ajax({
         type: "GET",
         async: false,
         contentType: "application/json; charset=utf-8",
         datatype: "json",
         url: odataSelect,
         beforeSend: function (XMLHttpRequest) { XMLHttpRequest.setRequestHeader("Accept", "application/json"); },
         success: function (data, textStatus, XmlHttpRequest) {
             var result = data.d;
             if (!!result) {
                 roleName = result.results[0].Name;
             }
         },
         error: function (XmlHttpRequest, textStatus, errorThrown) {
             //alert('OData Select Failed: ' + odataSelect);
         }
     });
     return roleName;
 }

Dynamics CRM - Get selected id from the grid and refresh.

1)Creates a custom button, contains one command (dns.dns_copies.Command.UnCopy)
2)command calling UnCopy javascript (library->$webresource:dns_js/Contact.js) function.
3)command contains 3 parameters
    i)[Crm Parameter]=SelectedControl   (grid name)
   ii)[Crm Parameter]=SelectedControlSelectedItemCount  (number of selected rows)
  iii)[Crm Parameter]=SelectedControlSelectedItemIds (selected entity ids)

Dynamics CRM Report - Generate SSRS report using CRM DB and Extension Database

1) Create 2 data sources
a)DNSCRM_EXT (connect ext database, not using filtered table)

b)MSCRM_DataSource (connect CRM database, using filtered table)

Dynamics CRM 2016/2015/2013/2011 Tools

CRM Rest Builder
Ribbon Workbench
CRM Snoop
SiteMap Editor for Microsoft Dynamics CRM 2011
Diagnostics Tool for Microsoft Dynamics CRM 2011
CRM Manipulation Library

Dynamics CRM Action

CRM Action is a process that allows a user to create it and add custom Workflow, but Action is only be able to be called or triggered by client (Javascript) or server code (C#).

Actions can be defined for an entity or none (Global entity).

Custom Action can be used as Custom Message or Event Handle and can be registered by using Plugin Registration Tool.

Example, storing config parameters (like server url), custom error message, used for complex business code, single point of integration for third party systems.

Create an Action without no steps, message name is edm_TestActionName, entity is none, passing one input ContactID (reference) & one output ValidContact (boolean) parameters. Activate it

Generate an Early Bound Class to get your action in your class library, refer this blog http://makdns.blogspot.com/2012/11/crmsvcutilexe-crm-dynamics-2011-code.html (parameter /generateActions)

Create a Plugin Class, compile & deploy

.NET Web Configuration - Access Service by both AJAX & SOAP.

Web Configuration

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
  <connectionStrings>
    <add name="" />
  </connectionStrings>
  <appSettings>
    <add key="aspnet:UseTaskFriendlySynchronizationContext" value="true" />
  </appSettings>
  <system.web>
    <compilation debug="true" targetFramework="4.5" />
    <httpRuntime targetFramework="4.5" />
  </system.web>

Unable to get property 'ClientVariables' of undefined or null reference - MS Dynamics CRM

CRM contact form loaded completely empty without error message, CRM UI server Event viewer is not giving any error. I checked FF & Chrome nothing produce any error details. Then i tried IE, IE throws 'Unable to get property 'ClientVariables' of undefined or null reference'

Finally i figure it our, If the CRM Role doesn't have read rights on the campaign entity, you will get this error. campaign empty is connected with contact entity. I never used campaign entity in my projects, CRM is internally tied with campaign entity.

Unsupported Dynamics CRM database delete operations

Before delete, run SQL Profiler, just delete a single delete operation in CRM and watch all queries involved. 

In this example i am deleting contact entity records, before delete all their related entity records

1.Delete related entities

for example contact related dns_mailings entity (object type code is 12345)
declare  @c table(c varchar(max))

insert into @c select  dns_mailingsid from dns_mailings where dns_name = 'Test 2015'
--select * from @c

delete from dns_mailingsExtensionBase where (dns_mailingsid in (select c from @c));
delete from dns_mailingsBase
OUTPUT DELETED.dns_mailingsid , 12345
into SubscriptionTrackingDeletedObject (ObjectId, ObjectTypeCode)
 where (dns_mailingsid in (select c from @c))

delete from [EmailSearchBase]
OUTPUT DELETED.[EmailSearchId], 4299
into SubscriptionTrackingDeletedObject (ObjectId, ObjectTypeCode)
 where ([ParentObjectId] in (select c from @c))

 update [ActivityPartyBase] set [IsPartyDeleted]=1 where ([PartyId] in (select c from @c)) and [PartyObjectTypeCode] =12345

Dynamic CRM Hide/Show ribbon button by OnChange lookup field

1)create a javascript (HideShow.js), 2 functions

var HideShowButton = {
//refresh the ribbon when lookup field on change.
OnChangeLookup: function () {
        Xrm.Page.ui.refreshRibbon();
  },

//Show and Hide when form load
EnableCustomRule: function () {
if (Xrm.Page.data.entity.attributes.get("contactid") != null) {
   var contactId = Xrm.Page.data.entity.attributes.get("contactid").getValue()[0].id;
   var req = new XMLHttpRequest();
   var url = Xrm.Page.context.getClientUrl() + "/XRMServices/2011/OrganizationData.svc/ContactSet(guid'" + contactId + "')?$select=photoonfile";
   req.open("GET", encodeURI(url), false);
   req.setRequestHeader("Accept", "application/json");
   req.setRequestHeader("Content-Type", "application/json; charset=utf-8");
   req.send();
   var data = JSON.parse(req.responseText).d;
   if (data.photoonfile == null || data.photoonfile == false) {
return true;
   }
   return false;
}
},
};

Dynamic CRM 2011/2013 Audit Report in SSRS

My client want the particular contact entity attributes audit history in the horizontal way SSRS report.
CRM will create one line record in the audit entity for each action (new, update or delete records).
Refer "Dynamics CRM - Audit Entity" - http://makdns.blogspot.com/2014/06/dynamics-crm-audit-entity.html

1)Step created dynamics sql query

declare @contactid uniqueidentifier="00000000-0000-0000-0000-000000000000"> Declare @AuditTable table (id int identity(1,1), AuditId uniqueidentifier, ObjectTypeCode int, AttributeMask varchar(max) null,
        ChangeData varchar(max), CreatedOn datetime)
declare @AuditColumnResult table (id int identity(1,1), rid int null, AuditId uniqueidentifier, Increment int, ColumnKey int,
        ColumnName varchar(max), OldValue varchar(max), NewValue varchar(max), ObjectTypeCode int, createdon datetime)
declare @AuditHisoty table (AuditId uniqueidentifier, Increment int, ColumnKey int, ColumnName varchar(max), Value varchar(max))

Dynamics CRM - Audit Entity

AuditBase table or Audit entity are captured previous data of records.

CRM will create one line record in the audit entity for each action (new, update or delete records).

For example, First Name  Last Name
New Record -  FNC             LNC  => entity record
Old Record -  FN~LN             => audit stored, for example audit id 2, here first name new value is current entity record value  "FNC" and old value is current audit record "FN".
Old Record -  F~L             => audit stored, for example audit id 1, here first name new value is next audit record value  "FN" and old value is current audit record "F"

Convert Dynamics CRM Entity GUID in SSRS

When i create hyperlink i passed  GUID (Fields!contactid.Value) in the query string, the link is not working. I tried to convert GUID to string, still the link is not working.

Finally i used CType function to concatenation with the querystring, it works.

 = Parameters!CRM_URL.Value + "/tools/audit/audit_details.aspx?_CreateFromId=&_CreateFromType=2&id={" & CType(Fields!contactid.Value, GUID).ToString  & "}"