Microsoft Dynamics CRM and Bulk Editing Records

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!

Limitations

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;

Edit Records

A different editing form appears;

Bulk Edit Form

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.

Benefits:

  • Very quick and easy, available from the grid tool bar
  • Easy to see (double check) the change that is about to apply

Weaknesses

  • 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.

Create Workflow

  • Save and Activate the workflow

Activate Workflow

The Workflow becomes read-only;

Workflow 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.

Run Workflow

  • Select your newly created manual workflow and press OK.

Select Workflow

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.

Benefits

  • 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

Weaknesses

  • 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.

Export to Excel Icon

Select Records

  • 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.

Working in Excel

  • Save your changes

Import the file back into CRM

  • Use the Import Data tool to locate your updated file

Import Data

Upload File

  • Select Next

Review Settings and Import Data

  • Chose the options that you want, the default settings should be appropriate in most cases
  • Select Submit

Import Data - Finished

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

Data Management

Data Import Job

Just like the Workflow editing, you will need to refresh any CRM views of customer records to see the edited changes

Benefits

  • 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

Weaknesses

  • 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.

Benefits

  • 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

Weaknesses

  • Requires specialist knowledge and experience to build
  • Needs a development cycle (slow, complex, testing required)
  • May not pass a business case to develop!
Advertisements

14 Responses to “Microsoft Dynamics CRM and Bulk Editing Records”


  1. 1 devon August 28, 2013 at 4:18 am

    Under the import-export to -from excel you have one of the possible side effects as “Has the potential to create duplicate records” can you explain or help me understand how, under what circumstances? I personally HATE the CRM and its workflow and prefer to just export everything to excel where I feel i can manipulate the data better, i tend to make more calls faster when i work with the spreadsheets rather than CRM and frankly i don’t waste my time with the stupid activities(seriously why) so i want to export. do my work in the appropriate fields and then import it back. this will not work if i am creating duplicate entries

    • 2 Ian Luxton - CRM August 29, 2013 at 8:18 am

      Hello Devon, Ok if you try to do everything in Excel you will miss a large number of CRM platform features that can be highly productive – but your call.

      To respond, working in Excel there is a chance that you could have a row duplicated and not notice. If you are exporting from CRM in the first place this is really not a huge risk.
      You can also (and should?) have your CRM duplicate detection rules enabled to help. Note on this feature though, if the duplicate detection rules trigger NONE of the records are imported until you sort them out in the source sheet.
      Overall if using the Excel Export to bulk edit the trade off is possibly bypassing CRM form business logic.

  2. 3 devon August 29, 2013 at 8:37 am

    Thanks for the response Ian! I appreciate it.

  3. 4 Radhi March 24, 2014 at 3:07 pm

    Hi Ian I have a question. Hope you can help. I want to bulk edit opts out. I have a big excel sheet come from concept (its a different system). I need to somehow do bulk opts out. plz suggest me what is my best bet. From this article I have concluded export & import but worried about duplicates & missing rows of excel as you mentioned. I already have a exported list from Concep not CRM so plz suggest.

    • 5 Ian Luxton - CRM March 28, 2014 at 4:52 pm

      Hi Radhi, I think I follow your question… You will find it difficult to automatically bulk update your CRM data from an external export file (from Concep). You will have to do an export from CRM to retain the record integrity.
      Unless you can do some kind of advanced V lookup or something to ‘link’ your Export file to the CRM records, it is going to be difficult to automatically bulk update.
      Just wondering if there is a way to make your Concep Export file a CRM Marketing List?
      Thanks for your question and sorry I can’t be more assistance.

  4. 6 Erick May 17, 2014 at 6:22 am

    Here’s another way I used a few years ago to bulk update existing records from an external Excel .csv file:

    Imagine you have an existing entity that contains 5 fields, “A”, “B”, “C”, “D”, “E”. Field “A” is the unique key and you want to update “C” and “D” fields.

    You create a new temporary entity containing fields A, C and D, then create a 1-to-N relationship between your temporary entity’s “A” primary key field and your permanent table’s “A” primary key in a manner that your temporary entity will be a children of your permanent entity. By this way, a workflow executed on the children entity can update a value of he mother entity.

    Then you create a workflow on your temporary entity that starts when a new recored is inserted. This workflow updates the permanent entity fields “C” and “D” using the relationship on primary key “A”.

    Then the only thing you now have to do is to import a CSV file containing fields “A”, “B” and “C” into your temporary entity and the workflow will start for each record imported and update the permanent entity for each of them. And what is interesting is that you’re not limited to 250 records. Your limit is the number of workflow processes your CRM is able to start at the same time without being overloaded (we often have problems with that).

    The “A” primary key column of your CSV file comes from an advanced find you previously did from your CRM. Fields C and D come from wherever you want.

  5. 7 Sameera Salih July 27, 2014 at 5:57 pm

    Hi Ian, which method can we use to update read-only fields in the records and read-only records for eg: won Opportunities?

    • 8 Ian Luxton - CRM July 27, 2014 at 7:48 pm

      Hi Sameera, you typically cannot update a record that is marked as inactive. Your won Opportunity will need to have its status changed to an ‘Active’ state to edit. For Read Only Fields on an Active record I typically use a Workflow. You have to create the workflow with the field editable, then when happy with the workflow, set the field Read Only again and the workflow will work.
      I do hope this helps.

      • 9 ukcrmguru April 22, 2015 at 9:04 pm

        Ian – you can update many entities while they are inactive, using a workflow. For example, Opportunities, as well as Accounts, Contacts and Leads. You can’t modify closed activities without re-opening them, and it seems that you can no longer modify Cases which are closed (I am investigating in which version this changed).
        For me this is one of the key benefits of using Workflows to do this work.
        Pretty sure export/import works fine too, it is only the UI that is blocked for editing / bulk editing.

  6. 10 Isil Gursoy August 7, 2014 at 6:37 pm

    Nice article. Thanks 🙂

  7. 11 Ingo December 11, 2014 at 8:45 pm

    Hi Ian. I could not find the field “Owner” in the “Update Account Set properties” section. Are you shure that one can Change the ownership with help of a workflow?

    best regards
    Ingo

    • 12 Ian Luxton - CRM December 12, 2014 at 7:19 am

      Hi Ingo. It has been a while since reviewing this article. Yes, you can easily update owner using a workflow. You can either directly specify the new owner in the workflow using a lookup or set owner based on another field on the form that is a lookup to CRMSystemUser. For example, if you have an exisiting lookup to CRMSystemUser called ‘Account Manager’ you could set the Record ‘Owner’ to be the ‘Account Manager’.
      I advise against ‘hard coding’ a user record in a workflow that is to be used by business process automation.
      It is fine to specify a user directly if the change is a one off edit as mentioned in this article.
      In the case of my example, there is another field called ‘Ownership’ (ownershipcode) that is an optionset (pick list). My example does not update the record owner.

  8. 13 Sagar Sekhri March 31, 2015 at 7:01 pm

    Hi Ian,
    We have a requirement where we need to bulk edit/bulk create some records USING THIRD PARTY PLUGINS.

    Currently we are using service.create method in a for loop.

    But, how can we use service.createmultiple method? Do you have a clue?


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s





%d bloggers like this: