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

(Anne Manzi) #1

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?

(Aleksi Alkio) #2

Do you have employees table in your app?

(Anne Manzi) #3

Yes, I do

(Aleksi Alkio) #4

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

(Anne Manzi) #5

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?

(Aleksi Alkio) #6

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

(Aleksi Alkio) #7

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) #8

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?

(Aleksi Alkio) #9

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

(Anne Manzi) #10

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