Summary
SharePoint lists can contain lookup columns, which allow selecting a value from a different list (similar to a foreign key). Lookup columns can be configured to allow selecting multiple values. Using multi-select lookup columns in Power Automate can be challenging because the "Get Item" action retrieves the values as an object array (in JSON). This article explains how to check if an array of objects - in this case from a SharePoint multi-select lookup column - contains a specific value, and what the data looks like inside Power Automate.
If you are looking for a quick and simple solution for lists with under 5,000 rows of data, see this article instead.
Steps
Get a specific item
- In Power Automate, create a flow that gets a specific list item.
- In this example, the flow uses a "Manual trigger" and a "Get item" action.
- This example uses a TrackMaster list called People, which contains records of persons, with a multi-value lookup field called "Group Membership" which allows selecting from multiple groups to which the person belongs. The groups are defined in a "Group List" list and contain a unique Group Name column, which is used as the lookup value in the Group Membership column of the People list. For simplicity, the example retrieves People item #721, the author's record.
Use a formula to convert array to XML, then use XPath to find the element
- Using either an Initialize Variable (Boolean) or Condition action, use an XPath Boolean formula to find the element in the array.
- The formula works as follows:
- The multi-value lookup field is an array with the following JSON representation:
[
{
"@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
"Id": 2,
"Value": "MDEN"
},
{
"@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
"Id": 3,
"Value": "MCN"
},
{
"@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
"Id": 5,
"Value": "MeTRIC"
}
]
- Each array element is an object containing a numeric ID (the built-in identifier of the group list item, which you can find in the URL when editing the item), and a Value (the label shown in the list, which in this case is the item's Title field since that is the field being retrieved in the lookup column).
- Since the Power Automate contains() function is not able to look at object arrays, unless one manually constructs all object elements or searches for a specific string, the array must first be converted to XML in order to use the xpath() function to find the element. However, the xml() function requires a single text node with no other elements as the root element, so it will not work with an array as the root.
- The first step in the formula is to convert the array to a string, so that a single root element can be appended at the beginning. Underneath this root element, an additional element called Items is needed to contain the array (since the root element itself cannot contain arrays nor attributes).
concat(
'{"Root": {"Items": ',
string(outputs('Get_item')?['body/Group_x0020_Membership']),
'} }'
)
- This produces a JSON string that looks like:
{"Root":
{"Items": [ ... array ... ]}
}
- The next step is to convert this back to JSON, and then pass the resulting object to the xml() function.
xml(
json(
concat('{"Root": {"Items": ', string(outputs('Get_item')?['body/Group_x0020_Membership']), '} }')
)
)
- This produces XML that looks like the following:
<Root>
<Items odata.type="#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference"><Id>2</Id><Value>MDEN</Value></Items>
<Items>...</Items>
</Root>
- Now that the array is in valid XML form, use xpath() to search for either the Id or Value, and return true/false when found/not found. Since Id and Value are child nodes of Items, the format /Root/Items[Id or Value = ...] can be used. Note that the boolean() function is used, which is an XPath function, not a Power Automate function.
xpath(
... xml() from previous step ... ,
concat('boolean(/Root/Items[Id=', string(... value here ...), '])')
)
- Note that this XPath expression is looking for the existence of a specific element by using /Root/Items[Id=xxx]. To search by value, use /Root/Items[Value=xxx].
- The resulting formula is as follows (in this example, a numeric ID of 2 is being passed, but this could be passed from a variable containing an Id or Value):
xpath( xml(json(concat('{"Root": {"Items": ', string(outputs('Get_item')?['body/Group_x0020_Membership']), '} }'))) , concat('boolean(/Root/Items[Id=', string(2), '])'))
- The above example returns True because the Person list item for "Gabriel Mongefranco" with Id 721, does in fact contain the Group list item for MDEN with Id=2 in the "Group Membership" lookup column.
Other approaches
- A very simple alternative is to access the /Id or /Title properties of the lookup fields directly inside a Query Filter in a Get Items action. This approach works fine for smaller lists but there can be accuracy and performance implications in lists that exceed 5,000 items.
- Another alternative is to convert the array to a string, and then use contains() to search for a specific string value. When searching for a string Value, this approach would not run into issues unless the Value contains special characters. When searching for a numeric Id, search for the string around the number as well such as "Id":<space>3<comma> to ensure it does not accidentally find a 3 in 33 or other similar situations.
- Another approach is a two-step process. First, use a Filter Array action, passing the lookup field's body and searching searching under item()?['Id'] or item()?['Value']. Next, use the output to check if the resulting array is of size 1, which means the element was found.
- There are other approaches that may require additional actions. However, the primary approach documented here has the advantage that it can be used directly in a Condition action without requiring extra steps.
Notes
Resources
About the Author
 |
Gabriel Mongefranco is a Mobile Data Architect at the University of Michigan Eisenberg Family Depression Center. Gabriel has over a decade of experience in data analytics, dashboard design, automation, back end software development, database design, middleware and API architecture, and technical writing.
| |
|