Filter Gallery with Multiple SearchBoxes

[MechEng2013]

Hi Everyone,

When I search the interwebs and this board for answers nothing I see solves the issue of delegation... I have a rather large dataset in Dataverse of Sales History going back to 2013-2023. I need users to be able to search the records in this table based on multiple fields, mostly text fields but also a date field. 

I would like these search boxes to acts as an AND function and not an OR function. If the searchbox is blank, then ignore it. My problem is that just doing  - searchbox.text in ThisRecord.'Item Code' - returns everything when the searchbox.text is blank. Of course, adding in an If(!IsBlank(searchbox.text)....) destroys delegation. With such a large dataset I NEED delegation.

Currently I have something like this:

Filter('VVI Sales History',

And(

SearchInputSalesHistory_1.Text in ThisRecord.'Customer Number',
SearchInputSalesHistory_3.Text in ThisRecord.'Customer PO Number',
SearchInputSalesHistory_4.Text in ThisRecord.'Invoice Number',
SearchInputSalesHistory_2.Text in ThisRecord.'Item Code',
SearchInputSalesHistory_5.Text in ThisRecord.'Sales Order Number',
SearchInputSalesHistory_6.Text in ThisRecord.'Salesperson Number'

)

)

Is this possible? Thanks!

[mmollet]

In is only delegable for dataverse if used with Text columns. The And part of what you are doing is fine. Here is a link with a table that will show what is and isnt delegable for dataverse. 

Not sure if wrapping them in Text() would fix this or not but worth a try. 

Filter('VVI Sales History',

And(

SearchInputSalesHistory_1.Text in Text(ThisRecord.'Customer Number'),
SearchInputSalesHistory_3.Text in Text(ThisRecord.'Customer PO Number'),
SearchInputSalesHistory_4.Text in Text(ThisRecord.'Invoice Number'),
SearchInputSalesHistory_2.Text in Text(ThisRecord.'Item Code'),
SearchInputSalesHistory_5.Text in Text(ThisRecord.'Sales Order Number'),
SearchInputSalesHistory_6.Text in Text(ThisRecord.'Salesperson Number)'

)

)

[MechEng2013]

Thanks for the reply.

The code I pasted currently is delegable. The problem I have is that the searchbox.Text being blank is still being applied to the filter... and returning nothing. I need to only filter based on the searchbox if I type something in it!

[mmollet]

Oh ok. Try something like this:

If(IsBlank(Searchbox), if blank do this, filter by what is typed in the searchbox)

[MechEng2013]

Thanks, but that destroys delegation.

[MechEng2013 · Accepted solution]

Apply multiple filters to Power Apps Galleries - Power Platform Community (microsoft.com)

@RezaDorrani 

GOT IT! Link to Video ^^^^^^^^^^^^^^^^^^^^^

Its a simple "OR" statement on each searchbox to check if empty. If empty, it satisfies that line, and then you keep going down the AND statement to look for other searchbox entries.

Then I added two datepickers to pick a date range... nice clean code 🙂

Filter('VVI Sales History',
IsBlank(SearchInputSalesHistory_1.Text) || SearchInputSalesHistory_1.Text in 'Customer Number',
IsBlank(SearchInputSalesHistory_2.Text) || SearchInputSalesHistory_2.Text in 'Item Code',
IsBlank(SearchInputSalesHistory_3.Text) || SearchInputSalesHistory_3.Text in 'Customer PO Number',
IsBlank(SearchInputSalesHistory_4.Text) || SearchInputSalesHistory_4.Text in 'Invoice Number',
IsBlank(SearchInputSalesHistory_5.Text) || SearchInputSalesHistory_5.Text in 'Sales Order Number',
IsBlank(SearchInputSalesHistory_6.Text) || SearchInputSalesHistory_6.Text in 'Salesperson Number',
'Invoice Date' >= fromDateSales.SelectedDate,
'Invoice Date' <= toDateSales.SelectedDate
)