Using a lookup list for mgr's email, since some are external - cannot use get user profile.

[calimwade]

Hi,

I've got a "Flow" and cannot get the lookup list to work. I cannot use the "Get User Profile", since I have people who are external to our organization, so had to create a "List" called "Mgr List" that has the email addresses or the requester and the email of the manager. All the "Form" information is directed to report to another "List" called "Committee Request List" on my "SharePoint".

I have installed the columns of the "Mgr List", specifically the Mgr's Email address, onto the "Committee Request List" and specified that column to be a "Lookup" field. But, when running the below flow, it will not populate the "Mgr's Email Address".  HELP, I'm so stuck!!!

Here is my flow:

1) Trigger - Form:

            - When a new response is submitted

2) Apply to Each:

           - List of response notification

3) Form - Get Response Detail:

          - Form ID: the form I'm using

          - Response Id: list of response notifications Response Id

4) SharePoint Create Item:

          - Site Address: my sharepoint site

          - List Name: Committee Request List

          - added title and all fields from the form I used

5) Start an Approval:

          - Approval Type: anyone from the assigned list

          - Title: committee request

          - Assigned To: Mgr's Email (from the lookup list via the committee request list)

          - Requestor: Responder's Email

          - Details: Body of request details to describe the request to the approver

          - Item Link: link to item

          - Item Link Description: entered "click to view information

6) Condition

          - Response     is equal to      Approved

7) If Yes - Send an Email (Outlook)               If No - Send an Email (Outlook) - pretty much identical to "Yes", denied

          - To: reponder's email

          - Subject: outcome of committee request

          - Body: described the outcome, approval, approver's name, approver's comments

          - From: approver email

😎 Apply to Each - Responses

9) SharePoint - Update Item:

          - Site Address: my sharepoint site

          - List Name: committee request list

          - Id: ID (sharepoint link)

          - Title: committee request

          - Mgr's Approval Status: chose approved or denied for the "No" option

          - Mgr's Comments: link the "reponses comments"

I know this isn't exactly pretty, but if anyone can help, I'd greatly appreciate it.

Thanks, Cali

[ScottShearer · Accepted solution]

@calimwade:

First, can you confirm that you are actually receiving the email of the person who submitted the Form?  I believe that if you have the Form set to "anyone who has the link" rather than "Only people in my organization" that you don't receive the email address of the individual who submitted the form.

A quick way to check is to open the run history of one of the times the flow ran and see if you actually have the email address.  If the answer is yes, then please post a screen shot of your Flow.  If the answer is no, then I believe that you'll have to rethink your solution.

Scott

[calimwade]

Scott,

I will not let me load the pictures.

[calimwade]

[ScottShearer]

@calimwade:

Can you take a look at one of the Flow runs and see if you actually have the email returned from Forms?

Scott

[calimwade]

1rst Image: This the the list that all form information resides in and ultimately will absorb the Mgr's Email address from the lookup.

2nd Image: This is the is the Mgr's email lookup setup.

The 3rd Image is the the actual list that shows the manager's email - this is where I'm pulling from.

[calimwade]

Sorry Scott, I realized I replied by email to your previous question.

Your question:

First, can you confirm that you are actually receiving the email of the person who submitted the Form?  I believe that if you have the Form set to "anyone who has the link" rather than "Only people in my organization" that you don't receive the email address of the individual who submitted the form.

A quick way to check is to open the run history of one of the times the flow ran and see if you actually have the email address.  If the answer is yes, then please post a screen shot of your Flow.  If the answer is no, then I believe that you'll have to rethink your solution.

Scott

Answer:

Yes, I get the email informing me that someone has filled out the form. And it does log the information to the correct list.

[calimwade]

Your next question:

Can you take a look at one of the Flow runs and see if you actually have the email returned from Forms?

Answer:

I ran a test, and when I go back to the list, the information is all missing. It's there when I fill out the form though. Here is what the failure looks like.

[ScottShearer]

@calimwade:

I believe  that you have two issues.

First, if the Form is configured such that anyone with a link can respond, the email address of the responder will be set to "anonymous".  You are using the responders email address to find a manager via a lookup - since it will always be equal to "anonymous" you will never have a manager value.

Second, I am not clear on how you are retrieving the manager from the list item via lookup list.  That said, it doesn't matter as you don't have a value for the responder.

Scott

Scott

[calimwade]

Hi Scott,

Your Question:

First, if the Form is configured such that anyone with a link can respond, the email address of the responder will be set to "anonymous".  You are using the responders email address to find a manager via a lookup - since it will always be equal to "anonymous" you will never have a manager value.

Answer:

The form is set that only specific people will have access to it. It will not be anonymous. In fact, there are only 10 people, so far, and should not be adding many more.

I have made a change to the form to include an entry for the date and also the their email address (all will have a MinnState email address, so I've asked for that format). The email address will also be listed in the Manager's Lookup list, so it should tie together there. I think I will add another line for each person, to add a secondary email address that is specific to their college. That shoud help pick up email addresses that are entered incorrectly.

2nd Question:

Second, I am not clear on how you are retrieving the manager from the list item via lookup list.  That said, it doesn't matter as you don't have a value for the responder.

Within the list that holds all the information from the form, I have created a column for Mgr's Email and set the type as "Lookup". It asks "Get information from:" and I have pointed it to the Manager's list (Mgr Lookup). See below.

[ScottShearer]

@calimwade:

My apologies if you have already made this clear, but I just am not understanding how you setting the value in the Manager Email column.  I see above that you are setting the Requestors email but I don't see how the Manager Email column is getting populated.  Can you explain?

I also noticed the the lookup column is set to allow for multiple selections - I suggest clearing that check box.

Also, you say that people outside your organization will need to use the Form so you can't use the Get Manage action.  However, you also say that only specific people will have access to the Form.  I was unaware that you could select specific people - my understanding is that it is either anyone who has a link (anonymous) or just people inside of your organization. 

[calimwade]

Hi Scott,

This is how the Manager's Email is set up. I had already unclicked the allow multiple selections box.

[calimwade]

Sorry, missed one of your questions...

The form link will only be supplied to specific people and will not be posted anywhere on a sharepoint site.

[calimwade]

Sorry, missed one of your questions...

The form link will only be supplied to specific people and will not be posted anywhere on a sharepoint site.

[calimwade]

Hi Scott,

I scrapped this flow and went with a totally different way of doing it. That way is working, so thanks so much for your help.

I was reading through some Microsoft documents and found that others were having problems with the "Lookup" function from a list. Maybe this function is not working??? Below is the information I found.