Count Approved leaves per employee and populate in the other table

Hey, I'm trying to create a Leave management application. But got stuck in between and need some assistance.
As you can see in the first Screen Shot below there are 2 employees as of now and their leave request in the status column is either Approved/pending/Denied.

Now, I want to calculate only the approved leaves per employee and autofill it in the "Availed" column in the "Count" table, as shown in the screenshot below. I'm already using a filter but it counts all the approved text from the 'status" column.

Let's say, if emp1 has got 3 approved leaves then in the "availed" column it should show 3 leaves for Emp1 and then the "Balance column" should show the balanced leaves accordingly.

I've scratched my head for hours but couldn't figure how it should be done.

Any help would be much much appreciated, Thank You!!!

And, below is the summary page in which the data should be automatically filled in the "Availed" section.

Hi @Sach-350d, welcome to the community and thanks for the screenshots! :smiley: :honeybee:

For this, one solution would be to update the automation that is tied to your approval button to subtract 1 from the available leave balance. This is how it would look:

Using the Time-Off Template, I first added a couple of columns like yours and added some starter balances:

I then went into the Manager app to update the "Approve" button's automation.

I added an Overwrite action to the "Approve" button, and in the "take data from" field, I put the following formula. This retrieves the current Leave balance of the given employee and subtracts 1:

=(FINDROW(People,"People[Employee]=People[Employee]")[LeaveBalance])-1

And for the "write to" field, I put the following, which overwrites the current Leave balance:
=FINDROW(People,"People[Employee]=People[Employee]")[LeaveBalance]

Now, if a manager approves a leave request for a user, it will subtract 1 from their balance as desired.

Something else that may be helpful would be to store in the table of users all of their approved leaves. This is done with a FILTER(), which you can hover over or reference to see all of their leaves:

This is the FILTER formula in my example:
=FILTER(Timeoff,"Timeoff[Employee]=People[Employee] AND Timeoff[Status]=%","Approved")

Hope all this helps, and let us know if you have any other questions! :honey_pot: :honeybee:

1 Like

Thanks for the reply.
This works for me as expected. Thank you.

1 Like

@Sach-350d Great :smiley: :star2: Always happy to help!

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.