There is no denying that when working with importable data in CRM, data integrity is of the highest importance. Microsoft has gone a long way to help make importable templates much more manageable and safe for end users. They have added a number of user friendly measures, such as tool tips that tell you the field type and set length, or minimum and maximum values of data for that field, including stopping measures if you exceed one of the rules.
For Lookups, they give you the tool tip that records must exist prior to importing them, and for Option Set Items, you are only able to select the available values that exist in the system:
This isn’t a revolutionary development… the CRM developers just learned to leverage the Microsoft Excel built-in Data Validation rules to enhance the user experience. For those of you that do not know where to find these, they can be navigated to in Excel by going to Data -> Data Validation -> Data Validation…
And here you can see the rule that was set for this particular text column:
If you look behind the curtain, you will see the rule for Option Set values as such:
This format is shouldn’t come as a surprise as we need to get the data from somewhere to populate the drop-down list. But that source…… hiddenSheet!$A$17:$Q$17
Where can we find this hiddenSheet?
Typically, you can find hidden worksheets by just selecting the worksheet tab at the bottom and choose the unhide option. But for some reason, that option is not showing that anything is hidden.
So, this sheet has to be somewhere right? The answer is hidden in an advanced feature, where few tend to look: under View Code:
This will pop up a separate VBA page that may look scary to a standard Microsoft Office user, but fear not, we can help you find what you need as long as you follow along closely.
Once inside the VBA editor, you will need to view the project files. To find them, Click View -> Project Explorer.
Once the Project Explorer comes up, look what we find. It’s our hidden DataSheet, and if we look at the Visibility status, it was set to a 2 – xlSheetVeryHidden (I’d say so).
To get that hidden sheet to appear, we want to change the Visibility value to 1 – xlSheetVisible. You don’t need to save, just click back on your Microsoft Excel Document.
Once you are done in here, go back to your VBA Editor, and set the visibility back to 2 – xlSheetVeryHidden and close the extra window without saving.
So, you may be wondering: why would someone ever want to do this?
For me, it's the same reason that the CRM developers went through all the work in the first place…to improve data integrity and make the end users life just a little easier.
When I am sending a data import template to my customers, I want to make this process as simple and easy as possible. I will commonly take the lookups that have 10 items or less that would be used, add them to the hiddenSheet file. Then, I create the Data Validation rule for that lookup column. This gives the user the ability to select the record from a dropdown and validate that it matches what exist already in CRM. When matching lookups, it must be precise as far as spelling, white spaces, abbreviations, punctuation, etc… If I can make this easier, by already having the options as they exist in CRM available to the end user, I know I have improved the data integrity. Additionally, if they are populating the file and something is missing from the list, we can be aware of that ahead of time to get the record added in CRM, so that it does not fail our import and have to be repeated multiple times.
This is certainly something that may only be relevant to a select crowd as it is more advanced, but in finding my way through this, it was not as easy as a quick internet search. So in writing this up, I hope that a few will find this useful, save them time and that they can make someone else’s life just a little easier. To me, that is a win, win win….
Written by Bryan Page, Developer at Rockton Software, a Microsoft Dynamics CRM Add-On Partner.
I am using MarketXLS. It works great for me.