New way for creating and updating records in Dynamics 365 CRM

By | February 10, 2020

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

New way for creating and updating records in Dynamics 365 CRM

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

New way for creating and updating records in Dynamics 365 CRM

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.

New way for creating and updating records in Dynamics 365 CRM

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

New way for creating and updating records in Dynamics 365 CRM

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

New way for creating and updating records in Dynamics 365 CRM

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.

New way for creating and updating records in Dynamics 365 CRM

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

New way for creating and updating records in Dynamics 365 CRM

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

New way for creating and updating records in Dynamics 365 CRM

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.

New way for creating and updating records in Dynamics 365 CRM

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

New way for creating and updating records in Dynamics 365 CRM

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

New way for creating and updating records 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.

New way for creating and updating records in Dynamics 365 CRM

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.

6 thoughts on “New way for creating and updating records in Dynamics 365 CRM

  1. Guy

    Hi Interesting, but from a devops perspective for reference data reasons will this work for multiple organisations. DEV, SIT, PROD etc.. and if so does it maintain guid id’s and relationships?

    Thanks
    Guy

    Reply
    1. Inogic

      Nice question. Yes, we can have multiple organizations like Dev, UAT, PROD etc. and in that case we need to connect to appropriate organization. Click on the gear button of Microsoft PowerApps Office add-in Data Connector tab and this will open a configuration page as shown below. Enter your CRM organization URL in Connection field and sign-in. This will load data and relationships from the connected environment.

      Multiple Organization

      Hope this helps.

      Thanks!

      Reply
  2. Mads

    Is there a way to use “User defined views” rather than only system views?

    Reply
    1. Inogic

      There is no way to use views. But you can filter the data using “Filter” option in Microsoft PowerApps Office Add-in.

      User defined views

      Hope this helps.

      Thanks!

      Reply
  3. Jan

    Hi how to find some audit log? For example I’m updating data which are in D365 (entity account) and I don’t know how to find information about status of this change – successfully updated / error.

    Reply
    1. Inogic

      You will use the Publish button to save changes in Dynamics CRM. If you get the “Publish successful” message then it means the changes have been saved successfully in Dynamics CRM. If you get the “Error Publishing” message then it means the changes have not been saved in Dynamics CRM.

      Publish successful

      Publish failed

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *