Let’s imagine you’re working with a list of Contacts that lists the company that they work at, doing a little clean-up before it gets used. Only some of the Contacts have an address listed, but they all have a Company address listed. This needs to be updated so each Contact record has an address.
In this case, the business requirements are:
Doing a simple Copy/paste could be your friend if the list is only a handful of Contacts long…but this can be tedious if you’re looking at dozens (or more).
I was doing something very similar to this for a client recently. My go-to tool for this is Excel…it really makes this problem simple to solve. Come to think of it I use this same concept in a lot of different places when doing data prep–so I’ll run with this example, but recognize that the same method can be used elsewhere in Excel.
OK I’m interested, what do I do?
Fortunately, the requirements above give us exactly the direction we need. We only need one address for each contact, and each row has as either one or two. We know that one address wins out (the contact’s address) if it’s there. So from a list standpoint, we need to do the following:
Accomplishing this isn’t too much work, but it does involve an Excel formula that’s going to look like Greek when you first see it. We’ll get there in a second, but indulge me for a slight diversion.
When working with a list of data, my best practice in Excel is to keep the source data as pristine as possible. This means I don’t like to add/delete columns in the original list. This ends up being important later on…when somebody comes with an updated list, or a slight variation of what rules to use when identifying the desired data. If you’ve made your changes directly in the original workbook it becomes much more likely that you’ll have to start from square one, whereas my method allows you to more easily sub in new data or change one of the underlying rules.
To accomplish this, I’m using two Worksheets (tabs) in one Excel Workbook (file). “Raw List” (the raw list of data) and “Final Product” (where our modified version of the data will go). When you see formulas such as =’Raw List’!C3 that really means this cell should equal the cell C3 in the “Raw List” worksheet.
Hopefully that little explanation will keep this next screenshot from looking like total hoodoo.
We’re going to use this formula for each address field (Street 1, Street 2, City, State, Zip). We’re also going to make the assumption that if the “Contact Street 1” field is blank, that it is not a valid address and the Company address should be used (make sure this is a valid assumption based on your situation :)). This translates to “If the Contact’s Street value is blank, use Company Street, otherwise use Contact Street“.
Adding the visual context in Excel:
Sweet, now we’re cooking. Next, we want to use this same formula for each of the rest of the Address fields.
Note that when we’re checking the subsequent fields like City, State, etc, we’re always checking the Contact Street value. This is important because we don’t want to use parts of both addresses…we want the whole contact address or the whole company address.
At this point, we’ve done the most complicated part. The other fields (like name, company name, etc) don’t need a funky formula, they just need to be identified and added as columns in the data set. You can take some time to review how this is done by downloading the Excel file I worked on, which has all of the formulas preserved on each of the two worksheets.
Great, so what’s next?
From here, there are a few common things that I’ll do as next steps:
- One option for doing this is to do a copy/paste values into a new workbook. This is great when you want to ensure there are no references to the original set of data
- An alternative is to copy the entire worksheet to a new workbook (by right-clicking on the worksheet name). This will also update the formula references to look back to the original file
Note that either of these options is acceptable if the new file will be saved as a .csv file, since Excel will remove the incompatible features (formulas) when you click Save. A .csv file is great if you want to import the data into Dynamics CRM.
- Hide the “Raw List” worksheet (tab) so it isn’t selected by accident
- Freeze the column headers in the Final Product worksheet by clicking Freeze Panes->Freeze Top Row on the View tab
- Protect the list from being accidentally modified by clicking Protect Sheet on the Review tab
Keep in mind, there are many other ways to accomplish this same task. If there’s another method you’d like to share, feel free to do so in the comments.
Though it may seem like a simple question, there’s not a lot of low-hanging fruit when trying to answer the question of “what’s different between the Dynamics CRM Web Client and Outlook Client?”
First off, they both offer the same core functionality. You can work with the same record types, Workflows/Dialogs function the same, the customized and role based forms are available in both, records can be created and shared, etc.
The Outlook Client is additive to the experience of the Web Client. Some of the notable enhancements offered by Outlook include:
Since the Outlook Client seems to do more, why would I bother with the Web Client?
The very first argument for the Web Client is speed. Pure and simple. Not so much the speed of “opening records and navigating between them” but rather the “I have ten minutes to take out my laptop and get a few things done.” I’ve seen first-hand the frustrations of road warriors who simply want to open Outlook and look up a prospect’s address or phone number, who feel thwarted by the extra load times and resource-thievery of the Outlook Client. For those who open Outlook and work out of their inbox for a decent chunk of time, the initial delay is well worth it. For the road warrior, it is going to take longer to load up the full Outlook client (if it’s not already running).
Another argument is that there is no question of exactly when the information will get into CRM. When you work in the Web Client, the information is immediately committed to the database. The Outlook Client does a periodic sync, with a minimum of 15 minutes between syncs. No that’s not awful, and Yes you can manually initiate a synchronization, but depending on the circumstance this can be a potential problem.
The Outlook Client only Synchronizes to one CRM Organization database at a time. The Web Client doesn’t have this same limitation. For anyone who has tried to regularly run the Outlook Client Configuration Wizard to regularly flip between the “Synchronizing” organization, rest assured that this is not a good standard practice. While you can still navigate to records in other CRM Organizations, you don’t enjoy the same benefits as the Synchronizing organization.
Having users synchronize to a local machine (or virtual desktop) has a performance cost at both the Application and Data levels of CRM, as well as a network utilization cost. The CRM application server and the SQL server take on extra overhead, especially when users are starting Outlook or preparing to Go Offline. Depending on the size of the the user base and the beefiness of the underlying servers, this could be negligible, or then again it could be noticeable.
The Outlook Client has updates that must be managed. Every time a CRM update rollup is released, there’s a corresponding Outlook Client update that is also required. While this can be handled by Windows Update, it’s not a surefire thing that all users are going to be up to date, meaning one more thing to manage for the helpdesk.
If I’m used to the Outlook Client, but I’m in a position where only the Web Client is available, what should I be aware of?
First and foremost, don’t fret the basics: navigation and the overall feel of the application are the same. You still have the site map (left-hand navigation), view selections, sorting & filtering of views,
If you’re used to the Outlook Client, you’re probably taking advantage of Pinned views. In the Web Client, only one view is available at a time. The first view you see when navigating to an entity is the (administrator-set) default view. You will need to use the view selector dropdown to choose your view. Alternatively, you can use the “Saved Views” in the Advanced Find window.
For email and other activities, there is not a “Track in CRM” button that you need to use. If you can view the activity through the Web Client, it’s already been tracked in CRM. This works great for new email threads that are initiated from CRM, as well as continuations of tracked email threads. However, if you receive a new email that you want to track in CRM it’s going to be more work. A few options include:
The key here is to make sure that the next outbound interaction comes from CRM, so you can rely on your email tracking settings to pick up future emails. Unfortunately, any reports that summarize email activities could potentially be missing the original email in the thread, depending on the method chosen.
If you’re not using the MS CRM email router as an organization, Outlook is required in order to physically send and receive CRM email. Email send/receive capability in CRM relies on either the email router (set up centrally for the entire organization) or the Outlook Client. For the latter case, the CRM Outlook Client must be actively running in order to handle email.
Lastly, if the user base exclusively uses the Outlook Client, they’re probably not familiar with the URL they should go to in order to use the Web Client. This is an easy one to overlook from a planning standpoint, but it’s a good idea to make sure they know the URL (and preferably bookmark the URL so they don’t have to track it down later).
It’s amazing how much businesses rely on Outlook today, yet some of the very major CRM players out there lack a solid Outlook functionality. Not only does Dynamics CRM offer a slick client, there are some really cool things that can be done, especially with the conditional formatting stacked on top of the excel-style filtering of data.
If you have the means, take full advantage of what the Outlook Client has to offer.
While it’s easier to import data to Dynamics CRM using the data templates, prepping that data can take a little work. One of the most common things to deal with is splitting a “Full Name” from a source file, which needs to be imported to CRM as “First Name”, “Middle Name”, “Last Name”.
Let’s assume the source file uses the format of “[Last Name], [First Name] [Middle Initial]”. This makes it just a little too complex to use the Text-to-Columns feature in Excel (but note, if you’re dealing with a basic “Last, First” format, keep it simple and use Text-to Columns).
Our solution? Build 3 simple formulas to handle that manual work.
Like Billy Blaze, I find it’s easiest if we break things down.
Start with the Last Name (Column C)
We know that the last name starts at the left of the cell:
English: Grab some characters from the left-hand side of A2. How many? One character behind the comma, so go find the comma in A2, then go back one character.
Pluck out the First Name (Column D)
This part is a little trickier, but not too much. We can’t use the left() function any more since it’s in the middle.
English: Grab some characters from the middle of A2, starting 3 characters after the last name. How many should we grab? The total length of the Full Name, minus the length of the Last Name, minus four characters (2 spaces, 1 comma, and 1 Middle Initial)
Grab the Middle Initial (Column E)
This one’s the easiest to decipher…
English: Grab some characters from the right-hand side of A2. Just 1 this time.
With those three formulas, here’s what we end up with:
The rabbit hole goes deeper
When you’re sent a file to work with, it’s pretty rare that names fall into a perfect little “Last, First MI” format. There’s a few ways to deal with this. I’ve put together a workbook with this and a few other examples of splitting up full name data, which you can feel free to thieve from me.
These cover a few different methods for carving up the data, but are by no means exhaustive.
Note, it’s important to know your data that you’re starting with. Take some time to ask some questions: Are all of the names in the same format? How should a record be handled if the name is missing? Is there funky CaPITAlizAtion that needs to be dealt with? Does anyone have two middle initials, or maybe a space in the middle of their name? Picking through these issues is often the most time-consuming part of prepping a list of data.
I’m always tempted to try to write some “super formula” that is so smart it can handle any case you run across…but remember, if there are only twenty records where there are two middle initials or a space in their first name, it’s often quicker to do those twenty by hand and use a simpler formula to help out with the rest. You’ve been warned 🙂
A co-worker recently shared this project with me as we’ve had some experience with clients who had large number of options that needed to be added to Picklists.
Unfortunately, Dynamics CRM doesn’t offer a way to batch import these lists, rather it’s up to the administrator to add them one-by-one. Adding in a dozen of these isn’t so bad…especially since you should only have to do it once…but when you start looking at two dozen, fifty, or more of these it becomes a real hassle (and a hotbed for fat-fingered garbage data). That’s where the CRM Option Set Utility comes into play.
The goals of the project are simple enough:
After spending some time with the application, specifically testing it using on-premise CRM Organizations, there are a few things that I’ve found worth highlighting:
The bit I like is the community aspect of hosting a repository of common option sets. There’s about a dozen so far, but as this builds out it will hopefully be a first-stop when looking for a cleansed list like this.
I’ve been working with Dynamics CRM reports quite a bit over the last few months. Most of the work has been using the BI Dev Studio (BIDS) in Visual Studio. It definitely falls into the artful science category for me, with flavors of Excel, SQL Server, and even Publisher when developing a report.
In the case of displaying Email activities, specifically the body of an HTML email, the art takes a decidedly sad turn.
Okay, let’s see what that looks like in a simple report that shows a summary of email messages….
This is useful for…nearly nobody.
There are many ways to try to tackle this…ranging from CRM plug-ins, to complex report design, to, well, something a mentor of mine tipped me off to.
This gets used in the report design as an expression, replacing the normal field reference in the report design. It uses a basic utility that strips out the HTML leaving only the plain text behind. Okay, if that sounded clunky, a picture is worth a thousand words:
There are certainly more elegant ways of doing this, but if the key is to provide easy, readable email contents in a report this sure foots the bill! From a user perspective, to see the full HTML version, I like to add in a clickable link to the email that pops the CRM Email Activity record.
I picked up a little tidbit while working through some applications of this: If you try passing a null field using this utility it returns an ugly little #Error instead of a blank string. Also, SSRS will still throw the #Error even if you’ve included an IIF statement in the expression to make sure there’s a value before calling the utility. Below is a little workaround that gets the job done.
Microsoft.Crm.Reporting.RdlHelper.HtmlUtility.RemoveHtml(field & “”)
If anyone knows an easier way to do that, please drop the details in the comments section.
Again, there’s surely a prettier way to handle this, rendering HTML in the report or including some basic formatting, but this is pretty lightweight and works well when printing a basic list of emails with the body text in a report.
As far as I can tell, this utility is an undocumented feature of CRM, so quite technically speaking it is not supported by Microsoft and is something that they could yank out in a future release without notice. That said, this is the method used in the out-of-the-box CRM reports to strip out HTML tags.
|Objective:||Strip out HTML tags from an email message when displaying in a CRM report|
|CRM Version:||Undocumented feature. I’ve tested with CRM 2011 through UR8|
|Valid as of:||9/10/2012|
|Permissions Required:||Create/Write reports.|
I received a question regarding a problem someone was having when importing a report that leveraged the expression above. After a little troubleshooting, the answer was that the report lacked a reference to the following Microsoft assembly:
Microsoft.Crm.Reporting.RdlHelper, Version=18.104.22.168, Culture=neutral, PublicKeyToken=31bf3856ad364e35
Vanilla CRM reports and those reports created using the built-in CRM Report Wizard include this reference automatically. However if you build your own report from scratch, you’ll need to add this manually. With the report file open in Visual Studio BI Dev Studio, hit the menu item at Report->Report Properties… Next, click Add and paste in the reference to the assembly and click OK.
Thanks for the question! Other comments and questions are welcome.
In the last post, we downloaded a Dynamics CRM Import Template. Now, it’s time to get some data INTO the system…since that’ll be much more useful.
Contacts are a pretty common import, being something that most sales users will want to do early on. Fortunately, it’s a super simple process to do this!
|Objective:||Download the import templates specific to your organization from the CRM application.|
|CRM Version:||CRM 2011, any version|
|Valid as of:||8/27/2012|
|Permissions Required:||Read/Write Import Job and at least User level Create permissions for the Contact entity.|
Whether I’m just starting a CRM project, or there’s a list of [something] that someone needs imported into Dynamics CRM. One of the easiest ways to get a list of records imported is using a file import…and the biggest hassle of that is making sure the field mappings are set up.
The vanilla product has an easy way of handling this, providing a way to download a template, specific to the record type you want, which includes all of your customized fields as well. It’s pretty slick not having to set up this field mapping on your own, and if you have some Excel data that you can copy/paste into the appropriate columns, you’re in business.
|Objective:||Download the import templates specific to your organization from the CRM application.|
|CRM Version:||CRM 2011, all versions|
|Valid as of:||8/20/2012|
|Permissions Required:||All default security roles.|
I’ve gotten some pressure from folks to noodle a bit on some topics that have come up when working with a Excel and MS CRM system…no time like the present to make good on some material.