Annual Leave Entitlement

[Gregorrrry]

Hi,

We are setting up an Annual Leave module, with a Holiday Request and then a Holiday Tracker. 

They are set up on two different sheets. However I was hoping that someone could give me an expression to show the users personal remaining entitlement.

When a user logs in, the name is automatically added from the Useremail. 

The user fills out the Holiday request form and then the 'Remaining Annual Leave' column is updated. 

Is there an expression that would I could use so that every time someone goes to fill out an Annual leave request they can see how many days entitlement they have left?

[WillowMobileSys · Accepted solution]

If I understand correctly, you are already calculating the remaining entitlement in the "Remaining Annual Leave" column in your Entitlements tables.  I suppose then you are asking how to pull that value from the Entitlement table into the Form?

To do that, you need to match up to the proper Entitlement table row by the Employee Name, grab the "Remaining Annual Leave" value and assign it to the Entitlement column.  You can use a LOOKUP expression (more info below):

LOOKUP([Employee Name], "Entitlement", "Employee Name", "Remaining Annual Leave")

NOTE:  You likely want to place the expression in the "Initial Value" property but set the column as not editable.  This way the value is assigned ONLY when the row is created and you will retain the "Remaining Annual Leave" entitlement at the time the request is made - even if the row was later edited.  

Lookup

https://support.google.com/appsheet/answer/10107410?hl=en 

[WillowMobileSys · Accepted solution]

If I understand correctly, you are already calculating the remaining entitlement in the "Remaining Annual Leave" column in your Entitlements tables.  I suppose then you are asking how to pull that value from the Entitlement table into the Form?

To do that, you need to match up to the proper Entitlement table row by the Employee Name, grab the "Remaining Annual Leave" value and assign it to the Entitlement column.  You can use a LOOKUP expression (more info below):

LOOKUP([Employee Name], "Entitlement", "Employee Name", "Remaining Annual Leave")

NOTE:  You likely want to place the expression in the "Initial Value" property but set the column as not editable.  This way the value is assigned ONLY when the row is created and you will retain the "Remaining Annual Leave" entitlement at the time the request is made - even if the row was later edited.  

Lookup

https://support.google.com/appsheet/answer/10107410?hl=en 

[Steve]

LOOKUP([_THISROW].[Employee Name], ...)

[Gregorrrry]

Amazing! Thank you both for your help.

[Gregorrrry]

Hi, I know I accepted this a solution, however now I have been testing it, and it isn't working the way I want it to.

The expression is saying 'Randomly selected'. So just pulling the top entry in the 'Remaining Annual Leave' column. 

[WillowMobileSys]

Correct but its the TOP row for that Employee.  According to your sample sheet shown in the original post, it doesn't seem reasonable to have more than one row per Employee.  If that is true then LOOKUP() should only ever return a single row so the fact that LOOKUP() selects the top row doesn't matter.

Does your data table have more than one row per Employee?  If so then show us what that looks like and we can help modify the expression.  Most likely the usage of SELECT() would be needed instead.

[Gregorrrry]

Thanks for a quick response. I have realised it was in the Formula and not the Initial Value. Now working as expected, thanks again.

[Steve · Accepted solution]

LOOKUP([Employee Name], ...) should be LOOKUP([_THISROW].[Employee Name], ...).