I need to sync an Azure groups members with a SharePoint List, it must have logic to not create an item in the SharePoint list if the member already exists in the list.
It should also be able delete a list item from the list if a user has been removed from the Azure group.
My current flow works when testing, the items are created but it throws errors because of unique columns such as email and username.
Solved! Go to Solution.
@JazzyN20 yea for final solution when list will have 1000 items, you should put a filter query o Group ID
So first you will need to identify the users from AD group which are not added to SharePoint list, to do that follow below steps:
1. Add select action after Get Items, and configure it as below screenshot, for From parameter select value (list of items) from Get Items dynamic content and for Map parameter, click on little icon on right of the input box, instead of Title column you can use the column which stores email of user in the list
2. Again add select action, and configure it as below screenshot, for From parameter select Group Members from dynamic content and for Map parameter, click on little icon on right of the input box and select Group Member Mail
3. Now add filter array action and select the second Select (group members) output in the From parameter, then in left side of value use join(body('Select'), ';') expression, select does not contain from dropdown and on right side of value user item() expression as shown in below screenshot
Now you will have array of list of users (email) which are added to AD group but not added to SharePoint list
to add the remaining users to the SharePoint list configure apply to each and create item action as below screenshot:
@JazzyN20 i think you need to add Get items action with filter query to first check if user is already added to the list or not.
Thanks @annajhaveri but I am already playing around with the get items action, it is more the question of how to get it to all work
@JazzyN20 can you share structure of your SP list (columns and column type). Do you have filter query on Get Items? Also if you can share your existing flow screenshot i can advise how you can take it further?
I have no filter yet, the list is small so no need to filter but the final solution will have over 1000 items.
I have added a column to my contacts list named 'GroupID', this is for the object ID of the user in the O365 group can be added so I have a shared record in common to perform the conditional check. It is the condition logic that I am struggling with. Thanks
@JazzyN20 yea for final solution when list will have 1000 items, you should put a filter query o Group ID
So first you will need to identify the users from AD group which are not added to SharePoint list, to do that follow below steps:
1. Add select action after Get Items, and configure it as below screenshot, for From parameter select value (list of items) from Get Items dynamic content and for Map parameter, click on little icon on right of the input box, instead of Title column you can use the column which stores email of user in the list
2. Again add select action, and configure it as below screenshot, for From parameter select Group Members from dynamic content and for Map parameter, click on little icon on right of the input box and select Group Member Mail
3. Now add filter array action and select the second Select (group members) output in the From parameter, then in left side of value use join(body('Select'), ';') expression, select does not contain from dropdown and on right side of value user item() expression as shown in below screenshot
Now you will have array of list of users (email) which are added to AD group but not added to SharePoint list
to add the remaining users to the SharePoint list configure apply to each and create item action as below screenshot:
Sorry for the silence, been busy but I will have a look this week and come back to you
Hi Anna
I do not see this final option...
So i typed in the expression 'item()' and hit save. It then populated the filter array value as per you instruction but i know receive this Flow error for the first filter array value you specified of join(body('Select'), ';')...
Just type in item() in the expression and click on Ok button as shown below
Hi Anna
I have this working now, thank you.
I do have an issue with the final 'Create Item' action . When using the 'Current Item' value, this only inserts the Email Address from the AD group into my SharePoint List.
The other fields such as 'Contact Name' cannot be accessed. If I use the 'Current Item' value then it inserts the email address value. How do I map these over too?
I tried simply inputting the AD Group Action get values but this then inserts another Apply to Each branch inside the existing one, when the flow is run it tries to create all of the items - one run for each and every value passed from the array!
@JazzyN20 what is the data type of the Contact Name and the Email address fields, what data you want to save into these fields?
Anna, I have figured it out now, thanks
Follow-up on how you achieved the mapping of the other AAD columns to the SP list would be helpful. 🙂 This example focus on one item.
User | Count |
---|---|
89 | |
41 | |
22 | |
20 | |
16 |
User | Count |
---|---|
138 | |
56 | |
47 | |
36 | |
26 |