Deployment Manager via SQL in CRM 2011

Although this is an unsupported way, we still need to get this sometimes... a developer hack :)

DECLARE @DomainName VARCHAR(max)

SET @DomainName = 'domain\username'--e.x. 'sandnes\ganesh'
DECLARE @Id AS UNIQUEIDENTIFIER
DECLARE @UserId AS UNIQUEIDENTIFIER
DECLARE @RoleId AS UNIQUEIDENTIFIER

SELECT @RoleId = id
FROM   mscrm_config..securityrole
WHERE  NAME = 'Administrator'

SELECT @UserId = id
FROM   mscrm_config..systemuser
WHERE  NAME = @DomainName

IF ( @UserId IS NULL )
  BEGIN
      SET @UserId = Newid()

      INSERT INTO mscrm_config..systemuser
                  (id,
                   NAME,
                   defaultorganizationid)
      VALUES     (@UserId,
                  @DomainName,
                  '00000000-0000-0000-0000-000000000000')
  END

SELECT @Id = id
FROM   mscrm_config..systemuserroles
WHERE  systemuserid = @UserId

IF ( @Id IS NULL )
  BEGIN
      SET @Id = Newid()

      INSERT INTO mscrm_config..systemuserroles
                  (id,
                   securityroleid,
                   systemuserid)
      VALUES     (@Id,
                  @RoleId,
                  @UserId)
  END 

Merge records with automatic selection of all fields with data

When merging the records in GUI, you have the option of selecting the option which updates the master record with the values from the child record when the value is missing in the master record.

The below code does the same. If does a retrieve on the master and child record and then compares the columns from the child record with the master record and update the "UpdateContent" property of the merge request.

For merging custom entities, the first part of the code can be used and If we have a custom masterid created, the lookup value can be updated with the master record id and the child record can be inactivated/deleted.

Code:

public static bool MergeRecord(IOrganizationService _service, string entName, string srcId, string targetId)
        {
            bool result = false;
            MergeRequest mreq = new MergeRequest();
            Guid targetRecordID = new Guid(targetId);
            Guid tobemergedRecordID = new Guid(srcId);
            Entity srcEntity = _service.Retrieve(entName, tobemergedRecordID, new ColumnSet(true));
            Entity tgtEntity = _service.Retrieve(entName, tobemergedRecordID, new ColumnSet(true));
            Entity updContent = new Entity(entName);
            foreach (KeyValuePair<String, Object> attr in srcEntity.Attributes)
            {
                if (!tgtEntity.Attributes.Contains(attr.Key))
                { 
                    updContent.Attributes.Add(attr);
                }
            }

            mreq.Target = new EntityReference(entName, targetRecordID);  // Target Account ID , where other account will be mergered
            mreq.SubordinateId = tobemergedRecordID;
            mreq.PerformParentingChecks = false;
            mreq.UpdateContent = updContent;
            try
            {
                MergeResponse mresp = (MergeResponse)_service.Execute(mreq);
                result = true;
            }
            catch (Exception ex)
            {
                string message = ex.Message;
                LogMessage(message, true);
                result = false;
            }

            return result;
        }

Import a solution from CRM 2011 to CRM 2015

If you keen on trying CRM 2015 and not willing to choose the upgrade path of CRM 2011-> CRM 2013 -> CRM 2015.... then you are not alone...

When you try to import the solution you will get an error saying the solution cannot be imported from CRM 2011...

Since the solution file is a mere XML, as a developer, I wanted to play around it..

This is what I did. I changes the customization.xml and updated the xml header like below.

<ImportExportXml version="7.0.0000.4013" SolutionPackageVersion="7.0" languagecode="1044" generatedBy="CrmLive" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">'

And the import went fine. Now it's for me to just start modifying the UI and fixing the javascript part :)

Have fun with crm 2015

Create a followup activity for campaign response on the customer

It's a little tricky if you want to create a followup activity on the campaign response. As the campaign response is not linked to the customer directly it's impossible to do it via workflow.

There is a customer field in the campaign response which is in fact a row in the activityparty table with a reference to the customer. So this field is not available in the list of fields for workflow substitution. Here is a piece of code which can be run later or used in plugin to create a followup activity

Code:

//search for campaign responses created yesterday and create followup activity
                ConditionExpression cr_ce1 = new ConditionExpression("responsecode", ConditionOperator.Equal, 1); //Interested
                ConditionExpression cr_ce2 = new ConditionExpression("createdon", ConditionOperator.Yesterday); //Yesterday
                FilterExpression cr_fe = new FilterExpression(LogicalOperator.And); 
                cr_fe.AddCondition(cr_ce1);
                cr_fe.AddCondition(cr_ce2);
                QueryExpression cr_qe = new QueryExpression();
                cr_qe.EntityName = "campaignresponse";
                cr_qe.Criteria = cr_fe;
                cr_qe.ColumnSet = new ColumnSet("activityid", "subject", "customer","createdon");
                cr_qe.PageInfo.ReturnTotalRecordCount = true;
                EntityCollection cr_coll = _service.RetrieveMultiple(cr_qe);

                string fetchXML = @"<fetch version=""1.0"" output-format=""xml-platform"" mapping=""logical"" distinct=""false"">
                                      <entity name=""team"">
                                        <attribute name=""name"" />
                                        <attribute name=""teamid"" />
                                        <attribute name=""queueid"" />
                                        <order attribute=""name"" descending=""false"" />
                                        <filter type=""and"">
                                          <condition attribute=""name"" operator=""eq"" value=""Team Customerservice"" />
                                        </filter>
                                      </entity>
                                    </fetch>";

                Entity team = _service.RetrieveMultiple(new FetchExpression(fetchXML)).Entities[0];

                foreach (Entity ent in cr_coll.Entities)
                {
                    try
                    {
                        if (ent.Attributes.Contains("customer"))
                        {
                            EntityCollection actParty = (EntityCollection)ent["customer"];
                            //create new task
                            Entity task = new Entity();
                            task.LogicalName = "task";
                            task["description"] = @"Some description";
                            task["scheduledstart"] = DateTime.Now;
                            task["scheduledend"] = (DateTime.Parse(ent["createdon"].ToString())).AddDays(7);
                            task["regardingobjectid"] = (EntityReference)(actParty[0]["partyid"]);
                            task["ownerid"] = new EntityReference(team.LogicalName, team.Id);
                            task["subject"] = ent["subject"];
                            Guid _taskId = _service.Create(task);
                            // add to customer service queue
                            Entity qItem = new Entity();
                            qItem.LogicalName = "queueitem";
                            qItem["objectid"] = new EntityReference(task.LogicalName, _taskId);
                            qItem["queueid"] = (EntityReference)team["queueid"];
                            Guid _itemId = _service.Create(qItem);
                        }
                    }
                    catch (Exception ex)
                    {
                        Helper.Log("Problem while processing campaign response Record with ID : " + ent["activityid"].ToString() + "\n" + ex.Message, w, true);
                    }

Get Optionset value from text

Here you go with the code

Code:

public static int GetOptionSetValueGivenText(IOrganizationService service, string entityName, string attributeName, string selectedValue)
        {
            try
            {
                RetrieveAttributeRequest retrieveAttributeRequest = new RetrieveAttributeRequest { EntityLogicalName = entityName, LogicalName = attributeName, RetrieveAsIfPublished = true };
                RetrieveAttributeResponse retrieveAttributeResponse = (RetrieveAttributeResponse)service.Execute(retrieveAttributeRequest);
                PicklistAttributeMetadata retrievedPicklistAttributeMetadata = (PicklistAttributeMetadata)retrieveAttributeResponse.AttributeMetadata;
                OptionMetadata[] optionList = retrievedPicklistAttributeMetadata.OptionSet.Options.ToArray();
                int selectedOptionValue = 0;
                foreach (OptionMetadata oMD in optionList)
                {
                    if (oMD.Label.UserLocalizedLabel.Label == selectedValue)
                    {
                        selectedOptionValue = oMD.Value.Value;
                        break;
                    }
                }
                return selectedOptionValue;
            }
            catch (System.ServiceModelFaultException ex1)
            {
                string strEr = ex1.InnerException.Data.ToString();
                return 0;
            }

        }

Strip HTML

Use this helper class for stirpping HTML. I am not the owner of this code. Got it from the web. But posting it here as it might be useful to many.

Code:

public static string StripHTML(string source)
        {
            try
            {
                string result;

                // Remove HTML Development formatting
                // Replace line breaks with space
                // because browsers inserts space
                result = source.Replace("\r", " ");
                // Replace line breaks with space
                // because browsers inserts space
                result = result.Replace("\n", " ");
                // Remove step-formatting
                result = result.Replace("\t", string.Empty);
                // Remove repeating spaces because browsers ignore them
                result = System.Text.RegularExpressions.Regex.Replace(result,
                                                                      @"( )+", " ");

                // Remove the header (prepare first by clearing attributes)
                result = System.Text.RegularExpressions.Regex.Replace(result,
                         @"<( )*head([^>])*>", "<head>",
                         System.Text.RegularExpressions.RegexOptions.IgnoreCase);
                result = System.Text.RegularExpressions.Regex.Replace(result,
                         @"(<( )*(/)( )*head( )*>)", "</head>",
                         System.Text.RegularExpressions.RegexOptions.IgnoreCase);
                result = System.Text.RegularExpressions.Regex.Replace(result,
                         "(<head>).*(</head>)", string.Empty,
                         System.Text.RegularExpressions.RegexOptions.IgnoreCase);

                // remove all scripts (prepare first by clearing attributes)
                result = System.Text.RegularExpressions.Regex.Replace(result,
                         @"<( )*script([^>])*>", "<script>",
                         System.Text.RegularExpressions.RegexOptions.IgnoreCase);
                result = System.Text.RegularExpressions.Regex.Replace(result,
                         @"(<( )*(/)( )*script( )*>)", "</script>",
                         System.Text.RegularExpressions.RegexOptions.IgnoreCase);
                //result = System.Text.RegularExpressions.Regex.Replace(result,
                //         @"(<script>)([^(<script>\.</script>)])*(</script>)",
                //         string.Empty,
                //         System.Text.RegularExpressions.RegexOptions.IgnoreCase);
                result = System.Text.RegularExpressions.Regex.Replace(result,
                         @"(<script>).*(</script>)", string.Empty,
                         System.Text.RegularExpressions.RegexOptions.IgnoreCase);

                // remove all styles (prepare first by clearing attributes)
                result = System.Text.RegularExpressions.Regex.Replace(result,
                         @"<( )*style([^>])*>", "<style>",
                         System.Text.RegularExpressions.RegexOptions.IgnoreCase);
                result = System.Text.RegularExpressions.Regex.Replace(result,
                         @"(<( )*(/)( )*style( )*>)", "</style>",
                         System.Text.RegularExpressions.RegexOptions.IgnoreCase);
                result = System.Text.RegularExpressions.Regex.Replace(result,
                         "(<style>).*(</style>)", string.Empty,
                         System.Text.RegularExpressions.RegexOptions.IgnoreCase);

                // insert tabs in spaces of <td> tags
                result = System.Text.RegularExpressions.Regex.Replace(result,
                         @"<( )*td([^>])*>", "\t",
                         System.Text.RegularExpressions.RegexOptions.IgnoreCase);

                // insert line breaks in places of <BR> and <LI> tags
                result = System.Text.RegularExpressions.Regex.Replace(result,
                         @"<( )*br( )*>", "\r",
                         System.Text.RegularExpressions.RegexOptions.IgnoreCase);
                result = System.Text.RegularExpressions.Regex.Replace(result,
                         @"<( )*li( )*>", "\r",
                         System.Text.RegularExpressions.RegexOptions.IgnoreCase);

                // insert line paragraphs (double line breaks) in place
                // if <P>, <DIV> and <TR> tags
                result = System.Text.RegularExpressions.Regex.Replace(result,
                         @"<( )*div([^>])*>", "\r\r",
                         System.Text.RegularExpressions.RegexOptions.IgnoreCase);
                result = System.Text.RegularExpressions.Regex.Replace(result,
                         @"<( )*tr([^>])*>", "\r\r",
                         System.Text.RegularExpressions.RegexOptions.IgnoreCase);
                result = System.Text.RegularExpressions.Regex.Replace(result,
                         @"<( )*p([^>])*>", "\r\r",
                         System.Text.RegularExpressions.RegexOptions.IgnoreCase);

                // Remove remaining tags like <a>, links, images,
                // comments etc - anything that's enclosed inside < >
                result = System.Text.RegularExpressions.Regex.Replace(result,
                         @"<[^>]*>", string.Empty,
                         System.Text.RegularExpressions.RegexOptions.IgnoreCase);

                // replace special characters:
                result = System.Text.RegularExpressions.Regex.Replace(result,
                         @" ", " ",
                         System.Text.RegularExpressions.RegexOptions.IgnoreCase);

                result = System.Text.RegularExpressions.Regex.Replace(result,
                         @"&bull;", " * ",
                         System.Text.RegularExpressions.RegexOptions.IgnoreCase);
                result = System.Text.RegularExpressions.Regex.Replace(result,
                         @"&lsaquo;", "<",
                         System.Text.RegularExpressions.RegexOptions.IgnoreCase);
                result = System.Text.RegularExpressions.Regex.Replace(result,
                         @"&rsaquo;", ">",
                         System.Text.RegularExpressions.RegexOptions.IgnoreCase);
                result = System.Text.RegularExpressions.Regex.Replace(result,
                         @"&trade;", "(tm)",
                         System.Text.RegularExpressions.RegexOptions.IgnoreCase);
                result = System.Text.RegularExpressions.Regex.Replace(result,
                         @"&frasl;", "/",
                         System.Text.RegularExpressions.RegexOptions.IgnoreCase);
                result = System.Text.RegularExpressions.Regex.Replace(result,
                         @"&lt;", "<",
                         System.Text.RegularExpressions.RegexOptions.IgnoreCase);
                result = System.Text.RegularExpressions.Regex.Replace(result,
                         @"&gt;", ">",
                         System.Text.RegularExpressions.RegexOptions.IgnoreCase);
                result = System.Text.RegularExpressions.Regex.Replace(result,
                         @"&copy;", "(c)",
                         System.Text.RegularExpressions.RegexOptions.IgnoreCase);
                result = System.Text.RegularExpressions.Regex.Replace(result,
                         @"&reg;", "(r)",
                         System.Text.RegularExpressions.RegexOptions.IgnoreCase);
                // Remove all others. More can be added, see
                // http://hotwired.lycos.com/webmonkey/reference/special_characters/
                result = System.Text.RegularExpressions.Regex.Replace(result,
                         @"&(.{2,6});", string.Empty,
                         System.Text.RegularExpressions.RegexOptions.IgnoreCase);

                // for testing
                //System.Text.RegularExpressions.Regex.Replace(result,
                //       this.txtRegex.Text,string.Empty,
                //       System.Text.RegularExpressions.RegexOptions.IgnoreCase);

                // make line breaking consistent
                result = result.Replace("\n", "\r");

                // Remove extra line breaks and tabs:
                // replace over 2 breaks with 2 and over 4 tabs with 4.
                // Prepare first to remove any whitespaces in between
                // the escaped characters and remove redundant tabs in between line breaks
                result = System.Text.RegularExpressions.Regex.Replace(result,
                         "(\r)( )+(\r)", "\r\r",
                         System.Text.RegularExpressions.RegexOptions.IgnoreCase);
                result = System.Text.RegularExpressions.Regex.Replace(result,
                         "(\t)( )+(\t)", "\t\t",
                         System.Text.RegularExpressions.RegexOptions.IgnoreCase);
                result = System.Text.RegularExpressions.Regex.Replace(result,
                         "(\t)( )+(\r)", "\t\r",
                         System.Text.RegularExpressions.RegexOptions.IgnoreCase);
                result = System.Text.RegularExpressions.Regex.Replace(result,
                         "(\r)( )+(\t)", "\r\t",
                         System.Text.RegularExpressions.RegexOptions.IgnoreCase);
                // Remove redundant tabs
                result = System.Text.RegularExpressions.Regex.Replace(result,
                         "(\r)(\t)+(\r)", "\r\r",
                         System.Text.RegularExpressions.RegexOptions.IgnoreCase);
                // Remove multiple tabs following a line break with just one tab
                result = System.Text.RegularExpressions.Regex.Replace(result,
                         "(\r)(\t)+", "\r\t",
                         System.Text.RegularExpressions.RegexOptions.IgnoreCase);
                // Initial replacement target string for line breaks
                string breaks = "\r\r\r";
                // Initial replacement target string for tabs
                string tabs = "\t\t\t\t\t";
                for (int index = 0; index < result.Length; index++)
                {
                    result = result.Replace(breaks, "\r\r");
                    result = result.Replace(tabs, "\t\t\t\t");
                    breaks = breaks + "\r";
                    tabs = tabs + "\t";
                }

                // That's it.
                return result.Trim();
            }
            catch (Exception e)
            {
                //MessageBox.Show("Error");
                throw e;
            }
        }

Update email properties

I had a client requirement to update few of the attributes of incoming email.

The incoming email was tracked on customers based on the from email address. Customer did not want the auto-resolution of emails to customers as many of the customers had the same email ( family members tend to have the same email for all of them). So they want to remove the customer name from the auto resolution.

The response from me was @£$€£€@$€{]}[$£

yeah it's possible. but HOW? We remove the activity party from the sender list... sounds simple :)

Go through the code to find yourself

For example they wanted to have a list view of email with the body of the email (a quick preview of the email).

This was not possible using the standard description field of the email as it contained HTML codes. The alternative is to create a new column to hold the plain text email. So I went ahead and created a new multiline text field with 2000 char limit.

I added a plugin on post activity create to update the description of the email. optionally the same plugin is to be added on email update for the description field.

Here you go with the code. target is the email entity

Entity target = context.InputParameters["Target"] as Entity;

Code:

Entity _email = service.Retrieve("email", target.Id, new ColumnSet("from","to", "description", "directioncode"));

                    if (!((bool)_email["directioncode"])) //Incoming
                    {
                        EntityCollection from = (EntityCollection)_email["from"];

                        if (from.Entities.Count > 0)
                        {
                            foreach (Entity actParty in from.Entities)
                            {
                                if (actParty.Contains("partyid"))
                                {
                                    actParty["partyid"] = null;
                                }
                                else
                                {
                                    actParty.Attributes.Add("partyid", null);
                                }
                            }
                        }
                        _email["from"] = from;
                    }
                    else //outgoing
                    {
                        EntityCollection to = (EntityCollection)_email["to"];

                        if (to.Entities.Count > 0)
                        {
                            foreach (Entity actParty in to.Entities)
                            {
                                if (actParty.Contains("partyid"))
                                {
                                    actParty["partyid"] = null;
                                }
                                else
                                {
                                    actParty.Attributes.Add("partyid", null);
                                }
                            }
                        }
                        _email["to"] = to;
                    }
                    if (_email.Contains("description"))
                    {
                        string desc = _email["description"].ToString();
                        string descStrip = PluginHelper.StripHTML(desc);
                        descStrip = descStrip.Trim();
                        string desc4 = "";
                        string prevLine = "";
                        foreach (string currLine in descStrip.Split('\r'))
                        {
                            if (currLine != prevLine)
                            {
                                prevLine = currLine.Trim();
                                desc4 += currLine.Trim();
                                if (currLine != "")
                                {
                                    desc4 += "\n";
                                }
                            }
                        }

                        if (desc4.Length > 2000)
                            desc4 = desc4.Substring(1, 2000);

                        if (_email.Contains("ssb_mail_text"))
                        {
                            _email["ssb_mail_text"] = desc4;
                        }
                        else
                        {
                            _email.Attributes.Add("ssb_mail_text", desc4);
                        }
                        _email.Attributes.Remove("description");
                    }
                    service.Update(_email);

Close queueitem

Use the code template from here

If you need to close the queueitem after creating a case from an email, here you go with the code

target is of type entity "email"

Code:

        if (target.Contains("regardingobjectid"))
        {
            EntityReference _object = (EntityReference)target["regardingobjectid"];

            if (_object.LogicalName == "incident")
            {
                CloseQueueItem(_object.Id, service);
            }
        }

Code:

public static bool CloseQueueItem(Guid _sourceId, IOrganizationService service)
    {

        Guid _queueItemId = Guid.Empty;
        bool _queueItemClosed = false;

        ConditionExpression ent_ce1 = new ConditionExpression("objectid", ConditionOperator.Equal, _sourceId);
        ConditionExpression ent_ce2 = new ConditionExpression("statecode", ConditionOperator.Equal, 0);
        FilterExpression ent_fe = new FilterExpression(LogicalOperator.And);
        ent_fe.AddCondition(ent_ce1);
        ent_fe.AddCondition(ent_ce2);

        QueryExpression ent_qe = new QueryExpression();
        ent_qe.EntityName = "queueitem";
        ent_qe.Criteria = ent_fe;
        ent_qe.ColumnSet = new ColumnSet("queueitemid", "queueid", "objectid");
        ent_qe.PageInfo.ReturnTotalRecordCount = true;
        EntityCollection ent_coll = service.RetrieveMultiple(ent_qe);

        if (ent_coll.Entities.Count > 0)
        {
            foreach (Entity _queueItem in ent_coll.Entities)
            {
                EntityReference enRef = (EntityReference)_queueItem["objectid"];
                PluginHelper.SetState(_queueItem.LogicalName, _queueItem.Id, 1, 2, service);
            }
            _queueItemClosed = true;
        }

        return _queueItemClosed;
    }

Code snippet to increase Aggregate Query Record Limit

Use the code template from here

Code:

DeploymentServiceClient service = Microsoft.Xrm.Sdk.Deployment.Proxy.ProxyClientHelper.CreateClient(new Uri("http://crmtest/XRMDeployment/2011/Deployment.svc"));

                RetrieveAdvancedSettingsRequest request3 = new RetrieveAdvancedSettingsRequest()
                {
                    ConfigurationEntityName = "Deployment", ColumnSet = new ColumnSet(false)
                };
                RetrieveAdvancedSettingsResponse response3 = (RetrieveAdvancedSettingsResponse)service.Execute(request3);
                ConfigurationEntity configEntity = response3.Entity;

                ConfigurationEntity entity = new ConfigurationEntity();
                entity.LogicalName = "Deployment";
                entity.Attributes = new Microsoft.Xrm.Sdk.DeploymentAttributeCollection();
                entity.Attributes.Add(new KeyValuePair<string, object>("AggregateQueryRecordLimit", 50000));
                     
                UpdateAdvancedSettingsRequest request2 = new UpdateAdvancedSettingsRequest();
                request2.Entity = entity;
                service.Execute(request2);

Update the deployment service URL 

Code snippet to increase max records exported to excel

Use the code template from here

In the example below I am updating max records exported to excel.

Code:

QueryExpression qe = new QueryExpression("organization");
                qe.ColumnSet = new ColumnSet("maxrecordsforexporttoexcel");
                EntityCollection entColl = _service.RetrieveMultiple(qe);
                if (entColl.Entities.Count > 0)
                {
                    Entity ent = entColl.Entities[0];
                    ent["maxrecordsforexporttoexcel"] = 500000;
                    _service.Update(ent);
                }