Below is how I would likely build the flow to get what you want. Note that it doesn't use an Apply to each which makes it much more efficient, especially if you have a lot of records to process.
I've based it on the following assumptions:
- Title field used for the User in the SharePoint List (Single line of text)
- Additional Specialty field is Single line of text
- There is only ever a single Additional Contact for each Specialty
Below is the SharePoint List used for this example.
Below is the full flow. I'll go into each of the actions.
Contacts is the array variable called varContacts that contains the initial contacts.
[
{
"ContactName": "User A",
"Specialty": "Painting"
},
{
"ContactName": "User B",
"Specialty": "Sculpting"
},
{
"ContactName": "User C",
"Specialty": "Writing"
}
]
Get items retrieves the additional contacts. Note that the Get items action will only return the first 100 items by default. If you have more than this you would need to change the Top Count to accommodate. I've set mine to 5000.
XML is a Compose that converts the items returned from Get items to XML so we can use XPath expressions to retrieve the matching data without having to use an Apply to each. The expression used is:
xml(json(concat('{"root": { value:', outputs('Get_items')?['body/value'], '}}')))
Select uses the varContacts data and the following expressions to build up the object array. Note that this will list all the Contacts regardless of whether they have an additional contact. If they don't have an additional contact, then AdditionalContactName will have an empty value.
//ContactName
item()?['ContactName']
//Specialty
item()?['Specialty']
//AdditionalContactName
xpath(xml(outputs('XML')), concat('string(//root/value[AdditionalSpecialty=''', item()['Specialty'], ''']/Title/text())'))
This would give you the following output.
[
{
"ContactName": "User A",
"Specialty": "Painting",
"AdditionalContactName": ""
},
{
"ContactName": "User B",
"Specialty": "Sculpting",
"AdditionalContactName": "User 3"
},
{
"ContactName": "User C",
"Specialty": "Writing",
"AdditionalContactName": "User 1"
}
]
If you then wanted to filter out the contacts that don't have an additional contact, you can use a Filter array.
Filter array uses the output from the Select and the following expression.
item()?['AdditionalContactName']
This will give you the final output.
[
{
"ContactName": "User B",
"Specialty": "Sculpting",
"AdditionalContactName": "User 3"
},
{
"ContactName": "User C",
"Specialty": "Writing",
"AdditionalContactName": "User 1"
}
]
