Answered

How to apply filter based two conditions on listbox

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hi Mates,

There are three listboxes(lstb1,lstb2,lstb3) and two textboxs(txtfrom, txtTo) field is Weight (ex. 1-100, 200-500 etc.)

Input Parameters : listbox(lstb1,lstb2) items and textbox with From -To Values.

OutPut : Listbox3(lstb3)

Action/Trigger : Button to filter on lstb3

lstb1 loading Table.type Vaues Item: Distinct(Filter(Table,Type <>""),Type)

lstb2 loading Table.source Values item: Distinct(Filter(Table,Source <>""),Source)

lstb3 loading Table.Location Values item:

if(CountRows(Gallery.Allitems)=0,Distinct(Filter(Table,Location <>""),Location),filterColl)

here we also have two textboxs to filter lstb3 values based textboxs.

Button : OnSelect :

ClearCollect(filterColl,Distinct(Filter('Table,Weight>Value(txtfrom.Text),Weight<Value(txtto.Text)),Location));

Current behavior in shot:

lstb3 is loading default values Location : this is correct as expected

selecting items from lstb1, lstb2 and input from -to values then only lstb3 loading/Filtering when press the button but

how to load/filter values when selected lstb1,lstb2 items with out From -To values.

Means user want to filter lstb3 on two condtion

1. when select the input parameters from lstb1, lstb2 items And txtfrom -txtTo(weight) values.

2. when select the input parameters from lstb1, lstb2 items

when trigger/button clicks then above condition 1 is filtering location values working as expected. But user also want to filter

with-out entering From-To Values, means only with lstb1,lstb2 items

how to filter with skip if From-To is blank, if not Blank then consider From-To textbox values in the same button.

With the above button formula i can filter when only enter the From-To values but not with blank.

Any body can help me here.

how can apply filter if From-To is blank then filter based lstb1,lstb2 values, if not Blank then consider From-To textbox values in the same button. kindly help me code.

Categories:

Power Apps Pro Dev & ISV

I have the same question (0)

All responses (12)

Answers (1)

Sort by

v-xida-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @Anonymous ,

Do you want to filter your records only with the lstb1,lstb2 items when the From-To is blank?

Based on the needs that you mentioned, I have made a test on my side, please take a try with the following workaround:

Set the OnSelect property of the Filter button to following:

ClearCollect( filterColl, Distinct( Filter( Table, Type = lstb1.Selected.Result && Source = lstb2.Selected.Result, If( !IsBlank(txtfrom.Text) && !IsBlank(txtto.Text), Weight > Value(txtfrom.Text) && Weight < Value(txtto.Text), true ), Location <> "" ), Location ) );

Set the Items property of the lstb3 to following:

If( CountRows(Gallery.Allitems)=0, Distinct( Filter(Table,Location <>""), Location ), filterColl )

Please consider take a try with above solution, then check if the issue is solved.

Best regards,

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @v-xida-msft ,

If not selected any item in listboxs(type,source) then its displaying as expected from filtercoll values no issues here,
but the moment when select type(list box1), Source( list box2 ) values are disappearing, even i am trying to enter Weight values and tying to get corresponding values but still not loading any values in List box 3.

here requirement is
1.Filter values with selected Type and Source after button click. once loaded list box 3 values, if users want to filter on-top of loaded values from existing items which is loaded a based on Type, Source, then users can enter From-To values to filter from already loaded values.
if user want to filter from displayed values which is already loaded from list box3 then they will enter weight values from 1- 100 then it has to filter based on already selected Type, source, FROM-To Values.
Please let me know if you available for 5 min call.

Was this reply helpful? Yes No
v-xida-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @Anonymous ,

I think I have gotten your needs. You mean that the Filter button just only filter records based on lstb1 and lstb2 selected options. And then the user could filter deep records from these loaded records in your lstb3 based on the From To Text Input box values, right?

Please consider modify your formula as below:

Set the OnSelect property of the Filter button to following:

ClearCollect( filterColl, Filter( Table, Type = lstb1.Selected.Result && Source = lstb2.Selected.Result ) );

Set the Items property of the lstb3 to following:

If( CountRows(Gallery.Allitems)=0, Distinct( Filter( Table, If( !IsBlank(txtfrom.Text) && !IsBlank(txtto.Text), Weight > Value(txtfrom.Text) && Weight < Value(txtto.Text), true ), Location <> "" ), Location ), Distinct( Filter( filterColl, If( !IsBlank(txtfrom.Text) && !IsBlank(txtto.Text), Weight > Value(txtfrom.Text) && Weight < Value(txtto.Text), true ), Location <> "" ), Location ) )

Please consider take a try with above solution, check if the issue is solved.

Note: The FROM-TO Text value filter would be applied to these loaded records in lstb3.

Best regards,

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @v-xida-msft ,
I am very close to accept answer, close this issue. because this first solution it self its working fine. but just cross checking with you once again for if there is any update required.
1. Filter has to work for multiple items from Type and source, means if user selected multiple type values and source items then it has to loop with selected items from Type and source.
Is this code already filtering based multiple items..?
2. yes user can filter deep filter, either with From-To values along with Type and Source OR only type and source then after loaded can filter FROM-To with already selected Type, source. From-To is not mandatory.
3. this is regarding clear the listbox3 values : if user is selected some Type and source values loading values fine, if Type unseleted its loading Default values from filterColl, till here its fine, but the moment when we select unseleted type value the its automatically loading(list box3) previous values this is not right, in fact list box3 has to Clear until press the button again.
Please reply.

Was this reply helpful? Yes No
v-xida-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @Anonymous ,

For your first question, I think the '=' operator in Type and Source Filter formula could achieve your needs, instead, the 'in' operator could achieve your needs.

Please modify your formula as below:

Set the OnSelect property of the Filter button to following:

ClearCollect( filterColl, Filter( Table, Type in lstb1.SelectedItems.Result && Source in lstb2.SelectedItems.Result ) );

For your second question, I think the solution I provided above could achieve your needs. Please try the formula I provided above within the Items property of the List Box 3.

For your third question, this issue may be related to filterColl collection. When you press the Filter button, the filterColl collection is created, but when you unselect the option in Type or Source List Box, the filterColl collection would not be reset or cleared.

So when you unselect the option in your Type or Source List Box, you need to re-calculate the filterColl collection based on Type and Source List box selections.

Please set the OnChange property of the List Box1 (Type) and List Box 2 (Source) to following:

Select(FilterButton)

Note: The FilterButton represents the name of your filter button in your canvas app screen, please replace it with actual control name

Best regards,

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @v-xida-msft ,

Its all fine, i just shown them, but still they are asking small changes. like filter from any one input(either listbox1, or listbox2 or textBox From-To)
1. Filter has to filter even any one source, example selected Type (when selected only type and press button), or Selected Source (when selected only type and press button) Or Textbox FROM- TO

Can we achieve this requirements.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @v-xida-msft ,
Let me clear requirement : please check the below screen. list box 1, list box 2, list box 3.
ListBox 1 is from Type Field.
ListBox 2 is from Location Field.
ListBox 3 is from Source field. (Filter has apply on Listbox 3 based on listbox1 and listbox2).
Initially all list boxes will load from data source. but
1.When not selected any items from list box 1 and list box 2 means users just want to filter on default existing values using FROM-To textbox( fieldName: Weight). when button press has to display output in list box3.
2. When Item/multiple items is selected from Type (list box 1) and Filter button has to click then only List box 3 has to load respective row Location values.
3. When value/multiple items is selected from Location (list box 2) and Filter button has to click then only List box 3 has to load respective row Location values.
4. When value/multiple items is selected from Type and Location (list box 1 and list box 2) and Filter button has to click then only List box 3 has to load respective row Source values.
5: When value/multiple items is selected from Type and Location (list box 1 and list box 2) and on-top of that deep filter on List box 3 using FROM -To when button press then only has to display list box 3.

Note : there is only one Button for filter, which meet the above 5 conditions.
at-least help me 1, 2 4,5 .
Help would be high appreciate.

Was this reply helpful? Yes No
v-xida-msft Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!
Hi @Anonymous ,

Do you want to filter records within your List Box3 based on any combination of these filter conditions? Right?

Does the solution I provided above could solve your original issue?

If the solution I provided above could solved your original issue, please consider go ahead to click "Accept as Solution" to identify this thread has been solved.

For your new question, I think the Filter button could not achieve your needs, instead, you could achieve your needs within List Box3 directly without Filter button.

Please set the Items property of the ListBox3 to following:

Distinct( Filter( Table, If( IsBlank(ListBox1.Selected.Result), true, Type in ListBox1.SelectedItems.Result ), If( IsBlank(ListBox2.Selected.Result), true, Location in ListBox2.SelectedItems.Result ), If( !IsBlank(txtfrom.Text) && !IsBlank(txtto.Text), Weight > Value(txtfrom.Text) && Weight < Value(txtto.Text), true ) ), Source )

please apply above formula within the Items property of the ListBox3, when you specify any combination of these Filter conditions (ListBox1, ListBox2, FROM-To textbox) in your app, the ListBox3 would be filtered with proper records.

Best regards,

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @v-xida-msft ,

Really thanks for your help on this, what ever you gave that code is working 100%,
here issue is, i am getting some wrong/own assumed requirements from organisation, but not client, so that i could not able to close this, but finally clients came with simple requirement, and its simplified almost 99% now.
That is:
They will just click first list box items then list box 2 will load automatically with out any trigger action, then same for list box 3 as well. this i just added below code for list box 2, 3. working as expected.
Sort(Distinct(Filter('Table', Type in lstbox1.SelectedItems),Source),Result,Ascending)
its filtering automatically with out button help.

Now as you said they also want deep filter on list box 3, using From-To textbox. but here they need to filter only after click the button.
Can you help me here how can we filter list box3 values with help of button. the moment i can filter using From-To using button going to close this tread.

Was this reply helpful? Yes No
v-xida-msft Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!
Hi @Anonymous ,

Sure, please set the OnSelect property of the Filter button to following:

If( !IsBlank(txtfrom.Text) && !IsBlank(txtto.Text), UpdateContext({FromToFilter: true}), UpdateContext({FromToFilter: false}) )

Please set the Items property of the ListBox3 to following:

Distinct( Filter( Table, If( IsBlank(ListBox1.Selected.Result), true, Type in ListBox1.SelectedItems.Result ), If( IsBlank(ListBox2.Selected.Result), true, Location in ListBox2.SelectedItems.Result ), If( FromToFilter = true, Weight > Value(txtfrom.Text) && Weight < Value(txtto.Text), true ) ), Source )

Please consider take a try with above solution, check if the issue is solved.

Best regards,

Was this reply helpful? Yes No