Working with Price List Items in Dynamics CRM

0 comments

Posted on 31st March 2014 by Jukka Niiranen in Annoyances |Tips

, , , , , ,

Despite of the recently refreshed user interface of Dynamics CRM 2013 that offers a much more fluid user experience than previous versions, there are still areas in the application that are not very user friendly. Many of these revolve around product and price information, regarding how it is presented and what actions are allowed on it. In this blog post I will drill into a common scenario that organizations who use CRM for managing price list data may run into and present a few options on how to make their lives easier.

Price List and Price List Item Views

A pet peeve of mine in Dynamics CRM has always been the UI that the Price List entity offers to the end user. As many of the readers of this blog will surely know, price list items are the way how products, units, price lists and the all important price figures come together in the CRM data model. If you want to leverage the product catalog and any price calculation features in the sales module, you’ll need to work with price list items and create at least one of them per each product you plan to include as line items on your opportunities, quotes, orders and invoices.

Unless you’ve built a custom integration to a back-end system that will automatically provide the latest pricing information for CRM, there’s quite a bit of work involved in maintaining individual price list item records when prices change or new products or lists are introduced as a normal part of the day to day business. When a CRM user opens a price list record, a reasonable assumption to make would be that he or she is interested in reviewing the pricing information given to the included products. Unfortunately the Dynamics CRM UI does not make such an assumption, rather it thinks the user is interested in only viewing a list of products and their units but not the actual price information in the amount field. Here’s what the default associated view of the price list items gives us:

Price_List_Item_CRM_2

Well, that sure looks like a good candidate for some entity customization work. Yes, it does, but there’s a “but”. When you open the customization UI and navigate to the price list item entity, you discover that the views are actually not customizable. Nor can you add any of your own views for that matter, which means you’re stuck with the default UI. If you think that the price list item entity should allow view customization, then there’s a suggestion on Microsoft Connect that you definitely should go and vote for (if you need help in registering to Connect itself, see this post).

Exporting the Price List Item Data to Excel

With this limitation in mind, what are our options of producing a true price list view with product and price information shown side by side? For any Dynamics CRM power user the first thing to come to mind will surely be to export the data into Excel. Unfortunately the uncustomizability of the Price List Item entity also means it has been blocked from showing up in Advanced Find, which would normally be our tool of choice for preparing a CRM data export.

Luckily there’s still an Export to Excel button visible in the ribbon of the price list form when we are viewing the associated price list items view. Clicking this will present us with an option to either export the data in static format (which would just give us the same columns as the current view) or to create a dynamic Excel sheet in two possible formats. Both of the latter options, pivot table and worksheet, present a follow-up dialog where choosing the required columns from the price list item entity and even any parental entity like product is possible.

Price_List_Item_CRM_3

When you export the view into a dynamic Excel sheet in an on-premises CRM environment, you can actually go and look at the SQL query that the view is using for pulling the data from CRM to Excel. Just click “Change Data Source – Connection Properties – Definition” and copy the query from the Command Text window into Notepad. With a little tweak that removes the reference to the currently viewed price list record we can use the same dynamic Excel sheet to retrieve price list item data for all the price lists in the system.

Price_List_Item_CRM_1_small

In the SQL query you’ve copied to Notepad you’ll find a reference to the price list from under which we exported the related price list items. It will look something like this: where  (“productpricelevel0″.pricelevelid = N’CEA84006-AD7B-E311-9405-00155D6214FA’) . Just remove this whole where clause, thus expanding the query to retrieve all records from the price list items table in CRM, regardless of the associated price list. Then with the Excel pivot table tools you can group and filter the data any way you please, effectively creating a price list report that views the latest information from CRM in a layout that best suits our purposes. (more…)

How to Import Primary Contacts

2 comments

Posted on 9th July 2013 by Jukka Niiranen in Tips

, , , ,

The Import Wizard in Dynamics CRM can do a lot more than what may initially seem possible. I’ve covered some of these features in a previous article called CRM 2011 Data Import Wizard in Practice. Among these capabilities is the possibility of importing records to different entities that have multiple relationships connecting them in both directions, not just the simple parent-child relationship pointing from one entity to the other.

A typical example of such a relationship would be the Primary Contact of an account. The account is a parental record to all the child contacts, but one of these contacts may however have a 1:N relationship back to the account and be presented in the Primary Contact lookup field on an account form. If you are importing both the account and contact columns in a single file (such as an Outlook contacts export) then mapping these relationships should only be a matter of mapping the right fields. If you have two separate files, then simply zipping them up into a single file will allow you to map both entities in the same import process.

Primary_contacts_account_formOne of the problems that you may encounter during such an import process is that there are multiple matching records for the mapping fields. If you have more than one account record called “Litware, Inc”, to for example represent different regional offices of the company, mapping the primary contact by account name won’t work. Similar problems will arise if several people have the same first and last names. Therefore it’s a good practice to always generate a unique ID for each record before importing it. You can construct the identifier field in Excel with the Concatenate function and combine several fields into a single Import ID string (account name + address 1 city, for example) which you then map into a temporary field in CRM. You can use this field as the lookup reference to the related entity instead of the standard primary field when importing data.

Appending Existing Records

In a recent import task I was once again faced with the Primary Contact issue. Only this time the account data that I needed to map the contacts into was already in the CRM database. As these were new contact records being imported, my first thought was to create a workflow rule to be triggered from the create event of a contact record. Using some temporary contact field to store the primary contact flag into, like “governmentid = PrimaryContact”, and then searching for this value in the workflow rule would have allowed me to start a record update step for the parent account of the newly imported contact. In the update step I could just state that the account’s primary contact would be the contact that the workflow process instance has been initiated on.

Fortunately I looked through the source data once again before proceeding any further, as that revealed a flaw in the assumptions behind the above workflow rule logic. A single contact was sometimes the primary contact for more than one account. Also, there were occurrences where the contact wasn’t the primary contact of its own parent account. The relationships were therefore more complex than what a single workflow rule could cover.

This doesn’t mean that leveraging workflows was out of the question, though. To enable the creation of multiple relationships from a single imported contact record I just needed to have an intermediate stage in my process, to store the data in CRM. What this requires in practice is that you first import the data into a different entity, then trigger the update step from that record into the actual record you want to append with new information.

One option would have been to create a new temporary entity just for the sake of getting the data imported correctly. However, since these were account and contact records for which we wanted to link the imported data, there was already a logical place available in the default data model of Dynamics CRM: connections. It’s in fact the perfect entity for importing any relationship data into, as the two parties of the connection can be references to any entity type that has connections enabled for it, meaning several default entities and any custom entity you’ve created. Therefore we could cover several different import scenarios with connections and not have to go into system customizations to add relationships to other entities.

The Import Process

First I had to add a new connection role for “Primary Contact” (step 1) to identify the connection records that I want to run my workflow process on. As this role will be used exclusively between account and contact records, I specified them as the available record types for the role (step 2). Also, I always tend to put the same role value on the “other side” of the relationship to keep the data consistent and simple to view/search for, so I set this new role to be its own matching connection role (step 3).

Primary_contacts_connection_role

Then I proceeded to creating a new workflow process that would be triggered on the create event of a new connection record. In the workflow rule I specified it to run only on connections that have the Primary Contact connection role. I also wanted to validate that the Connected From and Connected To entities are mapped the right way around in the connection record, so I simply check that the account and contact records behind each relationship contain data. Without these conditions being met, the update step wouldn’t produce any meaningful results anyway.

Primary_contacts_workflow_rule

In the update step I mapped the Connected To contact record into the Primary Contact field of the Connected From account record.

Primary_contacts_workflow_update

Now we were ready to start the actual import work. Since the primary contact relationship data was handled with a separate entity, I first imported the contact records through the normal process. (more…)

CRM 2011 Data Import Wizard in practice

11 comments

Posted on 8th November 2011 by Jukka Niiranen in Tips

, , , ,

Data migration typically isn’t the most joyful part of a CRM implementation, but you really need to pay attention to carefully importing all the relevant customer data it if you want the users to adopt the CRM system as an integral part of their day to day activities, rather than yet another business application searching for its purpose. When implementing Microsoft Dynamics CRM, the logical place to start planning the import process is having a look at what tools are available in the application itself.

The Data Import Wizard used to be a curse word among the Dynamics CRM crowd for a long time, but you shouldn’t ignore this option right away, just because of its bad reputation. Sure, there are many limitations with the built-in tool, but it has come a long way since the previous versions. Having recently spent some hands-on time with the CRM 2011 Import Wizard, I decided to put together some of the useful links and pieces of information I discovered during the process. There’s plenty of great blog posts out there on individual data import features, but perhaps this can serve as a “getting started” tookit for planning on how to import data into Microsoft Dynamics CRM 2011.

Mapping related records based on custom ID fields

The CRM database by definition is primarily a place for storing information about how different objects relate with one another. This means you will almost always be dealing with source data that needs to reference another set of data once imported into the system. In Dynamics CRM these relationships manifest themselves as lookup fields that point from a child record to its parent.

When you are mapping the lookup fields from a child entity into a parent entity that’s already in the system, you always need to consider the possibility of duplicate values in the list of parent entities. Contact names are not unique, and neither are account names in many cases. Yes, you could import lookup references by using a CRM GUID instead of the primary field (most often the name attribute) of the parent entity, but how often would you have that available in your source data to be imported? Exactly.

The first and in my opinion the best improvement with the Import Wizard in CRM 2011 is the possibility to reference the parent entity with an alternative field. Yes, if you have a reliable unique value available in your data, such as customer number or contact email address, you’re free to use that to link your records together. Alternatively, you can construct specific import ID’s out of your data that you first import into a hidden field, then later on use that as the reference which connects your related child records into exactly the correct parent record without the risk of import row failure.

For step by step instructions, check out this blog post by MVP Leon Tribe: Changing the Lookup Reference When Importing Related Data.

Importing data for multiple entities in one go

A common format for customer data coming from non-relational systems is a flat file that contains both account and contact data on the same “table”. In these cases you will have multiple instances of the account’s information repeated on each line where there is an individual contact related to that account. You first reaction might be “oh well, guess I’ll have to split that into an accounts file and a contacts file, then remove the duplicates. Well, good news: you don’t have to anymore!

Nowadays CRM comes with a built-in data map called  ”For Generic Contact and Account Data”, which will allow you to import a file that has data intended for both account and contact records. First of all, you can map some of the source fields into both the target entities. Address information is a good example, as it’s typically stored separately on both accounts and contacts (yeah, data redundancy, but often it’s just more convenient for your everyday CRM usage).

Secondly, you will not get duplicate account records from each of the rows, as the Import Wizard is smart enough to detect the distinct parent accounts needed for the child contacts. Now, in order to get the expected results, it’s also up to you to be smart with your source data and field mapping. If any of the fields you’ve mapped to the parent account have any variation in their contents (such as phone numbers with different spacing formats), you will get duplicates, simply because the system will not throw away any unique data rows. Additionally, your child record imports to those accounts will result in failures, as the parent account lookup field will point to a non-unique value in the database (unless you used the aforementioned method to specify an alternative lookup reference). You should also take into consideration if the source data actually has intentional duplicate values for account names, such as branch offices with only a different address.

Check out this article for step-by-step instructions on how to import accounts and contacts from a single file. But what if you need to perform the same type of import, only you’re not dealing with accounts and contacts? Say, importing data to custom entities with a parent-child relationship, like “event” and “event attendee”? No problem, you can build a data map just like the “For Generic Contact and Account Data” one, by leveraging the multi-entity data file import mapping feature.

How much data is too much?

Even if you are importing only into a single target entity, there’s a good chance that you’ll cross the line of allowed maximum size of the import file for the Import Wizard, which is 8 MB. While the XML data import templates available for download from the CRM UI provide very nice features for ensuring the input data is in the correct format, they have the downside of increasing the file size considerably. Compared to an Excel file (xls, xlsx) the size of a file saved in the Office 2003 XML file format can easily be tenfold.

One potential way to get around this limitation is to zip up your import files. You can read the requirements for the zip file contents here, but in general your everyday import files should be zip compatible without any extra tricks. This is actually how the multi-entity data file imports are also handled, as there will only be the possibility of uploading a single file into the Import Wizard to be processed, so you’ll need to package your import files into a zip archive.

In addition to the source data files, you can also include attachments into a zipped import file. Yes, the Import Wizard does support attachment import as well. You’ll need to be careful with the data structure, so have a look at this article for specifications on how to Import attachments with notes. Keep in mind that the 8 MB file size limit does still apply here, so a large number of big file attachments may not be a fun task to perform throug the Import Wizard.

Field and value mapping made easier

If you are working with a data set that contains several picklists with lots and lots of values, mapping them could potentially consume a lot of your time. The first thing you want to make sure is that there are matching values in the CRM picklist fields (nowadays known as option sets) for all the distinct values available in your source columns. Auto mapping will do the heavy lifting for you and match the source and target values as long as they are identical in both. One thing you may not initially notice while mapping the data, though, is that the Import Wizard will also automatically append the list of values in the option sets if it encounters new source values. While it sounds like a neat feature, this may mean you end up with an unexpected set of values, duplicates with slight differences in spelling, breaking workflows or plugins due to mismatch of value ID’s etc. In my opinion, it’s much better to plan ahead and be in the driver’s seat of how your CRM is customized, even if the Wizard offers powerful but dangerous new features that can extend the schema with new fields or even entities.

When you’re working with development, test/QA and production environments, performing the same data mapping procedures time and time again could quickly become a very tedious task. Not only that, but the chances of making a mistake in the process of mapping the fields and values becomes ever more likely if you have to repeat a manual task like that. Luckily Dynamics CRM allows you to save your data maps after you create them (and before you start the actual import job), so be sure to take advantage of this feature. Of course, saving your data map into a test server won’t provide you with that data once you move to production. That’s where the export/import feature of data maps comes in handy. Just create your field mapping once and then take it with you to the next organization you’re working on.

More handy improvements in the Wizard

There used to be limitations on some of the entity fields which you weren’t allowed to update in previous versions. A common pain point was the inability to directly set the record owner, so you had to import this information in a temporary field and perform bulk updates on the records after the import. This limitation has now been removed and you’re free to assign the records directly to users or even teams.

Another caveat of the Import Wizard was that you weren’t allowed to set the state of the imported records, meaning you couldn’t easily import inactive records for historical purposes. Well, now you can, so no more need to leave out information on past activities with your customers, just because you don’t want to re-send all your emails to get them appear as closed activities. Just set your activity status as completed, import opportunities as won/lost or whatever status it is you require.

One thing to note while importing records is that the status change will actually take place after record creation. Why is this important? Well, the closing event will trigger workflows you may have in the target system. Also with the new Activity Feeds functionality introduced in the Q4 2011 update, there’s a chance you may have activity feeds rules in place that will spam your import actions all over the personal wall of your CRM users. Since no one wants to see hundreds of “activity X closed” notifications in their activity feed, be sure to remember to deactivate all rules which could wreak havoc on your brand new internal collaboration channel.

How about update existing?

While creating new records with the CRM 2011 Import Wizard is supported, updating existing records isn’t. In case you would like to only import some new fields for existing customers, by using an identifier field like email address or customer number to locate the records to be updated inside the CRM database, you’ll need to look for alternatives to the Wizard.

It is supported to perform an “export for import” extraction of data from Advanced Find that provides you an Excel sheet you can import back to update records (by selecting “make this data available for re-importing by including required column headings” option). However, unless you’re willing to dump all your records into this Excel and then match them against your import file with your custom ID field by using a tool like Access, to get the corresponding GUID’s, this won’t be the tool you are looking for.

I guess you could also create a temporary child entity for the target entity, then import new records here with the required lookup reference linking them to the parent, followed by a set of workflow magic that would transfer the required values from child to parent. It all depends on how much effort you’re willing to put in working with the out-of-the-box data import tool.

Beyond the Import Wizard: ISV solutions

There will always be many data migration needs that simply cannot be covered with a wizard like application, no matter how much Microsoft would improve the feature set of the Dynamics CRM Import Wizard. At some point it would have so many parameters and options that it would no longer resemble a wizard at all. Since the out-of-the-box functionality has to remain approachable for the “normal” user who just wants to get a simple Excel list uploaded into the system, I’m pretty certain that the market for 3rd party solutions is not going to go away anytime soon.

Instead of rooting for one particular vendor, I’m going to provide a list of the data import solutions that I’m aware of and let you evaluate which one best fits your needs.

As further reading, I recommend everyone to have a look at this awesome article by Joel Lindstrom on Lessons Learned Migrating Data to Microsoft Dynamics CRM 2011. In it Joel lists the most important gotchas to be aware of before starting a data migration to Dynamics CRM, even when using a 3rd party tool like Scribe, such as activityparty data handling, record status, removed users etc.

Switch to our mobile site