Multiple filters

[lalford09]

Hi,

I am struggling to get this formula to work correctly and was hoping someone can help.

I have a gallery that originally had 2 filters that users would use and that worked fine, but they have since asked for a 3rd filter option and I cannot seem to get all 3 filters to work along side each other. 

This is my formula which doesn't error, it just doesn't seem to work:

If(IsBlank(txt_progressRC_search.Text),
With(
{
SelectedStatus: If(
'dd_progress-RC_status'.Selected.Value = "Pending", "Awaiting",
'dd_progress-RC_status'.Selected.Value = "Approved", "Approved",
'dd_progress-RC_status'.Selected.Value = "Rejected", "Rejected",
'dd_progress-RC_status'.Selected.Value = "Cancelled", "Cancelled"
)
},
SortByColumns(
colAllItems,
"ID",
SortOrder.Descending
)
),

If(!IsBlank(cmb_progressRC_RCcontact.Selected.Result),
Filter(colAllItems,
varRCFilter in ResourcingContact.DisplayName || varRCFilter in ResourcingDelegate.DisplayName),

Filter(
colAllItems,
txt_progressRC_search.Text in ID)))

The outcome needs to be that they all work in conjunction with each other, and none over write each other.

[lalford09]

What do you mean sorry?

[lalford09]

Same issue, it doesn't error but the gallery stays empty

[WarrenBelz]

@lalford09 ,

Please tag whoever you want to help you here - three way conversations confuse everyone.

[lalford09]

Sorry it was in response to your message and then I was replying to someone else trying to help @WarrenBelz 

[WarrenBelz]

@lalford09 ,

What I meant was

Filter(
   colAllItems,
   (
      PageStatus = "Awaiting" &&
      dd_progress_RC_status.Selected.Value = "Pending"
   ) ||
   PageStatus = dd_progress_RC_status.Selected.Value
)

If this works, then the problem is in the one of the other filters.

[lalford09]

@WarrenBelz Ah okay makes sense, I have just tried that and the gallery is still blank no matter which status I am selected.

The 4 status options are Pending, Approved, Rejected, Cancelled.

[lalford09]

@WarrenBelz However I think part of the issue may lie here

PageStatus = "Awaiting" as the PageStatus would be "Awaiting Business Approver" or "Awaiting Final Approver" etc... so maybe we need to use the In function?

[lalford09]

@WarrenBelz I believe this is working after a couple of tweaks

Filter(
colAllItems,
(Len(txt_progressRC_search.Text) = 0 || txt_progressRC_search.Text in ID) && (Len(cmb_progressRC_RCcontact.Selected.Value) = 0 || cmb_progressRC_RCcontact.Selected.Value in ResourcingContact.DisplayName || cmb_progressRC_RCcontact.Selected.Value in ResourcingDelegate.DisplayName) && ("Awaiting" in PageStatus && dd_progress_RC_status.Selected.Value = "Pending") || dd_progress_RC_status.Selected.Value in PageStatus
)

[lalford09]

@WarrenBelz Ah so this works if I am setting the Status drop down as Pending, I can then filter on the cmb_progressRC_RCcontact as well, however if I then change the Status drop down to filter on another status, that works fine, however the other filters no longer work along side that status.

[WarrenBelz · Accepted solution]

@lalford09 ,

That would have been useful information to know (the status was not actually Awaiting but Awaiting was in the Status). Firstly, you need to go down another level of bracketing here

Filter(
   colAllItems,
   (
      Len(txt_progressRC_search.Text) = 0 || 
      txt_progressRC_search.Text in ID
   ) && 
   (
      Len(cmb_progressRC_RCcontact.Selected.Value) = 0 || 
      cmb_progressRC_RCcontact.Selected.Value in ResourcingContact.DisplayName || 
      cmb_progressRC_RCcontact.Selected.Value in ResourcingDelegate.DisplayName
   ) &&
   (   
      (
         "Awaiting" in PageStatus && 
         dd_progress_RC_status.Selected.Value = "Pending"
      ) || 
      dd_progress_RC_status.Selected.Value in PageStatus
   )
)

 but I am also wondering how

_progressRC_search.Text in ID

works when ID is a number.

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.

MVP (Business Applications)   Visit my blog Practical Power Apps