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

Power automate banner

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

power automate select rows

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

Power automate apply to each 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

Power automate add a second apply to each action


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

power automate flow overview

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

Leave a Comment

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

Scroll to Top