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! 

[AuburnMist]

Can't wait to recreate this! I'm on it and will report back in a bit! Thanks a million! 

[RandyHayes]

@AuburnMist 

No problem.  Let me know how it goes.

[AuburnMist]

I can't believe how quickly I was able to build that! This is remarkable. The only thing I forgot to tell you about the need to include statutory holidays and ensure those present "0" hours of work by default. 

I added all of the holidays for 2022-2023 in a SP list, and wonder now with the fact that your solution can display info by fiscal year, if I should add a column called "Year" there as well and add the holidays for the next 3 years or so. 

Is there a way to include the holidays somehow in one of your codes? 

[AuburnMist]

Ok, so after fully absorbing how this solution could function, I identified some concerns. 

I think its great how you created a dropdown for the user to select a pre-defined pattern of 5 days (which represents the standard Mon-Fri schedule perfectly), then a 7, a 14. The concern is that I provided only examples of some custom 7-day or 14-day patterns. In reality, the daily hours of work could be anything. Not just 8 or 8.5 or 5.5. It's whatever the employee requests, as long as it adds up to 75 hours every two weeks. (Combinations are kind of endless).

I would love a way for the team leader to first choose between two options; 1) "Standard Schedule", or 2)"Custom Schedule". If they pick Standard Schedule, then the pattern you created for the 5 days would be used. If they pick Custom Schedule, then they need the ability to pick the cycle for the custom schedule (7, 14, 21, or 28), and then be able to identify the hours each day to create the pattern. 

Is that possible? If so, the app would be perfect! 

[RandyHayes]

@AuburnMist 

Yes...always keep it super simple and PowerApps will be your friend!!

Sure thing on the holidays.  

I would suggest that instead of an individual list for each fiscal year, that you instead just have a single Statutory Holidays list.  Add a Fiscal Year numeric column to the list to indicate the year for it.

Then change the Formula on the ddYear Items property to:

ForAll(Sequence(3, Year(Today())), {Value: Value, Holidays: Filter('Statutory Holidays', FiscalYear=Value)})

Change the Text property of lblDayType to:

Coalesce(
    ThisItem._Holiday,
    If(IfError(Value(txtHours.Text), 0) =0, "Day of Rest", "Work Day")
)

(optional) Change the FontWeight property of lblDayType to:

If(!IsBlank(ThisItem._Holiday), Bold, FontWeight.Normal)

Change the Default property of the txtHours control to:

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

And...done!!

[RandyHayes]

@AuburnMist 

Ooops...forgot one!! (kind of important!)

Change the Items property of the gallery to:

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),
                 _Holiday: LookUp(ddYear.Selected.Holidays, Date=_date, Title)
                }
            )
        )
    )
)

[RandyHayes]

@AuburnMist 

So...I got a notification you replied and in that was the reply you made, but it is not showing up in this thread.  I will answer what you wrote that I read in that notice.

Well, the app was based on the premise that there was an actual pattern.  What you are looking for now is to break away from the pattern and have a custom pattern for the schedule.  The challenge with that is that the pattern was based on one week of time (7 days).  If you go to a custom pattern, then the question would be, is the user then customizing a week of time even for say a 14 day cycle?  In other words, if they were to choose a 14 day pattern, would then be altering the hours for each day in the 14 days (week 1 and week 2) or just the pattern of 7 days (week 1), which would then be applied to the following 7 days (week 2).  

I am guessing it is the former that you want - customize all 14 days (or 21 or whatever is chosen).

It's not impossible, but it changes the basis that the solution I provided works.  Because, as you will see in the Pattern table of each choice, it is only specifying days 1 to 7 (as that was the original pattern you showed).

Please clarify.

[AuburnMist]

Hi again,

Yes, I sent a reply at the exact same time that you sent a reply so likely it got missed. Sorry for the pain I may be causing you here! I did indicate this requirement in my original post, but it's possible it wasn't clear. 

"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. 

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)."

The rule in our organization is that employees may have custom schedules of any sort as long as it doesn't interfere with operational requirements and that the hours add up according to 37.5/week (7-day cycle), 75 hrs/biweekly (14-day cycle), 112.5 every 3 weeks (21-day cycle), or 150 hours every month (28-day cycle). This is what is making this scheduling app so hard to create. 😞 

For a 14-day cycle, the hours for each of the 10 business days must add up to 75. So it could be different amounts of hours each day for each of those 10 days... and so on for each of the other cycles I described. 

I feel so bad for not explaining this more clearly earlier. And I apologize for the work/solution you may have designed that now needs redesigning. Wish I could send you a thank-you gift after this! 

[RandyHayes]

@AuburnMist 

Of course I missed the reply - now I see it!  And of course I missed the original customize point in the original post!  I'm batting a thousand today 🙄  Having to bounce between so many things today has been a challenge (what's new?)

It's not really a problem to get what you are explaining now.  I just need to rethink your process a little bit as there are some patterns to your needs.  One little challenge I see is that although you are mentioning Monday as the start of the week, that does not necessarily correspond to the start of the fiscal year, so any starting days leading up to the first Monday need some special attention.

Let me review it again and see what I can come up with for you.  I've got to wrap up on another project at the moment, but will get you what you need.  And gifts are always welcome 😂

[AuburnMist]

Good morning Randy, I wanted to let you know some new information I just learned. Schedules can begin on any given day of the week (not just Mondays as I previously understood). So the fact that a Monday may not correspond with the start date of a fiscal year is not a concern. Thank you mentioning that in your post above. It triggered me to dig deeper. Hoping this new fact makes the coding more simple! Thank you 🙂