Answered

Filter SQL table on multiple optional fields

(0) Share

Report

Posted on by MHackney

Im trying to create a series of optional fields to filter my data source.
I use check boxes to select the fields the user wish's to filter on.

I am having trouble adding conditional filters however, I found a solution online which doesn't work for me, it give's a delegation warning and fails to collect records.

ClearCollect(ArchiveSearch,Filter('[dbo].[Problems]',
If(ShowStatus.Value = false,true,FilterStatus.Selected.Result in Status) &&
If(ShowType.Value = false,true,FilterType.Selected.Type in Type) &&
If(ShowOwner.Value = false,true,FilterOwner.Selected.Result in Owner)))

Anyone have any suggestions how I can get this to work without having to write full filters for every possible combination of filter options which would be a couple of hundred lines long.

Categories:

Building Power Apps

All responses (2)

Answers (1)

Sort by

Verified answer

WarrenBelz 156,748 Most Valuable Professional on at

Like
a
(2)

Report
Copy link

Link copied!
Hi @MHackney ,

The non-delegable item is the in filter - it looks like you simply want them to equal

ClearCollect( ArchiveSearch, Filter('[dbo].[Problems]', If( ShowStatus.Value = false, true, Status=FilterStatus.Selected.Result ) && If( ShowType.Value = false, true, Type=FilterType.Selected.Type ) && If( ShowOwner.Value = false, true, Owner=FilterOwner.Selected.Result ) )

You are only ever going to get your Delegation limit anyway in the collection.

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.
MHackney 9 on at

Like
a
(0)

Report
Copy link

Link copied!

Hmmm I had tried it with both equals and 'in' with the same result, but direct copying your code seems to work correctly, Im not sure what was different but there must have been something slightly off.

Thank you very much for the help.