Power Platform Community Forum Thread Details

Hello I hope you are well, I am developing an app based on the PowerApps "Leave Request" template, unfortunately I am stuck. My problem is the following: I have a record of employees in an excel table, this record contains the day in which they started working in the company and the vacation days have been assigned the problem is that annually from the date in which they started working the days assigned vacations must be restarted with the possibility of saving only 5 days, for example: The employee Robert was assigned 10 days a year of which he only spent 3, which would result in a balance of 7 days but since he has completed his year in the company he can only save 5 days and he is assigned 10 more having a new result 15 days total in the new year. How can I develop this to be done automatically according to the current day and month for all employees at the same time?
Can i get some help with this??
Thank you very much.

Categories:

Building Power Apps

I have a sample app attached, that I built from inspiration of your request. I feel like there may be common needs to calculate running totals, and conditional minimums, so I figured I'd make something that sort of meets your needs, but will hopefully show you a few ways to possible do what you're looking to do. Please let me know if you have any questions about how this applies to your exact scenario.

For my app, I ended up creating a reference Collection of 12 rows. This is a useful approach if you ever need to repeat an action X times, as it gives you a simple list to execute a ForAll function against.

ClearCollect(colPTOChances,[1,2,3,4,5,6,7,8,9,10,11,12])

Then I created a Button which would let me generate records for this example. You will have data, so only parts of this function are useful - notably, conditional filtering, using a Max or Min function, etc.

// Set local variable to add a new Year, one greater than current max
UpdateContext(
 {
 locYear: Max(
 colYear,
 yearID
 ) + 1
 }
);
// Collect the new Year
Collect(
 colYear,
 {
 yearID: locYear,
 yearValue: Year(
 DateAdd(
 Now(),
 locYear * -1,
 Years
 )
 ),
 yearDaysRemaining: 0,
 yearDaysRunningTotal: 0
 }
);
// For all 12 opportunities in a year, 50% chance of taking a day off, with random date in that year
ForAll(
 colPTOChances,
 Collect(
 colPTO,
 {
 ptoTaken: Rand() > .5,
 ptoDate: Date(
 Year(
 DateAdd(
 Now(),
 locYear * -1,
 Years
 )
 ),
 Round(
 Rand() * 12,
 0
 ),
 Round(
 Rand() * 28,
 0
 )
 )
 }
 )
);
// Count how many leftover Days from the year, after determining how many days were PTO days
UpdateIf(
 colYear,
 yearID = locYear,
 {
 yearDaysRemaining: Min(
 5,
 10 - CountRows(
 Filter(
 colPTO,
 Year(ptoDate) = yearValue,
 ptoTaken
 )
 )
 )
 }
);
// Trigger Recalculation
Select(btnYearRecalculate)

I also have a Button which cleans up the two Collections which had added to them when 'Add a Year' was selected.

// Remove PTO Rows
RemoveIf(
 colPTO,
 Year(ptoDate) = LookUp(
 colYear,
 yearID = Max(
 colYear,
 yearID
 ),
 yearValue
 )
);
// Remove Year
RemoveIf(
 colYear,
 yearID = Max(
 colYear,
 yearID
 )
);
// Trigger Recalculation
Select(btnYearRecalculate)

Both of the blocks above will trigger a recalculation of the running totals. This is a function on a separate Button.

// Create a temporary loop collection, based on colYear
ClearCollect(
 colYearLoop,
 RenameColumns(
 ShowColumns(
 colYear,
 "yearID",
 "yearValue"
 ),
 "yearID",
 "loopNo",
 "yearValue",
 "loopValue"
 )
);
// Use that loop, to update each record in colYear, with the Remaining days from prior years and current year
ForAll(
 colYearLoop,
 UpdateIf(
 colYear,
 loopNo = yearID,
 {
 yearDaysRunningTotal: Sum(
 Filter(
 colYear,
 yearValue <= loopValue
 ),
 yearDaysRemaining
 )
 }
 )
)

As we add Years, we are emulating what it would be like to have multiple Years, with varying PTO taken. This way, you can see how several concepts, including conditional filtering, collecting, and recalculating are implemented.

PowerUser - Tracker 1.png

PowerUser - Tracker 2.png

PowerUser - Tracker 3.png

Above are some screenshots of the app in action, and I have attached it for reference.