Sequence in gallery, then patch

[AuburnMist]

Hi All, 

I'm working to design an app where managers and team leaders can input their employee's daily schedule for a full year (365 days) starting on the first day of the fiscal year (April 1st).  

I've been wracking my brain about how best to approach this challenge as some have standard Mon-Fri schedules of 7.5 hour-days and others have custom schedules where they may work Mon-Thu, 8 hours per day, then Friday 5.5 hours (for example). 

On step one (me designing the standard Mon-Fri schedules), I decided to use a gallery to create a sequence of number from 0-365 that display as dates, like this:

This is easy, but I need some help to patch this information to SharePoint.

For the Save button, I tried this:

ForAll(Sequence(365),Patch(Schedules,Defaults(Schedules), {Title:txtEmployeeName.Text, Date:DateValue(lblDate.Text), Hours:lblHours.Text,DayType:lblDayType.Text,Day:lblDay.Text}))

The issue I'm having is that the first record is patching 365 times, and not a separate record for each date and day of the week like in the gallery. This is what I get in SharePoint:

How can I patch 365 unique records that have a date for each day of the year, the corresponding weekday, hours of work, and day type? 

Thank you for any help you can provide. 

Also, if you can imagine a way to input a repeating custom schedule, that would be so amazing! (repeats of 7, 14, 21 or 28) 

[RusselThomas · Accepted solution]

Hi @AuburnMist ,

The reason it's not cycling the date for you is that your sequencer is the iterator, not the gallery items, so it cycles the sequence but always references the first gallery item.

If you're creating the view first (i.e. your gallery is based on a collection you've created already using Sequence), then there should be no reason to patch the source using Sequence() again because your data is already created.  Just patch the data.

If you don't need the view, then you can use the same logic you created the collection with to patch.

eg: let's assume your gallery is populated by a collection you created called myStagingCollection with the following columns that mimic your SPO list exactly;

Title

Date

Day

Hours

DayType

and let's assume you created it by using a Sequence() statement - something like this;

ClearCollect(myStagingCollection, ForAll(Sequence(365) As mySequence, {
    Title: "Angele", 
    Date: DateAdd(DateValue("03/31/2022"), mySequence.Value, Days), 
    Day: Text(DateAdd(DateValue("03/31/2022"), mySequence.Value, Days), "dddd"), 
    Hours: 7.5, 
    DayType: "WorkDay"
}))

Your save button could then be something like this;

Patch(Schedules, myStagingCollection)

As long as your collection columns are in the SharePoint list with the same names, it should be a quick bulk create.  If you don't need the gallery or the collection you can get rid of them and instead combine the two functions into a single button expression like this;

With({stagingTable: ForAll(Sequence(365) As mySequence, {
    Title: "Angele", 
    Date: DateAdd(DateValue("03/31/2022"), mySequence.Value, Days), 
    Day: Text(DateAdd(DateValue("03/31/2022"), mySequence.Value, Days), "dddd"), 
    Hours: 7.5, 
    DayType: "WorkDay"
    })
},
Patch(Schedules, stagingTable)
)

[Edit]

You can also build in logic to make sure only the relevant days of the week have hour allocations by using the Weekday() or similar function to detect what day of the week a particular iteration is;

With({stagingTable: ForAll(Sequence(365) As mySequence, {
    Title: "Angele", 
    Date: DateAdd(DateValue("03/31/2022"), mySequence.Value, Days), 
    Day: Text(DateAdd(DateValue("03/31/2022"), mySequence.Value, Days), "dddd"), 
    Hours: If(
              Weekday(DateAdd(DateValue("03/31/2022"), mySequence.Value, Days))=1 || 
              Weekday(DateAdd(DateValue("03/31/2022"), mySequence.Value, Days))=7, 0, 
              If(
                 Weekday(DateAdd(DateValue("03/31/2022"), mySequence.Value, Days))=6, 5.5, 7.5)), 
    DayType: If(
              Weekday(DateAdd(DateValue("03/31/2022"), mySequence.Value, Days))=1 || 
              Weekday(DateAdd(DateValue("03/31/2022"), mySequence.Value, Days))=7, "OffDay", 
              "WorkDay")
    })
},
Patch(Schedule, stagingTable)
)

Start of week is Sunday/1 by default, so for your example here if it's Sunday or Saturday it's an off day and if it's a Friday the hours are 5.5 instead of 7.5.

Hope this helps,

RT

[AuburnMist]

Russel, you are a frigging genius!! Wow! I had no idea I could forego the collection and the gallery to achieve this pattern! I'm so impressed. Thank you very much, especially for adding the additional code below to address the days off and the hours on specific week days! 

👏👏 

[AuburnMist]

Shoot, I forgot about something else! Hopefully I'm not pushing my luck here (lol), but may I ask if you can also resolve the issue with Statutory Holidays? These specific days need to be saved as "0" hours of work. 

I added these in a SharePoint list. Is there anyway to reference these inside of the code you provided in order to set those specific dates to "0" hours? 

Thank you, Thank you, Thank you! 🙂 

[RusselThomas]

Sure, just collect them first so the code doesn't have to do a lookup to a source each time.

For example:

ClearCollect(collectHolidays, 'Statutory Holidays 2022-2023')

 Then you can check iteration dates against holiday dates using the logic

DateAdd(DateValue("03/31/2022"), mySequence.Value, Days) in collectHolidays.Date

so your formula would look something like this;

With({stagingTable: ForAll(Sequence(365) As mySequence, {
    Title: "Angele", 
    Date: DateAdd(DateValue("03/31/2022"), mySequence.Value, Days), 
    Day: Text(DateAdd(DateValue("03/31/2022"), mySequence.Value, Days), "dddd"), 
    Hours: If(DateAdd(DateValue("03/31/2022"), mySequence.Value, Days) in collectHolidays.Date, 0, 
          If(
              Weekday(DateAdd(DateValue("03/31/2022"), mySequence.Value, Days))=1 || 
              Weekday(DateAdd(DateValue("03/31/2022"), mySequence.Value, Days))=7, 0, 
              If(
                 Weekday(DateAdd(DateValue("03/31/2022"), mySequence.Value, Days))=6, 5.5, 7.5))
       ), 
    DayType: If(DateAdd(DateValue("03/31/2022"), mySequence.Value, Days) in collectHolidays.Date, "Statutory Holiday",
          If(
              Weekday(DateAdd(DateValue("03/31/2022"), mySequence.Value, Days))=1 || 
              Weekday(DateAdd(DateValue("03/31/2022"), mySequence.Value, Days))=7, "OffDay", 
              "WorkDay")
           )
    })
},
Patch(Schedule, stagingTable)
)

 Kind regards,

RT

[AuburnMist]

Wow, this is soooo close now! Can you add in the means to show these holidays as "0" hours? 

[RusselThomas · Accepted solution]

Hi @AuburnMist ,

The code is in there - perhaps there's a typo somewhere?

Hours: If(DateAdd(DateValue("03/31/2022"), mySequence.Value, Days) in collectHolidays.Date, 0, 

The below seems to work, so the above should too;

DayType: If(DateAdd(DateValue("03/31/2022"), mySequence.Value, Days) in collectHolidays.Date, "Statutory Holiday",

I don't have a local version to test, so perhaps make sure all the parenthesis and whatnot are correct and in the right places. 

Kind regards,

RT

[AuburnMist]

So sorry, it was an error on my side! Your code was bang-on. Thanks again!!