Filter gallery by items with today's date or older?

Like (0) Share

Report

Posted on 6 Mar 2023 14:02:52 by Mojpage

103

Hi there,

I'd like to be able to filter this top gallery by the HandoverDate column if desired. I'd to add a filter that'll only show items with a handover date of today or older. I've already got the date turning colour to red if this criteria is met, so I'm sure there's cheet with the colour if another way is too messy. But ideally i'd do it properly. Thanks!

I have the same question (0)

All responses (4)

Answers (1)

Verified answer

Mojpage 103 on 06 Mar 2023 at 16:22:26

Like (0)

Report

Re: Filter gallery by items with today's date or older?

This worked, thank you. I actually want to add another for DueDate column too. How would I add another, making it so that they could both be applied, or either one could be, or neither?

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on 06 Mar 2023 at 14:43:32

Like (1)

Report
Re: Filter gallery by items with today's date or older?

@Mojpage

With({_items:Switch( Dropdown1.Selected.Value, "FASS", If(IsBlank(DataTableFilterStatus.Selected), Filter(ItemsFass, Title = ComboBox6_2.Selected.Result, Presentation = ComboBox7_2.Selected.Result), Filter(ItemsFass, Title = ComboBox6_2.Selected.Result, Presentation = ComboBox7_2.Selected.Result, HandoverStatus.Value = DataTableFilterStatus.Selected.Value)), "FBL", If(IsBlank(DataTableFilterStatus.Selected), Filter(ItemsFBL, Title = ComboBox6_2.Selected.Result, Presentation = ComboBox7_2.Selected.Result), Filter(ItemsFBL, Title = ComboBox6_2.Selected.Result, Presentation = ComboBox7_2.Selected.Result, HandoverStatus.Value = DataTableFilterStatus.Selected.Value)), "STEM", If(IsBlank(DataTableFilterStatus.Selected), Filter(ItemsSTEM, Title = ComboBox6_2.Selected.Result, Presentation = ComboBox7_2.Selected.Result), Filter(ItemsSTEM, Title = ComboBox6_2.Selected.Result, Presentation = ComboBox7_2.Selected.Result, HandoverStatus.Value = DataTableFilterStatus.Selected.Value)), "WELS", If(IsBlank(DataTableFilterStatus.Selected), Filter(ItemsWELS, Title = ComboBox6_2.Selected.Result, Presentation = ComboBox7_2.Selected.Result), Filter(ItemsWELS, Title = ComboBox6_2.Selected.Result, Presentation = ComboBox7_2.Selected.Result, HandoverStatus.Value = DataTableFilterStatus.Selected.Value)), "OTHER", If(IsBlank(DataTableFilterStatus.Selected), Filter(ItemsOTHER, Title = ComboBox6_2.Selected.Result, Presentation = ComboBox7_2.Selected.Result), Filter(ItemsOTHER, Title = ComboBox6_2.Selected.Result, Presentation = ComboBox7_2.Selected.Result, HandoverStatus.Value = DataTableFilterStatus.Selected.Value)), If(IsBlank(DataTableFilterStatus.Selected), ItemsFass, Filter(ItemsFass, HandoverStatus.Value = DataTableFilterStatus.Selected.Value)) // default value with HandoverStatus filter )}, Filter(_items, !Checkbox1.Value || Handoverdate<=Today()) )

Was this reply helpful? Yes No
Mojpage 103 on 06 Mar 2023 at 14:38:11

Like (0)

Report

Re: Filter gallery by items with today's date or older?

I should have mentioned that the gallery uses the Switch formula to choose from 5 different data sources. Not sure where/how to add in the checkbox formula to this!:

Switch(
Dropdown1.Selected.Value,
"FASS", If(IsBlank(DataTableFilterStatus.Selected), Filter(ItemsFass, Title = ComboBox6_2.Selected.Result, Presentation = ComboBox7_2.Selected.Result), Filter(ItemsFass, Title = ComboBox6_2.Selected.Result, Presentation = ComboBox7_2.Selected.Result, HandoverStatus.Value = DataTableFilterStatus.Selected.Value)),
"FBL", If(IsBlank(DataTableFilterStatus.Selected), Filter(ItemsFBL, Title = ComboBox6_2.Selected.Result, Presentation = ComboBox7_2.Selected.Result), Filter(ItemsFBL, Title = ComboBox6_2.Selected.Result, Presentation = ComboBox7_2.Selected.Result, HandoverStatus.Value = DataTableFilterStatus.Selected.Value)),
"STEM", If(IsBlank(DataTableFilterStatus.Selected), Filter(ItemsSTEM, Title = ComboBox6_2.Selected.Result, Presentation = ComboBox7_2.Selected.Result), Filter(ItemsSTEM, Title = ComboBox6_2.Selected.Result, Presentation = ComboBox7_2.Selected.Result, HandoverStatus.Value = DataTableFilterStatus.Selected.Value)),
"WELS", If(IsBlank(DataTableFilterStatus.Selected), Filter(ItemsWELS, Title = ComboBox6_2.Selected.Result, Presentation = ComboBox7_2.Selected.Result), Filter(ItemsWELS, Title = ComboBox6_2.Selected.Result, Presentation = ComboBox7_2.Selected.Result, HandoverStatus.Value = DataTableFilterStatus.Selected.Value)),
"OTHER", If(IsBlank(DataTableFilterStatus.Selected), Filter(ItemsOTHER, Title = ComboBox6_2.Selected.Result, Presentation = ComboBox7_2.Selected.Result), Filter(ItemsOTHER, Title = ComboBox6_2.Selected.Result, Presentation = ComboBox7_2.Selected.Result, HandoverStatus.Value = DataTableFilterStatus.Selected.Value)),
If(IsBlank(DataTableFilterStatus.Selected), ItemsFass, Filter(ItemsFass, HandoverStatus.Value = DataTableFilterStatus.Selected.Value)) // default value with HandoverStatus filter
)

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on 06 Mar 2023 at 14:23:17

Like (0)

Report

Re: Filter gallery by items with today's date or older?

@Mojpage

You could add a checkbox above the gallery that if checked, filters the gallery to show today's items or older by adding the filter to the gallery's items property.

Filter(yourtable, !Checkbox1.Value || Handoverdate<=Today())

Was this reply helpful? Yes No