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.

[Rajkumar_404]

Hi

Try this formula

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 ) ),

Filter( colAllItems, varRCFilter in ResourcingContact.DisplayName ||

varRCFilter in ResourcingDelegate.DisplayName, txt_progressRC_search.Text in ID, cmb_progressRC_RCcontact.Selected.Result in ResourcingContact.DisplayName, 'dd_progress-RC_status'.Selected.Value in {"Pending", "Approved", "Rejected", "Cancelled"} ) )

(Or)

SortByColumns(
Filter(
colAllItems,
If(
IsBlank(txt_progressRC_search.Text),
true,
txt_progressRC_search.Text in ID
) &&
If(
!IsBlank(cmb_progressRC_RCcontact.Selected.Result),
varRCFilter in ResourcingContact.DisplayName || varRCFilter in ResourcingDelegate.DisplayName
) &&
Switch(
dd_progress-RC_status.Selected.Value,
"Pending", SelectedStatus = "Awaiting",
"Approved", SelectedStatus = "Approved",
"Rejected", SelectedStatus = "Rejected",
"Cancelled", SelectedStatus = "Cancelled",
true, true
)
),
"ID",
SortOrder.Descending
)

Thanks!

If my response has been helpful in resolving your issue, I kindly request that you consider clicking "Accept as solution" and "giving it a thumbs up" as a token of appreciation.

[lalford09]

Thanks, both formulas give a couple errors, this is the second formula

The error is SelectedStatus isn't recognised

[Rajkumar_404]

Use Switch function 

Switch( dd_progress-RC_status.Selected.Value,
"Pending", "Awaiting", "Approved",
"Approved", "Rejected",
"Rejected", "Cancelled",
"Cancelled", true, false)


SortByColumns(
Filter(
colAllItems,
If(
IsBlank(txt_progressRC_search.Text),
true,
txt_progressRC_search.Text in ID
) &&
If(
!IsBlank(cmb_progressRC_RCcontact.Selected.Result),
varRCFilter in ResourcingContact.DisplayName || varRCFilter in ResourcingDelegate.DisplayName
) &&
Switch(
dd_progress-RC_status.Selected.Value,
"Pending", "Awaiting",
"Approved", "Approved",
"Rejected", "Rejected",
"Cancelled", "Cancelled",
true, false
)
),
"ID",
SortOrder.Descending
)

[lalford09]

I've updated it but seem to be getting a different error:

[Rajkumar_404]

Remove Boolean datatype true,true.

[lalford09]

That removed the error but the gallery is blank all the time now, so I have tried the first formula you sent but get this error:

 Here:

[lalford09]

I have tried this formula but the gallery is still showing as empty:

SortByColumns(
Filter(
colAllItems,
If(
IsBlank(txt_progressRC_search.Text),
txt_progressRC_search.Text in ID
) && If(
!IsBlank(cmb_progressRC_RCcontact.Selected.Result),
varRCFilter in ResourcingContact.DisplayName || varRCFilter in ResourcingDelegate.DisplayName
) && Switch(
dd_progress_RC_status.Selected.Value,
"Pending",
"Awaiting",
"Approved",
"Approved",
"Rejected",
"Rejected",
"Cancelled",
"Cancelled"
)
),
"ID",
SortOrder.Descending
)

[lalford09]

@Rajkumar_404 any ideas?

[Rajkumar_404]

Try this

Filter(
colAllItems,
If(
IsBlank(txt_progressRC_search.Text),
true,
txt_progressRC_search.Text in ID
),
If(
IsBlank(cmb_progressRC_RCcontact.Selected.Result),
true,
varRCFilter in ResourcingContact.DisplayName || varRCFilter in ResourcingDelegate.DisplayName
),
varSelectedStatus in 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"
)
)