Creating a List of All Employees Leaves and Calculating their Balances in PowerApps

[MH3]

Hi All,

I have a leave request application, in which user selects a leave type and then create a leave request and then a flow runs and sent to his/her Manager for Approvals.

But Now, I have to add the ability of Total Leaves, Sick Leaves, Casual Leaves and their balances and restriction for submitting the request if their requested days are greater than the balance.

Like this: 

first time when the user creates a request (depends on Annual Leave, sick leave or any type of leave). this can be updated using flow when the user submits the request

• Total Annual Leave field will be 14 (by default) and current balance is also 14.
• say if Annual Request for 2 days
  • CurrAnnualLeavebalance field is equal to 14-2=12 days. (current balance - days requested)

next time for Annual Leave Request

• Again Total Annual Leave should be 14(no change) and current balance will be 12 days
• but if the next request is for 13 days.(in the form itself you can calculate (current balance - requested days)>0 then only proceed or else show message the current balance and disable the save button)
  
  How can I acheive the above requirement? and I need to create a list of all employees with their leaves and balance columns and a ID which will match up with my LeaveRequest List (where all  the LeaveRequests of all employees are saved).
  Need to create a relation between the two lists to perform calculation of that particular user.

AnyHelp? 

[pagonza]

Hello @MH3 

Have you use the test to use the life request app? I think this may can help you in this app

Please go to Create -> templates, and you can open download the app. 

Also, I think you can create a entity on CDS with you employs and his data of holidays.

You can do easy calculation in the number fields, also for this requirement

"if the next request is for 13 days.(in the form itself you can calculate (current balance - requested days)>0 then only proceed or else show message the current balance and disable the save button)"

On the on select of the botton  that performance the submit vacation's you can do something like this:

First you can create on the onstart one variable set( visible ,false)

Then create a label, with "you can do this, you haven't enough vacations", and on the propertie visible change the value to the varible visible.

If((current balance - requested days)>0,  current balance - requested days, set(visible,true))

Hope this can help you!

[MH3]

Hi @pagonza 

Thanks for you kind reply.

well I dont have to use the CDS Database as per my manager requirements

But unfortunately, I haven't completed the first scenario, I am confused a lot and couldn't find anything on internet regarding what I need.

So, let me clear you and explain everything.

first, I created a SP List, Named LeaveRequests which holds the information:
Requestor,Approver(Manager Emails),Leave Type Name, for Annual, Casual and Sick Leave types I have the same columns of these 3 types (Start Date, End Date, Days Requested).

So, the user opens the application, selects the leave type from dropdown, it will show the fields related to that leave type and then he/she enter the details and submits the form.

after that a patch command runs and saves the data into SP List like this:
Leave Requests List:

nd then the Approval flow is being triggered whenever there is a new entry in the list.

Till that everything running fine, No Errors.

But now, I have to add the ability of Total Leaves of all employees and balances check as well to reduce the HR work.

So, I am confused how can I achieve that requirement, do I have to create all employee list, in which all leaves and balances are defined?

or I can do that with only 1 list the one I am using.

1. I want to do,if the user logs in it should see his/her Total Leaves and Balance Leaves from the list, if he selected Annual in Dropdown. 
2. if he/she is requesting for the first time, let's say Annual leave balance is 14, he requested 7 days so, 14-7 = 7 days in balance Now. (this will be updated in the balance column in the list).
3. if he/she requests for the 2nd time, so Annual Leave Balance, will be 7 and requested 8 days, so I have to show the popup requested days should be less than the balance.
   
   Hope this now clear you about my problem. I am really helpless out here.
   
   Thanks  

Help!

[pagonza]

Hello @MH3 

Thank you for sharing all the information.

I think maybe the best option is have two list, one for leave request and other for employees, I think that can make everything easier.

For your requirements:

I want to do,if the user logs in it should see his/her Total Leaves and Balance Leaves from the list, if he selected Annual in Dropdown. 

In the list employees, you can have field of annual balance, and the main information of this person,

if he/she is requesting for the first time, let's say Annual leave balance is 14, he requested 7 days so, 14-7 = 7 days in balance Now. (this will be updated in the balance column in the list).

Create a gallery where they can see the balance, and create a botton for request new leave. Then request the day of leave, and once they enter the dats with powerautomate you can update the two list, one with the new request, and once the manager accepted, you can update the other sharepoint list.

if he/she requests for the 2nd time, so Annual Leave Balance, will be 7 and requested 8 days, so I have to show the popup requested days should be less than the balance.

For the popup requeriment you can use this:

https://powerapps.microsoft.com/en-us/blog/creating-dialogs-in-powerapps/

Also you can put a condition before If((current balance - requested days)>0,  current balance - requested days, Popup)

Hope this can help you!

[MH3]

Dear @pagonza , 

Thanks for your kind reply can you please look at this logic of mine which I was thinking about it can you help achieve this.

So, The Logic is

Please Read it, this is what I wanted to achieve.

I will create a List First as EmployeeLeaveBalance, with Fields:   

1)   Person Column
2) Total Annual Leaves
3) Annual Balance
4) Total Casual Leaves
5) Casual Balance
6) Total Sick Leaves
7) Sick Balance

after this, I'll create two labels in my PowerApp

 Label1 > Property > Text  (I will use a formula here,

if the logged in user has selected Annual Leave Type From the DropDown, then It should Show Total Annual Leave (which I will Hardcode as 14 because I have no entry for a user till now in that EmployeeLeaveBalance List).   // This will be static because total leaves would never change.

else if  the logged in user has selected Sick Leave Type From the DropDown, then It should Show Total Sick Leave (which I will Hardcode as 10 because I have no entry for a user till now in that EmployeeLeaveBalance List).   // This will be static because total leaves would never change.

else the logged in user has selected Casual Leave Type From the DropDown, then It should Show Total Casual Leave (which I will Hardcode as 8 because I have no entry for a user till now in that EmployeeLeaveBalance List).   // This will be static because total leaves would never change. 

Label2 > Property > Text (I will use a formula here, if the logged in user is equal to the user in that list (which at first is not available there till yet), Show Total Leave - Days Requested, else show 14 as it's Annual Balance (I will hardcode this too because dont have Balance there.))

same thing goes for Sick and Casual for Label2

Now, At First, Scenario 1 User Comes and Selects the leave type, Let's say Annual

The Label1 Should Show 14 as Total Leaves
and Label2 Should Show 14 as Annual Balance.

and then, it selects the Annual Start Date and End Date , The Days Requested is calculated in a different Label in that form.

and then he hits Submit Button,  After that two Patch commands, should run, 

1) Patch Command: will enter data into the LeaveRequests List of my which stores information about the (User, Approver (The Manager he Selected), Reason, LeaveType, Days Requested, Start Date and End Date)

I'm confused in this second Patch, it will store the information of the labels, Label1.Text which total Balance it will save as 14, but Label2.Text should be Subtracted from the Days Requested while patching, Like if the user sees 14 at first, but when it's patching it should subtract that and store that subtracted value into the Annual Balance column.

2) Patch Command: it will store store information into the 2nd List EmployeeLeaveBalance (User, Total Leaves (Label1.Text), Annual Balance (Label2.Text), Days Requested (LabelXX.Text))

Scenario 1 Ends here

Scenario 2
and when the same user comes up again, for requesting Annual Leave:

The Label1 Should Show 14 as Total Leaves
and Label2 Should Show that Updated Annual Balance Field from the EmployeeLeaveBalance

and then, it selects the Annual Start Date and End Date ,

and then he hits Submit Button,  After that

The First Patch Command should run and creates a new record for that employee in my LeaveRequests, But is should Update the 2nd List of that Employee, because It should have only  1 record of every employee.

Scenario 2 ENDS Here

Scenario 3 

if the user comes again for requesting, lets Say Annual Leave,

and if he/she selects the days requested which are greater than the Balance, so It should see a Popup that Days Requested must be lesser than the Balance.

Any help!!

[pagonza]

Hello @MH3 

Hope I can help you, your powerapp seems good! 

For this, you need to do this based on your fields of sharepoint: 

but Label2.Text should be Subtracted from the Days Requested while patching, Like if the user sees 14 at first, but when it's patching it should subtract that and store that subtracted value into the Annual Balance column.

[MH3]

Hi @pagonza ,

Soryy for late reply.

Sir, actually I haven't achieved what I wanted, I am still stuck  with the patching thing in my application.

would you please help me step by step? 

I would like you to help me in my application and finalize it 🙂 I have been working on it for about two weeks but no success.!!!

Thanks

[RandyHayes · Accepted solution]

@MH3 

You had asked me about this in private message and I provided the solution there for you.  So I will copy here for others to examine as well.

To summarize:

1) I recognized that you would have issues with year-to-year sums.  As the year changes, what was the logic for determining the actual sum of leave for "this year" and not include sums from prior years?

2) I did not see any logic for partial leave (i.e. person leaves mid-day for a half day)

So, the basic point was that I saw no other need for another list (EmployeeLeaveBalance).  You can achieve everything you need from the one list of leave.

Now, I do not know what kind of columns you have in the LeaveRequests list, but I assume you have a start and end date and also a leave type.  For purposes of avoiding delegation issues, I would also suggest that you have a StartYear and an EndYear column that can be used for delegable operations.

I put together a sample app to explain what I am suggesting as I believe it might be a lot to simply explain in this message.  The sample app is attached to this post.

Essentially, I created a sample app that has sample data modeled around what I assume your LeaveRequests list has plus the added suggestions above.

The app starts with a Collection (because I am not using live data in this sample) which again is modeled on the list.

ClearCollect(colMH3,
    {Employee:"aaa@xxx.com", Leavetype:"Sick", StartYear:2020, EndYear:2020, StartDate:DateValue("3/2/2020"), EndDate:DateValue("3/3/2020")},
    {Employee:"aaa@xxx.com", Leavetype:"Annual", StartYear:2019, EndYear:2020, StartDate:DateValue("12/20/2019"), EndDate:DateValue("1/3/2020")},
    {Employee:"aaa@xxx.com", Leavetype:"Annual", StartYear:2020, EndYear:2020, StartDate:DateValue("4/2/2020"), EndDate:DateValue("4/3/2020")},
    {Employee:"aaa@xxx.com", Leavetype:"Casual", StartYear:2020, EndYear:2020, StartDate:DateValue("5/2/2020"), EndDate:DateValue("5/3/2020")},
    {Employee:"aaa@xxx.com", Leavetype:"Casual", StartYear:2020, EndYear:2020, StartDate:DateValue("6/2/2020"), EndDate:DateValue("6/3/2020")},
    {Employee:"aaa@xxx.com", Leavetype:"Sick", StartYear:2020, EndYear:2020, StartDate:DateValue("7/2/2020"), EndDate:DateValue("7/3/2020")},
    {Employee:"aaa@xxx.com", Leavetype:"Sick", StartYear:2020, EndYear:2020, StartDate:DateValue("8/2/2020"), EndDate:DateValue("8/3/2020")},
    {Employee:"bbb@xxx.com", Leavetype:"Annual", StartYear:2020, EndYear:2020, StartDate:DateValue("4/12/2020"), EndDate:DateValue("4/23/2020")},
    {Employee:"bbb@xxx.com", Leavetype:"Casual", StartYear:2020, EndYear:2020, StartDate:DateValue("5/22/2020"), EndDate:DateValue("5/24/2020")},
    {Employee:"bbb@xxx.com", Leavetype:"Casual", StartYear:2020, EndYear:2020, StartDate:DateValue("6/11/2020"), EndDate:DateValue("6/13/2020")}

)

I purposely have one record that spans the 2019 and 2020 year so that my app can demonstrate how it only calculates for the current year.  In the sample app, you will find this formula in the OnStart of the App.

There are two formulas that you will want to review that show how data as tabulated all from one list. 

The first one is in the Gallery3 Items property that is found on the screen scrnViewAll.

This is the formula:

With({lclYear:Year(Now())},
AddColumns(
    AddColumns(
        GroupBy(
            AddColumns(
                Filter(colMH3,
                    (StartYear=lclYear || EndYear=lclYear)
                ),
               "countedDays", DateDiff(
                           If(StartYear=lclYear, StartDate, Date(lclYear,1,1)), 
                           If(EndYear=lclYear, EndDate, Date(lclYear,12, 31)), Days) +1
            ),
           "Employee",
           "Records"
        ),
        "types", 
            AddColumns(GroupBy(Records, "Leavetype", "requests"),
                "Annual", If(Leavetype="Annual", Sum(requests, countedDays), 0),
                "Casual", If(Leavetype="Casual", Sum(requests, countedDays), 0),
                "Sick", If(Leavetype="Sick", Sum(requests, countedDays), 0)
            )
    ),
    "AnnualUsed", Sum(types, Annual),
    "CasualUsed", Sum(types, Casual),
    "SickUsed", Sum(types, Sick),
    "AnnualBalance", 14-Sum(types, Annual),
    "CasualBalance", 8 -Sum(types, Casual),
    "SickBalance", 10-Sum(types, Sick)
)

)

You will see in the Gallery how all of the consumed and balance leaves are shown for each employee in the list.

So, this demonstrates what could be more of a management screen to view leave.

The next formula of interest is on the scrnRequest screen it is on the Update property of DataCard1 (in Canvas1).

That formula is as follows:

With({lclEmployee:User().Email},
        With({lclData:
            With({lclYear:Year(Now())},
                AddColumns(
                    AddColumns(
                        Filter(colMH3, 
                            Employee=lclEmployee &&
                            (StartYear=lclYear || EndYear=lclYear)
                        ),
                        "countedDays", DateDiff(
                            If(StartYear=lclYear, StartDate, Date(lclYear,1,1)), 
                            If(EndYear=lclYear, EndDate, Date(lclYear,12, 31)), Days) +1
                    ),
                    "Annual", If(Leavetype="Annual", Sum(countedDays), 0),
                    "Casual", If(Leavetype="Casual", Sum(countedDays), 0),
                    "Sick", If(Leavetype="Sick", Sum(countedDays), 0)
                )
            )
            },
            {Annual:Coalesce(Sum(lclData, Annual), 0), AnnualBalance: 14-Sum(lclData, Annual), Sick:Coalesce(Sum(lclData, Sick),0), SickBalance:10-Sum(lclData, Sick), Casual:Coalesce(Sum(lclData, Casual), 0), CasualBalance:8-Sum(lclData, Casual)}
        )
)

This creates a record for the current employee and demonstrates what it might be like for a person entering their request for leave and seeing their current leave and balances.

The demo is active (meaning that you can enter in leave requests and it will update the data in the app)

Please take a look at this app and the way that it works.  Let me know if you have questions.  Overall I believe the solution is much more streamlined and does not require an additional SharePoint list to achieve what you need.

For the app...

Download the attached file to your computer.  Then either open an existing app or create a new one.  Then once in the designer, choose File->Open->Browse and then choose the file you saved.

Then explore...and let me know if you have questions.

[LugenG]

Hi all. Apologies if I'm supposed to be starting a new thread, but my dilemma has for to do with this topic which is for my app to be able to calculate and display my different leave types with the respective balances.

I downloaded and followed RandyHayes great app and instructions, however I am still struggling and I'm really hoping someone could assist me, please. I've been battling with this for quite a while now.

Perhaps I just need to edit the formulas of Randy to fit in with my app, but I'm not sure what to do. I'm a new user and lost ;(

Basically, I have 1 SharePoint List, called CWLeaveRequests with the following columns:

- Name and Surname

- Start Date

- End Date

- Type of Leave

- Status

- Supervisor Notes

- Staff Notes

- Number of Days

- Attachments

- Supervisor

- Start Year (I added this as per RandyHayes suggestion)

On my PowerApp, I have 4 screens namely:

- HomeScreen

- RequestFormScreen

- SuccessScreen

- RequestHistory

When I create a new leave request, it add the data to my SharePoint list, and then a flow runs to a supervisor to approve. If approved/rejected it updates the list. So far, that all works 100%.

My different leave types are as follows:

- Annual Leave

- Sick Leave (provide supporting evidence when applicable)

- Study Leave (provide supporting evidence)

- Family Responsibility Leave (provide supporting evidence)

- Unpaid Leave

- Maternity Leave

- Paternity Leave (provide supporting evidence)

Now, I want to display the different leave types with their balances on the RequestFormScreen perhaps and I'd really really appreciate some help with the coding and where to add it, etc.

If supervisor approves, it deducts the number of days that was requested and if rejected, the balance stays the same for that particular leave type.

If leave requested exceeds the leave balance (for the particular leave type) staff members gets a "sorry, leave exceeded " message of sorts.

In our company, we get 1.25 days per month (15 total days per year) for annual leave so this can probably be hard coded somewhere, not sure where though 😞

Sick Leave is 30 days over 3 years

Family Responsibility Leave is 3 days per year

I would also like the annual leave to be able to carry over to the next year

Please guys. I'll be super grateful if anyone could help.