sharepoint online – Leave Balance Calculation For Leave Application


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?