Create dynamic link for related record from Dynamic CRM Global Email Template

Very interesting article from my colleague – Rajesh

Amiz crm

One of my Requirement is to create the link for Opportunity while qualifying Lead. But in CRM AS – IS functionality we can’t give the ID value, it should give the Name value only. But when you create the link we need the Id of the record. So we need to do the following code.

So I open the Email Template and add the Qualifying Opportunity field in that template using Insert/Update button.

Template Link 1

Save the template and ran the template, It shows only name of the opportunity. In Default CRM will take {!lead:qualifyingopportunityid/@name;}, that so template will display name of the lookup values.

Template Link 2

But Creating link we need the opportunity id, So I did the following steps

Open the template and edit the following text Save the template.

{!lead:qualifyingopportunityid;}

Template Link 3

Now I ran the template, the Output was came with my exception.

Template Link 4

I hope this will helps U 🙂

View original post

Create automated email notification when your incoming emails stops synchronizing in your Dynamics CRM

First let me explain the requirement here. Our client had set up server side sync for incoming email synchronization. There is one support mailbox to which end-users sends email. Once the email comes to support mailbox, the email is forwarded to CRM at every specified interval. And once it comes inside CRM, it is assigned to some queue.

But one regular problem that our customer is facing that email synchronization stops due to mailbox errors or some other issues related to our async service and then it does not get caught unless some support user complains that customers have already sent an email, but the email does not appear in his queue.

Obviously this can be caught by continuous monitoring of the mailbox. But that is something very strenuous. So I asked my customer – “What if I could automate this?”. The word automation excites every customer and my customer was not an exception.

These days I do not get to much hands on. More on architecting and design. Perhaps one of the perils as you grow up in experience Smile. But being from a programming background, whenever I get a chance, I grab it with both hands.

So this is what I did.

  • Created a console application.
  • In the console application, I set-up connection to both our CRM organization and the Exchange Mailbox.
  • In then query the CRM Emails entity to find the timestamp of the last incoming email.
  • I take that timestamp and pass it to the below function.

internal static List<EmailMessage> GetEmailsNotInCrm(DateTime dateToCompare, ExchangeService service)
        {
            SearchFilter.IsGreaterThan search = new SearchFilter.IsGreaterThan();
            search.PropertyDefinition = ItemSchema.DateTimeReceived;
            search.Value = dateToCompare;

            FindItemsResults<Item> findResults = service.FindItems(
                        WellKnownFolderName.Inbox, search,
                                        new ItemView(100));

            var emailMessageCollection = new List<EmailMessage>();

            foreach(var emailItem in findResults)
            {
                EmailMessage message = EmailMessage.Bind(service, emailItem.Id);

                emailMessageCollection.Add(message);
            }

            return emailMessageCollection;
        }

        internal static ExchangeService CreateExchangeConnection()
        {
            ExchangeService service = new ExchangeService(ExchangeVersion.Exchange2007_SP1);

            service.Credentials = new NetworkCredential(ConfigurationManager.AppSettings["owausername"],
                System.Text.Encoding.UTF8.GetString(Convert.FromBase64String(ConfigurationManager.AppSettings["owapassword"])), "<your company domain where the mailbox is hosted>");

            service.Url = new Uri(ConfigurationManager.AppSettings["owaurl"]);

            return service;
        }

Please note that you would need to reference the Microsoft.Exchange.Webservices.dll for the Exchange related classes.

The function above queries for all the emails that have come to the Mailbox and are still not created in CRM. The method ‘CreateExchangeConnection’ creates a connection to the exchange mailbox.To explain the code a bit,

dateToCompare – This parameter is the datetime value of the last incoming email created in CRM.

So here I am searching the inbox for all the emails which arrived in the inbox after the last incoming email in CRM. And using the ItemView class, I am retrieving only 100 records.

I then create a collection of EmailMessage objects and then pass it to the below methods which prepares the email body and send the email to CRM support team alias for our customer.

/// <summary>
        /// Method to send email for debugging purpose when the job fails.
        /// </summary>
        /// <param name="emailBody">The email body.</param>
        /// <param name="subject">The operation which failed</param>
        internal static void SendEmail(List<EmailMessage> messages, DateTime dateToCompare)
        {
            try
            {
                bool sendEmail;
                MailMessage msg = new MailMessage();
                string[] arr = ConfigurationManager.AppSettings["ToMails"].ToString().Split(‘;’);
                foreach (var mail in arr)
                {
                    msg.To.Add(mail);
                }
                string fromEmail = ConfigurationManager.AppSettings["FromMail"].ToString();

                msg.From = new MailAddress(fromEmail);

                msg.Subject = string.Format("Email Age report");

                var htmlBody = EmailHelper.GetEmailBody(messages, dateToCompare, out sendEmail);
                if(!sendEmail)
                {
                    return;
                }

                msg.Body = "The following are the emails arrived in mailbox which are still not pending creation in CRM:<br />" + htmlBody;
              
                msg.IsBodyHtml = true;
                SmtpClient client = new SmtpClient();
                client.Host = ConfigurationManager.AppSettings["SMTPServer"].ToString();

                try
                {
                    client.Send(msg);
                }
                catch (Exception ex)
                {
                    if (ex is SmtpException)
                    {
                        try
                        {
                            client.Send(msg);
                        }
                        catch (Exception secondEx)
                        {
                            throw secondEx;
                        }
                    }
                    else
                    {
                        throw ex;
                    }
                }
                finally
                {
                    if (msg != null)
                        msg.Dispose();
                    if (client != null)
                        client.Dispose();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        internal static string GetEmailBody(List<EmailMessage> messages, DateTime dateToCompare, out bool sendEmail)
        {
            sendEmail = true;
            var removeList = new List<EmailMessage>();
            var sb = new StringBuilder();

            sb.Append("<table style=’border: solid 1px black’ cellspacing=’0′><tr><th style=’width:200px;border:solid 1px black’>Email details</th><th style=’border: solid 1px black’>Age</th></tr>");

            foreach(var email in messages)
            {
                var timeSpan = dateToCompare.Subtract(email.DateTimeReceived);

                if(timeSpan.Days == default(int) && timeSpan.Hours == default(int) && timeSpan.Minutes == default(int) && timeSpan.Seconds == default(int))
                {
                    removeList.Add(email);
                    continue;
                }

                sb.Append("<tr><td style=’border: solid 1px black’>");
               
                sb.Append(string.Format("From: {0}{1}", email.From.Address, "<br />"));
                sb.Append(string.Format("To Recipients: {0}{1}", string.Join(";",email.ToRecipients.Select(t => t.Address)), "<br/>"));
                sb.Append(string.Format("CC Recipients: {0}{1}", string.Join(";", email.CcRecipients.Select(t => t.Address)), "<br/>"));
                sb.Append(string.Format("Email subject: {0}", email.Subject));

                sb.Append("</td><td style=’border: solid 1px black’>");
               
                sb.Append(timeSpan.Days + "d: " + timeSpan.Hours + "hh: " + timeSpan.Minutes + "mm: " + timeSpan.Seconds + "ss");
                sb.Append("</td>");
                sb.Append("</tr>");
            }

            sb.Append("</table>");

            if (removeList.Count == messages.Count)
            {
                sendEmail = false;
            }

            return sb.ToString();
        }

The subject of the email is ‘Email Age Report’ and below is a sample of the emails that the support team received after I configured this console application.

image

 

What I did, is I scheduled the console application in the task scheduler in one of CRM async servers and configured it to run every 30 mins. So if the support team receives a report in which there the email age is more than 30 mins or so, they know that there is some problem.

Did the customer like it? Oh Yeah! the kind of accolades our team got for this was in fact more than some of the complex requirements we implemented for the customer. Smile And that too we achieved all this in 2 hours which was like icing on the cake.

Hope this helps!

{Tips & Tricks} Explore a managed solution’s plugin assembly programmatically in Dynamics CRM Online.

I never find a good title to explain my blog post. So I would rather suggest you read through my entire post even if blog title seems absolutely non-sense Smile.

So coming back to the topic here, first let me tell you why I needed to do all this stuff. My customer environment contained a managed solution which had a utility plugin. The solution was developed by some consultant company who was working for my customer initially. The source code for the managed solution was with the consultant firm. Unfortunately during handover, the source code handover got missed.

My client is running CRM 2015 online and all of a sudden, this managed assembly started throwing error. It contained some cool stuffs. So we cannot get rid of it. After multiple discussions with our client, we decided to rewrite the stuffs in the assembly. So we need the assembly. But how do we do it?

Since it is a managed solution, you cannot export it. After putting my head around it, I created a console application to get out the DLL. Just a few lines of code and I am pretty sure that if you are from a programming background like me, you would love this. So let’s see the code here.

private static void ReadPluginAssembly(OrganizationServiceProxy proxy)
        {
            var queryExp = new QueryExpression("pluginassembly");
            queryExp.Criteria.AddCondition("name", ConditionOperator.Like, "TestPlugins%");

            queryExp.ColumnSet.AddColumn("content");
            var result = proxy.RetrieveMultiple(queryExp);

            var base64Content = result.Entities[0].GetAttributeValue<string>("content");
            var byteContent = Convert.FromBase64String(base64Content);

            System.IO.File.WriteAllBytes(@"c:\debajit\TestPlugins.dll", byteContent);

        }

Let me explain the code here a bit. First I have queried the pluginassembly entity for the particular assembly in question here and retrieve the content field. The content field is where CRM stores the entire content of the plugin assembly as base 64 encoded string.

So I get the content and covert the base64 string to byte content. And then I saved the byte content to an assembly with the same name.

And how do I get the code from here. Use the wonderful Red-Gate .NET reflector or ILSpy and you would find much of the code. With little bit of modifications, you can just copy paste the entire code.

Hope you find this interesting.

Save your changes in CRM directly from excel online–Dynamics CRM 2015 online Update 1

Dynamics CRM 2015 Online update 1 brought many changes. And the most talked about changes I keep hearing about are the new SDK changes that came with it. I am from hard-core programming background and honestly nothing excites me than getting dirty with new stuffs hands-on.

However in my role as a consultant, I have to constantly thrive for a “wow” from my customer. And sometimes small small features unexpectedly brings that out from the customer.

So here was I demoing some CRM stuffs to the business. With online update 1, you can export your data to excel online, make some changes to the data and save the changes from the excel directly back to CRM.

Lets take the opportunity entity here for an example.

  • Go to My Opportunities view. The trick here is to put the most used columns in the view so that when the user exports this excel, he/ she can edit all the necessary fields.
  • From the ribbon, select Export –> Open in Excel Online.

image

  • The data will open in excel online. Make necessary changes to the data. Here for an example, I am updating the “rating” values for the opportunity.

image

  • after making all the changes, the interesting part is you can save the changes back in CRM. You have a button at the top ‘Save changes to CRM”

image

  • Once clicked, in the background CRM creates a data import job. It might take some time before the data is refreshed in CRM. So I come back in the CRM grid and find my data has been refreshed.

image

Internally CRM uses the same Data Import job to import the data. But for the end-user it’s so easy to just open in excel and import the data back in CRM virtually doing nothing with CRM doing all the trick in the background.

Certainly a WOW experience for the end-user.

So if you are demoing CRM online and don’t miss out on this. Believe me it’s this rich end-user experience which would impress the business more.

 

Hope this helps!

Explore Channel Engagement Framework for automatic creation of entities from activities–Dynamics CRM 2015 Online Update 1

Recently I got a chance to explore this while doing a small POC for one of my customers. And boy! I liked it. All this time I was hearing about this and also watched the MSDN video. But nothing is true experience until you get a first hand experience of it.

Here was the scenario for our customer. The customer has a social engagement portal which tracks the posts in the social engagement channels (like Facebook, Twitter, Linkedin etc) and feeds the JSON response to an activity in the form of JSON response. From the JSON response, we had to create a contact. So we had two options here.

  • Parse the JSON response and write some interface that would create the contact in CRM after parsing the JSON from the portal. This can be achieved by using SDK and JSON parsers like Newtonsoft.json.dll.
  • Use the ‘Automatic record creation and update rules’ feature that comes along with Dynamics CRM 2015 Online update 1

If something is present OOB, why re-invent the wheel. And also with configurations the advantage is the maintainability since you can get away with the engagement of a developer for any kind of changes/ fixes in the code.

First of all, I created a custom activity  of called – ‘Social Engagement Activity’. To identify the channel from where the activity is created, I created a custom field ‘Activity Channel’ to determine the social channel from which the activity is created.

The following is the json output from the social portal. Off-course we have lot more values. However for an example I am presenting a simplified version of the json we are getting.

{
FirstName: “Debajit”,
LastName: “Dutta”,
Email:
debajit.prod@gmail.com
}

  • Now go to Settings –> Service Management –> Automatic Record Creation and Update Rules.
  • Create a Record Creation Rule.
  • Enter the basic information for the rule

image

  • This is extremely interesting. Now create a new channel property. These are the properties that will be mapped to the JSON properties that I specified earlier in the Activity Additional Parameters’ field.

image

  • Create the Channel Property corresponding to each property of the JSON output. Remember the property names are case sensitive. Since my JSON input contained three parameters FirstName, LastName and Email, I have created the same three channel properties below.

image

  • Now the next step is to specify the rules for record creation. Go to the rules creation section and click to create a new rule.

image

image

  • You can specify the conditions but in my case no condition was needed. Now comes the important part. When we set the properties of the contact, in the form assistant you will see the channel properties you created. So I map the contact fields with value from the channel properties.

image

  • Now we need to activate the rule. And you are done with the settings.

image

  • Now I go ahead and create a new ‘Social Engagement Activity’ record. with the values specified below.

image

ideally this should be created from the social portal.

  • And voila, the contact record is created. Isn’t it great!

image

I have just given a simple example here but I guess with the features provided you can leverage it to build complex functionalities.

Hope this helps!

{Resolution}–SSRS report not rendering in Dynamics CRM

Since the title is not explanatory, let me explain the topic here first. Recently I was asked to look  into custom SSRS reports which were working fine until recently it started throwing intermittent errors where the report was not rendering. Users were getting the below errors.

  • Reports were rendering empty with no data
  • “An error occurred during client rendering”
  • The underlying connection was closed. An unexpected error occurred on receive

Since the errors were not consistent, the first thing we tried is identify if some change has been made in the report service configuration. Confirmed with our administrator that no changes in the report server configuration.

So we were back to the reports. So first of all I asked the developer to check if the reports are working fine from BIDS consistently. Verified it worked just fine. So after going over the issue for almost 3-4 hours finally found the root cause.

A recent parameter has been added to the report which contains significant number of values. This error occurs simply because your report uses more than the allowed number of elements in the filters/parameters. The default limit is 1000, but you can change it.

Locate the web.config files of the ReportManager and ReportServer. Files are usually in this location (I have reporting services 2012)

C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportManager\Web.config
C:\Program Files\Microsoft SQL Server\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\Web.config

 

Add the below appSettings elements. If the appSetting section is not present, then add the below values along with the section.

<appSettings>

   <add key="aspnet:MaxHttpCollectionKeys" value="30000" />

   <add key="aspnet:MaxJsonDeserializerMembers" value="30000" />

</appSettings>

Restarted the SQL server reporting services and the reports started coming up.

Please note that these errors we were getting might be caused for other issues as well. However this can be one of the possible causes.

Hope this helps.

Optimize your global search by enabling full text search in Dynamics CRM

Are you using CRM 2015 and you have complaints regarding the global search 2015 performance? Well then you are right place.

with CRM 2015 Update 1, Microsoft provides a feature to enable full text search for your organization. Once you download and install Update Rollup 1 for Dynamics 2015, this option would be available to you.

Go to Settings –> System Settings.

In the Setup Quick Find Settings, you would see a new option to enable full-text search

image

You would need to enable it.

Don’t expect to see magic immediately. It may take up to 24 hours for full text search since maintenance job has to be run on the server and make adjustments in the specified CRM Organization. Let’s get a bit under the hood here.

When you enable full text search from your CRM, internally CRM uses the full text catalog. If you open SQL server and navigate to your CRM Organization database –> Storage –> Full text catalogs, you would find the entities mapped for full search. Please note that as I said before, this takes considerable amount of time, hence if you immediately move to your database after enabling the setting in CRM, you might not find the table mappings.

image

 

All this about technical stuff but what about end-user experience?

Well the following are the changes

  • The first change that the user would notice is that they do not need to use wildcards anymore. For example – when before full text search is enable, if you search for the word test and if I take account entity, it would return all account whose any quick find column value begins with Test. However after you enable full text search, users no need to use wild card search. Search by any word and it would evaluate with contains operator.
  • Full Text search enables some data science on your data. It actually returns forms of a word.
    • Incorporates Pluralization (“child”, “children”, etc)
    • Incorporates Tense (“drive”, “drove”, “Driven” etc)
  • Now coming to performance – When I tested this in one of my customer environments, I was simply amazed and impressed by the performance. Previously when the search phrase – *Test returned results in 15 seconds. After enabling Full Text search, the query returns records in less than 2 seconds.

 

Before I close this out, this works only on string field. Optionset fields and likewise would not be incorporated in full-text search.

 

Hope this helps!

Read Committed Snapshot Isolation (RCSI)–Know before you use it for your Dynamics CRM Database

‘Read Committed Snapshot Isolation’ Or RCSI is short is something I continuously keep hearing in my CRM implementations every time there is any discussion related to the CRM performance.  On the lighter side of it, the term itself is very catchy isn’t it.

Since it’s not a database related blog, I will keep the concepts very simple here and focus more on the Dynamics CRM performance part of it. Typically from SQL sense, an isolation level is the degree to which one transaction must be isolated from resource or data modifications made by other transactions. The isolation level under which a Transact-SQL statement executes determines its locking and row versioning behavior.

By default the isolation level for your CRM Organization connection is ‘Read Committed’. You can check by running the below command. You would get a result similar to the below.

DBCC UserOptions

image

In ‘read committed’ isolation, your SQL statement views the most-recently committed data as of the moment each item is physically read. To put it simple, for read committed isolation, each row is locked briefly and physically read.

RCSI improves on this by removing row locking part totally while reading of rows. It provides transaction with the point-in-time view of the committed data, where the point-in-time is the time when the transaction starts executing. When this isolation level is maintained, SQL server maintains row versioning and during read no shared locks are acquired physically because the entire transaction reads from the row version store rather than being accessed directly.

To put in the words of MSDN – “Transactions that modify data do not block transactions that read data, and transactions that read data do not block transactions that write data, as they normally would under the default READ COMMITTED isolation level in SQL Server”

From the explanation above, it make obvious sense that it would result in increase in performance. And normally whenever there is any performance discussion on CRM, the question that I come across is – ‘we are thinking of implementing RCSI and it should increase performance. What’s your thoughts?’

Before we implement anything, we should also learn the caveats of it and personally I know many customers who have suffered (transactions rolled back and other stuffs). While this makes an attractive proposition for implementing, let’s see what can be the downside to it.

  • RCSI leverages the tempdb database to store a copy of the original row and adds a transaction sequence number to the  row. So it is important that the physical environment is configured to cope with this, primarily in terms of tempdb performance and memory/disk space requirements.
  • Many of CRM customers have complained that updates are rolled back frequently after enabling RCSI. This is because snapshot isolation uses an optimistic concurrency model. If a snapshot transaction attempts to commit modifications to data that has changed since the transaction began, the transaction will roll back and an error will be raised.
  • When snapshot is enabled, there are no shared locks. Any statement running with snapshot isolation cannot see any committed database modifications that occur after the statement starts executing. The longer the statement runs for, the more out-of-date its view of the database becomes, and the greater the scope for possibly-unintended consequences.

For e.g – You have a long running query which determines whether an email should be sent at certain step of the query, based on some attribute value of an entity. Since this is running in snapshot, the transaction at the beginning has taken a copy of the data for the transaction in the tempdb database. So when the email sending condition is evaluated, the value of the determining field might have changed in the database. But since the query is working on the snapshot of data some time back (might be few seconds), it would still evaluate the e-mail sending condition to true.

This is off-course an example. However I hope you guess what analogy I am trying to draw here.

So before you implement, just think what is the concurrency level for your CRM usage and then discuss with your DBA before reaching a decision.

Finally if you go ahead and enable the RCSI, run the below commands.

ALTER DATABASE <Your crm database>
SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE <Your Crm Database>
SET READ_COMMITTED_SNAPSHOT ON
Hope this helps!

{Utility}–Access team template migrator for Microsoft Dynamics

Access Teams! Great feature that came with Microsoft Dynamics CRM 2013 version. However after repeated implementations, despite the benefits that access templates provide, one common complain that I have heard

“Why are access teams not solution aware?”

Well lets get a bit deeper here. It’s not the access teams which is loosely mentioned in the above question, that is our problem. It is the access team template and the access team grid configurations on the entity forms which needs to be repeated across environments. And if you have more than a few of them to handle, it can become annoying.

Well, in case you are in the same boat as me, the following utility might come as a sight of relief to you. I have uploaded it in codeplex and it’s free to use.

https://accessteamsmigrator.codeplex.com/releases/view/618241

Please read the detailed documentation on how to use this tool to your advantage – https://accessteamsmigrator.codeplex.com/documentation

This is a beta version. So in case you have any issues with this tool, please drop an email to debajit.prod@gmail.com and I will get back you.

Hope this helps!

image_thumb3_thumb