cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ericatoelle
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
efialttes
Super User
Super User

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!



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

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!



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
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

Carousel_PP_768x460_Wave2 (1).png

2022 Release Wave 2 Plan

Power Platform release plan for the 2022 release wave 2 describes all new features releasing from October 2022 through March 2023.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (5,143)