Answered

Delegation ClearCollect And Multiple Search Critera

(0) Share

Report

Posted on by JPacella

My app presents users 1 text box for optional in-inline search of one column, and 5 combo boxes to make optional search in their respective columns of a SQL Data Table

I'm struggling to come up with a way to create a DataSource/Table/Collection that can handle the dynamic search critera.

Question 1 - Is it possible to build a string variable and apply it in Filter or Search such as:

Filter(TableSource, variableOfSearchArguments)

Question 2 - How to overcome the ClearCollect function's truncating the data source fed to it? How can I get my search critera into SQL Server so that it won't have to supply 2,000+ rows?

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

Sort by

WarrenBelz 156,454 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @JPacella ,

You can certainly have a Variable, but it is really no better than the string itself unless you are setting it in a complex manner, so

Search( DataSource, Variable, "FieldName" )

or

Filter( DataSource, Variable in FieldName )

will certainly work.

Also, depending on the size of the output you are requiring, I have a blog on collecting all records (in this case matching records), but you need a unique sequential numeric identifier that is Delegable. You would apply the second filter around where I have the list name.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Visit my blog Practical Power Apps

Was this reply helpful? Yes No
JPacella 21 on at

Like
a
(0)

Report
Copy link

Link copied!
Thanks for the response. I think maybe I need to clarify what I'm asking. This is the UI. The first row input controls are to get criteria from User. They may select values for none, some, or all of them. The Data Table below would return the (AND) matches

So at first I thought, maybe a big IF statement to see which values are present to use with Filter. But with 6 items, the permutations made for a nasty IF statement.

The beginnings looked like:

If( Len(TextInvoiceNumberSearch) > 0, If( CountRows(ComboInvoiceStatus.SelectedItems ) > 0 && CountRows(ComboGL.SelectedItems) > 0 && CountRows(ComboAudit.SelectedItems) > 0 && CountRows(ComboSCAC.SelectedItems) > 0, Filter('POWER_APP.FREIGHT_FILE_INVOICE_SHIPMENT', TextInvoiceNumberSearch.Text in INVOICE_NUMBER && First(ComboGL.SelectedItems).GL_STATUS in GL_STATUS && First(ComboAudit.SelectedItems).AUDIT_STATUS in AUDIT_STATUS && First(ComboInvoiceStatus.SelectedItems).INVOICE_STATUS in INVOICE_STATUS && First(ComboSCAC.SelectedItems).SCAC in SCAC), CountRows(ComboInvoiceStatus.SelectedItems ) > 0 && CountRows(ComboGL.SelectedItems) > 0 && CountRows(ComboAudit.SelectedItems) > 0 && CountRows(ComboSCAC.SelectedItems) = 0, Filter('POWER_APP.FREIGHT_FILE_INVOICE_SHIPMENT', TextInvoiceNumberSearch.Text in INVOICE_NUMBER && First(ComboGL.SelectedItems).GL_STATUS in GL_STATUS && First(ComboAudit.SelectedItems).AUDIT_STATUS in AUDIT_STATUS && First(ComboInvoiceStatus.SelectedItems).INVOICE_STATUS in INVOICE_STATUS), CountRows(ComboInvoiceStatus.SelectedItems ) > 0 && CountRows(ComboGL.SelectedItems) > 0 && CountRows(ComboAudit.SelectedItems) = 0
So then I thought of :
If (Len(TextInvoiceNumberSearch.Text) > 0, FilterTextForInvoiceTable = "TextInvoiceNumberSearch.Text in INVOICE_NUMBER && "); If (CountRows(ComboInvoiceStatus.SelectedItems ) > 0, FilterTextForInvoiceTable = FilterTextForInvoiceTable + " First(ComboInvoiceStatus.SelectedItems).INVOICE_STATUS in INVOICE_STATUS && "); If (CountRows(ComboGL.SelectedItems ) > 0, FilterTextForInvoiceTable = FilterTextForInvoiceTable + " First(ComboGL.SelectedItems).GL_STATUS in GL_STATUS && "); If (CountRows(ComboAudit.SelectedItems ) > 0, FilterTextForInvoiceTable = FilterTextForInvoiceTable + " First(ComboAudit.SelectedItems).AUDIT_STATUS in AUDIT_STATUS && "); If (CountRows(ComboSCAC.SelectedItems ) > 0, FilterTextForInvoiceTable = FilterTextForInvoiceTable + " First(ComboSCAC.SelectedItems).SCAC in SCAC && "); FilterTextForInvoiceTable = Left(FilterTextForInvoiceTable, Len(FilterTextForInvoiceTable - 4)); Filter('POWER_APP.FREIGHT_FILE_INVOICE_SHIPMENT', FilterTextForInvoiceTable)

Then there was something I found on-line that created a Collection and would iterate through the combo boxes that had values, and use Filter to add to the Collection but that wasn't working either because the "sub filters" would only search a non-delegable output of the SQL Table, which is way too much for 2,000 rows.

Was this reply helpful? Yes No
Verified answer

Drrickryp Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@JPacella

See https://www.matthewdevaney.com/power-apps-filter-gallery-with-multiple-dropdowns/ for a nice example of dealing with multiple dropdowns for filters.

Was this reply helpful? Yes No
JPacella 21 on at

Like
a
(0)

Report
Copy link

Link copied!

That link looks very promising and simple! Thanks. I'll try it out.

Was this reply helpful? Yes No
JPacella 21 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks so much.. That led me to my solution.
I think some of the syntax has changed between when he wrote that and now... so for anyone else out there, this is what my final code looks like. Note that ".Value" from the link has been replaced with the actual ComboBox column name.

Filter('POWER_APP.FREIGHT_FILE_INVOICE_SHIPMENT',
TextInvoiceNumberSearch.Text in SEARCH_STRING,
ComboAudit.Selected.AUDIT_STATUS = Blank() Or AUDIT_STATUS = ComboAudit.Selected.AUDIT_STATUS,
ComboGL.Selected.GL_STATUS = Blank() Or GL_STATUS = ComboGL.Selected.GL_STATUS,
ComboInvoiceStatus.Selected.INVOICE_STATUS = Blank() Or INVOICE_STATUS = ComboInvoiceStatus.Selected.INVOICE_STATUS,
ComboSCAC.Selected.SCAC = Blank() Or SCAC = ComboSCAC.Selected.SCAC )

Was this reply helpful? Yes No