Hello There, I am building a vacation app, w...

Anne_Manzi
Participant I

Hello There,

I am building a vacation app, where employees can request for a leave and the HR approves or denies through the same app. I want to add functionality where the employees will be able to know the number of days they are remaining with in

a year after they requested a leave supposing the total days per employee in a year is 18.

Any idea on how i could do that?

0 10 816
10 REPLIES 10

Do you have employees table in your app?

Anne_Manzi
Participant I

Yes, I do

I assume you are using ref field for the employee. First calculate how many days the vacation is in that request. Add a Y/N column for the manager to approve. Now you can calculate approved days in your employees table with the virtual column likeโ€ฆ

SUM(SELECT([RelatedRequests][Days], AND([Approval]=TRUE,YEAR([Timestamp])=YEAR(TODAY()))))

I tried this, but it seems not working.
I have a column on Number of Days Taken (End Date - Start Date)

Number of days taken in Total:

SUM
(SELECT([Number of Days Taken],
AND([Approval]=TRUE, YEAR([Date]=YEAR(TODAY()) )
) )

Anne_Manzi
Participant I

So after getting the sum of approved days per employee, is it best to add another virtual column tha substracts the sum from the total allowed days per year (18 in this case)? To get the remaining days per employee. Or there is a better way?

If itโ€™s the same for every year, you can add that fixed number directly into your formula.

One thing to rememberโ€ฆ if the request is starting this year and ending next year, you should find the best way to calculate the correct days. The same if the request is starting from the last year.

Anne_Manzi
Participant I

Thanks, Again the app include weekend days in the form where employees choose the dates they want to leave. Is there a way of removing the weekend days so they are not inluded in the employeeโ€™s days selection?

Yes you can use WORKDAY expression. help.appsheet.com - Date and Time Expressions Date and Time Expressions help.appsheet.com

Anne_Manzi
Participant I

can you help me with an actual syntax of the WORKDAY expression?

Top Labels in this Space