Nesting Filter inside Search doesn't work

[Anonymous]

Hi,

i have an app which is based on CDS. in the app i have a screen which has a gallery where its items can have multiple filters.

in order to apply nested filters, i have tried nesting Filter inside Search, Search inside Filter, and Filter inside Filter, but whenever both filter values are used, one of the functions loses the original filtered subset.

I have also tried using && and || but nothing has worked so far!

any suggestions?

[Khawaja]

Hi @Anonymous,

i have the same problem... check my post here:

https://powerusers.microsoft.com/t5/General-Discussion/Search-Filter-nested-functions-with-wrong-behaviour/td-p/99750

regards,

Mohammad

[Anonymous]

Hi @Anonymous

Please share the formula you are using to filter the data. Having multiple filters for data source is doable.

Here is an example of simple multiple filter

Filter(
  MyDataSource,
  Name = Dropdown1.Selected.Value,
  City = Dropdown2.Selected.Value,
  Country = Dropdown3.Selected.Value
)

If you give us more details on your specific needs we can show you more tailored answer.

[mokhawaja]

thanks @Anonymous for your suggestion... but we are talking about nesting Search inside Filter or vice versa:

Filter(Search(Doctors, TextInput6.Text,"DoctorName"), City=ComboBox2.Selected.Value)

[Anonymous]

Hi @mokhawaja

In your case if you are using a combo box there is no Selected.Value.

The correct syntax with ComboBox:

Filter(Search(Doctors, TextInput6.Text,"DoctorName"), City in ComboBox2.SelectedItems)

 I checked with my datasource (SQL Server) and this Filter + Search is working as expected.

[Anonymous]

Hi @mokhawaja

Actually, for it to work properly there needs to be adjustment. Because if you use the function I wrote above it will not work when ComboBox is empty, and sometimes when Search Input box is empty.

To overcome this you need to write IF() statements and it would be something like this:

If(
    IsEmpty(ComboBox2.SelectedItems) && IsBlank(TextInput6.Text),
    Doctors,
    IsEmpty(ComboBox2.SelectedItems) && !IsBlank(TextInput6.Text),
    Search(Doctors, TextInput6.Text,"DoctorName"),
    !IsEmpty(ComboBox2.SelectedItems) && IsBlank(TextInput6.Text),
    Filter(Doctors,City in ComboBox2.SelectedItems),
    Filter(Search(Doctors, TextInput6.Text,"DoctorName"), City in ComboBox2.SelectedItems)
)

However even this is not that good since the IN operator can not be delegated (means that you will be seeing only partial data if you have more than 500 rows). To solve this you can use Dropdown for cities instead of combo box (you will be able to search one city at a time instead of selecting multiple).

[mokhawaja]

Thanks again @Anonymous, actually i used something similar to your suggestion and this is my full formula 

SortByColumns(If(IsBlank(SpecialtyComboBox.Selected) && IsBlank(CityComboBox.Selected),
Search(Doctors, TextInput3.Text, "DoctorName","Area"), 
If(!IsBlank(SpecialtyComboBox.Selected) && IsBlank(CityComboBox.Selected), Filter(Search(Doctors, TextInput3.Text, "DoctorName","Area"), Specialty = SpecialtyComboBox.Selected.Value), 
If(!IsBlank(CityComboBox.Selected) && IsBlank(SpecialtyComboBox.Selected), Filter(Search(Doctors, TextInput3.Text, "DoctorName","Area"),  City = CityComboBox.Selected.Value),  If(!IsBlank(SpecialtyComboBox.Selected) && !IsBlank(CityComboBox.Selected),Filter(Search(Doctors, TextInput3.Text, "DoctorName","Area"), Specialty = SpecialtyComboBox.Selected.Value && City = CityComboBox.Selected.Value))))), "DoctorName", Ascending)

notice that i opted for the single selection in the comboboxes to use the selected.value, and to be a delegable function.

and as you can see in my screenshot i have two comboboxes to filter on and a search textbox, so whenever the comboboxes are selected with no search the filte works fine!

but when i use the search box then its results will override the filters and bring back everything.

[Anonymous · Accepted solution]

I managed to find a solution for this problem, in which i used Collections.

So for the first level of filteration:

ClearCollect(nameofCollectionvar,Filter(tablename,field1=value1 && field2= value2));

you will then use the same collection you generated above for the below search

Search(nameofCollectionvar,textfield.txt,"field3","field4"))

[mokhawaja]

Hi @Anonymous,

yes it is confirmed that my same formula works as expected on a collection, and this proves that this is a bug in the search function or filter when working on live data

Filter(Search(CacheDoctors, TextInput3.Text, "DoctorName","Area"), 
Specialty = SpecialtyComboBox.Selected.Value && 
City = CityComboBox.Selected.Value))))), "DoctorName", Ascending)

CacheDoctors is a collection of the Doctors entity.

i hope someone from Microsoft pays some attention to this

@v-micsh-msft, @v-monli-msft