cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Lookup field sharepoint list with multiple value s

Hi all, 

 

I'm fairly new to PowerApps and still struggling a bit creating a lookup field to see if an user already exists based on firstname and lastname. I hope you guys can help me out. 

Here it goes:

 

I have a form with 2 textboxes. 

1. Firstname

2. Lastname

 

I have a sharepoint list: "User" with columns: P_GivenName_0 and P_SurName_0

 

On the LastName textbox I created a rule, if it's true then a popup will show stating the user already exists.

 

!IsBlank(LookUp(User,P_Surname_0 = TB_LastName.Text && P_GivenName_0 = TB_FirstName.Text))
&& !IsBlank(TB_LastName.Text)

It... kinda.... works... But there is a huge flaw..

Let's say I have 2 users:

1. John Doe

2. Jane Smith

 

If I type in: John Doe, it generates the pop up which is good. But it also generates the popup if I fill in: Jane Doe since the lookup I currently have doesn't do a lookup on a single row but instead looks if the firstname and lastname exists somewhere in the "User" Sharepoint list. 

 

Any idea how to fix this :)?

 

Kind regards,

Dennis

1 ACCEPTED SOLUTION

Accepted Solutions

@Anonymous 

Bing!! Lightbulb is on now.  I missed the subtle point that "Jane Doe" was the point that failed in your first post.  Somehow had it in my head "Jane Smith" was the issue.

Okay, now that I'm passed that.  I actually don't see an issue with what you want and what you have.

 

I have attached a sample App to this post that, I believe, has the exact same logic in a rule and based on the same data.  I also put an option in there for you to review that is not based on a rule.

However, from what I see, either will work fine.

Please take a look at the App (download, save and open in PowerApps desginer - you have to open an existing app and then choose File-Open-Browse to open it)

Let me know if this is what you are or are not seeing or if there is something different, or if I am landing on the wrong planet.

 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

6 REPLIES 6
RandyHayes
Super User
Super User

@Anonymous 

Can you explain a little more about this?

As I read it, you get a popup box when entering in John Doe (which you expect), but you also get one for Jane Smith - I'm not sure how you wouldn't expect that - your logic is to lookup to see if the first and last name exist in the Users list.  As I read your posting...they both do.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
Anonymous
Not applicable

@RandyHayes 

Yes I understand that with the query/lookup i'm currently doing i'm getting both the popups. But that is not what I want. 

 

It means that I am using the query/lookup wrong and I want the correct syntax to achieve what I want. That means it should check on row level if the firstname/lastname match. 

 

@Anonymous 

I apologize that I am not understanding your issue.  You stated that you have a user list that has first and last names in it.  You want a popup box to appear if the first and last name entered by the person is in the list.  You mentioned an example of two names in the list and that you want the popup box to occur for the first name but somehow not for the second name, even though it is in the list.

Can you clarify a bit more about what you mean by "it should check on row level"?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
Anonymous
Not applicable

@RandyHayes 

 

Ok, i'll try to add a bit more context to it and try to explain what I want. I understand the query/lookup I have right now is giving me 2 popups, but that query/lookup is wrong. I don't want 2 popups!

 

We use this form for automating a request for a new user. So someone from the HR department fills in the form 'New User':

- Firstname: Randy

- Lastname: Hayes

 

and presses save. 

 

This will be saved in a Sharepoint list in which we check for new items, and it fires up a script that handles everything to create that user.. not relevant for the matter. 

 

The firstname and lastname in the script will be used to generate an User Principal Name. So let's say your firstname & lastname makes the UPN: r.hayes@company.com

I want the check if the user already exists happen in PowerApps. So I have a sharepoint list named: 'User' that is being synchronized from the ActiveDirectory. 

 

In that User Sharepoint list are all the employees we have in our organization, so for example:

Firstname      Lastname

Randy            Hayes

Will                Smith

 

When our HR department fills in the firstname and lastname in the 'New User' form with the values firstname:Randy lastname:Hayes, I want a query/lookup/filter/whatever to check if the user Randy Hayes exists. So then the pop up will appear!

 

But I don't want the pop up to appear when the HR department fills in the form with the values firstname:Will lastname:Hayes. 

 

Do you understand my issue now?

@Anonymous 

Bing!! Lightbulb is on now.  I missed the subtle point that "Jane Doe" was the point that failed in your first post.  Somehow had it in my head "Jane Smith" was the issue.

Okay, now that I'm passed that.  I actually don't see an issue with what you want and what you have.

 

I have attached a sample App to this post that, I believe, has the exact same logic in a rule and based on the same data.  I also put an option in there for you to review that is not based on a rule.

However, from what I see, either will work fine.

Please take a look at the App (download, save and open in PowerApps desginer - you have to open an existing app and then choose File-Open-Browse to open it)

Let me know if this is what you are or are not seeing or if there is something different, or if I am landing on the wrong planet.

 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
Anonymous
Not applicable

@RandyHayes 

It works!

 

I have no idea why I wasn't getting the same results in my PowerApp.. The only thing I did different is that I had my rule set up on lastname instead of the popup box.

 

Thank you so much for your time!

 

Helpful resources

Announcements
PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Top Kudoed Authors
Users online (2,559)