Accessing the “before” values of Dataverse rows, part 1: classic workflows
Sometimes you hit a wall when working with Power Automate: the desired functionality cannot be implemented. Or the implementation becomes such a mess that it doesn’t make any sense.
This happens, for example, when the workflow needs to know the contents of a Dataverse record before the update or delete event.
When the flow is triggered by an update, it only contains the updated values of the record.
When the flow is triggered by delete, we only get the identifier of the deleted record.
Most of the time this is enough. But what if it’s not?
Let’s dig into classic workflows; A familiar and safe tool for Dynamics 365 consultants, but for the rest of us… they are something new and mysterious. Compared to Power Automate cloud flows, they have some capabilities that aren’t available in the modern experience.
Later on, in part 2, we’ll explore even more Dataverse features that originate from the Dynamics CRM era.
Example – Employees and their devices
Let’s create an application to manage employees and their devices in Dataverse tables, using a model-driven Power App.
We store only the name of the employee.
For the devices, we store the name and the employee to whom the device belongs (Device owner).
The Employee form with devices looks like the following:
We want to know in real-time who is possessing which device. We are for example updating changes about the employees’ devices into another system, and it should happen immediately.
Now, it is important to understand how flows can be triggered. When a new device is added for Timo on the screen above, the action does not cause update event on employee record (there is a lookup from the device to the employee).
So, we cannot track (with flow) the employee row changes. We have to monitor changes made to the device rows.
We end up writing the following information in our own change log table (Device change log) when the owner of the device changes:
We can then use flow to react to the new records created in the change log table and take the necessary actions. Whatever they are.
We need to react to these events:
- Creating a new device. Who was it added to?
- Updating the owner of the device. Who was it added to?
- Updating the owner of the device. Who was it taken from?
- Removing the device. Who owned it?
Let’s start with the easy ones. The situations that can be handled with the flow.
Adding a new device / transferring a device to a new person
When a new device is added or an existing one is modified, a line is written to the log table. The record name will be the event type (triggerBody()?[‘SdkMessage’]).
We are only interested in changes where the owner of the device has changed and where the device has an owner after the change.
So we set the flow to trigger only when device owner column is edited:
We don’t care about the events where the owner information has been deleted. We can rule out these situations with the following trigger condition.
@not(empty(triggerOutputs()?['body/_tp_deviceowner_value']))
Let’s create a new device for Jukka (PS3) and transfer the existing one (Wii Sports) from Timo to Jukka.
The change information is stored in the log table.
All good so far! Next we’ll proceed to the unknown territory.
Who owned the device before?
How do we find out who was device owner before the the change?
With a real-time classic Dataverse workflow. These have the ability to run either before or after the platform data operation. For more details, see documentation on Initiating real-time workflows before or after status changes.
Creating a classic workflow
Create a classic workflow (Automation -> Process -> Workflow):
Give the flow a name and define the table triggering it. In order to access the data before the change, the workflow must be defined to start in real-time (real-time workflow).
So uncheck ‘Run Workflow’ in the background.
We are teleported to the 80’s. The user interface is called a ‘classic view’ and it’s very different from the Power Apps or Power Automate maker portals.
Classic workflow – Trigger conditions
First change the workflow to start when any user edits the device. Use Organization as a scope.
Change the workflow to trigger when the record is edited (Record fields change). We define (with the select button) which fields of the record must be modified in order to start the workflow.
We are only interested in the device owner’s changes, so we select that one.
Classic workflow – before or after?
And then the most important part: Change the workflow to start before the change instead of after the change. This way we can access all the information before the actual update.
We have now defined how the workflow triggers. But what does the triggered workflow do?
Classic workflow – Actions
The actions performed by the workflow are defined using steps. There are many options, but this time we will settle for adding a new row to the dataverse (Create record).
Give a name for the step (Create device change log row) and select the table where the new row will be created (Device change log).
A new dialog opens from the Set Properties button. Here you can define what is actually inserted in the selected table.
Give value for the name column (Update – Before). Everything else we can leave blank.
We want create a lookup to the device edited by the user (Device column). It is done like this:
The value {Device(Device)} appears in the Device field. That means value of the Device field on Device table row.
Next we save the owner of the device before the change (Employee). This is done in the same way.
You can easily extract any information about the subject of the update, as well as related records, with the help of the Form Assistant.
But in this case we are happy with this amount of information.
Save and Close.
And Save and Close.
Finally, we activate the workflow (Turn on).
Does this work? Change the device (Wii Sports) owner from Jukka to Timo.
And yes, our log has information both from whom the device has been transferred (Update – Before) and to whom it has been transferred (Update).
Removing the device
The workflow reacting to device deletion is done in a similar way.
But now we cannot fill the Device column, because that would be a lookup to the device that is being deleted. In that case removing the device would give the end user a generic SQL error.
We add the name of the removed device to the Name column so it easier to understand what has happened.
Testing time. We delete Wii Sports Timo is owning currently.
Row is immediately created in our log table!
Summary
Power Platform actually has a (low-code) way to find out the column values of an updated or deleted row before the event. But it’s a bit confusing to work with these two different UI. New and old. Modern and classic. I hope these features appear in the modern UI in the future.
Instead of storing the historical data into a custom log table, there is also another way to implement this change tracking. Read the part 2 in this series to learn how custom actions can be leveraged.