Filter SQL table on multiple optional fields

[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. 

[WarrenBelz · Accepted solution]

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]

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.