use 3 tables for filter Listbox

(0) Share

Report

Posted on by SelpaqM

Hi,

I have 3 tables.

1) The CustomerList, has customer and country columns

2) The ProviderList has provider and country columns

3) The customer-provider list has customer and provider columns and there is no country column.

I have 3 Listboxes

ListBox1 gets data from the ProviderList. connected a textinput (as used search box) and created using

Distinct(Filter(ProviderList, StartsWith('Provider Name', TextInput2.Text)), 'Provider Name')

ListBox2 gets data from the ProviderList. connected to the ListBox1 and created using

Distinct(Filter(ProviderList,'Provider Name'=ListBox1.Selected.Value),'Provider Country')

Now I want to create ListBox3 and need to see Customers that work with provider which is selected in ListBox1 and in the specific country which is selected Listbox2.

I used "in" to filter the new Listbox.

Filter(CustomerLists,'Customer Country'=ListBox2.Selected.Value And 'Customer Name' in Filter(Customer-ProviderList,'Provider Name'=ListBox1.Selected.Value).'Customer Name')

hwoever, that time get delegation error. how to fix it?

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Sort by

WarrenBelz 154,926 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
Hi @SelpaqM ,

A couple of things here - Delegation warnings are not errors and can be ignored if your data source numbers do not exceed your Delegation limit. Also, you have Delegation issues in the first two list boxes as the Distinct function is limited to acting on a data set no bigger than your Delegation limit. However, while the code below should not give you any warnings, the output of the top two filters (the input can be of any size) is also constrained by your Delegation Limit.

With( { _Data1: Filter( CustomerLists, 'Customer Country' = ListBox2.Selected.Value ), _Data2: Filter( Customer-ProviderList, 'Provider Name' = ListBox1.Selected.Value ) }, Filter( _Data1, 'Customer Name' in _Data2.'Customer Name' ) )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No
SelpaqM 14 on at

Like (0)

Report
Copy link

Link copied!

Hi @WarrenBelz , thanks for your reply. Getting invalid arguments: received 1 expected 2 or more error on last filter.

Was this reply helpful? Yes No
Verified answer

SelpaqM 14 on at

Like (0)

Report
Copy link

Link copied!
Hi @WarrenBelz I solved it with changing : to , and adding distinct because without distinct all the table is in there but listbox was empty. Anyway this is works now
Distinct( With( { _Data1: Filter( CustomerLists, 'Customer Country' = ListBox2.Selected.Value ), _Data2: Filter( Customer-ProviderList, 'Provider Name' = ListBox1.Selected.Value ) }, Filter( _Data1, 'Customer Name' in _Data2.'Customer Name' ) ), 'Customer Name')

Was this reply helpful? Yes No
WarrenBelz 154,926 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
@SelpaqM ,

That was just a typo (now fixed). I assume my post lead you to the solution here (your question was how to avoid the Delegation warning). It is also better to put the Distinct() in the bottom filter as it maximises your Delegation potential.

With( { _Data1: Filter( CustomerLists, 'Customer Country' = ListBox2.Selected.Value ), _Data2: Filter( Customer-ProviderList, 'Provider Name' = ListBox1.Selected.Value ) }, Distinct( Filter( _Data1, 'Customer Name' in _Data2.'Customer Name' ), 'Customer Name' ) )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No