Leverage Microsoft Excel to edit records

Have you ever wanted to edit multiple Microsoft Dynamics CRM records from within Microsoft Excel, without having to open each record’s individual form? With this new Dynamics CRM 2011 feature, it is possible to update Dynamics CRM records using Microsoft Excel (2003, 2007, or 2010). All you have to do is export data from any grid, save it to disk, edit it in Microsoft Excel, and re-import the file using the Import Data Wizard (the file will get auto-mapped). Within minutes your records will be updated by a system job running in the background. You can also add new rows in the downloaded Excel sheet and we’ll go ahead and create those records for you.

Updates are carried out on the fields that were changed. In case a record was updated in Dynamics CRM between the time data was exported and re-imported, we skip over your changes to that record so that invalid updates aren’t carried out.

Let’s have a brief walkthrough before diving into more detail.

1. Export the data from whichever grid you want.

2. Tick the “Make this data available for re-importing by including required column headings” checkbox.

3. Open the downloaded file in Microsoft Excel. Edit the records as appropriate, and add new records if required. Save the file to disk.

4. Upload this file into the “Import Data Wizard”.

5. On clicking next, CRM will auto-map the file and save you the hassle of having to map source file columns to CRM fields.

6. Submit the import job and wait for it to complete.

7. And, you’re done!

8. If you look at the successes column in the imports grid, only two records were processed. Since the other records were not edited, they are not picked up by the import job.

What all can be done?

• Modify the value of any updatable field of a record (e.g. Account Name)
• Create new records by adding rows to the exported Excel sheet
• Assign the record to another user or team by changing the value in the Owner column
• Change the Status/Status Reason of a record (e.g. mark an Account Inactive)

What isn’t allowed?

• Delete records in Dynamics CRM by deleting the corresponding row in Excel.
• Modify values for fields that are “Not Valid For Update” (e.g. Created On field of Account record type)
• When creating new records, add data into fields that are “Not Valid for Create”(e.g. Modified On field of Account record type)
• Modify values in related entity columns (e.g. the “E-Mail (Primary Contact)” column in the “My Active Accounts” view)

How are specific field types handled?

Option Sets

To assist you when entering values in a field of type option set, all option set values for that field are exported with the data. You can see the list of values in a dropdown associated with these cells. Whenever a value is changed in such a cell, it gets validated against the list of possible values.

Status / Status Reason

If status/status reason values are updated, a SetStateStatus SDK request is made for that record. However, this call might fail if there is some business logic associated with change of status for that record type. (E.g. Opportunity record type has special logic and requires WinOpportunity / LoseOpportunity SDK requests to change status)

Owner

We search for a User or Team having the same Display Name as specified by you in the sheet, and assign the record to that user.

Money

Money fields get exported in a special way to make the sheet re-importable. The currency symbol is removed from the value and instead exported as a separate column (the “Currency” column). To change the currency of that record, you can change the value in this “Currency” column.

Single/Multiple Lines of Text

Any changes done to cells of type Text (as defined in Dynamics CRM) are validated for minimum and maximum text length. You can see this information in the tooltip associated with each cell.

Numbers and Dates

Numbers and Dates are shown in Excel with the number/date formatting that is defined in your Operating System. It does not matter if you are using a different format in Dynamics CRM.

All changes are checked for the range of numbers/dates allowed in that field.

Things to remember

• If a row is not changed in Excel, then we do not call update on that row – you need not worry about workflows getting triggered unnecessarily!
• Full Names (Contact, User, and Lead) should be entered in your organization’s Full Name Convention Code for lookups on Contact, User, and Lead to work correctly.
• Fields of type Party List are not supported by data import – do not update such field types using the Data Import Wizard.
• If the grid contains multiple columns that have the same display name, then you will not be able to export data in a re-importable format.
• If a record type contains multiple fields with the exact same display name and your exported sheet contains one of these fields, then you will not be able to re-import this file.
• Record updates will not happen in case data is changed in a field on which you do not have the correct permissions. (in accordance with your Field Security Profile)
• If you are using Excel 2010 and haven’t installed Service Pack 1 yet, it is highly recommended to save the exported file to disk and unprotect it instead of directly opening it from Internet Explorer.

As always, we’d love to hear your feedback!

Cheers!

Hitesh Madan