Searching Workflow Processes

I was recently upgrading a 4.0 environment to 2011 and in the process I needed to identify cases where workflow plugins were used to ensure that they were compatible with the upgraded version. The problem is how do you go about finding which workflows use a specific workflow plugin?

The answer is fairly straight-forward and is useful not only for upgrade scenarios but in other cases where you might be searching for a particular workflow that has a specific condition, action, or workflow plugin. That is, you can use this as a general purpose mechanism for finding workflows with a particular clause.

Let's start with identifying workflow plugins. For example, let's say that I want to find workflows that use the following workflow plugins:

The following query will find cases where the GetDate method is used. You could similarly search for the other methods by simply replacing the search clause.

select distinct  name from FilteredWorkflow
where statecodename = 'Published'
and activities like  '%DateUtilities%'
and activities like  '%GetDate%'

This returns with a result of "App Server Install". And sure enough if I open up that workflow in CRM, I see that the workflow plugin is called.

Alternatively let's say I was searching for another clause within the workflow e.g. 

select distinct  name from FilteredWorkflow
where statecodename = 'Published'
and activities like  '%region= Australia%'

Once again this also returns the "App Server Install" workflow and if I scroll a little further down I will see that also in the workflow:

By now you should be getting the idea. But interestingly if you tried the same search in a CRM 2011 environment you will not get any results returned. To make it work in 2011, you will need to tweak the queries very slightly replacing the "Activities" column with the "Xaml" column. The following will now work in your CRM 2011 environment:

select distinct  name from FilteredWorkflow
where statecodename = 'Published'
and Xaml like  '%DateUtilities%'
and Xaml like  '%GetDate%'

select distinct  name from FilteredWorkflow
where statecodename = 'Published'
and Xaml like  '%region= Australia%'

Spell Checking Options

I came across this topic in my email archives. I think it's still a relevant topic so I thought I'd post it. It deals with options for spell checking within Dynamics CRM.

One of the simplest options of course is to use the spell checkers that come built into the various add-on toolbars such as the Google toolbar.

More sophisticated options employ the use of a spell checker API where the experience is similar to spell checking in applications such as Microsoft Word.I have implemented 2 such versions of the spell checker.

The first one powered by webspellchecker.net is a paid for solution ($250 per year for up to 10000 sessions and then $0.025 for each additional sessions - see web site for additional pricing options). This tool is very intuitive and will be familiar to anyone who has used the Microsoft Word spell checker solution.

The second option was to use a free product called NetSpell – this is deployed on the server entirely i.e. the check occurs against a dictionary defined on the server. This tool is also fairly intuitive however the way in which the spell checker works is not quite as comprehensive as that as webspellchecker.net which I think is in evidence by comparing the screenshots.

In summary, below  are the pros/cons of each approach:

webspellchecker.net Pros

• Quite a bit more comprehensive in terms of functionality including an easier to use interface as well as grammar and thesaurus options
• Very intuitive
• Allows for custom dictionary to be defined
• Is a supported product and will be maintained over time presumably with incremental improvements, dictionary term additions, etc.

webspellchecker.net Cons:

• Is not free. $250 per 10000 sessions. To get a true cost it is necessary to determine the number of users who will be using this on a daily basis multiplied by the number of times they are likely to use it per day e.g. the formula would look something like: #users*#dailysessions*200 (200 being roughly the number of work days per year)
• Is an online solution although there is also a licensed downloadable version for a not insignificant cost

NetSpell Pros

• Fairly comprehensive
• Fairly intuitive
• Allows for custom dictionary to be defined
• On premise solution so not reliant on network performance
• It is free

NetSpell Cons

• Is not a supported product in case of product issues
• Dictionary is not auto maintained
• User interface not as slick as that of webspellchecker.net 

TDE and CRM

Although the title of this post is "TDE and CRM" the reality is that it could be titled "TDE and SharePoint" or "TDE and Any App Running on SQL Server". The point is that the CRM database like any other application running on SQL can be encrypted using TDE. There really are no specific settings as far as the requirements for setting up TDE for CRM.

TDE stands for Transparent Data Encryption and became available in SQL 2008 R2 Enterprise. TDE essentially protects your "data at rest" i.e. it protects your database from being stolen. As in backing up the database and restoring it in some other location where the contents can be analyzed via SQL.

Essentially when you apply TDE you are applying a certificate used to encrypt your data to the back-end database files (it only encrypts to the database files, when you access a TDE database via SQL there is no difference to the usual experience). You will only be able to restore the database to another database server if that server has the same TDE certificate applied. Otherwise you will be prevented from restoring the database - you will receive the "cannot find certificate with thumbprint" error:

The backed up database file will be similarly unintelligible.

As the name itself implies, TDE is transparent. Meaning this is configuration is applied on the database itself and any applications with back-end SQL databases running on top of it will be oblivious to the TDE. The only impact will be a slightly increased level of CPU utilization on the database server as it handles the data encryption for the SQL transactions.

In light of this, it is difficult to not recommend using TDE. It is simple to apply, it is completely transparent to applications, it protects your data... with the only downside being higher CPU utilization which for most databases won't make a difference (assuming the old adage "use it or lose it") and easily compensated in cases where the additional churn pushes your CPU over the edge.

Below are some scripts for setting up TDE on a SQL 2008 R2 Enterprise server:

/* If you wish to copy the certificate from an old server you will need to back it up */
--Backup the certificate from the 4.0 database server:

USE master
BACKUP CERTIFICATE TDECert
TO FILE = 'C:\TDECRMCERT.BAK'
WITH PRIVATE KEY ( FILE = 'C:\TDE.key',
ENCRYPTION BY PASSWORD = 'EncryptionPassword' )

/* On a new server you will need to create a certificate using the backed up one from the old server.
   This procedure assumes you copied the backed up certificate to C:\ root on the new server
 */


-- Create a master key. NB: Record the password for future reference.
USE master
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MasterKeyPassword'

-- Backup the master key. NB: Store the backup file and record the password for future reference.
USE master;
BACKUP MASTER KEY TO FILE = 'C:\Master_Key.bak'
ENCRYPTION BY PASSWORD = 'MasterKeyBackupFilePassword'

-- Create a certificate by importing the certificate backed up in the first step.
USE master
CREATE CERTIFICATE TDECRM 
    FROM FILE = 'C:\TDECRMCERT.BAK'
    WITH PRIVATE KEY (FILE = 'C:\TDE.key',
    DECRYPTION BY PASSWORD = 'EncryptionPassword')

-- Backup the certificate. If you’re using the imported certificate this step is optional since the file 
-- you used to create the new certificate is already the backup file so you’ll just be recreating this file. 
-- NB: Store the backup file and record the password for future reference.

BACKUP CERTIFICATE TDECRM
TO FILE = 'C:\TDECRMCERT.BAK'
 WITH PRIVATE KEY ( FILE = 'C:\TDE.key',
ENCRYPTION BY PASSWORD = 'EncryptionPassword' )

-- For each database you wish to restore to from a TDE enabled database you will need to apply the certificate
-- and enable encryption.
USE Vanilla_MSCRM
GO
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256
Encryption By Server Certificate TDECRM
GO

ALTER DATABASE Vanilla_MSCRM
SET ENCRYPTION ON

Searchable Property Updater

I may be a little late to the party but I've recently discovered the "Searchable Property Updater" tool and I have to say that this rates as one of my all time favorite CRM utilities.

Because it addresses a pretty much inevitable need. That is, if improving usability is a major concern in a CRM installation... Out of the box pretty much all fields are searchable. Most are irrelevant in a given implementation e.g. latitude and longitude on the account and contact record. In fact, I'd venture to say that most of the highlighted fields below are going to be irrelevant in most CRM implementations. And that's just a small snippet...

In this case, "usability" means "simplicity". And weeding out the irrelevant values really helps with keeping the UI clean and simple. Confusion being the enemy of a successful implementation, I personally consider this to be a very important tweak.

Of course in CRM you are able to weed these out albeit very ... t e d i o u s l y ... by updating the searchable property. And of course tedium often leads to the lack of execution - important or otherwise.

That's why this tool is so valuable. It removes the tedium and makes it what it should be - a quick and virtually painless effort.

I especially love the "Check Attr. on Forms" option. That is pure genius and really contributes to the painlessness of this task. In most cases, the fields that you have on your user form are the fields that you are going to want to query via Advanced Find. Or at the very least - it's a good starting point. And you can just click that to automatically select all the form fields and deselect everything else. Publish and you're done.

Brilliant! Better late than never but big kudos to the creator of this utility.

Views and Filters Toolkit for CRM Online

While on the subject of CRM Online compatibility - below is a link to the CRM Online compatible version for the "Views and Filters Toolkit" workflow plugin - another exceedingly useful utility. The one currently uploaded in CodePlex fails import into the CRM online environment.



Though there are many conceivable uses for this utility, I find that the ability to update the Offline and Outlook Filters without having to resort to programmatic techniques is perhaps the most useful. A simple way to centralize the maintenance of these download rules has long been a feature missing in the Dynamics CRM product and this utility surmounts this oversight.

Another shout out to Wayne Wittman for his conversion efforts (not to mention the efforts of Alberto Gemin who first published this utility).

As a related aside... another useful utility - namely the CRM 2011 Workflow Utilities - has also recently been updated in its CodePlex location with a CRM 2011 Online compatible version.

Distribute Workflow for CRM 2011 Online

I have spoken previously quite extensively about the virtues of the Distribute Workflow utility. The problem I encountered was that the version that is currently uploaded in CodePlex cannot be imported into CRM Online.Whenever I tried the import would fail as shown below:

But thanks to the efforts of the talented Wayne Wittman this very useful utility has now been made compatible with CRM Online. This utility can be downloaded using the link below.

Announcement: CRM Grid Editor now freely available

Until now the Limited Edition CRM Grid Editor has been available as a free add on. This edition has been limited to working with out of  the box entities and views. As of today, I am pleased to announce that this restriction has been removed. You can now obtain a free and unlimited version of the CRM Grid Editor which can (drum roll) also be configured to work against custom entities and views.

The CRM Grid Editor works with all versions of Dynamics CRM - on premise, online and IFD.

You can get an overview of product capabilities by viewing the CRM Grid Editor Summary video. You can also view the product installation video.


In order for you to be able to use the product it is necessary to generate a license file for your installation. To obtain a license please send me an email (see details in Contact tab) with the following information:

On Premise
Provide your CRM server URL and organization as shown in the screenshots below.

Online:
Specify the org name that you are using to access CRM i.e.: https://orgname.crm.dynamics.com



Key features:

• Simple and intuitive Excel-like interface 
• Edit all editable CRM fields returned in view (all data types handled) 
• Based on CRM view definitions – no additional setup required 
• Bulk update of records – make changes on the fly without having to save between row edits 
• Launch Grid Editor from context sensitive CRM user interface or as a fully navigable standalone application 
• Drill down to child records from a parent record – not limited to the associated view! 
• CRM Security model enforced – you cannot do anything with the Grid Editor that cannot be done through the standard CRM user interface 
• The Grid Editor can be configured to work against all CRM entities and views – including custom entities and personal views 
• Tabbed interface allowing multiple different views to be open at the same time 
• Ability to define and save "GE Views" which allows for advanced pivot table-like grouping and filtering 
• Add new records using the CRM standard, quick or the new Grid Editor view sensitive add-new form 
• Run CRM workflows against selected records

Other features include:

• Deactivate and Activate records in line 
• Manually execute CRM workflows 
• Link back to CRM records with a single click 
• Advanced additional filtering and sorting options 
• Easy to identify color for changed cells, columns 
• Dynamic export to Excel 
• Supports opportunity and case close forms 
• Supports Drag and drop editing and Undo Changes Requirements: