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

Filter with ListBox

 Hi,

 

I am trying to use a List Box (LBBA) to produce a filter (i.e. add items dynamically based on what is selected to query a single filed in the datasource) in a gallery. So essentially what I am trying to do is start the filter "normally" by specifying Filter('[dbo].[APLMAC]'; - from here I want to add the string that completes the rest of the filter syntax. From checking in a textbox it seems as though the rest of the filter syntax builds correctly.

 

However, it seems as though the the "Items" field in the Gallery does not accept the string which I am trying to append. Below is what I have in the "Items" field of my Gallery.

 

Filter('[dbo].[APLMAC]'; Left(Concat(LBBA.SelectedItems.Value; 
Concatenate("Business_Area_Combined=""";Text(Value); """ || "));
Len(Concat(LBBA.SelectedItems.Value; Concatenate("Business_Area_Combined=";
Text(Value); " || """))) - 2) )

Any other suggestions to dynamically search on a field with multiple queries using a listbox would be appreciated.

 

Thanks

5 REPLIES 5
Steelman70
Level 10

Re: Filter with ListBox

Hello, you can do that using the in operator.  It depends a bit on what your data is like, but here is how I did it:

 

1. Create your list box

2. Create a text box that concatenates all your choices, in my case:

TextBoxFilterString.Text = Concat(ListBox1.SelectedItems, ISO_code & " ")

3. Create your gallery, and in my case set this:

Gallery1.Items = Filter('[Access].[Currencies]', ISO_code in TextBoxFilterString.Text)

Because of my source (SQL server), I got a warning (the white i in the blue circle) that the filter cannot be evaluated remotely, this is because the in operator cannot be delegated.  But actually it works, so I ignored the warning.

 

See a screenshot of the result below:

 

CaptureListBoxFilter.JPG

Steelman70
Level 10

Re: Filter with ListBox

PS you can actually use the in operator to check if the record is in a table, i.e. use the selection from your list box as a table.  This would be a more robust solution but I will try it when I have more time.

clinton
Level: Powered On

Re: Filter with ListBox

Thanks this seems to have worked as a filter. However I seem to run into delegation issues. My data is sitting on  Azure SQL...

 

Ok so after playing around with this it seems as though the as soon as I use the "in" operator on the filter, the delegation doesn't work properly and only filters on the first 500 rows. However, if I use the "=" operator all works fine and it searches within the whole my ~5000 row dataset... I would like to use "in" but "=" will do for now as a workaround.

Meneghino
Level 10

Re: Filter with ListBox

Hi again (this is still Steeman but have changed ID)

I have now had time to experiment, and the in operator can be made to work without needing to concatenate strings!!!

Basically you can use this pseducode from the example above:

 

Gallery1.Items = Filter('[Access].[Currencies]', ISO_code in ListBox1.SelectedItems.ISO_code)

Because of the column name being the same in both tables, you may need disambiguation.  I still get the delegation error, but if I ignore it, all works and really fast for a few hundred records.  I have not tested the 500 record limit.

 

What I am saying is that the in operator can look to see if a value is included in a list of values!!!  This is really useful!

Re: Filter with ListBox

Hi Meneghino, Thanks!! This works only when you have less items. Its not working for me when I have large number of items.

I hope In will be available to delegate in CDM soon...

Helpful resources

Announcements
thirdimage

Power Automate 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

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,332)