The January update for Microsoft Dynamics CRM 2016 has so much to offer! The update introduced cool new features to make a developer's life a lit bit easier. Features include Data Loader, one click document generator, the ability to hide and show sub grids, and many more. In an earlier blog, we showed you how to use the Data Loader feature, but in today's blog, we'll be talking specifically about to access the Data Loader service. So let's dive in!
The Data Loader service is an answer to your data migration scenario hiccups where a user has to rely on third-party solutions or has to write custom code to import/export large volumes of data. Essentially, it's a painless way to handle bulk data loading in to a CRM system.
Bulk data migrations can be imported and exported into CRM systems by using the Data Loader service. The feature enables bulk data to be uploaded to a staging area where you can perform light, data quality functions and then push the data in to your CRM system.
The Data Loader service feature is now supported in Dynamics CRM 2016 and Dynamic CRM 2015 Update 1. Here are a list of some key features:
Supports various source file formats
Encrypted data loading
Supports recurring data imports/exports
Supports the retrying of failure records
Data mappings are reusable
Easy to configure the import of data
Reduces use of third-party tool
Reduces the need to write custom code
Supports the use of data projects in multiple CRM instances
The Data Loader preview is enabled by default for all CRM administrators. Use the following steps to access the service:
Export to Excel: Understanding the Difference Between Static Worksheet and Static Worksheet (Page Only)
Learning to export to Excel in CRM is pretty straightforward and easy! However, even if you've worked with exporting to Excel before, you might not be aware of two little-known options for exporting to Excel: Static Worksheet and Static Worksheet (Page only). In today's blog, we will explain the differences between the two and show you how to use these options for exporting Static Worksheets in CRM. Let's begin!
For our example today, we have set the records per page in any list of records to 250 in the Set Personal Options section.
Scenario 1: Output in a Single Page
When the number of records displayed as the output of an Advanced Find are less than the Records Per Page personal setting, the Static Worksheet option will be grayed out and you will only be able to export the data using the Static Worksheet (Page only) option. In the screenshot below, the number of output records is six, which is less than the Records Per Page setting of 250. This is the reason why the Static Worksheet option is grayed out and you are only able to export the data using the Static Worksheet (Page only) option.
When you click Static Worksheet (Page only), six records will be exported to Excel.
Scenario 2: Output in Multiple Pages
When the number of records displayed as the output of an Advanced Find are more than the Records Per Page personal setting, both of the options to export data as static worksheets will be available. In the screenshot below, you can see that the number of output records is 5000+, which is more than the Records Per Page setting of 250. That is the reason why both options for exporting data in static worksheets are available.
When you select Static Worksheet (Page only), 250 records will be exported to Excel.
When you select Static Worksheet, more than 5,000 records (limited to 100,000) will be exported to Excel.
That's all for the blog today! We hope you learned something new to help you when working with CRM and Excel. To learn more about Dynamics CRM, why not register for one of our educational events? From webinars, to CRM boot camps, to customized trainings, PowerObjects' education team is the best in the business when it comes to equipping you to get the most out of Dynamics CRM!
10 Data Export and Import Redesign Features in Dynamics CRM Online 2015 Update 1
For many years, a Microsoft Excel export and import redesign has been one of top change requests from the Microsoft Dynamics CRM Community. The previous version of the feature did not allow you to import large sets of data, extract more than 10,000 records without risky database hacks, and wasn't always user friendly. In today's blog, we will highlight ten key MS Excel Import and Export redesign features in Microsoft Dynamics CRM Online 2015 Update 1.
Note: Currently, these features are only available in MS CRM Online 2015 Update 1, but we are expecting it to be released for on-premises installations soon.
1. Redesigned Export Options
Microsoft Dynamics CRM Online 2015 Update 1 introduced a new design for the CRM Data Export feature. Now, instead of just one Export to Excel button, users will see several Export options, including the default option, Export CRM View in Excel.
The full list of data export options available with Update 1 includes:
Static Worksheet/Open in Excel Online
Static Worksheet on Page
The first three options provide the ability to import the exported file back, making the process of CRM data modifications even more streamlined. Now users can export data in one click, modify the data, and import exactly the same file back. No file conversions required!
*Source: Microsoft Release Notes
2. Once Click Export Feature
In addition to the advanced Export to Excel options, Microsoft now offers the ability to export data to MS Excel Online directly from a CRM View. Users can click on the Export to Excel button and the downloaded file becomes available on your desktop. No more popup windows asking you to select the file format! Again, this option is only available for CRM Online.
3. .xlsx Import File
The default CRM data export and data import format is now .xlsx. The exported file is automatically converted to an Excel table which can be open and modified by any software supporting Excel files. This is a fantastic enhancement that will save time for CRM users working with data on a daily basis!
When data is exported from CRM, the Excel tab name corresponds with the selected CRM View.
Column width scales from View Definition for a consistent display.
Field format is now respected. Text stays as text, numbers are numbers, etc.
These changes will definitely improve the Excel export experience!
4. Enhanced Data Re-import
One of the most exciting changes in the redesigned CRM Data Export and Data Import is the ability to export CRM data in Excel format and re-import it back. Now, every export file brings the GUIDs associated with CRM records, and users can bulk edit and re-import the data back if required. Previously, CRM could only import data that was marked for data reimport. CRM is now intelligent enough to recognize the GUIDs of exported records and match them automatically when data is re-imported back to CRM.
5. Composite Fields
Another exciting feature is the ability to export composite fields in one column. Previously, CRM exported each field associated with a composite field in a separate column. Now, Full Name is exported as one column including First Name and Last Name. Address 1 is exported as one column as well. Please see details below*.
*Source: Microsoft Release Notes
6. Warning Dialogs are Fixed
In previous versions of CRM, CRM data export files were downloaded either in xls or xml format. When users opened the file in MS Excel, a warning message popped up every time. For example:
The file format and extension of "XXXX.xls" don't match. The file could be corrupted or unsafe. Unless you trust its source, don't open it. Do you want to open it anyway?
Be careful - files from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View.
External Data Connections have been disabled. Enable Content.
Now, with Update 1, these dialogs are completely removed and no longer appear on the screen! Depending on personal settings, users may still see the protected view messages, but this can be easily configured in individual MS Excel settings.
7. Immersive Excel Online: Ad hoc Analysis in MS Excel Online
Integrated MS Excel Online now supports the ad hoc analysis within CRM. Users can select a view to export data from and MS Excel Online will open the selected dataset right in a CRM window. No pop-up or local file copies!
In this view, users can create formulas and perform ad hoc and what-if analysis right from Excel Online within CRM. For example, you can summarize the total amount of open or won opportunities and quickly run what-if analysis by applying Excel formulas without leaving CRM.
The ad hoc analysis feature is only available for System and Personal Views. Advanced Find, Quick Search and Filtering Views do not support the functionality.
The default threshold value to refresh Excel Online files (generated in CRM) is defined to five minutes. If users do not save their changes in five minutes after the Excel file is opened, CRM will refresh the data in the file. We recommend saving changes before this limit is exceeded.
8. Immersive Excel Online: Bulk Updates in MS Excel Online
In the same view as above (Select a Viewà Export in Excel Online), users can modify the presented data and save their changes directly to CRM in one click. The changes are processed based on the following rules:
CRM uploads the exported and modified dataset as a new data import file that users with sufficient permissions will be able to see in Data Managementà Imports. The bulk updates will have an Excel Online prefix so they are easier to differentiate.
CRM maps Excel columns based on Display Name. This is the reason why a view with same column names (Display Names) cannot be exported.
CRM honors the sequence of changes made in CRM and MS Excel. If another user updates a record at the same moment you exported a view and modified it, the CRM modifications will not be overwritten by changes in MS Excel.
CRM updates records on a field-by-field basis. The bulk update feature only changes the fields that were actually changed in the Excel document. For example, if you export data in Excel Online and modify the City and Main Phone Number, then only these two fields are updated during bulk import.
Column names in Excel cannot be changed if the file is to be imported back to CRM.
Note: The bulk update feature is only available for System and Personal Views. Advanced Find, Quick Search, and Filtering Views do not support the feature.
9. CRM Export Limit has been Increased to 100000
Users and administrators used to only be able to export 10,000 records. This limit was controlled by an internal CRM setting that System Administrators and Customizers could not adjust within CRM. Any modifications to the number of records that users could export from CRM required a direct database update.
With CRM Online 2015 Update 1, users can now export up to 100,000 records with just one click! This increased limit will prevent users from having to update the database directly, which is never a great option. No additional customization is required!
Note: The exported file cannot exceed 32MB in size.
10. CRM Data Import File Limit has been Increased to 32 MB
Originally, the file size could not exceed 8MB if not zipped and 32MB for zipped files. Now, the limitation is gone, and you can upload up to 32 MB! No more hurdles of splitting a data import file into several XML or CSV files!
BONUS CONTENT: Microsoft Dynamics CRM Online 2015 Update 1 - Data Export Formats
In previous CRM versions, data exported from CRM was formatted as Text Columns regardless of the CRM field type (Text, Whole Number, Currency, Date, etc.). If a field had a leading zero, it would disappear in the exported file, which resulted in invalid exported values. For example, Account, Contact, and Order Numbers would have a completely different unique value once exported to Excel. This made the ability to import data back into CRM complex and cumbersome.
With the newly redesigned Export to Excel function, CRM exports data preserving all formatting. When data is exported from CRM, the Excel tab name will correspond to the selected CRM View and Column Width scales from the View Definition for a consistent display.
The key enhancement with data mapping rules in Export to Excel functionality is that now CRM retains the data format for the exported data. CRM will export data according to the configurations of CRM Views and metadata and will honor different data formats. The table below describes the new mapping rules for exporting data in Excel*.
*Source: Microsoft Release Notes
These 10 features of the redesigned data export and data import dialogs have improved the overall data management experience in MS CRM. These exciting integrations with MS Excel Online provides a seamless experience for end users! You can learn more about the updates for CRM 2015 by visiting our website.
Exporting Data to Re-import: The Invalid Column Headings Issue
Often you'll need to export data from CRM, make changes, and then re-import the data using CRM's out-of-box capabilities. In doing this, you might receive the message "The data has invalid column headings, so you will not be able to re-import it" pop up. What is this message and how do I successfully export my data?
The issue actually has to do with duplicate display names. Since the export writes the display name as the column name, you cannot have duplicates because CRM doesn't know how to match up the columns again when it re-imports the data.
In the scenario below, there is an issue with a duplicate status fields; the out-of-box status and a custom status field with the same name.
Hopefully this will help you get past this error message and successfully export your data for re-importing. Interested in taking your CRM experience to the next level? Consider attending this year’s PowerUp in Minneapolis, MN, November 10-11 with an optional hands-on day November 12! Hosted by PowerObjects, this can’t-miss CRM event features over 60 breakout sessions and includes tracks for all experience levels. Register today!
Exporting to an Existing Dynamic Excel Document from Dynamics 365
When exporting data to a dynamic Excel document from Dynamics 365, do you ever find yourself wishing there was a way to add your new Excel document to an existing one instead of always creating new workbooks? This blog will walk you through the steps to do just that!
In this series of screenshots, you can see that the dynamic Excel document created has connection string but cannot be edited and the data source is not readable. We cannot create a copy of this connection with the same URL in a new excel workbook.
Here is the solution: copy the excel sheet > move (command) > to existing workbook.
Here is a screenshot of copied data along with connection string:
And with that, the dynamic connections and data is copied in the existing Excel workbook.
Make sure to stop by our main blog page for even more slick tips!
Happy Dynamics 365'ing!
How to Disable Exporting or Printing Reports in Dynamics CRM 2016
Depending on the sensitivity of an organization's data, it is often necessary to disable the ability to export or print reports. This ensures that reports with confidential information such as sales numbers, company earnings, personal contact data, or lists of accounts do not leave the confines of CRM. The export permission is set at the Security Role level. In today's blog, we'll be showing you to disable exporting or printing reports in Dynamics CRM 2016.
To disable these options, follow these steps:
1. Expand the Menu from the Ribbon.
2. Select the Settings option.
3. Select Security.
4. Select Security Roles.
5. Select the desired security role and navigate to the Business Management tab.
6. Scroll to the Privacy Related Privileges section. Ensure that the Export to Excel and Print options are disabled.