Recurring Dates SharePoint Lists

[JordanDeVan]

Recently a need was identified to create a calendar using SharePoint/Microsoft Lists as a datasource. Normally, this would be an easy undertaking as you would simply create a column for start date and a column for end date. As most of us know, nothing is as simple as it ever seems. The business requirement was that an application should be created that limits the number of bookings that can be created in a day (due to covid and social distancing guidelines) and recurring dates are a must. Restricting the number of bookings was easy and to an extent recurring events was simple until it came to creating logic for recurring events on a weekly basis. With a little help from different examples in the community, I was able to find a way to set up recurring events while using SharePoint/Microsoft Lists as a data source.

To start create a SharePoint List with two datetime columns one column will function as your start time and the other as your end time. 

In PowerApps you'll need a control to indicate whether the event is recurring and a control to define the type of recurring event, a label, a button to pass the results to a collection, two data tables to display the results in the collections, and a button to submit the values in the collection to our SharePoint List. Reasonably, we can do without the extra button and data tables and workout all our formulas on one button, for testing purposes utilizing the data tables is a great exercise for visibility to our collections. I used a toggle control for the true/false and a drop to define the type of event. Additionally, the dropdown visibility was set to the value of the toggle and the Items Property was set to ["Daily","Weekly","Monthly","Yearly"].

Next, you'll need two Date Picker Controls, similar to list, one to act as the start date and the other to function as the end date. In the label set the text property to 

Switch(Weekday(startDate.SelectedDate),1,"Sunday",2,"Monday",3,"Tuesday",4, "Wednesday",5,"Thursday",6,"Friday", 7,"Saturday"),
If you are unfamiliar with the weekday function please visit 

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-datetime-parts.
I used a switch function to extrapolate the name of the weekday as the datepicker date changes. In production, the label should be hidden, for testing we will keep the label visible.

On the first button in the OnSelect property add the following formula's

With(
    {drop: Dropdown1.Selected.Value},
    With(
        {
            change: If(
                drop = "Daily",
                Days,
                drop = "Monthly",
                Months,
                drop = "Yearly",
                Years,
                drop = "Weekly",
                Days
            )
        },
        ClearCollect(
            DateRange,
            AddColumns(
                FirstN(
         
                    //Using Sequence to capture the number of weeks, days, months, or years to create the number of rows in the collection
                    Sequence(
                        DateDiff(
                            startDate.SelectedDate,
                            endDate.SelectedDate,
                            change
                        )+ If(change ="Yearly", 2,1)
                    ),
                    DateDiff(
                        startDate.SelectedDate,
                        endDate.SelectedDate,
                        change
                    ) + If(change ="Yearly", 2,1)
                ),
                //The day column list the number of days based on the size of the collection
                "Day",
                "Day " & (Value + 1),
                //Collects all of the dates in the range
                "Date1",
                
                DateAdd(
                    startDate.SelectedDate,
                    Value - 1,
                    change
                ),
                "End Date",
                endDate.SelectedDate
            )
        )
    )
);
ClearCollect(
    dateRange2,
    AddColumns(
        DateRange,
        "DayofWeek",
        Weekday(DateTimeValue(Text(Date1))),
        "FlatDay",
        Switch(
            Weekday(DateTimeValue(Text(Date1))),
            1,
            "Sunday",
            2,
            "Monday",
            3,
            "Tuesday",
            4,
            "Wednesday",
            5,
            "Thursday",
            6,
            "Friday",
            7,
            "Saturday"
        )
    )
);
//Creating The Collection for the weekly scheduled items.
ClearCollect(
    dateRange3,
    AddColumns(
        dateRange2,
        "FlatWeekday",
        FlatDay = Label13.Text
    )
);
ClearCollect(dateRange4, Filter(dateRange3,FlatWeekday = true))

The DateRange Collection captures all the dates in a specified range. The range is defined using DateDiff, the differences in the dates is calculated by difference between the start and end dates. You will notice that the formula to create the collection is encapsulated in two With functions, this was done for readability and I try to make my code as dynamic as possible just in case any changes need to be made in the future, instead of going line by line I can make my changes in one area.
The top level With function takes the value of the DropDown, I then start another With to create an If function based on the value of the first With, these values will be used as part of the parameters to specify the DateDiff. 

You will notice that the selected values in the If function Show Days if the selected value is "Daily", Months for "Monthly", Years for "Yearly", and Days for "Weekly". I used Days for Weekly because when using DateDiff there are no options for defining Weeks. Next the first collection is created, the first thing is to define the number of rows in the collection, we do this by using Sequence the amount of records in this Sequence are defined by the DateDiff between the start date, ending date, and the type of recurring event, the count is specified in the same way. The next column added to the collection is the Day column, this column is more for show and is not necessary. The Date1 column is where a lot of the magic will happen. For the specified range all of the dates in that range will be collected. The End date column is to specify the ending date of the range.

On to the next collection DateRange2. To create this collection we will take the DateRange collection and add two columns to it.  DayofWeek and FlatDay. The DayofWeek column will take the value of the Date1 column and convert the datetime value to a WeekDay value this column isn't necessary, but for testing purposes and I've included it, however the FlatDay column is. The FlatDay column operates the same way the DayofWeek column does, with some additional functions. In the FlatDay column we get the use a switch to get the WeekDay integer then mapping it to the corresponding date name.

Next we create collection 3 -- dateRange3 by collecting dateRange2 and adding a column to compare the start date name to the WeekDay name in our label this is how we grab dates that from a range that are scheduled weekly. If the the FlatWeekDay is true it matches the start date. It is important to remember that when an event is a weekly recurring event it will happen weekly on the weekday that it was scheduled so if it was scheduled on a Monday we should look for it to happen on Monday.

The next collection in the lineup is dateRange4, all we're doing here is collecting dateRange3 and grabbing the dates with a FlatWeekDay equal to true.

For testing purposes I have added two data tables, the first data table on the left holds all of the dates in the range and the items property is the dateRange3 collection. The second data table on the right holds all values from dateRange4 that recur weekly.

Using the last submit button we will patch our records to our SharePoint list. To do this we will be working with the dateRange3 and dateRange4 collections using If and ForAll functions. 

If(Not(Dropdown1.SelectedText.Value ="Weekly"),ForAll(dateRange3, Patch(Calendar,{Title:"New Stuff",startTime:ThisRecord.Date1, endTime:ThisRecord.'End Date'})),ForAll(dateRange4, Patch(Calendar,{Title:"Weekly",startTime:ThisRecord.Date1, endTime:ThisRecord.'End Date'})))