After a customer has their CRM for a while and customer information is starting to flow in and out, a common question is “How do I bulk edit records?”
The information here is to go over the various options and help you make a decision on the best method of bulk editing.
Dynamics CRM gives a number of options for bulk editing;
- The Bulk Edit feature
- The workflow engine
- Export to Excel, edit (in Excel) and import to CRM again
- Developer interface or third party applications to write custom code and edit
Each of the methods above has their own strengths and weaknesses and suitability for the editing that you wish to achieve.
Customers may not want all CRM users to be able to mass change customer data. Each of the features is independently controlled by the Dynamics CRM security architecture.
Custom code development is an exception and requires specialist skills and knowledge to develop and falls outside the scope of this article, it is an option that should be mentioned though!
One of the considerations to performing bulk editing (and another reason an organisation may wish to limit access) is that bulk editing bypasses any form loading and field ‘onChange’ logic. You may have a pick-list/option set that ‘If 1 is selected A is automatically populated and if 2 is selected B is automatically populated’. The A and B logic will not apply.
In fact, the CRM bulk edit feature may render fields with business logic associated as ‘read-only’ so that users cannot edit the information. The workflow option would help with this scenario.
Bulk Edit Feature
This is ideal for those quick modifications to a smaller number of records. Typically the set of customer records is a short list from a CRM view or Advanced Find.
How to use
Ascertain the customer records that you wish to bulk edit. Holding the control key and multiply selecting records with a mouse shift + left-click works in Dynamics CRM.
Once selected, press the Edit icon;
A different editing form appears;
Note the general information after the heading.
A Bulk Edit form appears. Simply enter the data in the field of choice. This information will apply to ALL customer records selected, replacing whatever the field contains.
- Very quick and easy, available from the grid tool bar
- Easy to see (double check) the change that is about to apply
- No good for clearing a field and making it blank (null)
- You can only apply a single value to a field (data applies to all selected)
- Not so good for large numbers of customer records where results ‘go onto the next page’ of the results grid
Using Workflow to Bulk Edit
The CRM workflow engine is a very powerful way to update customer records in batches. Workflows are a personal favourite of mine to help manipulate and protect CRM customer information.
How to use
Within the context of a one off bulk edit you prepare by creating a Manual workflow for the entity of interest. You can create a workflow from the Workflow icon on the tool bar, IF there are other workflows that are available.
If the Icon is greyed out go to the CRM Processes area in:
- CRM Main Menu > Settings > Processes > New
- Create a Workflow (not a dialogue) in the Category
Keep the ownership of the workflow as ‘User’ and enter in the workflow logic required.
- Save and Activate the workflow
The Workflow becomes read-only;
From the records of interest
As with Bulk Edit, ascertain the customer list of interest and select the records.
- Press the Run Workflow Icon on the Ribbon. If the icon is greyed out or your workflow is not in the list returned, check that your workflow has activated correctly.
- Select your newly created manual workflow and press OK.
The CRM platform will go through and update the records ‘asynchronously’. This means that the changes appear over the next few minutes. You will have to refresh the view to see the changes in CRM.
- You can wipe (clear) a field and make in blank
- You can apply testing logic and build in decisions to what fields get updated with what information
- Out of the box feature that is available with relative ease
- More certainty that the edit will be consistent if using over more records than the current view. For example if you are updating 1000 records, with 250 records per page, you will need to run the workflow 4 times. The edit will be the same for all 4 times.
- Has the ability to take data from one field and apply it to another (copy information) on a per record basis
- Has he ability to update a parent record
- Has the ability to create associated records
- Care must be taken with complex workflow logic or customer data could be damaged.
- Workflows also have the same the limitation as bulk edit in that it can only apply to records on the current Records displaying in the CRM View. You can use personal options to increase this to a maximum of 250 records but that is still a relatively low number.
- Slower to create editing rules than Bulk Edit above.
- The workflow creation wizard has a learning curve to overcome
- Cannot update records that are multiple child or parent relationships (the many side on a one-to-many)
Export to Excel to Bulk Edit
Export and Import data is a new feature to CRM 2011 and very powerful. The one stand out major benefit of this method is the ability to export many, many more records than the current CRM view.
How to use
The following is a quick overview of how to use the Data Export and Import. There is plenty of detailed information on the net on how to do this. For example; http://tinyurl.com/9rx64xb (Andrew Jeffers)
Start with the CRM customer records that are of interest to you. The Advanced Find feature is an excellent way to get quickly to CRM views of interest and the Export to Excel feature.
Selecting the records is less important as the Export process will offer wider options on what customer records will be exported, however if you do filter records using advanced find then only those records in the results will be exported.
- It is critical to select the ‘Make this data available for re-importing by including required column headings’
- Export the file and SAVE to a local destination
- Open the Excel file and edit the rows to whatever values you desire.
Import the file back into CRM
- Use the Import Data tool to locate your updated file
- Chose the options that you want, the default settings should be appropriate in most cases
- Select Submit
The wizard will now submit the job to the CRM system to be processed in the background (asynchronously).
- Press Finish to close the wizard.
You can monitor the progress of the job and see what records are affected from the global Data Management area in CRM;
- CRM main Menu > Settings > Data Management > Imports
Just like the Workflow editing, you will need to refresh any CRM views of customer records to see the edited changes
- Can export many customer records for edit in one go.
- You can update different values for multiple records. For example selecting different option set values for different customers
- Relatively easy to do and people tend to be comfortable with Excel
- Again, a very powerful feature that could overwrite customer information in error
- Has the potential to create duplicate records
- Will not wipe a field and leave it blank.
- Has a much greater chance of importing spelling mistakes and errors as there is basically no validation in Excel – unless you decide to script it into Excel
- There is an 8Mg import file size limit but assuming that you used an Export in the first place you should be ok if you are not creating thousands of customer records during the import.
Third Party Apps and Custom Code to Bulk Edit
I am not going to cover this in any detail. The assumption here is that there is a business need that can only be met through third party applications or bespoke development. Not really a day to day usage.
I cannot think of too many scenarios where you need custom code to bulk edit? The times that custom development might be considered are;
- Integration with external (to CRM) systems is required (third party applications can help with this)
- There is some customer record logic that MUST be performed before the form is saved (synchronously)
- Complicated business logic is required on record changes that the standard application won’t cater for. For example aggregation of numbers (maths in general), date/time manipulation.
How to use
Some kind of interface would need to be provided in some way. Topic is outside the intention of this article.
- A multitude of complex rules and processes can be catered for
- Can be used to update customer records in complicated relationships like Many to Many
- Requires specialist knowledge and experience to build
- Needs a development cycle (slow, complex, testing required)
- May not pass a business case to develop!