Expression for Updating Leave Balance Every Fortnight- For a Staff Leave App

AppSheet AppSheet Q&A
1

This is for an Employee Leave App. Leave balances are updated after each fortnightly pay periods.

I would like to know the expression for updating the leave balance every fortnightly pay periods, for all the three leave types:

1.Annual Leave - updated to 14 days (20 days for pre-opening staff and General Manager)

  1. Sick Leave - updated to 10 days

  2. Bereavement Leave - updated to 3 days

Please note the following tables with their columns, as a data source for the app:



Table 1: Employees

Table 2: Leave balance

Table 3: Leave


Start Date

Employee Name

Leave ID


Employment Number

Annual

Employee Name (shows Email instead)


Employee Name

Sick ‘

Leave Type


Work Phone

Bereavement

Deduct From


Email

From Date


Alternative Phone

To Date


Employment Type

Days Taken


Role

Remarks


Department

Status


Gender

DateTime


Date of Birth

Approved By


TIN Number


FNPF Number


Drivers License Number
2

You would need to give more details about your current app for people in this community to propose something to your question. Like.. how the data is recorded, what columns do they have, do they need to be approved, is it calculated per hour or per day, how accurate the result needs to be etc.

2 Likes
3

is this data stored in a table such as key, person, Leave 1, Leave 2, Leave 3.

if each cell holds the value of the user, and the value is consistent based on all users ( such as every 14 days all users get .5 hours). Then you can create a bot to update these fields on a specific day or run every day if the date that it updates is based on the user (will only run if a row needs to be updated). If this is the case you can have the employees start date or the date it was last updated on a column called “last updated” and this column would be changed based on the time frequency you define for the bot to update based on. Since all three cells have different dates of change you might need a last updated column for each. (This second route requires more since it is based on a per user assessment.) (Way easier if it is all users are on the same schedule. The the bot would just fire based on the defined days that are for these changes)