Filtered Date Dropdown Values

[MauriceWhelan]

Hi.

I want to allow the user to search a Resource_Plan_Master File through a simple form with a [From Date] and a [To Date]. However if possible I would like to filter the [From Date] options to be the dates that correspond to each Monday in the current year and for the [To Date] options to correspond to each Friday in the current year.

Is this or something close to this possible and if so could you advise on what might be the best way to go about it?

Thank you.

[Suvrutt_Gurjar]

Hi @MauriceWhelan,

Could you please add if the Resource_Plan_Master File table that you wish to filter has date column that has dates for every single day of the year?

If so, I believe we could attempt to create a simpler expression for From and To Date filters.

[MauriceWhelan]

Hi @Suvrutt_Gurjar

The table wouldn’t have dates for every single day of the year.

[Suvrutt_Gurjar]

Hi @MauriceWhelan,

Thank you. My proposal was on exactly on similar lines as @Steve. That is why I requested whether there are dates for all 365 days.

As @Steve, guided, since you do not have dates for all days, whenever a Monday or Friday date is missing in source table, you will not have that date in the From/To date drop down selection.

[Steve]

Date of each distinct Monday of this year used in date-column:

SELECT(
  table[date-column],
  AND(
    (WEEKDAY([date-column]) = 2),
    (YEAR([date-column]) = YEAR(TODAY())
  ),
  TRUE
)

Date of each distinct Friday of this year used in date-column:

SELECT(
  table[date-column],
  AND(
    (WEEKDAY([date-column]) = 6),
    (YEAR([date-column]) = YEAR(TODAY())
  ),
  TRUE
)

If date-column doesn’t include a Monday, that Monday won’t show up; ditto for a Friday.

[MauriceWhelan]

Thank you so much for this @Steve

[OptimiX_XcrY]

I, as a non expert, would create a second read only table with two dates columns, Mondays and Fridays, and use those columns in dropdowns.

This way I could select the third Monday from the current yeard, and the fifth friday frim the current year.

Another way, a second read only table with one Dates column, Mondays, and a dropdown for monday start date, and a field for number of weeks, the friday end date being autocalculated with a formula like :
[startddate]+7*[field]-3

If you only want a weeks filter, the enddate would be [startdate]+5

[Suvrutt_Gurjar]

Correct , the option mentioned by @OptimiX_XcrY is also possible. It will require a dates lookup table. You may either create the table for next next few years at a time or revise it yearly for one year the end of every year.

Another option could be use simple date type columns for both “From Date” and 'End Date" with Valid_if such as

AND(
(WEEKDAY([_THIS]) = 2),
(YEAR([_THIS]) = YEAR(TODAY())
)

for “From Date”

and

AND(
(WEEKDAY([_THIS]) = 6),
(YEAR([_THIS]) = YEAR(TODAY())
)
for “To date” column.

This will be a bit less user friendly but easier from app creator and maintenance point of view.

Both “From Date” and “To Date” will provide calendar date drop downs.

For “From Date” User can pick any dates but any dates other than Mondays in current year will be invalid , so user will need to necessarily pick up a Monday in current year.

Similarly for “To date” user can pick up only Fridays in current year as valid dates.

[MauriceWhelan]

Thanks a million @Suvrutt_Gurjar and @OptimiX_XcrY for your help with this.