Forum Discussion
If list multi-select choice column contains certain value, auto-populate flag column in related list
I have a SharePoint list called List1 with columns ID (single-line text) and Type (multi-select choice):
ID Type
---- ------
ID1 Apple
ID2 Apple, Pear
ID3 Pear, Banana, Apple
ID4 Pear
ID5
And a second list called List2 with columns ID (a Lookup column) and Has_Pear (yes/no).
I want to automatically populate Has_Pear with "Yes" if the related row in List1 has "Pear" in the Type column.
Like this:
ID Has_Pear
---- ------
ID1 No
ID2 Yes
ID3 Yes
ID4 Yes
ID5 No
How can I automatically populate the Has_Pear column?
3 Replies
Hi,
There are two simple ways to bring the values into the second list:
Option 1: Create a calculated column in the first list that returns a text value based on the selected choice. Then, include this calculated column in your lookup to the second list. (Calculated columns must be created from the list settings.)
Option 2: Add an autofill text column in the first list using a prompt such as: "Return 'Yes' if the value contains 'Pear'." Since lookup columns can also bring text columns, this value can then be retrieved in the second list along with your lookup.
Both approaches allow you to display the resulting text value in the second list.
Option 1 is a smart trick 😎, but since the source Type column is a Choice column with multiple selections enabled, a SharePoint calculated column cannot read those values. Therefore, this option will not work.
Option 2: I'm not sure how that would be achieved out of the box. SharePoint doesn't automatically populate a text column based on a prompt. You would likely need Power Automate (or custom form logic) to check whether the multi-select Choice field contains a specific value, such as "Pear", and then update a text column with "Yes" or "No". That text column could then be included in the lookup.
You can do this with a simple Power Automate flow.
When an item is created or updated in List1, check whether the Type column contains "Pear". If it does, update the related item in List2 and set Has_Pear = Yes. If not, set it to No.
Since the value (lookup)is coming from another list, a calculated column won't work here. A Power Automate flow is the simplest and most reliable solution.
