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

Nesting Filter inside Search doesn't work

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?

1 ACCEPTED SOLUTION

Accepted Solutions
yhijazeen
Level: Powered On

Re: Nesting Filter inside Search doesn't work

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"))

8 REPLIES 8
Khawaja
Level: Powered On

Re: Nesting Filter inside Search doesn't work

Anonymous
Not applicable

Re: Nesting Filter inside Search doesn't work

Hi @yhijazeen

 

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
Level 8

Re: Nesting Filter inside Search doesn't work

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
Not applicable

Re: Nesting Filter inside Search doesn't work

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
Not applicable

Re: Nesting Filter inside Search doesn't work

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
Level 8

Re: Nesting Filter inside Search doesn't work

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.

yhijazeen
Level: Powered On

Re: Nesting Filter inside Search doesn't work

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"))

Highlighted
mokhawaja
Level 8

Re: Nesting Filter inside Search doesn't work

Hi @yhijazeen,

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