cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Look up an approver in a 2nd SharePoint list

Hello PowerAddicts,

 

I have a SharePoint list form that asks a user to select a category. The category is a lookup column to a 2nd SharePoint list. The 2nd SharePoint list contains the category names (title) and one username per category (the approver). The categories are all unique values. I am doing this so an end user can easily edit the category approver when needed without editing the Flow. 

 

A screenshot of my attempt at this is below. It results in this error. I can see from the error that the correct category and user were identified. Any ideas? TYIA!

{
"error": {
"code": "GraphLookUpRequestFailedError",
"message": "User lookup request to Graph failed lookup for user identifier: '{\"@odata.etag\":\"\\\"1\\\"\",\"ItemInternalId\":\"1\",\"ID\":1,\"Title\":\"Records Management\",\"Person\":{\"@odata.type\":\"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedUser\",\"Claims\":\"i:0#.f|membership|adelev@m'."
}
}
 
2020-01-29_19-25-17.jpg
4 REPLIES 4
Highlighted
Dual Super User III
Dual Super User III

Re: Look up an approver in a 2nd SharePoint list

Hi

I think the problem is because of using 'Current item' value as 'Assigned to' input. 'Current item' probably equals to 'items('Apply_to_each)' expression, which represents the whole item, not only the email address of the target user.

 

As I understood you are iterating through the results of 'Get items', which is an array of all the items matching your criteria, and  you managed to get one only item as result, right?

I guess you need to identify the Column where the email address of your target is stored, and use it instead: let's assume the email adress is stored under a column called 'Email': in such case you should try with 'items('Apply_to_each)?['Email']' as 'Assigned to' input. You should be able to find the right input from the Dynamic content

Hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted
New Member

Re: Look up an approver in a 2nd SharePoint list

Thank you, @efialttes for the help! This is my first flow ever, so please be very specific 😁

 

I tried both items('Apply_to_each)?['Email'] and 'items('Apply_to_each)?['Email']' in the expression field and both give this error: "the expression is invalid". Do I do this correctly?

2020-01-29_20-12-29.jpg

 

The name of my column is "Person" (I'll make this a better name in prod). I also tried changing the Assigned To field to Person email from the 'Get items' data set. This results in my Flow running until it times out. Any ideas?

2020-01-29_20-11-16.jpg

Highlighted
Dual Super User III
Dual Super User III

Re: Look up an approver in a 2nd SharePoint list

Hi again

 

I assumed the email adress was stored under a text column called 'Email': in such case you should try with 'items('Apply_to_each)?['Email']' as 'Assigned to' input.

 

If you have a Person type column to store such info, then you will have to dig into the diffferent properties provided by this data structure. I implemented a simple SP list with a column of type 'Person' called 'TargetPerson', then added a Compose action block inside the 'Apply to each' to print the content of 'Current item' and 'items('Apply_to_each)?['TargetPerson']?['email']

Flow_personSP_1.png

When executing the flow, the Compose action block prints the results, so you can see the difference:

Flow_personSP_2.png

 

Hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted
Dual Super User III
Dual Super User III

Re: Look up an approver in a 2nd SharePoint list

Hi again

Since it is your first PA Flow, one more comment:

As you are inside an Apply to each, the Dynamic content helps you to add references to your inputs' parameters without the need to type a single character:

Flow_personSP_4.png

Happy flowing!



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Helpful resources

Announcements
firstImage

Super User Program Update

Three Super User rank tiers have been launched!

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

Top Solution Authors
Top Kudoed Authors
Users online (7,769)