Having trouble with the video? Try this link.
When you create a Leave Request app, you may want to include a field that shows the user how many days of leave he has left.
To do this, start by creating a Master. I’ll call mine LeaveBalance. Include the Employee Email, and then make a different column for all the types of leave you give. In our example, we have Vacation, Sick, and Personal.
For each employee, enter the number of days they get off for a given period, like a year.
Now go back to your form. Create a dropdown field called Leave Type. Include all the types of leave you listed in the Master. Make sure to use the exact same names.
Next, make a number field called Leave Allocated. Make it a computed field. Here is the formula to include:
IF(Leave_Type="Sick",DLOOKUP(LeaveBalance.Sick,LeaveBalance.EmployeeEmail=CreatedBy))
This formula is only for sick leave. The first part of the formula refers to the dropdown field we made earlier called Leave Type. Then, it will include the name of the master DOT the name of the column in the master COMMA the name of the master DOT the column to pull the data so that the person who initiated the item gets their balance.
Here is a formula to show all the types of leave:
IF(Leave_Type="Sick",DLOOKUP(LeaveBalance.Sick,LeaveBalance.EmployeeEmail=CreatedBy),IF(Leave_Type=”Personal”,DLOOKUP(LeaveBalance.Personal,LeaveBalance.EmployeeEmail=CreatedBy),IF(Leave_Type=”Vacation”,DLOOKUP(LeaveBalance.Vacation,LeaveBalance.EmployeeEmail=CreatedBy))))
Next, you need a field to show how much leave has been taken so far. This is another number field that is computed with a formula. For this formula, enter:
DSUM(self.Total_Days,AND(self.CreatedBy=CreatedBy,self.Leave_Type=Leave_Type,self.Stage=2))
Finally, you will need another number field to show the number of days available. Here is the formula for that field:
Leave_Allocated-Leave_Taken
That will show the final outcome. In your permissions, you can hide some of these fields if you don’t want them to display every time.