I have 2 classic calendar controls which I want to implement population of default value on app start (once) based on the scenarios below.
1) Set 'fromDate_Dp' as next working day
- exclude weekends and dates from a collection of Public Holidays
2) Set 'toDate_Dp' as 'fromDate_Dp' + 2 working days
- exclude weekends and dates from a collection of Public Holidays
3) Sudo code for above scenarios
//Exclude ToDate from Weekends to find NextWorkingDay
1a. If [Weekday(Today() + 1)] Not In [1,2,3,4,5], Increment [(Today() + 1)] by 1
1b. If [Weekday(Today() + 1)] In [1,2,3,4,5], SetContext [NextWorkingDate]:[Today() + N]
//Get Public Holidays collection
2. ClearCollect [ServiceCalendarCol] from [ServiceCalendar.View] where [ServiceCalendar.View.Date] >= [NextWorkingDate]
//Exclude ToDate from Public Holidays, and set FromDateVal*
3a. If [NextWorkingDate] Not In [ServiceCalendarCol.Date], SetContext[FromDateVal]:[NextWorkingDate]
3b. If [NextWorkingDate] In [ServiceCalendarCol.Date], Increment [NextWorkingDate] by 1
//Set temp_ToDateVal as 2 days after FromDateVal
4. SetContext[temp_ToDateVal]:[FromDateVal + 2]
//Exclude temp_ToDate from Weekends
5a. If [Weekday(temp_ToDateVal)] Not In [1,2,3,4,5], Increment [temp_ToDateVal] by 1
5b. If [Weekday(temp_ToDateVal + N)] In [1,2,3,4,5], SetContext [temp_ToDateVal]:[temp_ToDateVal + N]
/Exclude temp_ToDate from Public Holidays, and set ToDateVal*
6a. If [temp_ToDateVal] Not In [ServiceCalendarCol.Date], SetContext [ToDateVal]:[temp_ToDateVal]
6b. If [temp_ToDateVal] In [ServiceCalendarCol.Date], Increment [temp_ToDateVal] by 1
I'm also using classic calendar controls, so it utilizes .Value instead of .SelectedDate.
It only needs to trigger once on app start, or screen load.
I appreciate if anyone could formulate it. Thank you very much 😥
__________________________________________________________________________________________________________________________________
Below is my existing ClearCollect formula than I'm using to store all dates (exclude weekends and dates in Public Holiday collection) for reference of field names and such. (Not related to the logic of the objective of this post and scenarios above)
//Store selected dates
If (
((!IsBlank(FromDate_Dp.Value) && !IsBlank(ToDate_Dp.Value)) && (FromDate_Dp.Value <= ToDate_Dp.Value)),
With(
{
//Generate a one-column table of all dates between start date & end date
varDateRange: ForAll(
Sequence(ToDate_Dp.Value - FromDate_Dp.Value + 1),
FromDate_Dp.Value + Value - 1
)
},
//Store Working Day Dates (Exclude weekends + Public Holidays)
ClearCollect(
WorkingDayDates_BetweenSelectedDates,
Filter(
varDateRange,
And(
Weekday(Value) in [ 1, 2, 3, 4, 5 ],
Not(
Value in Filter(
'Service Calendars',
'Service Calendars (Views)'.'Active Service Calendars'
).Date
)
)
)
);
//Store Public Holiday Dates and Name Between Selected Dates
ClearCollect(
ServiceCalendarDateAndName_BetweenSelectedDates,
AddColumns(
Filter(
varDateRange,
Value in Filter(
'Service Calendars',
'Service Calendars (Views)'.'Active Service Calendars'
).Date
),
"Name", LookUp('Service Calendars', Date = Value).Name
)
);
)
);
