Lookup Person Field in Power Automate
By Anatoly Mironov
Hello Citizen Developers!
I get more and more questions about Power Automate.
One of the recent questions is about how to look up a person field from SharePoint using Power Automate.
I’ll give you a bit of the background in a second. Power Automate is really awesome for citizen developers.
There are a lot of triggers and actions that are easy to understand and use, such as send email, create a list item in Sharepoint etc.
But there are also techier, nerdier actions that are less citizen developer friendly. One of them is Send an HTTP Request to SharePoint. It might be the answer to many challenges, intimidating at first, but powerful and, hopefully, not so complicated once you’ve tried it.
This post is about this very action and how you can use it to simplify your flow.
Background
Let’s imagine this scenario. (Actually it is a bit simplified - with only these fields, the aim is to make this blog post easier to follow). There are two lists in SharePoint, Responsible and Reports. Reports need the “Responsible” field from the first list.
If you enter Reports manually, you can rely on lookup fields or check the responsible persons manually.
But in our case we also have a Forms form that we distribute to a broader audience, so we need something smart.
The Lookup problem
Whenever a form is submitted, we create a new entry in the Reports List using Power Automate. Based on what type of report, we want to fill in the right Responsible (by looking up it in the Responsible list).
A very simple flow just creates a list item in the Reports list whenever the form is submitted.
As expected, the Responsible is not filled in, we’ll take care of it in the next step.
The solution
As already announced, the solution is called Send an HTTP Request to SharePoint. With this we can make an extra call, and get the right Responsible and then paste it into the creation step. Now, calling the SharePoint API is not a straightforward action, but it is not as complicated as it seems. By trying this example, you should be able to create your simple calls.
First let’s add the new action: Send an HTTP Request to SharePoint. You can see that there is a hint on how you can form the endpoint.
Paste the following endpoint:
_api/web/Lists/GetByTitle('Responsible')/Items?$top=1&$filter=Title eq 'XXXXXXXXXXXX'&$expand=Responsible&$select=Responsible/EMail
Where XXXXXXXXXXXX is the answer of “What type of report” from the form.
This will make a call to the Responsible list and check who the responsible is for the report type (a variable from the submitted form). We need the EMail. Please note, all the names in the http call are case sensitive.
Also, add the following header to avoid a wordier result (= skip metadata).
Accept:application/json; odata=nometadata
You can see my complete action block here:
Reading the Email Address
Now comes another tricky part, this time not complicated though.
In order to use the Email we got, we have to parse it (Parse JSON). But before we can parse we need to have a data sample. So let’s trigger the flow by filling in the form.
Once it runs, we now can copy the result and use it to create another step. So open the recent run and copy the outputs of the Send an HTTP Request to SharePoint action:
Next edit the flow again and add a new action: Parse JSON. Choose the body as Content.
Generate schema by pasting in the sample result from the previous run.
Once you have parsed JSON, you can use the Email as a variable in the Responsible Claims in the Create List Item step.
End Result
Fill in the form again and you’ll get the right Responsible filled in automatically. A smart lookup with Power Automate.
That’s it. If you want to ask something or comment it, please share it on LinkedIn or Twitter and mention me (@mirontoli).
BTW, it could be simplified even further by storing e-mail addresses in text fields (rather than person fields). But Person fields are better, they look nice, especially the popup cards with contact information and profile pictures.