Hello AppSheet Community! 
I’m building a Compensatory Leave and Overtime
Management application for an industrial company
and need expert help with complex business logic.
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
APP STRUCTURE:
Tables:
• Employees
• CompensatoryLeave
-
ID (Key) → format: LCM-DR-26-001
-
EmployeeID (Ref → Employees)
-
Date
-
Days
-
Reason
-
CreatedBy (USEREMAIL())
-
Requisition (File/PDF)
-
Status (Pending/SectorManager Approved/DRH Approved/Rejected)
-
SectorManagerApproval
-
DRHApproval
-
ApprovedDate
• OvertimeRequests (NEW)
-
ID (Key)
-
EmployeeID (Ref → Employees)
-
Date
-
StartTime
-
EndTime
-
OvertimeHours (auto-calculated)
-
OvertimeType (50% or 100% - auto-calculated)
-
CompHours (auto-calculated)
-
CompDays (auto-calculated)
• LeaveBalance
-
EmployeeID (Key)
-
Balance (Virtual Column)
• Holidays
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
SECURITY LOGIC:
Each Manager sees ONLY their employees:
Security Filter on CompensatoryLeave:
IN([EmployeeID],
SELECT(Employees[EmployeeID],
\[Manager Email\] = USEREMAIL()
)
)
Dropdown filtered by Manager:
Valid If on EmployeeID:
SELECT(Employees[EmployeeID],
[Manager Email] = USEREMAIL()
)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
WORKFLOW (already working):
Manager adds compensatory leave
↓ Email notification
SectorManager approves/rejects
↓ Email notification
DRH approves/rejects
↓ Balance updated automatically
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
ISSUE 1 — Custom Sequential ID:
I need IDs in this format: LCM-DR-26-001
• LCM = site name (fixed)
• DR = Demande de Récupération (fixed)
• 26 = current year last 2 digits (automatic)
• 001 = sequential number per year
I tried:
“LCM-DR-” & TEXT(YEAR(TODAY())-2000,“00”)
& “-” & TEXT(COUNT(SELECT(
CompensatoryLeave[ID],
YEAR([Date])=YEAR(TODAY())
))+1,“000”)
Is this correct? Any better approach?
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
ISSUE 2 — Overtime Auto-Calculation:
Business Rules:
• ONLY Type1 employees are eligible
(Sunday→Thursday, 08:00→16:30)
• Cadre, Type2, Type3 = NOT eligible
50% Overtime hours:
• Sunday→Thursday: 16:30→21:00
• Sunday→Thursday: 05:00→08:00
• Saturday: 05:00→21:00
100% Overtime hours:
• Every day: 21:00→05:00
• Friday: all day
• National & Religious holidays: all day
Compensation calculation:
• 50% → 1 hour worked = 1.5 hours compensation
• 100% → 1 hour worked = 2 hours compensation
• 1 working day = 8 hours
• Minimum entry = 0.5 hour
Example:
4 hours at 100% = 8 compensation hours = 1 day off
Input by Manager:
• Date
• Start Time
• End Time
→ App calculates everything automatically
My formula for OvertimeType:
IF(
OR(
WEEKDAY(\[Date\]) = 6,
IN(\[Date\], Holidays\[Date\])
),
“100%”,
IF(
AND(
MOD((\[StartTime\]-0.875)\*24,24) >= 21,
MOD((\[EndTime\]-0.875)\*24,24) <= 5
),
"100%",
"50%"
)
)
Questions:
1. Is this formula correct for time comparison
in AppSheet?
2. How to handle shifts that cross midnight
(e.g., 22:00 → 03:00)?
3. How to split mixed shifts automatically?
(e.g., 18:00→23:00 = 2.5h at 50% + 2h at 100%)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
ISSUE 3 — Hide fields in Form View only:
I want to hide these fields ONLY in Form View
(hide when adding, show in Detail View):
• Status
• SectorManagerApproval
• DRHApproval
• ApprovedDate
I used Show_If = FALSE but this hides them
everywhere. How to show them only in Detail View?
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
ISSUE 4 — Balance Virtual Column:
I’m using this formula in LeaveBalance table:
SUM(SELECT(CompensatoryLeave[Days],
AND(
\[EmployeeID\] = \[EmployeeID\],
\[Status\] = "DRH Approved"
)
))
Is Virtual Column the best approach or should
I use Automation Bot to update the balance?
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Additional Info:
• Data source: Google Sheets
• Users: ~50 managers + sector managers + DRH
• Platform: Mobile + Desktop
• Industry: Industrial/Manufacturing (Holcim)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Any help would be greatly appreciated! 
Thank you in advance.
Context: