Tip #223: Firefox and a message box

Browsers are on a war path. First, in case you were wondering why editing emails in workflows no longer work, that’s because Chrome disabled showModalDialog, breaking bucketloads of CRM functionality relying on that. See this good summary for a comprehensive feature list and a temporary workaround.

Firefox has been much sneakier than that. It kind of works but every now and then drops the checkbox on unsuspecting user, that is way too easy to accidentally click, press OK and render your CRM useless until the browser restart.
Prevent this page from creating additional dialogs
To stop Firefox from ever sneaking this option in:

  1. Open new tab, type about:config in address bar and press Enter
  2. Ignore irreverent message about voiding your warranty since there is none to start with
  3. Copy and paste (or type) this into Search box: dom.successive_dialog_time_limit
  4. If found, right mouse click > Modify
  5. If not found, right mouse click > New > Integer > name is dom.successive_dialog_time_limit
  6. Value is magic 0

Tip #222: Mixed signals

I am very honored to have Joel as my fellow CRM tipster. Most of the time, that is. Sometimes I find myself closely studying the state of my own footware thinking if I should ever be seen in one room with that person. Take, for example, this post.

Update each message for the entity, replacing the old name with the new entity name.

I suspect that from time to time Joel employs an eight-years old to write his posts (I have no other explanation for his insane productivity). “Update EACH message”?!

To stop Joel from publishing another tip on how to modify EACH label as well, let me show how professionals do that.

  1. Open the solution your entity is a part of. No, not the default one. You do use solutions in your daily development, don’t you? If you don’t have one, create one and add the entity to it.
    Export Translations
  2. Click that magic Export Translations button
  3. Extract CrmTranslations.xml file from downloaded zip archive
  4. Open it with Excel
  5. Use magic functionality called Search & Replace to turn Accounts into Companies. You may need couple passes to deal with all the forms and capitalization. Use common sense: case-sensitive search, plural first, etc. Deal with Display Strings and Localized Labels at the same time.
  6. Save, re-pack, click Import Translations, upload updated zip file, publish.

Like a boss memeDone.

Tip #221: Lobachevskian geometry

When things get out of control in a parallel universe, people notice and intervene. As turned out to be the case. One comment from Chad Rexin was so good that it deserved its own post. Welcome to the twisted SQL world where, like in Lobachevskian geometry, there is more than one degree of parallelism.

Another way to get good performance is to leave the Max Degree of Parallelism at 0 or set to a max of 4 if you have 4 or more CPUs/Cores, but also simultaneously setting the ‘Cost Threshold of Parallelism’ = 60 up from the default of 5, which is a reflection of what SQL estimates the query time in seconds will be and at which point it will generate a parallel plan. This way you can let SQL Server run with a maximum of 1 CPU in query plans for all but the more expensive query plans. This way SQL will only generate a query plan allowing for multiple CPUs if it thinks the query will take longer than 60 seconds and giving you the best of both worlds in case you have some Reporting databases or Analysis service databases that happen to be on the same SQL Server as the CRM databases so that both can run optimally. Even with servers that have many more than 4 cores, you may not see much of an advantage or may even see a drop in performance setting the max degree of parallelism higher than 4 from what I have seen in very busy Microsoft CRM environments where there is 1000 or more active users and/or active integrations going on.

Ultimately, you may need to run with a constant work load and test to determine which setting is optimal. Below is the T-SQL statements to make this change.

sp_configure ‘max degree of parallelism’,4
Reconfigure with Override;
GO
sp_configure ‘cost threshold for parallelism’,60
Reconfigure with Override;
GO

Tip #220: Mixed messages

You are implementing Dynamics CRM, and you don’t like the entity name “Accounts.” Maybe you work for a bank, where account has a specific meaning in your organization. So you rename the entity to “Company.” That was easy.

Not so fast. Just changing the name of the entity to “Company” will change the name of the entity links in the application, but it will not update the text included in error messages, buttons, and tool tips. For example, when you hover over the “Deactivate” button on the command bar, the tool tip says “Deactivate these accounts.”

deactivate account

This will cause user confusion. If the entity is called “Company,” the message about accounts will not make sense. A user may think that since it says ‘deactivate these accounts,” pushing the button will deactivate the financial accounts associated with the company, while in reality it will deactivate the company record.

That is why, when renaming an entity, it is very important to update the message text. In customization for the entity, click on “Messages.”

Messages

Update each message for the entity, replacing the old name with the new entity name.

Tip #219: You want the top one

One of the challenges of creating reports is the lack of real data in development environment. There is not enough Nancies Davolios to simulate real volumes. For that reason alone production organization is often replicated “back” to test and development environments. Reports suddenly come to life and designers can see 7-figures opportunities and otherwise empty categories.
There is a price to pay, however. If you ever designed report with explicit pre-filtering then at some point you have a data source with dynamic SQL that looks like this:

SET @SQL = 'SELECT Name, DueDate, TotalAmount
FROM ('+@CRM_FilteredInvoice+') AS FA 
where ShipTo_StateOrProvince = ''FL''

UNION

SELECT Name, DueDate, TotalAmount
FROM ('+@CRM_FilteredInvoice+') as CA 
where ShipTo_StateOrProvince  = ''CA'' '

EXEC (@SQL)

All is well until you press OK button at which point you are presented with this:
Parameter prompt

It’s understandable, report designer is very determined to parse the statement to figure out the columns. There is a temptation to type select * from filteredinvoice and be done. If you are developing in a replica of production environment, chances are that will be your opportunity to have a very long lunch. There is not much fun watching parser trying to digest 2 million records for the sake of figuring data type of TotalAmount.

The solution? Use top 1 clause to limit resultset to one record only as parser does not need any more than that:
One invoice only

Tip #218: Sales Literature vs. SharePoint

So you have implemented Dynamics CRM with SharePoint document integration. The mindset of many companies with SharePoint is that SharePoint is where all documents should live, and that is a good idea, as SharePoint provides document collaboration features not found in CRM.

However, Microsoft Dynamics CRM includes Sales Literature functionality. The value of sales literature is that it makes product brochures available to sales reps from where they work—sending emails in Outlook. It also allows related documents to be grouped together, so if a product has 5 documents that have to be sent when someone emails a customer with a quote for that product, sales literature can be a big time saver. I refer to sales literature as “email attachment templates” because it pre-groups related attachments that are frequently sent and makes life much easier. By inserting one sales literature record into an email, the sales rep gets all of the related attachments that are part of the sales literature record. It is much more accurate than sales reps sending a copy of the literature from their hard drive that may be outdated or incorrect. Documents only stored in SharePoint are not going to be as easily accessible to salespeople as they send emails to clients, nor are they going to be connected to products and campaigns like they are with Sales Literature.

It doesn’t have to be either/or. Many companies use SharePoint for marketing to collaborate on the documents as they design them and store the master copies in SP, but also use sales literature to group and expose these documents to sales.

It can be a win-win—marketing collaborating on the document in SharePoint, exposing it to sales via sales literature, and sales representatives saving time when sending product brochures and other marketing collateral to customers..

Tip #217: Bad Id, bad

Following my anal retentive attention to the tightness of LINQ code and SQL it generates, I decided to refactor an old piece of code that extracted identifiers of the accounts matching certain criteria. Being very smug with all the newly acquired knowledge, I quickly produced an equivalent of:

var activeAccounts = crm.AccountSet
   .Where(a => a.Name.StartsWith("Bar"))
   .Select (a => a.Id);
           
foreach (var id in activeAccounts)
{
   Console.WriteLine("{0}", id);
}

To my horror, this code generated an equivalent of “select *” SQL statement (ok, it listed the columns explicitly but it listed all columns), neutralizing all my efforts. Explicit column name, on the other hand, generated mean and lean SQL:

var activeAccounts = crm.AccountSet
   .Where(a => a.Name.StartsWith("Bar"))
   .Select (a => a.AccountId);

object.Id is a very handy abstraction, but when it comes to LINQ, it’s a leaky one.

Tip #216: In a parallel dimension

I’ve had the following discussion with two different CRM administrators in the past week. We’ll call them “Larry.”

Larry: “My CRM organization is very slow. I try to search for records and it takes 25 seconds. I tried to import an organization, and it took 25 hours to complete.”

Me: “What is your Max Degrees of Parallelism setting in SQL?”

Larry: “0”

Me: “Change it to 1.”

Larry: “That’s much better!”

This is an old tip (I think I blogged about it in a “optimal settings for CRM 3” post, but I can’t find it), but it is one of the most frequent causes of less than optimal CRM performance, because this property is set to 0 by default when SQL Server is installed. The default setting means that SQL server gets to choose how many processors are used in the execution of a query. Sometimes you can be ok with the default setting, but it is especially a problem when you have a SQL server that CRM shares with other applications. SQL may decide to use all of the processors for another application’s queries, and CRM requests may be delayed. In both of the cases I saw it in the last week, CPU resources did not appear to be overloaded at the time the slow performance took place, but changing the Max Degrees of Parallelism to 1 made a huge difference.

Common objections to changing this setting

  • If it should be set to 1, Microsoft would have it default to that setting.

Microsoft sets it to 0 as that is the most common setting, but strongly recommends that you change it based on application query patterns. CRM documentation like the implementation guide and the  Server Optimization Whitepaper specifically recommend that Max Degrees of Parallelism be set to 1 for CRM.

  • I have another database, like a reporting data warehouse, that needs to have a different setting

If you want to sacrifice CRM performance to make your reporting data warehouse work better, that is your choice, but in trying to save a little bit of money on SQL licensing, you will probably wind up with frustrated users and lost time waiting for CRM forms to load. If you want to save money and cut corners on your deployment infrastructure, SQL is not the place to do it.

Tip #215: Computer says ‘no’

Well, this is embarrassing. Remember that flogging I unleashed on unscrupulous developers being disrespectful to LINQ, performance and humanity in general? I was basking in my own cleverness of possessing the knowledge of LINQ operators including magic Any:

if(crm.OpportunitySet
    .Any(opp => opp.EstimatedValue >= 1000000m))
{
   // that would have been a winner if it worked
}

Yesterday I had a chance to use what I preached and this is what I got in return:

System.NotSupportedException was unhandled
HResult=-2146233067
Message=The method ‘Any’ is not supported.
Source=Microsoft.Xrm.Sdk

That proves two things:

  1. Never assume anything when it comes to complex products like Dynamics CRM. Have a theory? Run a test – usually it does not take long.
  2. Support for LINQ syntax is a responsibility of LINQ-to-CRM provider that translates LINQ expressions into QueryExpression. LINQ providers are notoriously difficult to write and they are created by humans who pick their battles and choose spend time elsewhere but sweating on support for obscure operators that may end up never being used.

So what about that “yes/no” test? This is the best result so far:

if(null != crm.OpportunitySet
  .Where (opp => opp.EstimatedValue > 1000000m)
  .Select(opp => new { opp.OpportunityId })
  .FirstOrDefault())
{
   Console.WriteLine("Computer says 'yes'!");
}

That LINQ results in not the best but still paltry SQL clause “select top 2 opportunityid from …”.

Tip #214: What to do if your option set won’t drop down

In Dynamics CRM 2013 or 2013 SP1, sometimes you will find that an option set won’t drop-down when you click on it. It doesn’t happen every time, but occasionally, when you click on the option set with your mouse, the option set will not drop down, but you can toggle through the options with your keyboard arrow keys.

This is a known issue. If you find that this is happening with one of your option sets, one workaround is to position the label for the option set to the left of the field. After changing the label position, the option set should drop down as desired.

Thanks to Scott Jung for this tip!