February 11, 2021

Dynamically storing a value in a multi-valued lookup column in a SharePoint List/Library using Power Automate (MS Flow)


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.


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).


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



Single Line of Text

City names will be stored


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



Single Line of Text

Order details will be stored



 All the states of the City list will be there in the choices option



Multi-Lookup field with Title column of City list



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.

Step 13: Once the flow is executed, we can see all city of selected state populated in the City field.


This is how we can dynamically store value in a multi-valued lookup column using Power Automate (MS Flow). Hope this helps!

If you have any questions you can reach out our SharePoint Consulting team here.

No comments:

Post a Comment