On some pages within D365FO there will be an Office button in the upper right hand corner that allows you to either export grid data to Excel or use Excel to easily update, create, or delete data. Security for these actions are controlled slightly differently than the normal menu item method instead they are controlled by security via data entities.
Integration Points
There are two different ways to interact with Data Entities, through the OData service and DIXF (Data Import/Export Framework). These will be listed as the following under the security configuration:
Data Services -The ability to use OData services (API) for the entity.
Data Management – The ability to use asynchronous integration options for the entity, such as import/export and connector integration.
Open In Excel vs Data Import/Export Framework (DIXF)
The ‘Open in Excel’ functionality makes for updating, creating, or deleting data very easy, but it should not be used for mass updates of data due to performance issues. If you are looking for a way to do a mass update, using DIXF through Data Management is the way to go.
How to Determine Which Data Entity Controls the Open in Office Functions
The easiest way that I’ve found to determine which data entities control these functions is to navigate to the form, and use the ‘Open in Excel’ option. Sometimes there are multiple options listed here with some entries that start with [Obsolete], these options should not be used if possible.
By clicking on the Vendors V2 option in the above example, you are presented with a download dialog and the default filename will include the data entity name. In this case, the data entity name is VendVendorV2Entity.
Setting up Security for Office Functionality
If you have Read permission to the form itself, the Export To Excel option will show up. You can also grant Read permission to the Data Entity that is tied to the data source of the form.
If you want the Open in Excel options to appear you will need to assign Create, Update, or Delete permissions to the Data Entity that is tied to the data source of the form depending on which options you want the user to be able to perform using the Open In Excel functionality.
Once you are done making your changes in Excel you can use the Publish button to actually push your changes back into D365FO.
Support for XDS
Although Microsoft’s documentation states data entities do not support XDS, through testing and another great blog post from Andre Arnaud de Calavon I can confirm that XDS policies are applied correctly to data entities. In the example below, I have a test user that has access to customers with an XDS policy to only show customers with customer group of 10 and when using the Open in Excel or Export to Export options the XDS policy is applied correctly.
Resources
Open entity data in Excel and update it by using the Excel add-in
Create Open in Excel experiences
Extensible Data Security and Data Entities – Microsoft Dynamics 365 for Finance and Operations
Hi Alex. Enjoyed your post. Do you know how to disable the “Open in Excel” option in order to prevent accidental mass changes to data. We would like to keep the “Export to Excel” option which appears in the same dialog.
Nathan,
The ‘Open in Excel’ button is controlled by the data entity security for that particular object. If you deny and/or don’t assign access to the data entity this option will not show up. I’m not sure I know what you mean by the ‘Export to Excel’ option but the ‘Export all rows’ option within a grid is controlled by access to the information in the grid which is controlled by access to the menu item of the form.
Thank you Alex for your help!