The following uses dynamic entity to update a Lookup field on a related contact:


using System;
//using System.Collections.Generic;
using System.Collections;

using System.Text;
using Microsoft.Win32;

// Microsoft Dynamics CRM namespaces
using Microsoft.Crm.Sdk;
using Microsoft.Crm.Sdk.Query;
using Microsoft.Crm.SdkTypeProxy;
using Microsoft.Crm.SdkTypeProxy.Metadata;

using System.IO;
using System.Configuration;
using System.Net.Mail;

namespace CTCA.Crm.Plugins
{
public class EmbraceCaregiver : IPlugin
{

// Related SDK topic: Writing a Plug-in
public void Execute(IPluginExecutionContext context)
{
Guid currentQualifier;
Guid previousQualifier;
Int32 PatientCaregiver;

ICrmService crmService = context.CreateCrmService(true);

DynamicEntity preTask = (DynamicEntity)context.PreEntityImages["contpre"];
DynamicEntity postTask = (DynamicEntity)context.PostEntityImages["contpost"];

if (preTask.Properties.Contains("ctca_isqualifierownerid") && postTask.Properties.Contains("ctca_isqualifierownerid"))
{

previousQualifier = ((Microsoft.Crm.Sdk.Lookup)preTask.Properties["ctca_isqualifierownerid"]).Value;
currentQualifier = ((Microsoft.Crm.Sdk.Lookup)postTask.Properties["ctca_isqualifierownerid"]).Value;
PatientCaregiver = ((Microsoft.Crm.Sdk.Picklist)preTask.Properties["ctca_contacttype"]).Value;
//RelatedCaregiver = ((Microsoft.Crm.Sdk.Lookup)preTask.Properties["ctca_relatedcaregiverid"]).Value;

//if the qualifierownerid field has been changed
if (previousQualifier != currentQualifier && currentQualifier != null && preTask.Properties.Contains("ctca_relatedcaregiverid"))
{
if (PatientCaregiver == 2)
{
Guid patientId = ((Key)preTask.Properties["contactid"]).Value;
//Guid relatedId = ((Lookup)patientId.Properties["ctca_relatedcaregiverid"]).Value;

if (patientId != null)
{
try
{
//Get the related contact
TargetRetrieveDynamic target = new TargetRetrieveDynamic();
target.EntityId = patientId;
target.EntityName = EntityName.contact.ToString();

RetrieveRequest retrieve = new RetrieveRequest();
retrieve.ColumnSet = new Microsoft.Crm.Sdk.Query.AllColumns();
retrieve.ReturnDynamicEntities = true;
retrieve.Target = target;

RetrieveResponse response = (RetrieveResponse)crmService.Execute(retrieve);
DynamicEntity contact = (DynamicEntity)response.BusinessEntity;

Guid isQualifierownerId = ((Lookup)contact.Properties["ctca_isqualifierownerid"]).Value;
Guid relatedContact = ((Lookup)contact.Properties["parentcustomerid"]).Value;

//Get the related contact
TargetRetrieveDynamic target2 = new TargetRetrieveDynamic();
target2.EntityId = relatedContact;
target2.EntityName = EntityName.contact.ToString();

RetrieveRequest retrieve2 = new RetrieveRequest();
retrieve2.ColumnSet = new Microsoft.Crm.Sdk.Query.AllColumns();
retrieve2.ReturnDynamicEntities = true;
retrieve2.Target = target2;

RetrieveResponse response2 = (RetrieveResponse)crmService.Execute(retrieve2);
DynamicEntity contact2 = (DynamicEntity)response2.BusinessEntity;

ArrayList arrProps = new ArrayList();

Property qualifier = new LookupProperty();
((LookupProperty)qualifier).Name = "ctca_isqualifierownerid";
((LookupProperty)qualifier).Value = new Lookup();
((LookupProperty)qualifier).Value.type = "contact";
((LookupProperty)qualifier).Value.Value = new Guid(isQualifierownerId.ToString());
arrProps.Add(qualifier);

contact2.Properties.AddRange((Property[])arrProps.ToArray(typeof(Property)));

TargetUpdateDynamic updateDynamic = new TargetUpdateDynamic();
updateDynamic.Entity = contact2;
UpdateRequest updateReq = new UpdateRequest();
updateReq.Target = updateDynamic;
UpdateResponse updatedRes = (UpdateResponse)crmService.Execute(updateReq);
}
catch (System.Web.Services.Protocols.SoapException ex)
{
throw new InvalidPluginExecutionException("An error occurred in the Update plug-in.", ex);
}


//crmService.Update(contact2);
}

}
}
}
}

#region Private methods
///

/// Creates a CrmService proxy for plug-ins that execute in the child pipeline.
///

/// The execution context that was passed to the plug-ins Execute method.
/// Set to True to use impersontation.
/// A CrmServce instance.
private CrmService CreateCrmService(IPluginExecutionContext context, Boolean flag)
{
CrmAuthenticationToken authToken = new CrmAuthenticationToken();
authToken.AuthenticationType = 0;
authToken.OrganizationName = context.OrganizationName;

// Include support for impersonation.
if (flag)
authToken.CallerId = context.UserId;
else
authToken.CallerId = context.InitiatingUserId;

CrmService service = new CrmService();
service.CrmAuthenticationTokenValue = authToken;
service.UseDefaultCredentials = true;

// Include support for infinite loop detection.
CorrelationToken corToken = new CorrelationToken();
corToken.CorrelationId = context.CorrelationId;
corToken.CorrelationUpdatedTime = context.CorrelationUpdatedTime;
corToken.Depth = context.Depth;

RegistryKey regkey = Registry.LocalMachine.OpenSubKey("SOFTWARE\\Microsoft\\MSCRM");

service.Url = String.Concat(regkey.GetValue("ServerUrl").ToString(), "/2007/crmservice.asmx");
service.CorrelationTokenValue = corToken;

return service;
}

///

/// Creates a MetadataService proxy for plug-ins that execute in the child pipeline.
///

/// The execution context that was passed to the plug-ins Execute method.
/// Set to True to use impersontation.
/// A MetadataServce instance.
private MetadataService CreateMetadataService(IPluginExecutionContext context, Boolean flag)
{
CrmAuthenticationToken authToken = new CrmAuthenticationToken();
authToken.AuthenticationType = 0;
authToken.OrganizationName = context.OrganizationName;

// Include support for impersonation.
if (flag)
authToken.CallerId = context.UserId;
else
authToken.CallerId = context.InitiatingUserId;

MetadataService service = new MetadataService();
service.CrmAuthenticationTokenValue = authToken;
service.UseDefaultCredentials = true;

RegistryKey regkey = Registry.LocalMachine.OpenSubKey("SOFTWARE\\Microsoft\\MSCRM");

service.Url = String.Concat(regkey.GetValue("ServerUrl").ToString(), "/2007/metadataservice.asmx");

return service;
}

#endregion Private Methods
}
}

CRM - Get and Set the current logged in user to a lookup control

There are many posts around finding the current user logged in and setting values in lookup controls for MSCRM 4.0. However, this posts puts them together. Some pratical uses may include:

--Stamping changes when multiple people save to a particular record where the Last Updated only shows one person.
--Auditing control.

Please let me know of any other applications for this code. The code listed in red is the only code you have to change to implement this in your environment.

/********************************************************************/
/* Get the current logged in user and set a systemuser lookup control
/********************************************************************/
var xml = "" +
"" +
"" +
GenerateAuthenticationHeader() +
" " +
" " +
" " +
" systemuser" +
" " +
" " +
" businessunitid" +
" firstname" +
" fullname" +
" lastname" +
" organizationid" +
" systemuserid" +
" " +
" " +
" false" +
" " +
" And" +
" " +
" " +
" systemuserid" +
" EqualUserId" +
" " +
" " +
" " +
" " +
" " +
" " +
"" +
"";

var xmlHttpRequest = new ActiveXObject("Msxml2.XMLHTTP");

xmlHttpRequest.Open("POST", "/mscrmservices/2007/CrmService.asmx", false);
xmlHttpRequest.setRequestHeader("SOAPAction", "http://schemas.microsoft.com/crm/2007/WebServices/RetrieveMultiple");
xmlHttpRequest.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
xmlHttpRequest.setRequestHeader("Content-Length", xml.length);
xmlHttpRequest.send(xml);

var resultXml = xmlHttpRequest.responseXML;
var entityNode = resultXml.selectSingleNode("//RetrieveMultipleResult/BusinessEntities/BusinessEntity");

var systemUserIdNode = entityNode.selectSingleNode("q1:systemuserid");
var fullNameNode = entityNode.selectSingleNode("q1:fullname");

//fill current user
var lookupData = new Array();

var lookupId = systemUserIdNode.text;
var lookupTypecode = 8;
var lookupName = fullNameNode.text;

lookupData[0] =new LookupControlItem( lookupId, lookupTypecode, lookupName);

crmForm.all.new_userid.DataValue = lookupData;
crmForm.all.new_userid.ForceSubmit = true;
}

CRM - Dynamic Picklists Using Arrays

For this example, I have a picklist Status and another Reason Code for that status. This code is very easy to maintain assuming some of the values in either the status or reason code changes in the future. This code is very easy to transform into your own environment. Simply change the values listed in red.

/********************************************************************/
/* status and reason code dynamic picklists
/********************************************************************/
var REASON_CODE_VALUES = new Array();
var REASON_CODE_COMPLETE_LIST = new Object();
var STATUS_ELEMENT = document.getElementById('new_status');
var REASON_CODE_ELEMENT = document.getElementById('new_reasoncode');

//REASON_CODE_VALUES['status picklist names'] = [reason code picklist values];
REASON_CODE_VALUES['Approved'] = [47,31,32,70,69,33,34,35,36,37,38,30,54];
REASON_CODE_VALUES['Delayed'] = [39,40,75,73,72,41,57,42,43,58,45,46,61,59,74,60,8,50,14,12,15,49,11,6,9,13,10,4];
REASON_CODE_VALUES['Denied'] = [67,76,19,18,68,21,20,22,53,24,17];
REASON_CODE_VALUES['Incomplete'] = [62,26,63,25,65,29,28,27];

// Store the complete set of values for the pick lists and wire up events
if (REASON_CODE_ELEMENT)
{
REASON_CODE_COMPLETE_LIST = REASON_CODE_ELEMENT.Options;
}
if (STATUS_ELEMENT)
{
STATUS_ELEMENT.onchange = OnStatusChange;
STATUS_ELEMENT.FireOnChange();
}

function OnStatusChange()
{
if (STATUS_ELEMENT && STATUS_ELEMENT.SelectedText && REASON_CODE_ELEMENT)
{
SetupReasonCodeValuesFor(STATUS_ELEMENT.SelectedText);
}
else
{
RemoveAllItemsFrom(REASON_CODE_ELEMENT);
REASON_CODE_ELEMENT.DataValue = null;
}
}

// Clears all the pick list items from the specified element
function RemoveAllItemsFrom(element)
{
if (element)
{
for (var i = element.Options.length - 1; i > 0; i--)
{
element.DeleteOption(parseInt(i));
}
}
}


// Returns true if the passed in array contains the value
function ArrayContains(array, value)
{
var result = false;
if (array && value)
{
for (var i = 0; i < result =" true;" currentvalue =" REASON_CODE_ELEMENT.DataValue;" options =" REASON_CODE_COMPLETE_LIST;" reasoncodevalues =" REASON_CODE_VALUES[StatusType];" i =" REASON_CODE_ELEMENT.Options.length"> 0; i--)
{
if (!ArrayContains(ReasonCodeValues, i))
{
REASON_CODE_ELEMENT.DeleteOption(parseInt(i));
}
}
}
else
{
RemoveAllItemsFrom(REASON_CODE_ELEMENT);
}
// Set the current value back into the element
REASON_CODE_ELEMENT.DataValue = currentValue;
}

Clean AsyncOperationBase Table

declare @DeleteRowCount int
Select @DeleteRowCount = 2000
declare @DeletedAsyncRowsTable table (AsyncOperationId uniqueidentifier not null)
declare @continue int, @rowCount int
select @continue = 1
while (@continue = 1)
begin
begin tran
insert into @DeletedAsyncRowsTable(AsyncOperationId)
Select top (@DeleteRowCount) AsyncOperationId
from AsyncOperationBase
where OperationType in (1, 9, 12, 25, 27, 10) AND StateCode = 3 AND StatusCode in (30, 32)

Select @rowCount = 0
Select @rowCount = count(*) from @DeletedAsyncRowsTable
select @continue = case when @rowCount <= 0 then 0 else 1 end

if (@continue = 1)
begin
delete WorkflowLogBase from WorkflowLogBase W, @DeletedAsyncRowsTable d
where W.AsyncOperationId = d.AsyncOperationId

delete BulkDeleteFailureBase From BulkDeleteFailureBase B, @DeletedAsyncRowsTable d
where B.AsyncOperationId = d.AsyncOperationId

delete AsyncOperationBase From AsyncOperationBase A, @DeletedAsyncRowsTable d
where A.AsyncOperationId = d.AsyncOperationId

delete @DeletedAsyncRowsTable
end

commit
end



Select Count(*) from AsyncoperationBase Where OperationType = 10 AND StateCode = 3 AND StatusCode = 30

Removing Unwanted CRM E-mail Signature Graphics

Some e-mail signatures contain graphics that over time unnecessarily grow the ActivityMimeAttachment table. First run this script to identify how many of these attachments there are and their names:

select FileName, count (*) from activitymimeattachment group by FileName having count (*) >1000

Next, verify that you do not need that graphic file stored in CRM by running the following script and pasting the activityid into an exisiting e-mail activity and then open the attachments:

select top 10 * from activitymimeattachment where FileName = 'FILENAME.gif/jpg/png'

To delete those attachments run the following script:

declare @DeleteRowCount int
Select @DeleteRowCount = 2000
declare @DeletedActivityRowsTable table (ActivityMimeAttachmentId uniqueidentifier not null)
declare @continue int, @rowCount int
select @continue = 1
while (@continue = 1)
begin
begin tran
insert into @DeletedActivityRowsTable(ActivityMimeAttachmentId)
Select top (@DeleteRowCount) ActivityMimeAttachmentId
from ActivityMimeAttachment

--replace Filename and Filesize here
where filename in ('FILENAME.gif/jpg/png') and filesize = 'FileSize'

Select @rowCount = 0
Select @rowCount = count(*) from @DeletedActivityRowsTable
select @continue = case when @rowCount <= 0 then 0 else 1 end

if (@continue = 1)
begin
delete ActivityMimeAttachment from ActivityMimeAttachment AMA, @DeletedActivityRowsTable d
where AMA.ActivityMimeAttachmentId = d.ActivityMimeAttachmentId

delete @DeletedActivityRowsTable
end
commit
end

Once you identify the main e-mail graphics (most likely internal) you can set this script to run daily/weekly.

CRM - E-mail Router Maintenence

The CRM e-mail router was designed to accept incoming e-mail that has been forwarded from CRM users inboxes and track those e-mails (w/tracking tokens) in CRM. If the e-mail does not contain a tracking token it will permanently delete that e-mail from the e-mail router’s inbox.

The e-mail router only discerns e-mails sent within the past 24 hours. On occasion the e-mail router is unable to process an e-mail and therefore does not process e-mails into CRM but rather remain in the router's inbox. This does not happen often, but when it does a back-log of e-mails begins to build up in the router’s inbox. These e-mails have a tracking token and need to be tracked in CRM and here is how to make that happen:

1. Log onto the server that the router is installed
2. Stop the Microsoft CRM email router service
3. Open C:\Program Files\Microsoft CRM Email\Service
4. Delete the file Microsoft.Crm.Tools.EmailAgent.SystemState.xml
5. Start the Microsoft CRM email router service

Once the service is restarted, the file Microsoft.Crm.Tools.EmailAgent.SystemState.xml will be recreated with no time stamp. The e-mails in the router's inbox will then be processed into CRM.

Deploying an MSCRM 4.0 Test Environment with production data

1. You will need the most recent backups of the MSCRM_CONFIG and Org_MSCRM databases from your production environment.

2. Install MSCRM on the application server as a new installation with the same Organization name as the production environment.

3. Restore both backups directly over the newly created databases. It is very important that you do not make any changes to the data at this point because your test application server is pointing to your production database.

4. Run the following script on the test database to unorphan database users:

exec sp_change_users_login 'report'
declare @usrname varchar(100), @command varchar(100)
declare Crs insensitive cursor for

select name as UserName from sysusers
where issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
order by name

for read only
open Crs

fetch next from Crs into @usrname
while @@fetch_status=0

begin
select @command=' sp_change_users_login ''auto_fix'', '''+@usrname+''' '
exec(@command)
fetch next from Crs into @usrname
end

close Crs
deallocate Crs

5. Log onto the test application server and disable the Org that was just created.

6. Highlight the Organization and click edit.

7. In the next screen make the appropriate changes and run through the rest of the wizard. This usually takes up to 10 minutes to complete.

8. Open the table [Server] on the MSCRM_CONFIG database and change the servers from the production names to the test names.

9. Open the table [DeploymentProperties] on the MSCRM_CONFIG database and change the 2 entries of the production servers to the test servers. Run the following script against the MSCRM_CONFIG database to ensure everything is pointing to the correct place:

SELECT ConnectionString, SrsUrl FROM Organization


10. Go back to the Deployment Manager and re-enable the test Organization.

11. Finally, the last change that needs to be made is to export the isv.config and sitemap from the test CRM environment and edit in Notepad++. Find and Replace all references of the production CRM to the test CRM. Save those files and import them into the Test environment.