Look up an approver in a 2nd SharePoint list

[ericatoelle]

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'."

}

}

[efialttes]

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

[ericatoelle]

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?

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?

[efialttes]

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']

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

Hope this helps

[efialttes]

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:

Happy flowing!