Leave Balance Calculation For Leave Application

Question

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

Total Annual Leave field will be 14 (by default) and current balance is also 14. say if Annual Request is for 2 days CurrAnnualLeavebalance field Column 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?

Answer 1

Create a list of Employees, just with columns like Employee Name, DOB, Start Date, End Date. In list 2, which I'll call the 'Leave' list, create a look-up column that points towards the Employees list - you can select the Employee Name field; this will 'borrow' the Employee Name field for use on your Leave list.

For the 'calculation' of remaining leave you can use the list Validation Settings (this is configured via List Settings not on an individual column setting) - see Mike's answer here.

I do like something visual in my answers, so take a look below.

Picture 1 - List Validation Settings.

This is what I see when I'm trying to submit a new leave request on the Leave list:

NOTES

I've not created a look-up field for Employee Name in my example, but I'd suggest you do so.

Also I've rather unhelpfully called my fields differently to yours, sorry about that, though it's quite straightforward...

The field types I've used are all number fields. Your flow would update the 'Leave Remaining' balance.

Answer 2

Tally's suggestion is great for simple scenario.

As you have access to flow, I will suggest you to create a new list for recording the user leave balance and using flow to update it with your leave request.

The new list, let us call it Leave Balance list. You can use it to hold all the employees with detailed information of leave balance.

As you have already got a flow for approval, change it a bit will be enough. User has already select the leave type and date, then you can use that in your flow to update the leave balance list.

Example for your reference: https://powerusers.microsoft.com/t5/Building-Flows/Leave-request-flow-with-sharepoint/td-p/384027