New way for creating and updating records in Dynamics 365 CRM

Introduction

We often need to create or update records in bulk in Dynamics 365 CRM and there are several methods available to do so. The Import Data feature enables us to create and update the records in Dynamics 365 CRM. Further, the Bulk Edit button and Excel Online within Dynamics 365 CRM helps us to update the already existing records in bulk.

Recently while working on the PowerApps I come across another method that is very easy to use and very helpful for the users who are used to working in Excel and who loves to work in the Excel sheets.

Now, let’s see this new feature available for us in PowerApps environment to create and update the records in mass.

What is the new feature for creating and updating records in Dynamics 365 CRM?

The new feature is called ‘Edit data in Excel’. This is not just limited to Dynamics 365 CRM. As it is a part of the PowerApps Platform, it can be used for your other PowerApps Environments.

Open and sign-in to https://web.powerapps.com. Select the Entities under the Data tab and then click on the entity that you want to work with the data. You will notice one button called ‘Edit data in Excel’.

How is it different than the Import Data Wizard, Bulk Edit and Excel Online?

The key difference between this new feature and other existing aforementioned features is, it allows us to not only update the records in bulk but also it allows us to create records on the go in Excel without navigating to anywhere else. It does not require to import the excel sheet in the Dynamics 365 CRM. With very few clicks you will get the updated data in the Dynamics 365 CRM.

When can we use Edit data in Excel?

• The organization may have employees who are more comfortable with Excel or their job is only to create and update records in CRM of specific needs. As an Administrator you can provide them an excel sheet to work on. For example, you could provide an excel sheet of Leads (which will be connected to Dynamics 365 CRM environment) to a sales person to make updates in Dynamics 365 CRM.

• As a Dynamics 365 Administrator you many want to test your business processes by creating different sets of records in the environment. You can also use this feature to create and update records in Dynamics 365 for testing purpose.

Prerequisites

In order to use this, you must have ‘Microsoft PowerApps Office Add-in’ installed. Go to Microsoft AppSource and search for ‘Microsoft PowerApps Office Add-in’ and install.

https://appsource.microsoft.com/en-us/product/office/WA104380330?src=Office

How Edit data in Excel works?

In this blog, I will illustrate this feature by taking an example of Lead entity data.

• Open and sign-in to https://web.powerapps.com and select Data under the Entities tab. From the list of entities, then select the Lead entity and click on ‘Edit data in Excel’ button.

This will download the excel sheet.  If you are not able to see the entity, then change the filter to all.

• Open the downloaded excel sheet. Sign-in to Dynamics 365 CRM using Common Data Service user account in Microsoft PowerApps Office Add-in.

• On successful sign-in you will get the below options in the add-in panel.

Source: Defines the source of the Data. In this case Lead entity data is the source.

Field: Defines the column which I’m on. In this case the Field showing ‘Last Name’ as I’m on the ‘Last Name’ column’s row cell.

New: Adds a new row in the table which you can use to create new entry and save that entry as a new record in the Dynamics 365 CRM.

Refresh: Refreshes the data in the Excel sheet. It retrieves the new changes from Dynamics 365 CRM and reloads the excel sheet with updated data.

Publish:  Push the changes made in the excel sheet to Dynamics 365 CRM. You can make changes in your data in Excel sheet and once you done with changes and confirms the data, use this button to commit all the changes in Dynamics 365 CRM.

Filter: Apply filter on the data present in the Excel sheet. By default, ‘AND’ condition get applied. Use ‘Refresh’ button to reload the data based on the condition added in the Filter window.

• Create new record and update existing records: Go to the cell in the excel sheet that you want to edit the data for. We can set every types of field i.e. text, multiline, money, datetime, Lookup, owner, optionset etc.

For optionset you will get available options to select in the add-on panel. For owner and lookup field you will get the list of records of user and records of the related entity in the add-on panel.

Optionset: Select the option from the list to select the new value for optionset type of field. Same applies for Boolean type of field.

Lookup: Select the record from the list of records to select the new lookup value.

Create new record: Click on ‘New’ button to add a new row in a table. Enter data for all the required fields. Once you are done with making changes in the excel sheet click on publish to save the changes in the Dynamics 365 CRM.

Upon successful publish you will get the ‘Publish Successful’ message.

You can verify the changes in the Dynamics 365 CRM. In our case new record get created in Dynamics 365 CRM.

So, in this way you can go on creating and updating multiple records through excel sheet using Microsoft PowerApps Office Add-in.

How it works with Read-only field?

The downloaded excel sheet shows all the fields of the entity. But we can only update the data of editable field and not the read-only field. For example, in the Lead entity, the Status field is the read-only field. If we try to change the status to Qualify/Disqualify it throws the error as the Status field is read-only.

Does it maintain the Dynamics 365 CRM Security?

Yes, it does. If you, as an administrator downloads the lead entity excel sheet and shared it with a user who do not have access to read the others leads, then that user will only be able to see the leads that are owned by him/her. We would not need to worry about the security as Dynamics CRM security applies while using this feature in Excel sheet.

Conclusion

This new way of editing data directly in the Dynamics 365 CRM environment is very useful and quicker. We can go on making the changes in the excel sheet and publish all the data changes to Dynamics 365 CRM in a single click.