cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AuburnMist
Helper IV
Helper IV

Repeating array of specific numbers for employee schedules

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)

 

AuburnMist_0-1651760607366.png

 

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

 

AuburnMist_1-1651760607367.png

 

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:

 

AuburnMist_2-1651760607372.png

 

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! 

 

27 REPLIES 27
RandyHayes
Super User
Super User

@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):

RandyHayes_0-1651846791777.png

 

 

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.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

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
Super User
Super User

@AuburnMist 

Go for it!!

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
RandyHayes
Super User
Super User

@AuburnMist 

Go for it!!

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

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
Super User
Super User

@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:

RandyHayes_0-1651852183365.png

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

RandyHayes_1-1651852247076.png

 

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:

RandyHayes_2-1651852342966.png

 

 

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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

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
Super User
Super User

@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!! 😁

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Helpful resources

Announcements
Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Users online (3,194)