cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Resolver I
Resolver I

Filter Values on Lookup Columns

I'm trying to figure out how create a filter based on another DDL.  I see examples but none appear to cover how to go about doing this with a couple of lookup columns.
I have two lookup columns use in a list.  The Customer lookup is looking up information in a Customers list.  The "Customer Location" is being looked up in a "Customer Locations" list.  The "Customer Locations" list has the Customer in it but it's been renamed on the list to "Customer Name".
 
When I go to modify the form in powerapps I have two Choice fields.
 
Choices('TestList'.Customer)
Choices('TestList'.Customer_x0020_Location)
 
I'm trying to get the Customer choice field to be used as a filter in the "Customer Location" field.
 
Instructions so far is to add the Customer and Customer Locations list as datasources.
 
Then change "Choices('TestList'.Customer)" to Distinct('Customers'.Customer) to populate that list.  This appears to work.
 
Then I go to Choices('TestList'.Customer_x0020_Location) and use the wizard to create a dependency.  This is where I have issues.  I keep getting errors about the dependency.
 
I choose the CustomerDDL.Value and the matching field is the "Customer Locations"."Customer Name".  I don't know if this is right but I certainly get an error.
 
I get Invalid argument and the = sign is red.  The code looks like:
Filter('Customer Locations', 'Customer Name' = CustomerDDL.Selected.Value)
 
I'm not sure how to procede.
1 ACCEPTED SOLUTION

Accepted Solutions

I finally got it working.

 

Filter(Choices(TestList.CustomerFab),Id in Filter('Customer Fabs','Customer Name'.Id = DataCardValueCustomer.Selected.Id).ID)

 

It seems I needed to use the ID's.

View solution in original post

5 REPLIES 5
Resolver I
Resolver I

I keep seeing mention of code like this:

Filter(Choices('Event Registration'.Title), Value in Filter(Events List, Status=statusDropdown.Selected.Value).Title)

 

I've modified my code to look like this and still have issues.  I get a red bang on the '=' sign every time.

 

Please anyone.  I'm trying to learn how to use PowerApps and I would have thought this to be pretty standard.  I've now spent all day and yesterday basically doing the same things over and over again like an insane person.

 

Community Support
Community Support

Hi @ComputerHabit2 ,

Do you want to modify this formula:Filter('Customer Locations', 'Customer Name' = CustomerDDL.Selected.Value)?

According to your description, 'Customer Locations' is a list name,  'Customer Name' is a look up column.

Could you tell me is 'Customer Name' multiple look up column?

If it is a multiple look up column, try this:

Filter(Customer Locations,CustomerDDL.Selected.Value in 'Customer Name'.Value)

If is not a multiple look up column, try this:

Filter(Customer Locations, 'Customer Name'.Value = CustomerDDL.Selected.Value)

Here's a doc about how to use complex type of sharepoint list in PowerApps for your reference:

https://powerapps.microsoft.com/en-us/blog/default-values-for-complex-sharepoint-types/

 

 

Best regards,

Community Support Team _ Phoebe Liu

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I'm still having issues but getting closer.  Right now the filter works but I end up with repeating values even with only one entry.

 

To try to resolve what is happening I created a TestList with the two dropdowns in it.

 

My current query looks like this:

 

Filter(Choices(TestList.CustomerFab), Value in Distinct(Filter('Customer Fabs','Customer Name'.Value = DataCardValueCustomer.Selected.Value),Fab))

 

I did get an issue about to many datalookup row limits and had to increase the value from 500 to 2000.  

 

 

 

 

 

This is closer but when I save it doesn't populate the item after saving.  I'm not getting duplicates.

 

Distinct(Filter(Choices(TestList.CustomerFab),Value in Filter('Customer Fabs','Customer Name'.Value=DataCardValueCustomer.Selected.Value).Fab),Value)

 

 

I finally got it working.

 

Filter(Choices(TestList.CustomerFab),Id in Filter('Customer Fabs','Customer Name'.Id = DataCardValueCustomer.Selected.Id).ID)

 

It seems I needed to use the ID's.

View solution in original post

Helpful resources

Announcements
News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

Power Apps Community Call

Power Apps Community Call- January

Mark your calendars and join us for the next Power Apps Community Call on January 20th, 8a PST

PP Bootcamp Carousel

Global Power Platform Bootcamp

Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.

secondImage

Power Platform Community Conference On Demand

Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!

Top Solution Authors
Top Kudoed Authors
Users online (9,245)