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 · Accepted solution]

@AuburnMist 

Okay...so I reconsidered your design to be more flexible.

After a good night of sleep - Here is how it goes:

App primary controls:

Dropdown for Fiscal Year - ddYear

Dropdown for Employee - ddPerson

Dropdown for Cycle - ddCycle

Gallery for Customization of Pattern - galPattern

Gallery for Schedule - galSchedule

Icon for clearing customizations - icnUndo

Icon for Saving Schedule - icnSave

Icon for removing fiscal year records - icnRemove

Html Text control for displaying calculation of total hours in customization pattern - htmlTotals

Properties:

ddYear

Items property: 

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

DisplayMode property: 

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

ddPerson

Items property (obviously this part needs more):

["Angele", "Randy"]

 DisplayMode property:

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

ddCycle

Items property:

Table(
    {Value: "Standard Week", Pattern: Table({Day: [1, 2, 3, 4, 5], Hours: 7.5}), Weeks: 1},
    {Value: "7 Day Cycle", Pattern: Table({Day: [1, 2, 3, 4], Hours: 8}, {Day: [5], Hours: 5.5}), Weeks: 1},
    {Value: "14 Day Cycle", Pattern: Table({Day: [1, 2, 3, 9, 10, 11], Hours:8.5}, {Day: [4, 5, 12], Hours:8}), Weeks: 2},
    {Value: "21 Day Cycle", Pattern: Table({Day: [3], Hours:8.5}, {Day: [1, 2, 4, 5, 8, 9, 10, 11, 12, 15, 16, 17, 18], Hours:8}), Weeks: 3},
    {Value: "28 Day Cycle", Pattern: Table({Day: [1, 2, 3, 9, 10, 11, 15, 16, 17, 22, 23, 24], Hours:8.5}, {Day: [4, 5, 12, 18, 25, 27], Hours:8}), Weeks: 4}
) 
/*
    
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}, {Day: [7], Hours:0})}
)*/

DisplayMode property:

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

galPattern

Items property:

With({_days: Calendar.WeekdaysLong()},
    ForAll(Sequence(ddCycle.Selected.Weeks * 7),
        With({_dayNumber: Mod(Value, 7)},
            {Week: RoundUp(Value/7, 0),
            DayName: Index(_days, _dayNumber + 1).Value,
            Day: If(_dayNumber=0, 7, _dayNumber),
            PatternSequence: Value,
            Hours: Coalesce(LookUp(ddCycle.Selected.Pattern, Value in Day, Hours), 0)
            }
        )
    )
)

DisplayMode property:

If(ddCycle.DisplayMode = Disabled && icnUndo.DisplayMode=Disabled, Disabled, Edit)

TemplateFill property:

Switch(Mod(ThisItem.Week, ddCycle.Selected.Weeks),
    1, LightSteelBlue,
    2, LightGray,
    3, LightCyan,
    0, LightGoldenRodYellow,
     RGBA(0, 0, 0, 0)
)

Two controls in the galPattern template - Label for day of week - lblPatDay, TextInput control for entry of customization - txtPatHours

lblPatDay Text Property: 

ThisItem.DayName

txtPatHours Default Property:

Coalesce(LookUp(ddCycle.Selected.Pattern, ThisItem.PatternSequence in Day, Hours), "0")

txtPatHours Reset property:

lclReset

galSchedule

Items property:

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),
                 _Week: Mod(WeekNum(_date, StartOfWeek.Monday) - WeekNum(Date(ddYear.Selected.Value, _fiscalStartMonth, 1), StartOfWeek.Monday), ddCycle.Selected.Weeks) + 1,
                 _Holiday: LookUp(ddYear.Selected.Holidays, Date=_date, Title),
                 _weekDay: Weekday(_date, StartOfWeek.Monday)
                }
            )
        )
    )
)

TemplateFill property:

Switch(Mod(ThisItem._Week, ddCycle.Selected.Weeks),
    1, LightSteelBlue,
    2, LightGray,
    3, LightCyan,
    0, LightGoldenRodYellow,
     RGBA(0, 0, 0, 0)
)

Four controls in galSchedule gallery template:

    Label for Date - lblDate

    Label for Day Type - lblDayType

    Text Input for hours changes - txtHours

    Rectangle icon control - to separate weeks - rctSep

lblDate Text Property:

Text(ThisItem.Date, "mmmm dd, yyyy  dddd")

lblDate Color Property:

If(ThisItem._weekDay in [6, 7], Blue, RGBA(0, 0, 0, 1))

lblDaytype Text Property:

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

lblDayType FontWeight Property:

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

txtHours Default Property:

Coalesce(
    ThisItem._Item.Hours,

    If(!IsBlank(ThisItem._Holiday), 0),
    LookUp(galPattern.AllItems, Week = ThisItem._Week && Day = ThisItem._weekDay, txtPatHours.Text)
 
)

rctSep Y property: 

Parent.TemplateHeight - Self.Height

rctSep Height Property:

1

rctSep Visible Property:

ThisItem._weekDay=7

icnUndo

OnSelect Action:

UpdateContext({lclReset:true}); UpdateContext({lclReset:false})

DisplayMode property:

If(CountRows(Filter(galPattern.AllItems, !(Value(txtPatHours.Text) = Hours)))>0, DisplayMode.Edit, Disabled)

icnSave

OnSelect Action:

Patch(Schedules,
    ForAll(Filter(galSchedule.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
        }
    )
)

icnRemove

OnSelect Action:

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

DisplayMode Property:

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

htmlTotals

HtmlText Property:

With({_weeks: GroupBy(galPattern.AllItems, "Week", "_days")},
    Concat(_weeks, "Week " & Week & ": <b>" & Sum(_days, Value(txtPatHours.Text)) & "</b> hrs<br>") & 
    "<hr>Total:<b> " & Sum(galPattern.AllItems, Value(txtPatHours.Text)) & "</b> hrs"
)

The look as I put it together for you (design as you see fit):

All of this does what you were looking for.

The user can select the fiscal year and person.  If the schedule for that person already exists, then it is displayed in the scheulde gallery and can be customized or removed (via trash icon).  Once changes are made, user can save updates.

If the schedule does not exist, then the user can select the cycle.  Each cycle has a "default" pattern to it.  That pattern is then shown in the pattern gallery.  The user can then alter the pattern while monitoring the totals in the totals area.  The schedule will update itself based on that pattern as they make the changes.  Once complete, the schedule can be further altered and saved, or just saved.

From the design standpoint - it is quite basic and just galleries of rows.  Ultimately I would have preferred a more "calendar-like" view of everything, but that is a future enhancement if you wish.

SO...lots of pieces to put together above, but put them in your app and see how things go.  I think that I covered all the properties, but if something doesn't work quite right, just let me know.

[AuburnMist]

I'm so excited! This sounds and looks amazing. I'm going to create it right now and will send you my feedback afterwards. Thanks a million! 

[RandyHayes]

@AuburnMist 

Go for it!!

[RandyHayes]

@AuburnMist 

Go for it!!

[AuburnMist]

This tool is meeting all my needs! I'm so thrilled! 

The only thing I cannot seem to get to work like yours, is the stat holidays piece. The hours remain at 7.5 (or whatever is input), instead of changing to 0, and the holiday name isn't displaying. 

I cannot figure out why.  Any ideas?

UPDATE: Nevermind! I got it! I had failed to enter the years in the new "FiscalYear" column in the SP List. All good now!! 

Woohoo, this app is perfect! 

[RandyHayes]

@AuburnMist 

Gald it is getting you where you want!

So, the holiday is governed in the Items property of the Schedule Gallery.  Specifically, this part:

It originates from the ddYear control in the Items property there:

If all the above is working, then each record in the Schedule Gallery will have a _Holiday column value. And then, the label text will reflect it with this formula:

SO...if there is no holiday showing, I would make sure you altered the Statutory Holidays list like I mentioned to include the fiscal Year.  Then try to hone in on one specific date and see if you can track back through the three areas above to determine where the disconnect is.

To try and hone in, you can put a temporary button on the screen and set the OnSelect to:

   Set(glbTest, ddYear.Selected.Holidays)

Perform that button action and then highlight the glbTest part in the formula and see what the editor is showing as the table that is there...it should have all the holidays from your list based on the year selected.

[AuburnMist]

Oh no! We got tangled up in communication again. I resolved the holiday issue. I edited my comment above. 

Everything is perfect now! I'm beyond grateful for all of your help. Have a wonderful day!! 😘

[RandyHayes]

@AuburnMist 

Ah yes, I didn't see the edit before I replied.  Oh well, a little extra info 🙂

So glad to help you out on this and that it is giving you what you need.  And, one of the things I strive for is to educate people that simplicity is the key in PowerApps!  Hopefully this is seeming quite simple and is giving you what you want without complication.  One of the things about PowerApps is that you really need to think through things in a much different way.

Anyway, again, happy to help and have a wonderful day as well!! 😁