cancel
Showing results for
Did you mean:
Resolver I

## 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
Resolver I

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

Super User

No problem.  Let me know how it goes.

_____________________________________________________________________________________
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!
Resolver I

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?

Resolver I

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!

Super User

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

_____________________________________________________________________________________
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

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

_____________________________________________________________________________________
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

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

_____________________________________________________________________________________
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!
Resolver I

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!

Super User

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 😂

_____________________________________________________________________________________
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!
Resolver I

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 🙂

Announcements

#### Announcing | Super Users - 2023 Season 1

Super Users – 2023 Season 1    We are excited to kick off the Power Users Super User Program for 2023 - Season 1.  The Power Platform Super Users have done an amazing job in keeping the Power Platform communities helpful, accurate and responsive. We would like to send these amazing folks a big THANK YOU for their efforts.      Super User Season 1 | Contributions July 1, 2022 – December 31, 2022  Super User Season 2 | Contributions January 1, 2023 – June 30, 2023    Curious what a Super User is? Super Users are especially active community members who are eager to help others with their community questions. There are 2 Super User seasons in a year, and we monitor the community for new potential Super Users at the end of each season. Super Users are recognized in the community with both a rank name and icon next to their username, and a seasonal badge on their profile.    Power Apps  Power Automate  Power Virtual Agents  Power Pages  Pstork1*  Pstork1*  Pstork1*  OliverRodrigues  BCBuizer  Expiscornovus*  Expiscornovus*  ragavanrajan  AhmedSalih  grantjenkins  renatoromao    Mira_Ghaly*  Mira_Ghaly*      Sundeep_Malik*  Sundeep_Malik*      SudeepGhatakNZ*  SudeepGhatakNZ*      StretchFredrik*  StretchFredrik*      365-Assist*  365-Assist*      cha_cha  ekarim2020      timl  Hardesh15      iAm_ManCat  annajhaveri      SebS  Rhiassuring      LaurensM  abm      TheRobRush  Ankesh_49      WiZey  lbendlin      Nogueira1306  Kaif_Siddique      victorcp  RobElliott      dpoggemann  srduval      SBax  CFernandes      Roverandom  schwibach      Akser  CraigStewart      PowerRanger  MichaelAnnis      subsguts  David_MA      EricRegnier  edgonzales      zmansuri  GeorgiosG      ChrisPiasecki  ryule      AmDev  fchopo      phipps0218  tom_riha      theapurva  takolota     Akash17  momlo     BCLS776  Shuvam-rpa     rampprakash  ScottShearer     Rusk  ChristianAbata     cchannon  Koen5     a33ik        AaronKnox        Matren        Alex_10        Jeff_Thorpe        poweractivate        Ramole        DianaBirkelbach        DavidZoon        AJ_Z        PriyankaGeethik        BrianS        StalinPonnusamy        HamidBee        CNT        Anonymous_Hippo        Anchov        KeithAtherton        alaabitar        Tolu_Victor        KRider        sperry1625        IPC_ahaas      zuurg    rubin_boer   cwebb365      If an * is at the end of a user's name this means they are a Multi Super User, in more than one community. Please note this is not the final list, as we are pending a few acceptances.  Once they are received the list will be updated.

#### Microsoft Power Platform Conference | Registration Open | Oct. 3-5 2023

We are so excited to see you for the Microsoft Power Platform Conference in Las Vegas October 3-5 2023! But first, let's take a look back at some fun moments and the best community in tech from MPPC 2022 in Orlando, Florida.   Featuring guest speakers such as Charles Lamanna, Heather Cook, Julie Strauss, Nirav Shah, Ryan Cunningham, Sangya Singh, Stephen Siciliano, Hugo Bernier and many more.   Register today: https://www.powerplatformconf.com/

#### Check out the new Power Platform Communities Front Door Experience!

We are excited to share the ‘Power Platform Communities Front Door’ experience with you!   Front Door brings together content from all the Power Platform communities into a single place for our community members, customers and low-code, no-code enthusiasts to learn, share and engage with peers, advocates, community program managers and our product team members. There are a host of features and new capabilities now available on Power Platform Communities Front Door to make content more discoverable for all power product community users which includes ForumsUser GroupsEventsCommunity highlightsCommunity by numbersLinks to all communities Users can see top discussions from across all the Power Platform communities and easily navigate to the latest or trending posts for further interaction. Additionally, they can filter to individual products as well.       Users can filter and browse the user group events from all power platform products with feature parity to existing community user group experience and added filtering capabilities.     Users can now explore user groups on the Power Platform Front Door landing page with capability to view all products in Power Platform.    Explore Power Platform Communities Front Door today. Visit Power Platform Community Front door to easily navigate to the different product communities, view a roll up of user groups, events and forums.