Lookup Columns
– In this post I’ll show how to use Power Automate to access lookup columns and change multiple child records in Dataverse based on the parent table’s record value
Boxes & Files
In my example, I have a number of physical boxes in storage containing one or more files. This simple structure is represented in Dataverse by two tables called Box and File. Box has a one-to-many relationship to File
Each box has an expiry date after which its contents are destroyed and I want the status of each of the files to be updated by Power Automate to reflect this
Creating the Flow
I will create a flow that identifies all the boxes that have an expiry date in the past, and put my name in the ‘Checked Out’ column in the child Files records to signify that I am person who has removed the files
This sounds like it might be complex, but with Power Automate it isn’t. Here is how to do it
Step 1
Use a Dataverse ‘List rows’ action to create an array containing each box that has an expiry date in the past
Here is the query. sits_triggerdate is the logical name of the Dataverse column in the Box table containing the expiry date, and ‘lt’ represents ‘less than’ so we are getting only those records where the expiry date is in the past
Step 2
Use an ‘Apply to each’ containing another ‘List rows’ action to go through each item in the box array and create another array containing all the files from all the boxes
To do this we have to filter the File table against each of the GUIDs in the box array. Filtering a lookup column requires the column name (in this case ‘sits_parentbox’) to be prefixed by ‘_’ and suffixed by ‘_value’. eq represents equals and Box is the GUID of the parent record in the Box table taken from the preceding ‘List rows’ action
Step 3
Finally use a second ‘Apply to each’ action within the first ‘Apply to each’ action, to go through each of the records in the files array and put my name against the ‘Checked Out By’ column
This is done with the ‘Update a row’ action
Summary
And that it, all the files contained within boxes that have an expiry date in the past will have their records updated. Here is a summary of the overall flow with the action names updated to indicate the respective table
There’s nothing overly complex in this flow, you just have to remember the logical lookup column name must be prefixed by ‘_’ and suffixed by ‘_value’
Want to know more about Power Automate? Here are some of my other posts: Power Automate