Create the lookup inside the same Sharepoint List from Poweer Automate

I am trying to create a Power Automate flow that imports from Office 365 Users all my users to a SharepointList, in order to build the structure for Plumsail Org Chart.
I am trying to replicate the data of the example, however I am having problems with the manager, as it is a lookup inside the same list I see the field in Power Automate is difficult to fullfill, I attach some screenshots of my try ons.

I would advise you to split the process into two steps:

  1. import all users without specifying managers,
  2. set managers to the imported items.
    For the first step, use the approach described in this article. All UPNs should be saved in a separate column. Call it "UPN" and require unique values in the column settings.
    For the second one, I would advise the following.
    Get all items in the Org Chart list:

    Use the "UPN" column to get a manger for the current item in Microsoft 365 profiles:

    Use the "Get items" action with OData query to find the manager in the list by the "UPN" column:

    It will return an array with just one item if the UPN values are unique. Then, use the data of the current item to update it and the ID of the manager to set it:

    Please let me know whether it works for you.

Thank you , im having an error, because not all users have manager, so I am having this

because only some users have the manager on office 365 users. thank you

If the current user does not have a manager, it fails only the current iteration, the next one will be successful:

Thus, if the user does not have a manger, then there is no information for importing and the flow will skip the person and leave it without the manger in the list either.

it is strange beacuase it does not create the managers at all, I see my flow is exactly the same.

thanks again!

I tried it with other little list and the result is the same, when we find this error no manager is written

I see in the outputs it is writing the same manager that finds in the List, it means when it is empty it writes that one, because it put one by hand and it writes that one as well, it is not taking it from a match from office 365, there is something to modify.

Just to illustrate the previous reply

OMG my mistake, it was ID of the second get items instead of manager ID, thank you again and again!!

1 Like

Thank you for continuing researching it and the update in the thread! I am glad that the issue is resolved.

Thanks to you, but I have some problem now.. I cannot figure out this, When run again the flow it creates now items, how can I do to only create new ones and if it exist just to update them, and if it is not anymore in office 365 users, to eliminate them.
Thank you

As I understand, you mean the flow that synchronise user profiles with the list. The latter should have the "UPN" column filled.

  1. Retrieve all the items from the Org Chart list. Append the "UPN" values to an array variable:

  2. Then retrieve the user profiles:

  3. Create a condition and check whether the array contains the current UPN from the user profiles (it will be automatically added to the "Apply to each" block):

  4. If "Yes" - do nothing or update the existing item with the data from the current user profile. If "No" - create a new list item.

Hello, thanks for the answer, I could got all , however in the part of the update I think Im making something wrong as the row is updated but with the same name over and over again. I add screenshots.


Hello! Do you use the approach suggested in my last reply? It was about creation new items in the list if the current user is not presented there. According to you last screenshots, you updated list items and I do not know the context. Please share screenshots of the whole flow with all actions expanded. If required, please clarify the data source for certain actions (if it is not obvious).

Hello I attach all the way from top to bottom

Issue 1 - UPN vs e-mail
To the UPN array you appended the e-mail addresses. The UPN may not match the e-mail address and that may lead to errors in updating the list. Also, in the condition, you also need to use the UPN (User Principal Name).

This action is supposed to receive a UPN so I am not sure how it works with the "User ID" property.

Issue 2 - Senseless iteration

The "Get user profile" iteration is senseless. I mean that it will retrieve a profile for each of the found users but will do nothing with the retrieved data. If you use the output of the "Get user profile (V2)" action further, it will pass only the values of the last retrieved profile. And most likely, this step caused your issue with the same name for all items.

Also, I do not see any sense in this "Apply to each" step (you named it "Get user profile"). The users search should return you an array of profiles that you can use further. But in your repeating block you retrieve a profile for each profile that you already have.

The same is true for the "Apply to each Photo" iteration. If you use the output of the "Get user photo (V2)" action further, it will pass only the last retrieved value.

To solve this issue, you need to do all the actions you need within the iteration using the output of each retrieved item as you wish.


Thus, these actions will use only the last output from the iterations above:


The user search already retrieves profiles with their properties so I would advise you to delete this step:

Then, move your condition into the repeatable block retrieving the photo:

You create the photo file anyway so it has no sense to put it into the condition branches. Instead, create a photo file right after retrieving it from the user profile:

Then check whether the array variable contains the current UPN as I advises:


And update or create a new list item depending on the condition result.

Thank you! Btw I cannot see a UPN variable in the Sharepoint variable list. Maybe it is the one called ID? Or I need to extract it? Thanks again!

I meant that you should create a custom single-line text column in the SharePoint list and put there the UPN values when you create items in the flow

I see, so i create the UPN pulling the UPN from the Office 365 Users connector, I did replace the variables, however the same is happening, when updates, it replaces the values many times with the same user each time, I show you the enterire new path:

Ieinso, I advised you to remove this block:


And put the condition with all further action into this one: