Problems with leading and trailing blank spaces in CRM data

I recently ran into a number of problems that ended all up being due to blankspaces in some CRM data. I thought of sharing where these problems come from, how blank spaces can affect CRM functionality and how you can resolve them.

You might wonder: how can you end up with leading or trailing blank spaces in text fields in CRM? While it is true that CRM forms are smart enough to remove these blank spaces upon form save, it might not be the case when the data comes in from data import or during integrations with external systems. It sounds like a small problem, but if you are not careful, blank spaces can become a data integrity nightmare for the following reasons:

1. You might end up with duplicate data that is hard to resolve later on. For example you might have account “123” and account “ 123 “ which reference to your same customer. Now all your activities, invoices and all related records are potentially spread across 2 different records. Fortunately, duplicate detection ignores leading/trailing blank spaces so these 2 records would be considered duplicate if you have a published rule on account number field. However, if these accounts are created from the SDK (or through an integration layer), chances are that the duplicate detection is not enforced because when making web service calls duplicate detection is off by default unless specified in the create/update message. Fixing duplicate parent records can be a really long and boring task.

2. Assume you have multiple contacts related to account “123” that you want to import using the data import wizard. However, your account appears in CRM as “ 123 “ (with blank spaces). It will be impossible for import wizard to match the contact’s parent account and import will fail because the parent account was not found. You might say: I can just change my import spreadsheet to reference account “ 123 “ instead of “123”. The answer is: it does not matter if in your import XML file you enter “123” or “ 123 “ as the parent account, CRM will not be able to resolve the parent account!! In short: You cannot use the out-of-the-box data import feature to import related records to that account until you fix the blank spaces, argh.

3. Once you fix all your integration points to trim all text fields before committing a create / update to CRM, how do you clean the thousands of records that already exist? My first guess was: Export to Excel for re-import, use some Excel functions to remove blank spaces and import back to CRM. Again to my surprise, removing leading/trailing blankspaces in text fields is not considered a change for CRM so during import, the records were not even processed! When you export records for re-import, CRM is smart enough to know that if you did not update a row, it will not re-import that row. However, it is not smart enough to figure out that you removed blank spaces so it also ignores your update.

4. Sometimes the CRM forms trim blank spaces for text fields. This can become a problem because if your data has trailing blank spaces, the CRM form will remove then on load, and automatically the form is marked as dirty! Therefore, just by opening the record your form is dirty and you get a warning about pending changes when you close the form even if you did not update the record. It also prevents some functionality since some of our ribbon buttons require the form to not be dirty. It is also not evident what was happening and took us a while to figure out why some forms where always getting marked as dirty.

 

We let our production CRM get really messy because we did not notice this problem until too late (we never noticed the external systems were sending data padded with blank spaces) so cleansing the data became quite challenging since it was spread to multiple fields in multiple entities, many duplicate parent and child records. As I mentioned earlier, fortunately duplicate detection does consider these 2 cases as duplicates (“123” and “ 123 “) so you can execute multiple duplicate detection jobs to find and resolve duplicates. However, it can be a really long task to do. In our case, because of the magnitude of the data cleanse that was required we had to invest in a data cleansing tool that would iterate through multiple records resolving the blank spaces and the duplicates which reduced considerably the amount of manual work required to solve the problem. The “tool” in a nutshell consisted simply in a custom workflow activity that takes as input a CRM query (Rollup Query) and then retrieves and cleans all the records specified as input. Then we can use on-demand workflows and provide different queries to resolve duplicates in multiple entities. In any case our conclusion is that we should have thought about this from the beginning and I hope this post helps other avoid the same problem before it is too late.

Script# (ScriptSharp) and CRM: The good and the bad

If you are familiar with Script# you probably understand how it can accelerate JavaScript development and completely eliminate annoyances such as accidental JavaScript syntax errors. However, does it work well for Dynamics CRM for scripts and web resources?

If you are not familiar with Script#, here is a very simple definition: Write your code in C# and you can then compile it into a JavaScript (instead of programming in JS directly). I have been using Script# for many years now (and love it), but I recently took the challenge for the first time to use it for designing and deploying all script customizations for an enterprise CRM project and quickly realized that there are a few issues about Script# and CRM that will make me think twice next time. Let’s start with the “good”:



1. Forget about JS syntax. Honestly, I hate JavaScript and client programming. One of the reasons is because I find it too error prone, I don’t know how many times I have run into issues with my JavaScript and often turns out to be something like forgetting a curly braket “}” somewhere in the syntax or accidentally typing a capital letter when it shouldn’t be. I am devoted to C# and that’s why I’m so motivated with Script#; isn’t it just awesome that I can write all my JavaScripts in C#, a language that I know inside out, gives me compile-time errors and intelli-sense? I’m sold already! Also because you write your form scripts in C#, you could even share some code between plugins (server) and web resources (client-side JS)!



2. Excellent Script# Xrm.Page Library. You can download for free this library that Gayan Perera has shared with the community. It is as simple as adding a reference assembly to your Script# project and now you have access to the entire Xrm.Page object model from Script# including intellisense for different APIs and enumerations available in the CRM SDK. This is an amazing productivity gainer.



3. Access to mscorlib from your JavaScripts. JavaScripts can get ugly with complex operations. Having a subset of mscorlib available from Script# makes things much simpler. You can have access to useful classes like String, Dictionary, Queue, List<T>, CultureInfo, XmlDocument, XmlHttpRequest , Math and many more, and all this with the intelli-sense we love. Some of these functions would be painful and/or ugly to implement in JavaScript alone. You can also use OOP/C# concepts such as inheritance, static classes and members, Properties, Events and Delegates. This way you can program your JavaScripts in C#and you can use API’s and syntax you are familiar with from .Net. Although it would be possible to do al this in JavaScript, for a C# developer, the usability and efficiency to write Script# does not compare with that of writing JavaScript.



On the other hand, I ran into the following “issues”:

1. Library size. When you make use of any API from a referenced assembly (except Xrm.Page) then you need to include the JavaScript version of that referenced assembly (e.g. mscorlib.js) to be loaded with the CRM form. These libraries can be big and typically, you only need a small subset of the library. It could slow down loading the forms, however, you will have the same problem even without Script# when for example you want to use the JQuery library.

2. You cannot reference any JavaScript code from Script#. If you want to re-use a JavaScript library from a previous project or a library you found online, you cannot reference any JS function from a Script# project. You would have to write “empty wrappers” in C# for each JavaScript function you plan to use, then compile it as a Script# library and then add the reference assembly to your code. It is not too complicated but it adds some pain.

3. AJAX incompatibility. I noticed that my dates in the form were not getting formatted as per the CRM user settings and some other fields and controls had strange issues (e.g. missing the month from the calendar control). After some investigation the culprit ended up being Script#. The reason: mscorlib.js defines classes and functions such as Date.prototype.format and Date.prototype.localeFormat. These functions are also defined in the AJAX framework that the CRM application uses in global.ashx and guess what: They are not implemented the same way. Because the CRM application relies on the MicrosoftAjax.js implementation it will be buggy if it uses the mscorlib.js implementation. Unfortunately, as soon as you add mscorlib.js library to your form, it will overwrite the functions from global.ashx and there is no way to “force” the CRM application to use the functions defined in global.ashx instead. I had to implement an emergency workaround to fix this problem which consisted in adding another JS library to the form, which would define the functions back with the AJAX implementation (something like overwriting the overwrite). Ugly, but it fixed the problem.



In summary, I had a good experience using Script# for CRM, but the issue with AJAX framework became a deal-breaker for me. Other things that I will explore in the future are TypeScript and the new Xrm.Page script template available with the CRM SDK 5.0.13+ which gives you intellisense for your CRM JavaScripts. TypeScript sounds promising since it is simply a superset of JavaScript which allows you to reference other JavaScript libraries; however, it is not C#-based and the syntax is not the same, so it would be yet another language to learn.