Repeating array of specific numbers for employee schedules

[AuburnMist]

Hi folks, 

I have a really tough challenge here for someone!

I have little experience with creating math-based formulas in PowerApps. My objective calls for some math functions and I'm hoping someone here may be able to provide me some code.

I am trying to achieve a way for managers and team leaders to input their employees' schedules for a full year (365 days). I resolved how to achieve this when the schedule is fixed at 37.5 hours per week (Mon-Fri, 7.5 hrs/day, 5 days/week)

What I need next is to develop a way for them to input a custom schedule, which repeats either every 7 days, 14 days, 21 days or 28 days. 

Each of the cycles (7, 14, 21 or 28), must repeat over and over to fill 365 days.

For example:

Employee A may have a schedule in a 7-day cycle like this: 

Mon	Apr 4	8 hours
Tue	Apr 5	8 hours
Wed	Apr 6	8 hours
Thu	Apr 7	8 hours
Fri	Apr 8	5.5 hours
Sat	Apr 9	0 hours
Sun	Apr 10	0 hours
Total = 37.5 hrs/wk or 150 hrs/mth

Employee B may have a schedule in a 14-day cycle like this: 

Mon	Apr 4	8.5 hours
Tue	Apr 5	8.5 hours
Wed	Apr 6	8.5 hours
Thu	Apr 7	8 hours
Fri	Apr 8	8 hours
Sat	Apr 9	0 hours
Sun	Apr 10	0 hours
Mon	Apr 11	0 hours
Tue	Apr 12	8.5 hours
Wed	Apr 13	8.5 hours
Thu	Apr 14	8.5 hours
Fri	Apr 15	8 hours
Sat	Apr 16	0 hours
Sun	Apr 17	0 hours
Total = 75 hrs/biweekly or 150 hrs/mth

What I need is a text box where the manager or team leader can input the number of days in the schedule (7, 14, 21, or 28)

 Plus, another text box where they can input the starting date of the schedule

If the selection is a 7-day schedule, they need to then get 7 rows of data to input. If the selection is 14 days, they need to get 14 rows of data to input, etc. (always starting on a Monday).

Once they have entered the schedule in the appropriate number of days (cycle), then that cycle needs to automatically repeat for the remainder of the fiscal year (ends Mar 31).

I would like to use a gallery to achieve this – possibly by some type of sequence as in my regular Mon-Fri solution:

The Items property of my gallery is: ForAll(Sequence(365, 0), DateAdd(Date(2022,04,01), ThisRecord.Value, Days))

It has no individual controls to set cycles or hours of work.

To build a custom schedule, the user needs these controls and the gallery needs to reflect the cycle of hours against the appropriate dates.

I guess the first solution that is required is to find a code that will take this array and repeat it over and over from the start date selected to March 31st.

Example: 8, 8, 8, 8, 5.5, 0 ,0, (then repeat over and over as: 8, 8, 8, 8, 5.5, 0, 0)

Hoping someone knows how to write a code that will take a series of numbers and repeat them over and over. 

Fingers crossed that someone can help! 

Thanks a bunch! 

[RandyHayes]

@AuburnMist 

Yes, this is quite simple to do, but before mentioning how, I have a couple of questions:

1) If you are generating values for a whole year (and don't assume 365 days as you could be dealing with a leap year), then why would the user enter a starting date?

2) Do you really want the user to enter a number for the cycle?  What if they enter 5 as the cycle instead of 7 or 14, etc.?  I would suggest a dropdown instead, but want to know if that is an option or there is some other purpose for the text entry.

3) Is there any editing or manipulation of the hours for the days expected in your gallery, or is it just for display?  And if just for a display, do you really need to display it or can you skip the display and just go to data?

Let me know and I can provide an answer for you.

[AuburnMist]

Hi Randy, so glad it's you! You have helped me before tremendously and I enjoy your YouTube videos! 

You asked some great questions. 

1) If you are generating values for a whole year (and don't assume 365 days as you could be dealing with a leap year), then why would the user enter a starting date?

A) I never thought about leap years! I guess what I need are the daily schedules between the start of our fiscal and  the end of our fiscal (April 1 - March 31 annually). This could be 365 or 366. Hmm..how do I make a sequence between two days then as opposed to by number of days per year? The user may have to enter a starting date if the employee requests a custom schedule sometime during the fiscal year as opposed to right at the start of the fiscal year. They are allowed to request one at any time and the manager or team leader would need to modify it from the approval date onwards all the way to end of fiscal. 

2) Do you really want the user to enter a number for the cycle?  What if they enter 5 as the cycle instead of 7 or 14, etc.?  I would suggest a dropdown instead, but want to know if that is an option or there is some other purpose for the text entry.

A) We need to use 7 days as opposed to 5 because if the employee works overtime on a weekend, the team leader would enter the number of overtime hours worked (for ex: 4 hours on Saturday), and these 4 hours need to add to the employee's total hours for the cycle, manipulating the employee's production expectations upwards. The purpose of this whole schedule app is to feed into a production app. So I need to ensure there is a way to capture additional hours worked on any day of the week (and also, they will deduct hours for absences etc.) resulting in accurate production expectations based on total hours worked. A dropdown would be absolutely (and better even) then a text box. Good observation! 

3) Is there any editing or manipulation of the hours for the days expected in your gallery, or is it just for display?  And if just for a display, do you really need to display it or can you skip the display and just go to data?

A) There is no real need to display the employee's schedule for 365/366 days in a gallery. I just liked to provide a visual of the schedule after it was built to instill confidence in the user that they input it correctly. 

[RandyHayes]

@AuburnMist 

Glad to help out and lucky to be on the forum and see your post today.

So in regard to point #1 - don't worry about the leap year at this point, I already have that worked out for you.  

However, this statement - "The user may have to enter a starting date if the employee requests a custom schedule sometime during the fiscal year as opposed to right at the start of the fiscal year. " has some concerns:

1) You are implying an "edit" sort of ability for a specific period of the fiscal year...however, your concept is based on an entire year entry system, not s specific period.  In other words, you are generating a pattern to apply for the year - so how would the above statement relate to a specific period?  It would seem to me that what you are creating is a way to generate a year worth of records with hours values.  It would then make sense to me that you have an alternate method for modifying a portion of the schedule (or the schedule in general).  Ideally, you could do this by making the entire gallery editable, but this would depend some on how you are storing this information to determine the mechanism you need for that - but, quite possible to do.

For Point #2 - yes...go with a dropdown control.  This will save you a lot of grief!

For Point #3 - This really relates to what I just mentioned above in Point #1...you seem to imply in #1 an editing capability, but I am not getting a clear picture on that from you yet.  If the concept I mentioned above is applied (the ability to create (initially based on a pattern) AND edit the year schedule (based on random changes)) then the Gallery (or a more calendar-like display) would be just fine for this.

[AuburnMist]

Oh boy, now I'm starting to rethink this whole solution. YES, I definitely need a way to allow the schedule the be modified at any time during the year. The idea of using a sequence/pattern likely isn't going to work because one cannot edit a hard coded pattern, I assume.

I have been wracking my brain for a month on this, reading everything I can online, and watching a ton of videos and cannot find the best way to approach this. When I discovered the sequence function, I thought "there, that will get me on the right track", but alas, now I'm thinking I may have been incorrect. 

I'm totally stumped. 

I am trying to avoid the user having to input a separate entry for each day of the year (that would be preposterous!) The data must go into a SharePoint list in individual records in order for the complimenting solutions (leave/overtime app, and the PowerBI report) to be able to utilize the number of hours each and every day to calculate productive and non-productive hours and finally, performance results. 

Without the scheduled-hours-per-day piece, nothing can be achieved. 

Do you have a fantastic idea how I can make this happen!? (I bet you do!) 😉

[RandyHayes]

@AuburnMist 

Of course! 🙂  I was kind of "baking" that into my last response...

As I see it, your app would create a schedule for a person.  This would generate a table (gallery) that would be editable.  Initially, if there are no records for the selected person recorded in your list, then it simply creates the records based on the pattern and/or any changes made.  When saved, new records are created.

If the person already has records for the year chosen, then the user would see the records in the gallery (just like when creating) and can modify at that point as needed.  When saved records are updated.

Does that sound like a valid approach? 

[AuburnMist]

Yes, this sounds fantastic!

I wonder if its possible for me to have ONE solution in the app that could create the regular Mon-Fri schedule, OR any custom schedules. 

I have a good solution for the regular Mon-Fri schedule. 

I have this code on a button; 

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

With just the push of this button, a full year's schedule populates my SharePoint list. 

 Is there a way to integrate the solution you are envisioning (whatever that might be as I don't know yet lol), to work together with the above? I'm afraid that if an employee has a regular schedule entered, and then wants to change it to a custom schedule, that it will send new records to this list IN ADDITION to their previous Mon-Fri schedule. 

[RandyHayes]

@AuburnMist 

Well, that formula is a little easier to handle, and it can all be incorporated into the other pattern based.

So the remaining question then is, is the Title what ties the person to the records?  And, I assume you want this to function for any year besides just 2022 as it is hard-written into your formulas!?

[AuburnMist]

Yes, this one was easy(ish) because the pattern doesn't change. The Title is the employee's name who's schedule this belongs to. I will have a means for the user to indicate the person, and the code will be updated because currently it is hard-coded with my first name (not what I need, but I know that). Same with the year; I will have a dropdown where the user can select the appropriate fiscal year start (2022,03,31, or 2023,03,31 etc.)

Still a work in progress! 

🙂 

[RandyHayes]

@AuburnMist 

So here is the approach that seems to fit what you are looking for:

NOTE: for the below, there is one additional column needed for your SharePoint list - a numeric column called Year which will represent the fiscal year.

In the app:

3 dropdowns - one for the fiscal year, one for the person and one for the pattern.  (I will call them ddYear, ddPerson, ddPattern)

1 Gallery - to display a year worth of schedule for the fiscal year that will also allow editing of the hours per day. (I will call it galSchedule)

1 Save icon - to save all changes. (I will call it icnSave)

1 Trash Icon - to remove an entire schedule for the year selected (I will call it icnRemove)

For ddYear -

    Items property of Sequence(3, Year(Today()))  This will provide three years to choose from, adjust as needed.

For ddPerson -

    Items property of (Since we have not discussed logic for the person selection, it is just hard-written as) ["Angele", "Randy"]

For ddPattern -

    Items property of:

Table(
    {Value: 5, Pattern: Table({Day: [1, 2, 3, 4, 5], Hours: 7.5})},
    {Value: 7, Pattern: Table({Day: [1, 2, 3, 4], Hours: 8}, {Day: [5], Hours: 5.5})},
    {Value: 14, Pattern: Table({Day: [1, 2, 3, 9, 10, 11], Hours:8.5}, {Day: [4, 5, 12], Hours:8})}
)

    (NOTE: This only includes the patterns for 5, 7 and 14 day cycles...additional can be added)

    DisplayMode property of: 

If(LookUp(Schedules, Title=ddPerson.Selected.Value && Year=ddYear.Selected.Value, true), Disabled,  DisplayMode.Edit)

For galSchedule - 

    Items property of: 

With({_fiscalYear: Value(ddYear.Selected.Value), _fiscalStartMonth: 4},
    With({_items: Filter(Schedules, Year=_fiscalYear, Title=ddPerson.Selected.Value)},

        ForAll(Sequence(DateDiff(Date(_fiscalYear, 1, 1), Date(_fiscalYear+1, 1, 1)), 0),
            With({_date: DateAdd(Date(_fiscalYear, _fiscalStartMonth, 1), Value)},
                {Date: _date,
                 _Item: LookUp(_items, Date=_date)
                }
            )
        )
    )
)

The template of the Gallery contains 3 controls:  A label called lblDate, a label called lblDayType and a Textinput control called txtHours

The Text property of lblDate is: Text(ThisItem.Date, "mmmm dd, yyyy dddd")

The Text property of lblDayType is: If(IfError(Value(txtHours.Text), 0) =0, "Day of Rest", "Work Day")

The Default property of the txtHours controls is:

Coalesce(
    ThisItem._Item.Hours,

    With({_pattern: ddPattern.Selected.Pattern},
        Coalesce(LookUp(_pattern, Weekday(ThisItem.Date, StartOfWeek.MondayZero) + 1 in Day, Hours), 0)
    )
)

For icnSave - 

    OnSelect action of: 

Patch(Schedules,
    ForAll(Filter(Gallery3.AllItems, !(IfError(Value(txtHours.Text), 0) = _Item.Hours)),    
        {ID: _Item.ID,
         Title: ddPerson.Selected.Value,
         Date: Date,
         Year: Value(ddYear.Selected.Value),
         Day: Text(Date, "dddd"),
         Hours: Value(txtHours.Text),
         DayType: lblDayType.Text
        }
    )
)

For icnRemove -

    OnSelect action of:

Remove(Schedules, Filter(Schedules, Year=Value(ddYear.Selected.Value) && Title=ddPerson.Selected.Value).ID)

     DisplayMode property of:

If(ddPattern.DisplayMode=Edit, Disabled, Edit)

Putting it to work...

The app will allow you to select the fiscal year and the person.  The gallery will show all dates for the fiscal year (accounts for leap years too).

If there is no schedule recorded for that person for the fiscal year, then the pattern dropdown will be enabled and the user can select the pattern of hours to apply.  If there IS a schedule for the person for the fiscal year, then the gallery will be displaying what has been recorded previously (including any edits).

The user can edit any day and any hour amount if needed.

When the user hits save, the records are created if not existing already, or updated if already existing.

See how that works for you!