Filter with ListBox

[clinton]

 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

[Steelman70]

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:

[Steelman70]

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]

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]

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!

[AmitLoh-Powerap]

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...