How to get "update item" flow to work with null values as well as populated ones?

Question

I want my flow to update two text fields in my list to be empty if the user modifies a lookup field to make it empty.

In my first list (Address Change) list I have a lookup field (Approved School) that references a list of schools and their email addresses in a second list (School Email Address). When a user edits an item in Address Change and modifies the Approved School field value, a flow runs to update two other fields in Address Change:

1. Populate ApprovedSchoolEmailH (text field) with the email address related to that school (from the School Email Address list)
2. Populate ApprovedSchoolComp (text field) with the name of the school from the lookup field (Approved School) so I can use that as a reference for other calculated fields (I've also tried using the name of the school from the Address Change list, with no different results)

This works as expected if I change Approved School from NULL to having a value. It also works if I change it from one value to another different value. But it doesn't work when I remove the value from Approved School (choose "None" in the lookup). The flow runs, but it doesn't overwrite the existing values in ApprovedSchoolEmailH or ApprovedSchoolComp with NULL values.

I've even tried adding an entry in my School Email Address list with null values for both the SchoolName and School Email Address fields, hoping that I could still retrieve an empty SchoolName value and School Email value if High Schools is empty.

Here's the flow

First, when an item is modified in Address Change I retrieve that item:

Then I go to the School Email Address list to find the related email address:

Then, for each school email address (only one will be returned), I update ApprovedSchoolComp and ApprovedSchoolEmailH in my Address Change list to match the school that was chosen:

Here's the flow in action.

I select a new value for Approved School in Address Change:

The flow runs as expected, and updates the ApprovedSchoolComp and ApprovedSchoolEmailH fields accordingly in my Address Change list:

But if I change the value of Approved School to not have any school selected:

My ApprovedSchoolComp and ApprovedSchoolEmailH fields don't change:

The flow runs, seems to do the "Get Item" without any problems, but when it needs to run the Get Items to pull the email address related to the selected school (in this case, empty), it doesn't come back with anything. In the Get Items output, it shows the body value as a set of square brackets:

And though the Apply to Each action has a checkmark indicating that it ran without an issue, it didn't do the update item step:

How can I get the flow to insert NULL values into ApprovedSchoolEmailH and ApprovedSchoolComp when the user changes the value of Approved School to be empty?

Answer 1

Not sure this is the most elegant solution, but I've put it in place and it's working.

When the lookup field (Approved School) was changed to empty/null, I could not get it to initiate the Update Item action. To compensate, I added a condition after the trigger to determine if the Approved School field is populated or not, and then act accordingly:

1. If Approved School is populated, use Get Items to retrieve the corresponding School Email address and then use Update Item to write the email address to ApprovedSchoolEmailH and to write the Approved School name to ApprovedSchoolComp, similar to what was shown in my question
2. If Approved School is not populated, skip the Get Items step (since there is no email address to retrieve) and just use Update Item to populate ApprovedSchoolEmailH and ApprovedSchoolComp both with NULL values

I also realized that in my question, my second action (Get Item) isn't needed - I can retrieve the information from the initial When an Item is Created or Modified action, so I don't need to follow it up with Get Item.

This is what I ended up with:

The trigger is the same as before, and in the condition I use the length function to determine if the Approved School field is populated:

length(string(triggerOutputs()?['body/ApprovedSchool/Value']))

If it is populated (length is > 1), use Get Items for the school email address, then Update Item to write in the new values

If it is not populated, use Update Item to make those fields NULL

If you do this, you'll likely get warnings from the Flow Checker like I do:

Power Automate is warning me that I may be creating the dreaded perpetual loop: my flow is triggered when an item is modified, and as a result it makes a modification which could then trigger the flow to start again. To manage this, I've just added two trigger conditions to my initial When an Item is Created or Modified trigger.

The first trigger checks to see if the value the user has just defined for Approved School matches the value that's currently in ApprovedShoolComp (which is the reason the ApprovedSchoolComp field exists). If they haven't changed it, then I don't want to make an update. I could have used a Get Changes action to see what's changed, but this way I can check it before the flow runs:

@not(equals(triggerOutputs()?['body/ApprovedSchool/Value'],triggerOutputs()?['body/ApprovedSchoolComp']))

The second condition checks to see if the user who last modified the item is the service account used to run the flow. If the most recent update was made by a flow, then I don't want to trigger this flow again... I only want it to run if someone else made the change:

@not(equals(triggerBody()?['Editor']?['DisplayName'], 'Automation NoReply'))

The flow seems to be quite efficient - in my initial testing, it takes about a second if there is a value for Approved School (to do the test, retrieve the school email address, and then update the record). If Approved School is empty, then it's much quicker: