cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Resolver I
Resolver I

Re: Filter Values on Lookup Columns

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
Highlighted
Resolver I
Resolver I

Re: Filter Values on Lookup Columns

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.

 

Highlighted
Community Support
Community Support

Re: Filter Values on Lookup Columns

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.
Highlighted
Resolver I
Resolver I

Re: Filter Values on Lookup Columns

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.  

 

 

 

 

 

Highlighted
Resolver I
Resolver I

Re: Filter Values on Lookup Columns

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)

 

 

Highlighted
Resolver I
Resolver I

Re: Filter Values on Lookup Columns

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
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (12,087)