Filter and Search

[LeeJBS]

Hi All

Hope someone will be able to help me.

Im currently building an app for the sales team to register and monitor sales ops.

What I have is a search which is working but I would like to add a couple of filters and then have the search the filtered results.

My search formula is 

Search('Practice Cadence Source',TextSearchBox1.Text,"Customer_x0020_Name_x0020_","Brief_x0020_Description")

I want to add 3 Combo Boxes for multi selection being the Status (won, lost, qualification, proposal) and also the department and lastly account managers.

Im just not sure how to do the filters on the gallery then have the search only search on the filtered results.

Thanks in advance for any help here.

[yashag2255]

Hi @LeeJBS 

To add the filters based on Dropdown, you can update the expression as:

Search(Filter('Practice Cadence Source', Status in Combobox1.SelectedItems.Value && Department in Combobox2.SelectedItems.Value && 'Account Manager' in Combobox3.SelectedItems.Value),TextSearchBox1.Text,"Customer_x0020_Name_x0020_","Brief_x0020_Description")

Here, Combobox1.SelectedItems.Value need to be replaced with the name of combobox control and value need to be replaced with the column that you want to extract.

If Account Manager is a person type field, then the formula should be updated to:

Search(Filter('Practice Cadence Source', Status in Combobox1.SelectedItems.Value && Department in Combobox2.SelectedItems.Value && 'Account Manager'.DisplayName in Combobox3.SelectedItems.DisplayName),TextSearchBox1.Text,"Customer_x0020_Name_x0020_","Brief_x0020_Description")

If this differs, please share more details about the data type of the three columns in your datasource and we might be able to help you better.

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[LeeJBS]

@yashag2255 Thanks so much for the quick reply

So I have updated with the combobox names 

Search(Filter('Practice Cadence Source', Status in FilterStatus.SelectedItems.Value && Practice in FilterPractice.SelectedItems.Value && 'Account Manager' in FilterStaff.SelectedItems.Value),TextSearchBox1.Text,"Customer_x0020_Name_x0020_","Brief_x0020_Description")

 However im getting a delegation warning so have an empty gallery.

[v-siky-msft]

Hi  @LeeJBS ,

What is the data source? What is the data type of these columns 'Status', 'Practice', 'Account Manager'?

Could you please share the screenshot of the delegation error? which part of formula causes the delegation error?

If your data source is less than 2000 rows, increase the delegation threshold to 2000 in Advance Setting.

If this doesn't make sense, please post back more details.

Sik

[LeeJBS]

@v-siky-msft 

Sorry for the delay with the reply. Been flat out.

So, I got it working by dropping the .value

Search(Filter('Practice Cadence Source', Status in FilterStatus.SelectedItems && Practice in FilterPractice.SelectedItems && 'Account Manager' in FilterStaff.SelectedItems),TextSearchBox1.Text,"Customer_x0020_Name_x0020_","Brief_x0020_Description")

One issue i still have though is that if NO filter is applied i dont get anything in the gallery. It must have something in each of the 3 filters. Is there anyway to not have to have something in the filters?

[poweractivate]

@LeeJBS 

---

@LeeJBS wrote:

@v-siky-msft 

Search(Filter('Practice Cadence Source', Status in FilterStatus.SelectedItems && Practice in FilterPractice.SelectedItems && 'Account Manager' in FilterStaff.SelectedItems),TextSearchBox1.Text,"Customer_x0020_Name_x0020_","Brief_x0020_Description")

One issue i still have though is that if NO filter is applied i dont get anything in the gallery.... Is there anyway to not have to have something in the filters?

---

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-filter-lookup

Search( Table, SearchString, Column1 [, Column2, ... ] )

• Table - Required. Table to search.
• SearchString - Required. The string to search for. If blank or an empty string, all records are returned.
• Column(s) - Required. The names of columns within Table to search. Columns to search must contain text. Column names must be strings and enclosed in double quotes. However, the column names must be static and cannot be calculated with a formula. If SearchString is found within the data of any of these columns as a partial match, the full record will be returned.

In your case, Table is this long part right?

Filter('Practice Cadence Source', Status in FilterStatus.SelectedItems && Practice in FilterPractice.SelectedItems && 'Account Manager' in FilterStaff.SelectedItems)

You are essentially doing this: 

Search(Table, TextSearchBox1.Text,"Customer_x0020_Name_x0020_","Brief_x0020_Description")

That means you are applying Search with text from TextSearchBox1.Text and are searching within columns of 

"Customer_x0020_Name_x0020_" and "Brief_x0020_Description" for TextSearchBox1.Text - so they likely must be present in your table.

You can also consider changing "Customer_x0020_Name_x0020_" and "Brief_x0020_Description" for TextSearchBox1.Text to something else.

You should not absolutely have to have a Filter. It is looking for a Table there. It could have to do with the Columns you are searching on, does changing them help you not have to have the filter?

Also check - instead of Filter - what are you putting there instead? Check that as well.

[LeeJBS]

Thanks,

Yes the fields "Customer_x0020_Name_x0020_","Brief_x0020_Description" are in all records. And the search works fine when all filters are selected. I just cant get it to show any data at all in the gallery unless some options are selected under each of the 3 filter comboboxes. 

Maybe i need to do some If Blank skip the filter or something?

[poweractivate]

@LeeJBS 

You are using And condition, that could be why, or && - but you probably need to here.

So if it is empty, then there is nothing that fits the empty criteria, that's why.

Suppose

Status in FilterStatus.SelectedItems

in

Filter('Practice Cadence Source', Status in FilterStatus.SelectedItems && Practice in FilterPractice.SelectedItems && 'Account Manager' in FilterStaff.SelectedItems)

is empty

Therefore you said Filter(Source,NOTHING and Maybe Something and Maybe Something)

The result will be only the set that is shared in common with all thee individual clauses

1. NOTHING

2. Maybe Something 

3. Maybe Something 

So here the result will be:

NOTHING

because of the AND

So Yes, you may need to say this instead:

If(!IsBlank(FilterStatus.SelectedItems),Status in FilterStatus.SelectedItems,true)

and do similar for all three of these

to force a TRUE evaluation if the SelectedItems is left blank

Does this help?

[v-siky-msft]

@LeeJBS,

yeah, just add an if condition into your filter function, If the SelectItems is empty, do not filter.

Search(Filter('Practice Cadence Source', If(IsEmpty(FilterStatus.SelectedItems), true, Status in FilterStatus.SelectedItems) && If(IsEmpty(FilterPractice.SelectedItems),true,Practice in FilterPractice.SelectedItems) && If(IsEmpty(FilterStaff.SelectedItems), true, 'Account Manager' in FilterStaff.SelectedItems)),TextSearchBox1.Text,"Customer_x0020_Name_x0020_","Brief_x0020_Description")

 Sik

[yashag2255]

Hi @LeeJBS 

You can update the expression to work in case a filter is not selected by using the below expression:

Search(Filter('Practice Cadence Source', If(CountRows(FilterStatus.SelectedItems) = 0, true,Status in FilterStatus.SelectedItems) && If(CountRows(FilterPractice.SelectedItems) = 0, true, Practice in FilterPractice.SelectedItems) && If(CountRows(FilterStaff.SelectedItems)=0,true, 'Account Manager' in FilterStaff.SelectedItems)),TextSearchBox1.Text,"Customer_x0020_Name_x0020_","Brief_x0020_Description")

Hope this Helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!