Compare list item values to SharePoint Group and update item accordingly

[BenVdP]

Hi,

I need help with building my Flow as per following requirements; I have a SharePoint list which acts as an inventory of created sites and some metadata about it such as Site Title, Site Owners, and some other 

SharePoint site inventory (on site A) - contains about 5 000 items

Site Title	Site Owners (Multi Person field)	Total Site Owners	Site Owner valid
Site ABC	Person X	1	Yes/No
Site XYZ	Person X; Person Y	2	Yes/No
Site KLM	Person A; Person E	2	Yes/No

Then there is a SharePoint group 'Valid Site Owners' on site B. This group holds all employees who have completed a training and are thus seen as legitimate Site Owners.

I am now trying to build following Flow that:

1. iterates to all sites in the list 'Site Inventory' and see if the listed Site Owners are valid.
2. For sites with multiple Site Owners listed following logic is applied:
   1. All Site Owners are valid -> update the field 'Site Owner valid' to 'Yes'
   2. As soon as one Site Owner is invalid -> update the field 'Site Owner valid' to 'No'

I have no clue how to do this, so all help is welcome.

[Expiscornovus]

Hi @BenVdP,

I believe this would be possible. 

First of all you could use the Site Owners item value from step 1 to retrieve the user ID from the User Information List. Pieter Veenstra has a nice blog about this approach: https://sharepains.com/2020/08/07/people-field-get-items-power-automate/

For step 2 you could use a condition and check if the site owner is valid. If the site owner is valid you can update the list item to yes. 

Btw, as far as I am aware you can't terminate a flow within a condition. So you probably want to use another choice field on the Site Inventory list to set an overall status of your validation process (e.g. new, in progress, stopped). So you can easily add that to your condition from step 2.

Let me know if this helps or if you need more help from us.

[BenVdP]

Hi @Expiscornovus ,

Thanks for the help, in the meanwhile I already have these steps in place but am now stuck in verifying if the Site Owners for a given site are trained.

Where the action "Store all PrincipalNames in array" results in all principal names of the valid site owners (site B).

How do I now check if the current site collection has valid owners?

[Expiscornovus · Accepted solution]

Hi @BenVdP,

Sorry I misread. I thought you were querying a SharePoint list, not a Group. 

Are you by any chance storing your site owners from site A also in an array with UPNs? If that's the case, you could use an intersection to cross reference/compare the two arrays with each other via an intersection function:

intersection(variables('SiteOwnersArray'),variables('TrainedUsersArray'))

https://docs.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#i...

After that you can use the count to compare it to the original count of items. If that has a mismatch you will have users which aren't trained.

[BenVdP]

@Expiscornovus , thanks that did the trick.

Initially, first had to do some rework on retrieving the site collections. I now collect them using an SharePoint HTTP request instead of the 'Get items' actions.

Thanks again!