SharePoint Consulting Blog

Introduction

We implemented the automated approval process in SharePoint Online using Power Automate (MS Flow) for a Postal and Parcel company based out of Melbourne, Victoria, Australia. We came across a scenario where we needed to store/update values in a multi-valued lookup column in the SharePoint list. We are familiar with storing values for lookup columns in a SharePoint list/library using SPFx/JSOM/CSOM, but it's a bit tricky when it comes to updating a field using Power Automate. So, in this blog, we will learn how to dynamically store a value in a multi-valued lookup column in a SharePoint list/library using Power Automate.

Scenario

Here, we will be using two lists as we'll be storing values in a multi-valued lookup column based on the selection of another column and also fetching the values for that selected column from another list. For this, we will be assigning an array value of another list to this lookup column using Power Automate (MS Flow).

Approach

To store a value in a lookup column, we will associate the ID of an item with this lookup column. For easier understanding, we are going with the example lists here, below are the two lists which we will be using:

List Name: City

This is the Primary list, from where we will fetch the values and store them in the secondary list’s lookup column.

Column Name	Column Type	Comments
Title	Single Line of Text	City names will be stored
State	Single Line of Text	State associated with the city will be stored

List Name: Orders

This is the Secondary list, where we will store the multiple values from the Primary list to the lookup column “City”.

Column Name	Column Type	Comments
Title	Single Line of Text	Order details will be stored
State	Choice	All the states of the City list will be there in the choices option
City	Lookup	Multi-Lookup field with Title column of City list

Process

When a new item is created in the Orders list, the user will select a State in that item. The flow will be triggered when an item is created and we will find all the cities of that selected state from the City list and will store those cities in the City lookup column of the Orders list.

Follow the below steps to store value in multi-valued lookup column:

Step 1: Create a Flow for "When an item is created" and add an action for Initialize Variable. Provide the variable name "AllCities" and select Array in Type.

Step 2: Add an action for "Get items". We will get all the cities for the selected state using this action.

Step 3: Select site and list In "Get items" action and also add "filter query" to get cities of selected state only.

Step 4: Now add Apply to each action. We need to iterate through each returned items from "Get items" action and append IDs in the array variable.

Step 5: In Apply to each action, select the value of "Get items" action from Dynamic content as shown below.

Step 6: Now in Apply to each, add "Append to array variable" action.

Step 7: In the Name field, select the array variable "AllCities" which we initialized at the top. And in Value pass the JSON for ID as below format and select expression int(items('Apply_on_each')?['ID']) in a value of id.

{

“id”: int(items('Apply_to_each')?['ID'])

}

Step 8: Now add a new step for the Update item. Select site address and list name. Now in the Id field, select the ID from Dynamic content as shown below:

Step 9: Here Title is a required field, so we need to select the value of Title field also.

Step 10: Now In a City field, click on Switch to input array icon as shown below:

Step 11: Once we click on it, it will show the text box. Now select the array variable "AllCities" from Dynamic content as shown below:

Step 12: Save your flow and add a new item to the Orders list. While creating an item, we are not selecting the city (it's hidden from the form), we are only selecting the State.