cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ComputerHabit2
Level: Powered On

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
ComputerHabit2
Level: Powered On

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
ComputerHabit2
Level: Powered On

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.

 

Community Support Team
Community Support Team

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.
ComputerHabit2
Level: Powered On

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.  

 

 

 

 

 

ComputerHabit2
Level: Powered On

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)

 

 

ComputerHabit2
Level: Powered On

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
thirdimage

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors
Users online (5,420)