Look up a value from list 2 and populate field in list 1

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hi 🙂

I have 2 Parts that would be great for some help please

I have 2 lists.

List 1: Name + City (BLANK to start with)+ other fields

List 2: Name + City (Contains a value)

If users name/email exists in list 2, then autopopulate the city field with the city field in list 2.

P.S

If list 1 was to contain lets say over 2,000 records, is that a problem for powerapps and should I stick to using MS Flow instead?

Is this easy to do/ please any tips/pics?

What would the formula be?

Set L2City Default to If(L1Email=L2Email, L1City, "").

Categories:

Building Power Apps

All responses (24)

Answers (1)

Sort by

WarrenBelz 156,532 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Anonymous ,
When you say IN list one, do you want either the name or email being a match and are they an exact match for fields in List 2?
If so, what is the field names
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @WarrenBelz Thank you for reply 🙂 Please see pics

List 1) I will use a trigger when an email arrives create an item in a SPO list and fill in details from email.

Here I want, from List 1, the E-mail to match (pic ). List 1 also contains 'City' field later on

Then list 2 contains:
City + Email, if emails match then populate City field in list 1

Does this help? The emails will be exact matches unless it is not found on list 2. The email fields are also a 'Person/Group' on SPO list that I have formatting to show as xyz@abc.com if that helps/ is needed
WarrenBelz 156,532 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!

Ok @Anonymous ,
I don't use the O365 directory (very large company - we keep local lists), so I will tag a colleague for this one @mdevaney .
He is also in a better timezone to take this forward,
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you @WarrenBelz @ @mdevaney

I hope you will be able to provide some guidance :). Is there much difference in me using the O365 directory? Otherwise I look forward to some thoughts 🙂
mdevaney 29,993 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
@Anonymous

I have to make an assumption here that you want to populate a form with a Default value for City where the Email in List1 matches List2. In this example the Form has List1 as the datasource and the form contains at least two fields:

DataCardValue_Email_List1 which is a ComboBox showing the Email

DataCardValue_City_List1 which is a Text Input showing the city

To show the matching city in List 2 you must input this code into the Default property of DataCardValue_City_List1

LookUp(List2, Email = DataCardValue_Email_List1.Selected, City)

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @mdevaney

Thank you for the reply! Could I please clarify the default value part before proceeding?
It should work like this:
List 1= Email + city + other fields
List 2 = Email + city
Example:
List one gets filled in by flow so its xyz@mail.com and city field is blank
Meanwhile List 2 is a database that would have Email = xyz@mail.com and city = USA already filled in

Now powerapps would check if email in list 1 = list 2, if yes then take that SPECIFIC CITY FIELD FOR THAT email.

If another record comes in with aaaa@mail.com but the city field is Australia, then I would want Powerapps to know that aaa@mail.com exists in list 1 + list 2 and for aaaa@mail.com the city field is Australia, so it would use that value and not say USA.

Does your reply still accomplish this goal? Please let me know if more info needed 🙂 Thank you!
mdevaney 29,993 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

@Anonymous

I think it does if you are using a form for data input.
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you! Yes I will use a form to fill it in.

I will try the solution soon, if this works, do you know if power apps has any limitation to do the look up thing in list 2?

Say list 2 has 4000 records with fields Email + City (the data base list). Would this pose problems for power apps to look up say an email from someone who is record number 3999? I remember something about power apps delegations being an issue?
mdevaney 29,993 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

@Anonymous

Suggest you try the idea first. Any delegation issues can be discussed later.

In general, no issues.
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @mdevaney

Could you please help with the following error?

I have list 1+ list in the data sources (and list 3 but ignore that for now) see pic 1

I filled in the formulas as follows. Please see the pic below. Is there something obvious I have missed / messed up? Thank you 🙂

2

Would this all work if my aim is that, when an email is sent to an inbox, create an item in a SPO list and fill in the 'E-mail' box with the email from the sender to the shared mail box, then powerapps/ flow would do the lookup/ check to see that this email exists in List 2, and so autopopulate the city field?

I'm unsure if powerapps will solve this idea because everytime it will look on list 2 for the record it will be blank? unless there is some sort of trigger.. the best case would be a new item is added to list using Microsoft Flow.
Microsoft Flow autopopulates the 'E-mail' field by using the 'Sender' email from whoever sends an email to a shared mailbox
Powerapps would then do the check that sees if the autopopulated E-mail exists in List 2, if yes fill in city..
Do you think this is possible or stick to Microsoft Flow? I had got a good amount of it working in Flow before running into an issue so thought powerapps could be easier.. I guess step one is to get your suggested formula in place then we will see :)!