Get Dataverse row changes

Within Dataverse you can trigger a flow when a row is deleted. However, the response details are very limited. In this article I will show you how to lookup change details like who deleted the row and in which project it was located via a Power Automate flow.

Inspiration

This question from ALDERM23:

I’m looking to build a flow in power automate that emails me if a user accidentally deletes any task within any project I’ve created on Project for the Web. I’m looking for the email notification to include details on which task, which project and who has deleted. I’ve tried the below but the flow keeps running into an error and I can’t figure out why.

Power Users Community thread: Flow triggered when a task in “Project for the Web” is deleted.

The Challenge

Whenever the flow gets triggered the response is very limited. Basically you only get a response that mentions which row id has been deleted.

When you try to retrieve details via for example a Get row by ID action you will get a NotFound error. The row is already deleted and does not exist anymore. You cannot use that action to retrieve details about the row.

This makes triggering this flow pretty useless. It would be great to know the name of the task that has been deleted (what), the name of the user who has deleted it (who) and in which project it was located (where).

Surely we can collect these type of details of a row, right?!

Auditing to the rescue

In this scenario you can use the Dataverse auditing feature. Kudos to Eric Cheng who pointed me into the right direction with this response on the Microsoft Power Automate community.

When enabled the Audits table should contain changes made to a table like Project Tasks. While the Project Tasks row is already deleted, the Audits table rows for that Project Tasks row should still exist.

The Audits table has a ChangeData property. That property contains a CSV of old values of all the attributes whose IsAuditEnabled property is true and are being changed.

If you haven’t enabled auditing for your table yet make sure you check out these steps from the Microsoft documentation: Configure auditing for one or more tables and columns in Power Apps.

changedAttributes

The changeData property has a nested changedAttributes property. Within that array each property of the table is shown with it’s logicalName and with the newValue and oldValue. In case of the Deleted change type we are only interested in the oldValue, all the newValue properties will be null.

Below is an example of the returned changedAttributes value of a single Audits table row.

Flow Setup

For this sample I have chosen the Project for the Web product which stores their data in Dataverse in tables like Project Tasks. However, you should be able to use the same approach for other tables as well.

1. Add a When a row is added, modified or deleted action.

a. Select the Deleted Change Type
b. Select a Table, in this example Project Tasks
c. Select a Scope, in this example Organization

2. Add a List rows action.
The Audits table rows are filtered on the related project task id.

a. Select the Audits Table
b. Add the below value in the Filter Rows field

3. Add a Delay action.
The delay has been added because in earlier test I noticed the Audits row was not available directly after deleting the Project Tasks row.

a. Add a Value for Count, in this example 1
b. Select a Unit, in this example Minute

4. Add a Filter Array action.
The first returned row of the Audits table is used. This should be the last one with the deleted event, since rows are returned in descending creation order. The changedAttribute value is converted to json because the List rows returns it as a string and not an array. After that the changedAttribute array is filtered for three specific fields.

a. Use the code snippet below for the From

b. Switch the Where field to Advanced mode
c. Use the code snippet below for the Where field

5. Add a Select action.
The Filter Array results are sorted on logicalName, ascending order in the From field of the Select action. This is to make sure we can easily hard-code the index [0], [1] and [2] in the expressions. A nicer approach would be to use xpath instead.

a. Use the code snippet below for the From

b. Switch the Map field to Text mode
c. Use the code snippet below for the Map field

6. Add a Get a row by ID action.

a. Select a related table, in this example the Projects Table
b. Add the below value in the Row ID field

7. Add a second Get a row by ID action.

a. Select a related table, in this example the Users Table
b. Add the below value in the Row ID field

8. Add a Send an email (V2) action.

a. Select a recipient in the To
b. Use the code snippet below for the Subject field

c. Use the code snippet below for the Body field

That should be it for the setup. When the flow gets triggered you should receive an e-mail with more details.

What, who and where!

Now you should be able to see what has been deleted (task name), who has deleted it (which user) and where the task originally was located (project).

Happy testing!