cancel
Showing results for
Did you mean:
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)

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!

27 REPLIES 27
Super User

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),
{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.

_____________________________________________________________________________________
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!
Helper IV

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!

Super User

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

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!
Helper IV

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!

Super User

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.

_____________________________________________________________________________________
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!
Helper IV

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!! 😘

Super User

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!

Announcements

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

#### Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Top Solution Authors
Top Kudoed Authors
Users online (4,219)