On Load - Create

Runs when a blank Work Order form opens. Detects role, sets defaults, builds the WO-number preview once a SITE is known, locks system fields, and hides every field that only matters later in the lifecycle.

// ===== work_orders : On Load (Create / Add) =====

// --- 1. Defaults for a brand-new ticket ---
input.raised_on = zoho.currenttime;      // moment the form opened
input.status = "Open";                    // every new ticket starts Open
input.priority = "Medium";                // safe default SLA band
input.completion_percent = 0;            // nothing done yet
input.scheduled_date = zoho.currentdate; // planned for today unless changed
input.job_type = "Corrective";           // manual tickets are Corrective

// --- 2. Role detection ---
// Portal logins expose the email in zoho.loginuserid on THIS app (not zoho.loginuser).
loginEmail = zoho.loginuserid;
supSiteId = getSiteForUser(loginEmail);  // owner-context fn: Supervisor SITE id, else 0

if(supSiteId != 0)
{
    // ROLE = SUPERVISOR -> locked to their own hospital
    input.SITE = supSiteId;              // lookup set by record ID
    hide SITE;                           // must not pick another site
}
else
{
    // ROLE = ADMIN (portal user OR app user) -> may raise for any hospital
    show SITE;                           // leave the site picker visible
}

// --- 3. WO number preview (only once a SITE is known) ---
if(input.SITE != null)
{
    input.work_order_number = buildWorkOrderNumber(input.SITE);  // provisional
}

// --- 4. Lock system-owned fields ---
disable work_order_number;   // generated, never typed
disable raised_on;           // system timestamp

// --- 5. Hide everything that belongs to later stages ---
hide checklist_template;
hide status;
hide completion_percent;
hide desk_ticket_id;
hide source_schedule;
hide location_path;
hide target_completion;
hide started_on;
hide completed_on;
hide on_time;
hide downtime_hours;
hide failure_cause;
hide work_performed;
hide technician_sign_off;
hide verified_by;
hide verified_on;

SITE - On user input

Fires when an Admin picks or clears the hospital site. Keeps the WO-number preview in sync with the chosen center; blanks it when the site is cleared.

// ===== work_orders : SITE field -> On user input =====

if(input.SITE != null)
{
    // A site is selected -> rebuild the preview for that center
    input.work_order_number = buildWorkOrderNumber(input.SITE);
}
else
{
    // Site cleared -> wipe the stale preview
    input.work_order_number = "";
}

On Load - Edit

Runs when an existing Work Order is opened. The whole lifecycle is now relevant, so every field hidden at create time is revealed. work_order_number and raised_on stay disabled - both are immutable once set.

// ===== work_orders : On Load (Edit) =====

// --- Reveal the execution / verification fields ---
show status;
show scheduled_date;
show target_completion;
show assigned_technician;
show started_on;
show completed_on;
show completion_percent;
show downtime_hours;
show failure_cause;
show work_performed;
show on_time;
show technician_sign_off;
show verified_by;
show verified_on;
show checklist_template;
show source_schedule;
show desk_ticket_id;
show location_path;

// --- Keep system-owned fields locked ---
disable work_order_number;   // frozen once stamped
disable raised_on;           // original creation timestamp is immutable

On Validate

Last step before save. Stamps the FINAL, collision-free WO number (buildWorkOrderNumber re-scans the max serial, so it stays unique even if another ticket was created while this form was open) and sets target_completion from priority. Bind this to the Created event; on Edit, guard the number line so it is not re-stamped (see devNotes).

// ===== work_orders : On Validate =====

// --- 1. Final, unique WO number ---
// buildWorkOrderNumber() reads the current max serial for this center+month,
// so calling it here yields a fresh number with no clash.
if(input.SITE != null)
{
    input.work_order_number = buildWorkOrderNumber(input.SITE);
}

// --- 2. SLA target from priority + raised time ---
if(input.raised_on != null)
{
    input.target_completion = computeTargetCompletion(input.priority, input.raised_on);
}

On Success - on add

Runs AFTER the new record is saved. Writes a Created row to audit_log and, if a technician was chosen at creation, emails them. Uses the real audit_log field link names, including action_1 (not 'action').

// ===== work_orders : On Success (Add only) =====

// --- 1. Audit trail: one Created row ---
auditRec = insert into audit_log
[
    SITE           : input.SITE
    reference_type : "Work order"
    reference_id   : input.work_order_number
    action_1       : "Created"
    changed_by     : zoho.loginuserid        // stores the raiser email
    changed_on     : zoho.currenttime
    new_value      : "Status " + input.status + " - Priority " + input.priority
];

// --- 2. Assignment email (only if a technician was set on the new ticket) ---
if(input.assigned_technician != null)
{
    techRec = technicians[ID == input.assigned_technician];
    techEmail = ifnull(techRec.email,"");
    if(techEmail != "")
    {
        sendmail
        [
            from    : zoho.adminuserid
            to      : techEmail
            subject : "New Work Order assigned - " + input.work_order_number
            message : "You have been assigned Work Order " + input.work_order_number + ". Priority " + input.priority + ". Please review it in the CAFM app."
        ]
    }
}

Delete

Zoho Creator has no native pre-delete veto in a form event, so remove the Delete permission from the report and drive deletion through a custom record button running this Deluge. It refuses to delete Completed/Verified tickets (the compliance record) and always writes a Deleted row to audit_log first.

// ===== work_orders : custom "Delete Work Order" button (record context) =====

woStatus = input.status;

if(woStatus == "Completed" || woStatus == "Verified")
{
    // Closed tickets are the compliance record - never delete them
    alert "This Work Order is " + woStatus + " and cannot be deleted. Cancel it instead.";
}
else
{
    // Log the deletion BEFORE removing the row
    insert into audit_log
    [
        SITE           : input.SITE
        reference_type : "Work order"
        reference_id   : input.work_order_number
        action_1       : "Deleted"
        changed_by     : zoho.loginuserid
        changed_on     : zoho.currenttime
        old_value      : "Status " + input.status + " - Priority " + input.priority
    ];
    // Remove just this record
    delete from work_orders[ID == input.ID];
}

computeTargetCompletion(priority, raisedOn)

Maps a ticket's priority to its SLA response window (Critical 4h, High 8h, Medium 24h, Low 72h) and returns the datetime by which the work order must be completed. Called from On Validate and from every generator function that inserts a work order programmatically.

datetime computeTargetCompletion(string priority, datetime raisedOn)
{
    // Priority -> SLA response window in clock hours
    slaHours = 24;                 // Medium is the default / fallback
    if(priority == "Critical")
    {
        slaHours = 4;
    }
    else if(priority == "High")
    {
        slaHours = 8;
    }
    else if(priority == "Low")
    {
        slaHours = 72;
    }
    // In Deluge, adding a DECIMAL to a datetime adds that many DAYS.
    // Dividing SLA hours by 24 therefore shifts the clock by exactly slaHours.
    dayFraction = slaHours / 24.0;
    target = raisedOn + dayFraction;
    return target;
}

buildWorkOrderNumber(siteId)

Builds the work order number WO-{Center}{yyMM}-{4-digit serial}. Reads the SITE's Center code, scans existing work_orders for the highest serial already used this month at that center, and returns the next free number. Called for the preview On Load, on SITE change, at On Validate for the final stamp, and by every generator function. WHILE loops are not used - the serial pad and the max-scan both use bounded for-each logic.

string buildWorkOrderNumber(int siteId)
{
    // No site chosen yet -> no number possible
    if(siteId == null || siteId == 0)
    {
        return "";
    }
    // Read the chosen SITE to get its short Center code
    siteRec = SITE[ID == siteId];
    centerRaw = ifnull(siteRec.Center,"");
    // Compact it: uppercase, keep only letters + digits (3rd arg true = regex)
    centerCode = centerRaw.toUpperCase().replaceAll("[^A-Z0-9]","",true);
    if(centerCode == "")
    {
        centerCode = "S" + siteId;   // guarantees the number is never blank
    }
    // Period segment: 2-digit year + 2-digit month, e.g. Sep 2026 -> 2609
    yymm = zoho.currentdate.toString("yyMM");
    prefix = "WO-" + centerCode + yymm + "-";
    // Highest serial already used for this center + month
    maxSerial = 0;
    existing = work_orders[work_order_number.startsWith(prefix)];
    for each  rec in existing
    {
        tail = rec.work_order_number.subString(prefix.length());  // the serial part
        serialNum = tail.toLong();
        if(serialNum > maxSerial)
        {
            maxSerial = serialNum;
        }
    }
    nextSerial = maxSerial + 1;
    // Left-pad to 4 digits WITHOUT a while loop
    padded = "0000" + nextSerial;
    serialStr = padded.subString(padded.length() - 4);
    return prefix + serialStr;
}

1. Supervisor raises a manual Corrective ticket

1. Supervisor opens the Add Work Order form.
2. On Load - Create: getSiteForUser(zoho.loginuserid) returns their SITE id, so input.SITE is auto-set and the SITE field is hidden.
3. System defaults are stamped: raised_on = now, status = Open, priority = Medium, completion_percent = 0, scheduled_date = today, job_type = Corrective.
4. buildWorkOrderNumber(input.SITE) fills work_order_number with a preview; work_order_number and raised_on are disabled; all lifecycle fields are hidden.
5. Supervisor fills Section1 (trade), asset or General_Asset, floors/location_path, Description, and adjusts priority if needed.
6. On Validate: work_order_number is re-stamped to the final unique value and target_completion is computed from priority + raised_on.
7. On Success - on add: a Created row is inserted into audit_log (changed_by = supervisor email); if assigned_technician was set, an assignment email is sent.
8. Fields set by the user: Section1, asset/General_Asset, Description, priority. Set by the system: SITE (auto), work_order_number, raised_on, status = Open, target_completion, completion_percent = 0.

2. Admin raises a ticket for any hospital

1. Admin (portal or app user) opens the Add form.
2. On Load - Create: getSiteForUser returns 0, so the SITE picker stays visible; the same defaults are applied.
3. Admin selects a SITE; SITE - On user input fires and buildWorkOrderNumber rebuilds the WO preview for that center (clearing the site blanks it again).
4. Admin fills the job details and may assign a technician from any site.
5. On Validate stamps the final number and target_completion exactly as for the supervisor path.
6. On Success - on add writes the audit Created row and sends the assignment email if a technician was chosen.
7. Difference from scenario 1: SITE is chosen by the Admin (not auto-filled) and can be any of the six hospitals.

3. PPM auto-generated Work Order

1. A scheduled owner-context generator function reads ppm_schedules where status = Active and next_due_date <= today + lead_time_days.
2. For each due schedule it inserts a work_orders record - form On Load / On user input scripts do NOT run for a programmatic insert, so the function sets every field explicitly.
3. Set from the schedule: SITE = schedule.SITE, job_type = Preventive, source_schedule = schedule ID, checklist_template = schedule.checklist_template, asset = schedule.asset, assigned_technician = schedule.assigned_technician, scheduled_date = schedule.next_due_date.
4. Set by the function: work_order_number = buildWorkOrderNumber(schedule.SITE), status = Assigned (or Open), priority default, raised_on = now, target_completion = computeTargetCompletion(priority, raised_on).
5. The function then advances the schedule (last_completed_date, next_due_date) and inserts a Created row in audit_log itself.
6. Key marker: source_schedule is populated and a checklist_template is attached - this is how a PPM-origin ticket is recognised downstream.

4. Predictive Work Order from running hours

1. A condition-based generator function monitors asset running-hours/meter readings against a threshold (running-hours source lives on the assets side - see devNotes assumption).
2. When an asset crosses its threshold the function inserts a work_orders record with job_type = Predictive and asset = the triggering asset.
3. Set by the function: SITE (from the asset's site), Description (auto text such as running-hours threshold reached), work_order_number via buildWorkOrderNumber, status = Open, raised_on = now, target_completion via computeTargetCompletion.
4. source_schedule is normally left null (no PPM schedule); the trigger is the meter, not the calendar.
5. A Created row is written to audit_log by the function.
6. Key marker: job_type = Predictive with no source_schedule distinguishes it from a PPM ticket.

5. Desk-raised ticket via desk_ticket_id

1. An external help-desk integration (e.g. Zoho Desk) calls an inbound function when an end user logs a facilities ticket.
2. The function inserts a work_orders record with desk_ticket_id = the external ticket reference and job_type = Corrective.
3. Set by the function: SITE (mapped from the desk department/site), Description (from the desk ticket body), priority (mapped from desk priority), work_order_number via buildWorkOrderNumber, status = Open, raised_on = now, target_completion via computeTargetCompletion.
4. desk_ticket_id keeps the two-way link so status updates can be pushed back to the help desk.
5. A Created row is written to audit_log with reference_id = work_order_number.
6. Key marker: desk_ticket_id is populated - the ticket originated outside the CAFM app.

Service_Requests > Form Workflow > On Load (event = Created)

Defaults the ticket. For portal users getSiteForUser returns their Supervisor SITE so both SITE and the number are auto-filled; for an internal user with no mapping (returns 0) SITE stays empty for the picker and the number is generated on SITE user-input instead.

if(input.status == null || input.status == "")
{
	input.status = "New";
}
input.raised_on = zoho.currenttime;
input.raised_by = zoho.loginuserid;
sid = getSiteForUser(zoho.loginuserid);
if(sid != 0)
{
	input.SITE = sid;
	input.request_number = genRequestNumber(sid);
}

Service_Requests > Field > SITE > On User Input

Only fires for internal users who pick the site manually. Generates the number once so a portal-generated number is never overwritten.

if(input.SITE != null)
{
	if(input.request_number == null || input.request_number == "")
	{
		input.request_number = genRequestNumber(input.SITE);
	}
}

Service_Requests > Form Workflow > On Success (event = On Add)

Ticket Email + Ticket Confirmation-to-supervisor. Requester email is the portal login id (raised_by). from must be a verified sender.

supEmail = "";
supRec = Supervisor[SITE == input.SITE];
if(supRec != null)
{
	supEmail = supRec.Email;
}
reqEmail = input.raised_by;
if(reqEmail != null && reqEmail.contains("@"))
{
	sendmail
	[
		from : zoho.adminuserid
		to : reqEmail
		subject : "Service Request received - " + input.request_number
		message : "Dear " + input.requester_name + ",<br><br>We have received your service request <b>" + input.request_number + "</b>.<br>Issue: " + input.description + "<br>Status: New.<br>Our facilities team will review it shortly.<br><br>Jood FM Facilities"
	]
}
if(supEmail != null && supEmail != "" && supEmail.contains("@"))
{
	sendmail
	[
		from : zoho.adminuserid
		to : supEmail
		subject : "New Service Request " + input.request_number + " [" + input.priority + "]"
		message : "A new service request has been raised.<br>Number: " + input.request_number + "<br>Requester: " + input.requester_name + " (" + input.requester_type + ")<br>Location: " + input.location_text + "<br>Section: " + input.section + "<br>Priority: " + input.priority + "<br>Description: " + input.description
	]
}

Service_Requests > Custom Button 'Acknowledge' (facilities report, stateless On User Input)

This is the Ticket Confirmation step. Runs on the open record, sets status + who/when, emails the requester. Show this button only when status == New.

input.status = "Acknowledged";
input.acknowledged_by = zoho.loginuserid;
input.acknowledged_on = zoho.currenttime;
reqEmail = input.raised_by;
if(reqEmail != null && reqEmail.contains("@"))
{
	sendmail
	[
		from : zoho.adminuserid
		to : reqEmail
		subject : "Your Service Request " + input.request_number + " is acknowledged"
		message : "Dear " + input.requester_name + ",<br><br>Your request <b>" + input.request_number + "</b> has been acknowledged by our facilities team and is now in the queue. You will be updated as work proceeds.<br><br>Jood FM Facilities"
	]
}

Service_Requests > Custom Button 'Convert to Work Order' (facilities report)

Ticket Assigning starts here. Calls the function which creates the WO, links it back and flips status to Converted. Show only when status == Acknowledged (or New). Assign the technician on the created Work Order using the existing Work Orders screen.

convertToWorkOrder(input.ID);

Service_Requests > Custom Button 'Reject' (facilities report)

Rejected side branch. Sets status and emails the requester so a ticket is never silently dropped.

input.status = "Rejected";
reqEmail = input.raised_by;
if(reqEmail != null && reqEmail.contains("@"))
{
	sendmail
	[
		from : zoho.adminuserid
		to : reqEmail
		subject : "Service Request " + input.request_number + " could not be accepted"
		message : "Dear " + input.requester_name + ",<br><br>Your request <b>" + input.request_number + "</b> was reviewed and could not be accepted as raised. Please contact the facilities desk for details.<br><br>Jood FM Facilities"
	]
}

Service_Requests > Custom Button 'Close Request' (facilities report)

Manual Ticket Closing step, for tickets closed without a Work Order. The automatic closure on WO completion is the work_orders workflow below.

input.status = "Closed";
reqEmail = input.raised_by;
if(reqEmail != null && reqEmail.contains("@"))
{
	sendmail
	[
		from : zoho.adminuserid
		to : reqEmail
		subject : "Service Request " + input.request_number + " closed"
		message : "Dear " + input.requester_name + ",<br><br>Your request <b>" + input.request_number + "</b> has been closed. If the issue persists please raise a new request.<br><br>Thank you,<br>Jood FM Facilities"
	]
}

work_orders > Form Workflow > On Success (event = On Edit) - auto close linked Service Request

Ticket Status update + Ticket Closing driven by the WO. When a Work Order reaches Completed or Verified, close every Service Request linked to it and email the requester. Uses for each with no while loop.

if(input.status == "Completed" || input.status == "Verified")
{
	linkedReqs = Service_Requests[work_order == input.ID];
	for each r in linkedReqs
	{
		if(r.status != "Closed")
		{
			r.status = "Closed";
			rEmail = r.raised_by;
			if(rEmail != null && rEmail.contains("@"))
			{
				sendmail
				[
					from : zoho.adminuserid
					to : rEmail
					subject : "Service Request " + r.request_number + " completed"
					message : "Dear " + r.requester_name + ",<br><br>The work for your request <b>" + r.request_number + "</b> has been completed. If the issue persists please raise a new request.<br><br>Thank you,<br>Jood FM Facilities"
				]
			}
		}
	}
}

genRequestNumber

Return the next ticket number for a site as SR-<Center><yyMM>-<serial>, reading Center from the SITE record and counting existing tickets with the same prefix. Serial is zero-padded to 4 digits.

string genRequestNumber(int siteId)
{
	center = "GEN";
	siteRec = SITE[ID == siteId];
	if(siteRec != null && siteRec.Center != null && siteRec.Center.trim() != "")
	{
		center = siteRec.Center;
	}
	today = zoho.currentdate;
	yymm = today.toString("yyMM");
	prefix = "SR-" + center + yymm + "-";
	usedCount = Service_Requests[request_number.startsWith(prefix)].count();
	serial = usedCount + 1;
	serialStr = serial.toString();
	if(serial < 10)
	{
		serialStr = "000" + serial;
	}
	else if(serial < 100)
	{
		serialStr = "00" + serial;
	}
	else if(serial < 1000)
	{
		serialStr = "0" + serial;
	}
	return prefix + serialStr;
}

convertToWorkOrder

Create a Corrective Work Order from a Service Request, carrying site, section, location, description and priority across, set an SLA target_completion from priority (hours/24 = days), then link the new WO back onto the request and set the request status to Converted. Guards against double conversion.

void convertToWorkOrder(int requestId)
{
	req = Service_Requests[ID == requestId];
	if(req == null)
	{
		return;
	}
	if(req.work_order != null)
	{
		return;
	}
	sec = req.section;
	if(sec == null || sec == "" || sec == "Other")
	{
		sec = "CIVIL";
	}
	genAsset = "Staff or resident reported";
	if(req.room != null)
	{
		genAsset = "Room ref " + req.room;
	}
	locPath = ifnull(req.location_text,"");
	if(req.department != null && req.department != "")
	{
		locPath = req.department + " - " + locPath;
	}
	slaHours = 72;
	if(req.priority == "Critical")
	{
		slaHours = 4;
	}
	else if(req.priority == "High")
	{
		slaHours = 24;
	}
	else if(req.priority == "Low")
	{
		slaHours = 168;
	}
	slaDays = slaHours / 24.0;
	wo = insert into work_orders
	[
		SITE : req.SITE
		job_type : "Corrective"
		Section1 : sec
		General_Asset : genAsset
		location_path : locPath
		Description : req.description
		priority : req.priority
		status : "Open"
		raised_on : zoho.currenttime
		target_completion : zoho.currenttime + slaDays
		desk_ticket_id : req.request_number
	];
	req.work_order = wo.ID;
	req.status = "Converted";
}

Staff raises a request via the portal

1. A ward nurse opens the Staff/Resident portal and clicks New Service Request
2. On Load the form sets status New, raised_on now, raised_by to her login email, and because getSiteForUser returns her site it fills SITE and generates request_number e.g. SR-KKH2609-0007
3. She fills requester_name, requester_type Staff, department, room, location_text, section Plumber, description, attaches a photo, sets priority High and submits
4. On Add she receives a confirmation email and the site facility supervisor is emailed the new ticket

Supervisor acknowledges (Ticket Confirmation)

1. The supervisor opens the ticket in the facilities report where status is New
2. He clicks the Acknowledge button
3. Status flips to Acknowledged and acknowledged_by and acknowledged_on are stamped with his login and the current time
4. The requester is emailed that the ticket has been acknowledged and queued

Convert to Work Order and assign

1. On the acknowledged ticket the supervisor clicks Convert to Work Order
2. convertToWorkOrder creates a Corrective work_orders record with the same SITE, Section1 from section, location_path and General_Asset from room and department, Description, priority, status Open and a target_completion set from priority
3. The new WO id is written to the ticket work_order lookup and the ticket status becomes Converted
4. The supervisor opens that Work Order and sets assigned_technician on the normal Work Orders screen

Requester emailed when the job closes

1. The assigned technician does the work and sets the Work Order status to Completed or Verified
2. The work_orders On Success workflow finds the Service Request linked by work_order
3. It sets that request status to Closed
4. The requester receives the completion email closing the loop

work_orders — On Validate (add On Add > On Validate AND On Edit > On Validate)

Paste under Form Workflow for work_orders on both On Add > On Validate and On Edit > On Validate so create and edit both stamp. It calls the standalone function resolveSlaHours via thisapp. Because asset is multi-select the criticality read must loop with break. target_completion (existing Date-Time field) is set to resolution_due as required.

// Stamp SLA due dates on every save. Runs before the row is saved, so it
// writes input.* directly (a new record has no ID yet). Mirrors stampSlaDue.
section = ifnull(input.Section1,"");
if(section == "")
{
	section = "General";
}
prio = ifnull(input.priority,"Medium");
// asset is MULTI_SELECT_LOOKUP -- read the first selected asset criticality
crit = "Any";
for each  aId in input.asset
{
	crit = assets[ID == aId].criticality;
	break;
}
if(crit == null)
{
	crit = "Any";
}
slaMap = thisapp.resolveSlaHours(section,prio,crit);
respH = slaMap.get("response");
resltnH = slaMap.get("resolution");
baseTime = input.raised_on;
if(baseTime == null)
{
	baseTime = zoho.currenttime;
	input.raised_on = baseTime;
}
// adding decimal N to a Date-Time adds N days -> hours/24
input.response_due = baseTime + ((respH * 1.0) / 24);
input.resolution_due = baseTime + ((resltnH * 1.0) / 24);
input.target_completion = input.resolution_due;

work_orders — field "status" On user input

Attach to the status Dropdown field, event On user input, on work_orders. This is the form path; captureFirstResponse / closeSla are the identical server-side twins the daily scan or a blueprint uses when status changes outside the form. response_due is normally already stamped from a prior save; if a ticket is created and set to In progress in one session before the first save, the daily scan reconciles sla_response_met once response_due exists.

// Capture first response and closure directly on the open form.
newStatus = input.status;
// first time the ticket enters In progress
if(newStatus == "In progress" && input.first_response_on == null)
{
	fr = zoho.currenttime;
	input.first_response_on = fr;
	if(input.raised_on != null)
	{
		input.response_time_mins = input.raised_on.minutesBetween(fr);
	}
	input.sla_response_met = false;
	if(input.response_due != null && fr <= input.response_due)
	{
		input.sla_response_met = true;
	}
}
// closure
if(newStatus == "Completed")
{
	cot = input.completed_on;
	if(cot == null)
	{
		cot = zoho.currenttime;
		input.completed_on = cot;
	}
	if(input.raised_on != null)
	{
		input.resolution_time_mins = input.raised_on.minutesBetween(cot);
	}
	input.sla_resolution_met = false;
	if(input.resolution_due != null && cot <= input.resolution_due)
	{
		input.sla_resolution_met = true;
	}
}

SLA_Definitions — On Load (Create)

Attach to the SLA_Definitions form, On Load event, Create state only (so it does not overwrite an existing row on edit). Gives the admin a working Medium/General row to adjust.

// Sensible defaults for a new SLA definition row.
input.active = true;
input.applies_criticality = "Any";
input.priority = "Medium";
input.service_type = "General";
input.response_hours = 24;
input.resolution_hours = 72;

resolveSlaHours

Return the response and resolution hours for a ticket. Picks the best matching active SLA_Definitions row (exact criticality beats Any), and if none exists falls back to a built-in priority map. Parameters are renamed sectionIn/priorityIn/criticalityIn so the fetch criteria compare a field to a variable, not a field to itself. Returns a Map with keys response and resolution.

map resolveSlaHours(string sectionIn, string priorityIn, string criticalityIn)
{
	result = Map();
	svc = ifnull(sectionIn,"");
	if(svc == "")
	{
		svc = "General";
	}
	prio = ifnull(priorityIn,"Medium");
	crit = ifnull(criticalityIn,"Any");
	// fallback priority map (used when no definition row matches)
	respH = 24;
	resltnH = 72;
	if(prio == "Critical")
	{
		respH = 4;
		resltnH = 8;
	}
	else if(prio == "High")
	{
		respH = 8;
		resltnH = 24;
	}
	else if(prio == "Medium")
	{
		respH = 24;
		resltnH = 72;
	}
	else if(prio == "Low")
	{
		respH = 72;
		resltnH = 168;
	}
	// best-match active definition -- exact criticality wins over Any
	chosen = null;
	anyRow = null;
	defs = SLA_Definitions[active == true && service_type == svc && priority == prio];
	for each  d in defs
	{
		if(d.applies_criticality == crit)
		{
			chosen = d;
			break;
		}
		if(d.applies_criticality == "Any" && anyRow == null)
		{
			anyRow = d;
		}
	}
	if(chosen == null)
	{
		chosen = anyRow;
	}
	if(chosen != null)
	{
		respH = chosen.response_hours;
		resltnH = chosen.resolution_hours;
	}
	result.put("response",respH);
	result.put("resolution",resltnH);
	return result;
}

stampSlaDue

Saved-record twin of the On Validate stamping. For an existing work order it reads Section1, priority and the first selected asset criticality, calls resolveSlaHours, and writes response_due, resolution_due and target_completion. Called by the daily scan and by any Recompute SLA custom action; the On Validate form script does the same inline for the create/edit path.

void stampSlaDue(int woId)
{
	for each  wo in work_orders[ID == woId]
	{
		section = ifnull(wo.Section1,"");
		if(section == "")
		{
			section = "General";
		}
		prio = ifnull(wo.priority,"Medium");
		// asset is MULTI_SELECT_LOOKUP -- take the first selected asset criticality
		crit = "Any";
		for each  aId in wo.asset
		{
			crit = assets[ID == aId].criticality;
			break;
		}
		if(crit == null)
		{
			crit = "Any";
		}
		hoursMap = thisapp.resolveSlaHours(section,prio,crit);
		respH = hoursMap.get("response");
		resltnH = hoursMap.get("resolution");
		baseTime = wo.raised_on;
		if(baseTime == null)
		{
			baseTime = zoho.currenttime;
		}
		// adding a decimal N to a Date-Time adds N days, so hours -> hours/24
		wo.response_due = baseTime + ((respH * 1.0) / 24);
		wo.resolution_due = baseTime + ((resltnH * 1.0) / 24);
		wo.target_completion = wo.resolution_due;
	}
}

captureFirstResponse

Saved-record twin of the status field script. Stamps first_response_on once, computes response_time_mins from raised_on, and sets sla_response_met when the response landed on or before response_due. Used by the daily scan and any blueprint transition that moves a ticket to In progress outside the form.

void captureFirstResponse(int woId)
{
	for each  wo in work_orders[ID == woId]
	{
		if(wo.first_response_on == null)
		{
			fr = zoho.currenttime;
			wo.first_response_on = fr;
			if(wo.raised_on != null)
			{
				wo.response_time_mins = wo.raised_on.minutesBetween(fr);
			}
			metFlag = false;
			if(wo.response_due != null && fr <= wo.response_due)
			{
				metFlag = true;
			}
			wo.sla_response_met = metFlag;
		}
	}
}

closeSla

Saved-record twin of the status field script for closure. Ensures completed_on is set, computes resolution_time_mins from raised_on, and sets sla_resolution_met when closure landed on or before resolution_due. Used by the daily scan and blueprint/API closures.

void closeSla(int woId)
{
	for each  wo in work_orders[ID == woId]
	{
		cot = wo.completed_on;
		if(cot == null)
		{
			cot = zoho.currenttime;
			wo.completed_on = cot;
		}
		if(wo.raised_on != null)
		{
			wo.resolution_time_mins = wo.raised_on.minutesBetween(cot);
		}
		metFlag = false;
		if(wo.resolution_due != null && cot <= wo.resolution_due)
		{
			metFlag = true;
		}
		wo.sla_resolution_met = metFlag;
	}
}

sendSlaBreachMail

Compose and send one breach email for a work order. Recipient is the site supervisor found via Supervisor[SITE == wo.SITE]; assigned technicians (MULTI_SELECT_LOOKUP) are cc-ed by iterating their email field. If no supervisor is found it falls back to the org admin so a breach is never dropped. breachType is response or resolution and selects which due date is quoted.

void sendSlaBreachMail(int woId, string breachType)
{
	for each  wo in work_orders[ID == woId]
	{
		toAddr = "";
		for each  s in Supervisor[SITE == wo.SITE]
		{
			if(s.Email != null && s.Email != "")
			{
				toAddr = s.Email;
				break;
			}
		}
		ccAddr = "";
		for each  tId in wo.assigned_technician
		{
			te = technicians[ID == tId].email;
			if(te != null && te != "")
			{
				if(ccAddr != "")
				{
					ccAddr = ccAddr + ",";
				}
				ccAddr = ccAddr + te;
			}
		}
		if(toAddr == "")
		{
			toAddr = zoho.adminuserid;
		}
		label = "response";
		dueVal = wo.response_due;
		if(breachType == "resolution")
		{
			label = "resolution";
			dueVal = wo.resolution_due;
		}
		subjectTxt = "SLA " + label + " breach -- WO " + wo.work_order_number;
		bodyTxt = "<p>Work Order <b>" + wo.work_order_number + "</b> has breached its " + label + " SLA.</p>";
		bodyTxt = bodyTxt + "<p>Priority " + ifnull(wo.priority,"-") + "<br>Section " + ifnull(wo.Section1,"-") + "<br>Due " + dueVal + "<br>Status " + wo.status + "</p>";
		sendmail
		[
			from : zoho.adminuserid
			to : toAddr
			cc : ccAddr
			subject : subjectTxt
			message : bodyTxt
		]
	}
}

scanSlaBreaches

Daily scheduled scan. Iterates open work orders (status not Completed/Verified/Cancelled/Draft) and applies both breach checks: past response_due with no first response, and past resolution_due while still open. Sends one email per breach through sendSlaBreachMail and sets the matching guard checkbox so it never re-sends.

void scanSlaBreaches()
{
	nowT = zoho.currenttime;
	openWos = work_orders[status != "Completed" && status != "Verified" && status != "Cancelled" && status != "Draft"];
	for each  wo in openWos
	{
		// response breach -- overdue with no first response yet
		if(wo.first_response_on == null && wo.response_due != null && nowT > wo.response_due && wo.response_breach_notified == false)
		{
			thisapp.sendSlaBreachMail(wo.ID,"response");
			wo.response_breach_notified = true;
		}
		// resolution breach -- overdue and still open
		if(wo.resolution_due != null && nowT > wo.resolution_due && wo.resolution_breach_notified == false)
		{
			thisapp.sendSlaBreachMail(wo.ID,"resolution");
			wo.resolution_breach_notified = true;
		}
	}
}

Define SLA rows per service and priority

1. Open the SLA_Definitions form.
2. Create a row: name HVAC Critical, service_type HVAC, priority Critical, applies_criticality Any, response_hours 4, resolution_hours 8, active checked, SITE blank.
3. Repeat for each service_type and priority you want to govern; use applies_criticality to add a tighter row (e.g. HVAC High for Critical assets).
4. Leave SITE blank for a global rule, or pick a site to create a site-specific override that beats the global fallback.

Ticket first response within SLA

1. Raise a work order: Section1 HVAC, priority Critical, an asset selected, raised_on 09:00.
2. On Validate calls resolveSlaHours -> 4h/8h, stamps response_due 13:00, resolution_due 17:00, target_completion 17:00.
3. Technician sets status to In progress at 10:30.
4. status On user input stamps first_response_on 10:30, response_time_mins 90, and sla_response_met true because 10:30 <= 13:00.

First response over SLA then resolution met

1. Same ticket, but the first In progress happens at 14:00.
2. response_time_mins 300 and sla_response_met false because 14:00 > 13:00.
3. Technician sets status Completed at 16:30; completed_on 16:30.
4. resolution_time_mins recorded and sla_resolution_met true because 16:30 <= 17:00.

Breach escalates once

1. Ticket reaches 13:00 still Assigned with first_response_on empty.
2. The next Daily SLA Scan sees now > response_due and first_response_on null, so sendSlaBreachMail emails the supervisor and cc's the technicians, then sets response_breach_notified true.
3. Following scans skip it (guard true) so it is not re-emailed.
4. If the ticket is still open past 17:00, the resolution branch emails once and sets resolution_breach_notified true.

ppm_schedules — On Load — Create (new record)

Pre-fills defaults so a supervisor only picks the asset and cadence. Sets a provisional code, defaults, seeds next_due_date, toggles the custom-days field, and pins SITE for portal supervisors.

// PPM Schedules :: On Load :: Create
// 1) Provisional code - finalized to a unique PPM code in On Success
if(input.schedule_code == null || input.schedule_code == "")
{
    seq = ppm_schedules.count() + 1;
    input.schedule_code = "TMP-" + seq;
}
// 2) Cadence + lifecycle defaults
if(input.frequency == null || input.frequency == "")
{
    input.frequency = "Monthly";
}
if(input.status == null || input.status == "")
{
    input.status = "Active";
}
// 3) Anchor to today when no start date is carried in
if(input.start_date == null)
{
    input.start_date = zoho.currentdate;
}
// 4) Seed the first due date from the start date
input.next_due_date = computeNextDueDate(input.frequency, input.custom_interval_days, input.start_date);
// 5) Custom interval only matters for the Custom days cadence
if(input.frequency == "Custom days")
{
    show custom_interval_days;
}
else
{
    hide custom_interval_days;
}
// 6) Portal supervisors are pinned to their own site
loginEmail = zoho.loginuserid;              // portal login exposes the EMAIL here, not loginuser
siteId = getSiteForUser(loginEmail);        // owner-context helper, returns 0 when none
if(siteId != 0)
{
    input.SITE = siteId;                    // single-select lookup = one record id
    disable SITE;
}

ppm_schedules — On Load — Edit (existing record)

Keeps the custom-days field visibility correct when reopening a saved plan and keeps SITE locked for portal supervisors.

// PPM Schedules :: On Load :: Edit
if(input.frequency == "Custom days")
{
    show custom_interval_days;
}
else
{
    hide custom_interval_days;
}
// Portal supervisors cannot move a plan to another site
loginEmail = zoho.loginuserid;
siteId = getSiteForUser(loginEmail);
if(siteId != 0)
{
    disable SITE;
}

ppm_schedules — frequency — On user input

Toggles and clears the custom-days field, then recomputes next_due_date from the best available base date.

// PPM Schedules :: frequency :: On user input
if(input.frequency == "Custom days")
{
    show custom_interval_days;
}
else
{
    input.custom_interval_days = null;   // clear stale value
    hide custom_interval_days;
}
// Base the recompute on last completed date, else start date, else today
baseDate = input.start_date;
if(input.last_completed_date != null)
{
    baseDate = input.last_completed_date;
}
if(baseDate == null)
{
    baseDate = zoho.currentdate;
}
input.next_due_date = computeNextDueDate(input.frequency, input.custom_interval_days, baseDate);

ppm_schedules — custom_interval_days — On user input

The interval drives the due date, so recompute whenever it changes on a Custom days plan.

// PPM Schedules :: custom_interval_days :: On user input
if(input.frequency == "Custom days" && input.custom_interval_days != null && input.custom_interval_days > 0)
{
    baseDate = input.start_date;
    if(input.last_completed_date != null)
    {
        baseDate = input.last_completed_date;
    }
    if(baseDate == null)
    {
        baseDate = zoho.currentdate;
    }
    input.next_due_date = computeNextDueDate(input.frequency, input.custom_interval_days, baseDate);
}

ppm_schedules — start_date — On user input

A brand-new plan is anchored to its start date, so re-seed the first due date when it changes.

// PPM Schedules :: start_date :: On user input
if(input.start_date != null)
{
    input.next_due_date = computeNextDueDate(input.frequency, input.custom_interval_days, input.start_date);
}

ppm_schedules — On Validate — Create and Edit

Runs on Submit/Update before the save. Blocks bad combinations and repairs a missing next_due_date. Confirm the exact abort keyword (cancel submit) in your Creator build.

// PPM Schedules :: On Validate :: Create and Edit
// 1) Custom cadence needs a positive interval
if(input.frequency == "Custom days")
{
    if(input.custom_interval_days == null || input.custom_interval_days <= 0)
    {
        alert "Enter a positive Custom Interval days value for a Custom days schedule.";
        cancel submit;
    }
}
// 2) End date cannot precede start date
if(input.end_date != null && input.start_date != null && input.end_date < input.start_date)
{
    alert "End Date cannot be earlier than Start Date.";
    cancel submit;
}
// 3) Guarantee a sane next due date
if(input.next_due_date == null || (input.start_date != null && input.next_due_date < input.start_date))
{
    input.next_due_date = computeNextDueDate(input.frequency, input.custom_interval_days, input.start_date);
}

ppm_schedules — On Success — Create and Edit

Finalizes a unique schedule_code once the record ID exists. Optional block raises the first WO immediately if the plan is already due; delete it if the nightly scheduler alone is enough. Assigning input.<field> here persists to the saved record.

// PPM Schedules :: On Success :: Create and Edit
// 1) Finalize a unique readable code now that the record ID exists
if(input.schedule_code == null || input.schedule_code.startsWith("TMP-"))
{
    input.schedule_code = "PPM-" + zoho.currentdate.toString("yyyyMMdd") + "-" + input.ID;
}
// 2) OPTIONAL - raise the first WO now if already due within the lead window.
//    Delete this block if the nightly scheduler is enough.
if(input.status == "Active" && input.next_due_date != null)
{
    lead = 0;
    if(input.lead_time_days != null)
    {
        lead = input.lead_time_days;
    }
    if(input.next_due_date <= zoho.currentdate.addDay(lead))
    {
        alreadyRaised = false;
        sameDayWOs = work_orders[job_type == "Preventive" && scheduled_date == input.next_due_date];
        for each wo in sameDayWOs
        {
            if(wo.source_schedule.contains(input.ID))
            {
                alreadyRaised = true;
                break;
            }
        }
        if(alreadyRaised == false)
        {
            insert into work_orders
            [
                job_type = "Preventive"
                SITE = input.SITE
                asset = input.asset
                checklist_template = input.checklist_template
                assigned_technician = input.assigned_technician
                source_schedule = {input.ID}
                scheduled_date = input.next_due_date
                estimated_duration_minutes = input.estimated_duration_minutes
                raised_on = zoho.currentdate
                priority = "Medium"
                status = "Open"
            ];
            // keep the calendar moving so tonight scheduler does not raise it again
            input.next_due_date = computeNextDueDate(input.frequency, input.custom_interval_days, input.next_due_date);
        }
    }
}

ppm_schedules — On Delete

Preserves live work orders that point at the deleted plan by emptying their multi-select source_schedule link and annotating them, so no dangling reference is left.

// PPM Schedules :: On Delete
// Detach live WOs from the plan being deleted so history is preserved
openWOs = work_orders[status == "Open" || status == "Assigned" || status == "In progress"];
for each wo in openWOs
{
    if(wo.source_schedule.contains(input.ID))
    {
        wo.source_schedule = list();   // empty the multi-select lookup - no dangling link
        wo.Description = wo.Description + " [Source PPM schedule deleted on " + zoho.currentdate + "]";
    }
}

work_orders — On Success — Edit (closes the PPM loop; lives on the work_orders form)

When a generated WO becomes Completed: Preventive stamps last_completed_date on the source schedule; Predictive resets hours_at_last_service on the asset. Uncomment the recompute line to switch to a floating (completion-based) calendar.

// Work Orders :: On Success :: Edit  (documented in this module because it closes the PPM loop)
if(input.status == "Completed")
{
    // Preventive - stamp the schedule actual completion date
    if(input.job_type == "Preventive")
    {
        for each schedId in input.source_schedule   // multi-select lookup = list of ids
        {
            schedRecs = ppm_schedules[ID == schedId];
            for each s in schedRecs
            {
                s.last_completed_date = zoho.currentdate;
                // Fixed calendar - the scheduler already rolled next_due_date.
                // For FLOATING PPM instead, uncomment the next line:
                // s.next_due_date = computeNextDueDate(s.frequency, s.custom_interval_days, zoho.currentdate);
                break;
            }
        }
    }
    // Predictive - reset the asset hours baseline so the meter starts fresh
    if(input.job_type == "Predictive")
    {
        for each assetId in input.asset
        {
            astRecs = assets[ID == assetId];
            for each a in astRecs
            {
                a.hours_at_last_service = a.current_running_hours;
                break;
            }
        }
    }
}

computeNextDueDate(frequency, customDays, fromDate)

Shared helper. Returns the next due date for a cadence label from a base date. Used by On Load, the frequency and interval on-user-input handlers, On Validate, and both scheduled generators.

// FUNCTION (Standalone) : computeNextDueDate
// Returns the next due date for a cadence from a base date.
date computeNextDueDate(string frequency, int customDays, date fromDate)
{
    // Anchor to today when no base date is supplied
    baseDate = fromDate;
    if(baseDate == null)
    {
        baseDate = zoho.currentdate;
    }
    nextDate = baseDate;
    // Add the interval that matches the chosen cadence
    if(frequency == "Daily")
    {
        nextDate = baseDate.addDay(1);
    }
    else if(frequency == "Weekly")
    {
        nextDate = baseDate.addDay(7);
    }
    else if(frequency == "Monthly")
    {
        nextDate = baseDate.addMonth(1);
    }
    else if(frequency == "Quarterly")
    {
        nextDate = baseDate.addMonth(3);
    }
    else if(frequency == "Half-yearly")
    {
        nextDate = baseDate.addMonth(6);
    }
    else if(frequency == "Yearly")
    {
        nextDate = baseDate.addYear(1);
    }
    else if(frequency == "Custom days")
    {
        // Fall back to 30 days when the interval is missing or invalid
        interval = customDays;
        if(interval == null || interval <= 0)
        {
            interval = 30;
        }
        nextDate = baseDate.addDay(interval);
    }
    return nextDate;
}

generatePpmWorkOrders()

SCHEDULED (daily). Scans Active schedules whose next_due_date is within today plus lead_time_days, raises one Open Preventive work order per plan (guarded against duplicates), then rolls next_due_date forward one cycle. Auto-ends plans past their end_date.

// FUNCTION (Standalone / owner context) : generatePpmWorkOrders
// Called ONCE per day by the daily schedule. Raises Preventive WOs and rolls plans forward.
void generatePpmWorkOrders()
{
    today = zoho.currentdate;
    // Only Active plans that still have a due date are candidates
    dueSchedules = ppm_schedules[status == "Active" && next_due_date != null];
    for each sched in dueSchedules
    {
        // Auto-end a plan that has passed its end date
        if(sched.end_date != null && sched.end_date < today)
        {
            sched.status = "Ended";
            continue;
        }
        // Lead time defaults to 0 days when blank
        lead = 0;
        if(sched.lead_time_days != null)
        {
            lead = sched.lead_time_days;
        }
        // Fire once the due date is within (today + lead time)
        if(sched.next_due_date <= today.addDay(lead))
        {
            // Duplicate guard : any Preventive WO for this plan + due date already?
            // source_schedule is MULTI-select, so test with .contains, not ==
            alreadyRaised = false;
            sameDayWOs = work_orders[job_type == "Preventive" && scheduled_date == sched.next_due_date];
            for each wo in sameDayWOs
            {
                if(wo.source_schedule.contains(sched.ID))
                {
                    alreadyRaised = true;
                    break;
                }
            }
            if(alreadyRaised == false)
            {
                // asset / checklist_template / assigned_technician are MULTI-select lookups:
                // the plan already holds them as lists, so assign straight across.
                // source_schedule is MULTI-select too, so wrap the single id in a list {}.
                insert into work_orders
                [
                    job_type = "Preventive"
                    SITE = sched.SITE
                    asset = sched.asset
                    checklist_template = sched.checklist_template
                    assigned_technician = sched.assigned_technician
                    source_schedule = {sched.ID}
                    scheduled_date = sched.next_due_date
                    estimated_duration_minutes = sched.estimated_duration_minutes
                    raised_on = zoho.currentdate
                    priority = "Medium"
                    status = "Open"
                ];
            }
            // Roll forward from the due date we just actioned (fixed calendar)
            sched.next_due_date = computeNextDueDate(sched.frequency, sched.custom_interval_days, sched.next_due_date);
        }
    }
}

generatePredictiveWorkOrders()

SCHEDULED (daily, right after the PPM pass). Scans metered assets where current_running_hours minus hours_at_last_service has reached runtime_threshold and raises one Open Predictive WO per asset. Hours are NOT reset here - that happens on WO completion.

// FUNCTION (Standalone / owner context) : generatePredictiveWorkOrders
// Called ONCE per day right after generatePpmWorkOrders. Raises Predictive WOs on run-hour breach.
void generatePredictiveWorkOrders()
{
    // Metered assets with a real positive threshold only
    meteredAssets = assets[track_running_hours == true && runtime_threshold != null && runtime_threshold > 0];
    for each ast in meteredAssets
    {
        // Hours accumulated since the last service reset
        baseline = 0.0;
        if(ast.hours_at_last_service != null)
        {
            baseline = ast.hours_at_last_service;
        }
        current = 0.0;
        if(ast.current_running_hours != null)
        {
            current = ast.current_running_hours;
        }
        hoursSinceService = current - baseline;
        if(hoursSinceService >= ast.runtime_threshold)
        {
            // One Open Predictive WO per asset at a time
            alreadyOpen = false;
            openPred = work_orders[job_type == "Predictive" && status == "Open"];
            for each wo in openPred
            {
                if(wo.asset.contains(ast.ID))
                {
                    alreadyOpen = true;
                    break;
                }
            }
            if(alreadyOpen == false)
            {
                // asset is MULTI-select on work_orders, so wrap the single id in a list {}
                insert into work_orders
                [
                    job_type = "Predictive"
                    SITE = ast.SITE
                    asset = {ast.ID}
                    scheduled_date = zoho.currentdate
                    raised_on = zoho.currentdate
                    priority = "High"
                    status = "Open"
                    Description = "Auto raised - run hours since last service " + hoursSinceService + " reached threshold " + ast.runtime_threshold
                ];
                // Do NOT reset hours here. hours_at_last_service is reset to
                // current_running_hours when this Predictive WO is marked Completed.
            }
        }
    }
}

1. Create a monthly PPM plan

1. Supervisor opens New PPM Schedule. On Load fills a provisional schedule_code, frequency Monthly, status Active, start_date today, and computes next_due_date one month out.
2. SITE auto-fills to their site via getSiteForUser and is disabled so they cannot change it.
3. They pick the asset(s) and checklist_template, set lead_time_days 7, assigned_technician and estimated_duration_minutes.
4. Frequency stays Monthly so custom_interval_days remains hidden and On Validate needs no interval.
5. On Save, On Success replaces the TMP- code with a unique PPM-yyyyMMdd-<recordID>. Result: an Active monthly plan with next_due_date set.

2. Daily scheduler raises due WOs

1. At 01:00 the daily Creator schedule calls generatePpmWorkOrders.
2. It loads Active schedules with a next_due_date and, for each, checks whether next_due_date is within today plus lead_time_days.
3. For a due plan with no existing Open Preventive WO on that date, it inserts a work_orders record: job_type Preventive, SITE and asset and checklist_template and assigned_technician copied from the plan, source_schedule set to the plan as a one-item list, scheduled_date equal to next_due_date, status Open.
4. It rolls next_due_date forward one cycle via computeNextDueDate so the plan will not re-raise tomorrow.
5. Any Active plan past its end_date is set to Ended and skipped.

3. Predictive trigger from running hours

1. Ops updates an asset current_running_hours to 1510. The asset has track_running_hours ticked, runtime_threshold 500 and hours_at_last_service 1000.
2. The daily generatePredictiveWorkOrders computes 1510 minus 1000 = 510, which is greater than or equal to 500.
3. No Open Predictive WO exists for the asset, so it inserts a Predictive WO with SITE and asset from the asset record, status Open, priority High, and a description noting the hours.
4. hours_at_last_service is left unchanged, so the asset is not re-flagged every night while that WO is still Open.

4. Completing a PPM WO rolls the schedule forward

1. The technician sets the generated work order status to Completed.
2. work_orders On Success sees status Completed with job_type Preventive and writes last_completed_date = today onto the source_schedule record.
3. next_due_date was already advanced by the scheduler when the WO was raised, so the plan sits ready for its next cycle under the fixed-calendar model.
4. For a completed Predictive WO instead, hours_at_last_service is reset to current_running_hours so the run-hour counter restarts from zero.

assets — On Load (Create)

Binds the new asset to the supervisor's site, defaults status, and seeds a provisional Asset ID. The category prefix is refined later by the category On-user-input script.

// Fires when a NEW Assets form is opened.
// 1) Bind to the supervisor's site (SITE is admin-only, so this fills it server-side)
loginEmail = zoho.loginuserid;				// portal login exposes the email HERE (not zoho.loginuser)
mySite = getSiteForUser(loginEmail);		// owner-context fn -> SITE record id, 0 if none
if(mySite != 0)
{
	input.SITE = mySite;					// set the single-select lookup by record id
	disable SITE;							// lock it for admins who can see the field
}
// 2) Default operating status
if(input.status == null || input.status == "")
{
	input.status = "In service";
}
// 3) Seed a provisional Asset ID using the site code + a generic prefix + serial
centerCode = "";
if(mySite != 0)
{
	for each s in SITE[ID == mySite]
	{
		centerCode = s.Site_Code;			// real field on the SITE form
		break;								// bounded loop, single record expected
	}
}
input.asset_id = buildAssetId(centerCode, "GEN", input.serial_number);

assets — On Load (Edit)

PREREQUISITE: add hidden field prev_status first (Dev Note 1). Locks identity fields and snapshots status so On Success (Edit) can detect a transition.

// Fires when an EXISTING Assets record is opened for editing.
// Lock the identity fields so Asset ID and site cannot drift
disable asset_id;
disable SITE;
// Snapshot the current status for change detection.
// Requires hidden single-line field prev_status - ADD IT FIRST (Dev Note 1).
input.prev_status = input.status;

assets — asset_category — On user input

asset_category is a MULTI-select lookup (a list of IDs). Uses the first selected category to set the Asset ID prefix, then rebuilds the ID.

// Fires when the user selects/changes Asset Category (multi-select lookup -> asset_categories).
catPrefix = "GEN";
for each catId in input.asset_category		// multi-select = list of record ids
{
	for each c in asset_categories[ID == catId]
	{
		nm = c.category_name;
		if(nm != null && nm.trim() != "")
		{
			clean = nm.trim().toUppercase().replaceAll(" ","");
			prefixLen = if(clean.length() >= 3,3,clean.length());
			if(prefixLen > 0)
			{
				catPrefix = clean.subString(0,prefixLen);
			}
		}
		break;								// use the first category record only
	}
	break;									// bounded: stop after the first selected id
}
// Rebuild the Asset ID keeping the site code + serial
centerCode = "";
if(input.SITE != null && input.SITE != 0)
{
	for each s in SITE[ID == input.SITE]
	{
		centerCode = s.Site_Code;
		break;
	}
}
input.asset_id = buildAssetId(centerCode, catPrefix, input.serial_number);

assets — On Validate (Create)

asset_id is NOT DB-unique, so enforce per-site uniqueness here. In the On Validate event, the alert statement both warns the user AND cancels the save.

// Runs before the record is saved. WHILE is not allowed - use for-each + break.
idToCheck = input.asset_id;
if(idToCheck != null && idToCheck.trim() != "")
{
	hit = false;
	// Same-site scope: IDs are only meant to be unique per site
	for each m in assets[asset_id == idToCheck && SITE == input.SITE]
	{
		hit = true;
		break;								// one match is enough
	}
	if(hit)
	{
		// alert in On Validate stops the submission
		alert "Asset ID " + idToCheck + " already exists at this site. Change the Serial Number or Category and try again.";
	}
}

assets — On Success (Create)

Writes the creation entry to the audit trail.

// Fires after a NEW Assets record is saved.
logAudit("Asset", input.ID.toString(), "Created", "asset_id", "", input.asset_id);

assets — On Success (Edit)

Detects a status transition using the prev_status snapshot and logs a Status change row.

// Fires after an EXISTING Assets record is saved.
if(input.status != input.prev_status)
{
	logAudit("Asset", input.ID.toString(), "Status change", "status", input.prev_status, input.status);
}

assets — On Delete

Set on the Assets report (On Delete). Keeps a tombstone in the audit log before the row is gone.

// Fires when an Assets record is deleted.
logAudit("Asset", input.ID.toString(), "Deleted", "asset_id", input.asset_id, "");

Asset_Contract — On Load (Create)

Fills SITE from the supervisor and defaults Start_Date to today.

// Fires when a NEW Asset Contract form is opened.
loginEmail = zoho.loginuserid;
mySite = getSiteForUser(loginEmail);
if(mySite != 0)
{
	input.SITE = mySite;					// single-select lookup by record id
}
if(input.Start_Date == null)
{
	input.Start_Date = zoho.currentdate;	// default start = today
}

Asset_Contract — On Success (Create)

audit_log has no Contract reference type, so the contract is logged against its parent Asset with the term in new_value.

// Fires after a contract is saved. assets is a single-select lookup (one record id).
astId = "";
if(input.assets != null && input.assets != 0)
{
	astId = input.assets.toString();
}
logAudit("Asset", astId, "Updated", "service_contract", "", "Contract " + input.Start_Date + " to " + input.End_Date);

Scheduled function — Contract expiry reminder (daily)

Standalone scheduled function, run once per day (~30 runs/month, well under the 90/user/month cap). Set the real FM recipient before enabling.

// Standalone function on a DAILY schedule. Emails 30 days before a contract ends.
void remindContractExpiry()
{
	targetDate = zoho.currentdate.addDay(30);		// the day exactly 30 days out
	for each c in Asset_Contract[End_Date == targetDate]
	{
		astName = "Asset";
		if(c.assets != null && c.assets != 0)
		{
			for each a in assets[ID == c.assets]
			{
				astName = a.asset_name;
				break;
			}
		}
		toAddr = "facilities@jood.example";			// TODO set real FM mailbox / per-site supervisor
		sendmail
		[
			from : zoho.adminuserid
			to : toAddr
			subject : "Contract expiring in 30 days - " + astName
			message : "The service contract for " + astName + " ends on " + c.End_Date + ". Please start the renewal."
		];
		logAudit("Asset", c.assets.toString(), "Updated", "contract_reminder", "", "30-day expiry notice sent " + zoho.currentdate);
	}
}

Scheduled function — Warranty expiry reminder (daily)

Standalone scheduled function, run once per day. Skips decommissioned assets.

// Standalone function on a DAILY schedule. Emails 30 days before warranty lapses.
void remindWarrantyExpiry()
{
	targetDate = zoho.currentdate.addDay(30);
	for each a in assets[warranty_expiry == targetDate && status != "Decommissioned"]
	{
		toAddr = "facilities@jood.example";			// TODO set real FM mailbox / per-site supervisor
		sendmail
		[
			from : zoho.adminuserid
			to : toAddr
			subject : "Warranty expiring in 30 days - " + a.asset_name
			message : a.asset_name + " " + a.asset_id + " warranty ends on " + a.warranty_expiry + "."
		];
		logAudit("Asset", a.ID.toString(), "Updated", "warranty_reminder", "", "30-day warranty notice sent " + zoho.currentdate);
	}
}

buildAssetId

Builds a readable, well-formed Asset ID from the site code, a category prefix, and the serial number. Falls back to safe defaults so the ID is never malformed, e.g. AST-HAIL-HVAC-004521.

// Standalone (custom) function. Return type: string.
// Example result: AST-HAIL-HVAC-004521
string buildAssetId(string siteCenterCode, string categoryPrefix, string serial)
{
	// Normalise the three parts - trim, upper-case, and default when blank
	cc = if(siteCenterCode == null || siteCenterCode.trim() == "","STE",siteCenterCode.trim().toUppercase());
	cp = if(categoryPrefix == null || categoryPrefix.trim() == "","GEN",categoryPrefix.trim().toUppercase());
	sn = if(serial == null,"",serial.trim().toUppercase());
	sn = sn.replaceAll(" ","");
	// No serial captured yet - use a timestamp so two blank-serial assets do not collide
	if(sn == "")
	{
		sn = zoho.currenttime.toString("yyMMddHHmmss");
	}
	return "AST-" + cc + "-" + cp + "-" + sn;
}

logAudit

Single choke-point that writes one row to audit_log. Stamps changed_by with the portal email (zoho.loginuserid) and changed_on with the current date-time. For Asset references it best-effort resolves and fills the SITE lookup.

// Standalone (custom) function. Return type: void.
// Call it as: logAudit("Asset", input.ID.toString(), "Created", "asset_id", "", input.asset_id);
void logAudit(string refType, string refId, string action, string field, string oldVal, string newVal)
{
	// Best-effort site scoping. Only Asset references can be resolved to a SITE here.
	siteId = 0;
	if(refType == "Asset" && refId != null && refId.trim() != "")
	{
		if(refId.matches("[0-9]+"))				// guard - refId must be a numeric record id
		{
			for each a in assets[ID == refId.toLong()]
			{
				siteId = a.SITE;				// lookup returns the linked SITE record id
				break;						// one record expected - bounded loop (no WHILE)
			}
		}
	}
	// Write the row. SITE = 0 leaves the lookup blank (the app's convention for none).
	insert into audit_log
	[
		SITE = siteId
		reference_type = refType
		reference_id = refId
		action_1 = action
		field_changed = field
		old_value = oldVal
		new_value = newVal
		changed_by = zoho.loginuserid		// portal email - NOT zoho.loginuser
		changed_on = zoho.currenttime		// current date-time
	];
}

Register an asset and link its O&M manuals

1. Supervisor opens the Assets form. On Load fills SITE (admin-only, set via getSiteForUser), defaults status to In service, and seeds a provisional Asset ID.
2. They enter asset_name, make, model, serial_number, then pick asset_category (multi-select) — the On-user-input script rebuilds the Asset ID with the category prefix, e.g. AST-HAIL-HVAC-250918.
3. They upload the actual O&M PDFs into the site's WorkDrive/S3 folder (Creator storage is capped) and paste that FOLDER LINK into documents_folder, which is a URL field, not an upload box.
4. On Save, On Validate confirms the Asset ID is unique for that site (alert blocks a duplicate). On Success writes a Created row to audit_log stamped with the portal email and current time.

Add a service contract with files

1. Open the Asset Contract form. On Load fills SITE and defaults Start_Date to today.
2. Select the parent asset in the assets lookup, set End_Date, and upload up to 10 contract files into Files.
3. On Save, On Success logs an Updated row against the PARENT ASSET (audit_log has no Contract type) with the term recorded in new_value.
4. The daily Contract expiry reminder will email the FM team 30 days before End_Date.

An edit is captured in the audit log

1. Open an existing asset. On Load (Edit) locks Asset ID and SITE and snapshots status into the hidden prev_status field.
2. Change status from In service to Under repair and Save.
3. On Success (Edit) sees input.status != input.prev_status and writes a Status change row: field_changed=status, old_value=In service, new_value=Under repair, changed_by=portal email, changed_on=now.
4. If status is unchanged, no audit row is written — only real transitions are logged.

Planned enhancements (handover checklist)

1. Add hidden prev_status to assets BEFORE deploying the edit scripts (Dev Note 1).
2. Add an ownership field to assets and a revision/supersedes pair to Asset_Contract (Dev Notes 2 and 3).
3. Convert photo to an Image/File field or store an image URL, and confirm documents_folder is treated as a link, not an upload (Dev Notes 4 and 5).
4. Optionally add a category_prefix field to asset_categories and a Contract option to audit_log.reference_type (Dev Notes 7 and 8).

stock_movements — On Load - Create

Runs only when the Add form opens. Prefills the number, timestamp, user and a sensible default type, and defaults SITE to the supervisor's site via the existing owner-context helper.

// Auto number + stamps - only on the Create / Add form
input.movement_number = thisapp.genMovementNumber();
input.moved_on = zoho.currenttime;
input.moved_by = zoho.loginuserid;  // portal login email lives here, NOT zoho.loginuser
if(input.movement_type == null)
{
	input.movement_type = "Receipt";  // default type
}
// default SITE from the existing owner-context function
siteId = thisapp.getSiteForUser(zoho.loginuserid);
if(siteId != 0)
{
	input.SITE = siteId;
}

stock_movements — part - On user input

Fires when the Part lookup value changes. Shows the live on-hand balance in remarks so the storekeeper sees stock before saving, and copies the part's SITE if none is set.

// Show the part's current balance the moment it is chosen
if(input.part != null)
{
	pm = parts_master[ID == input.part];
	bal = ifnull(pm.current_balance, 0);
	input.remarks = "On hand for " + pm.part_name + " is " + bal + " " + ifnull(pm.unit_of_measure, "");
	if(input.SITE == null)
	{
		input.SITE = pm.SITE;  // keep the movement on the part's own site
	}
}

stock_movements — On Validate (Add and Edit)

Guards stock. For Issue and Transfer it blocks the save when the requested quantity exceeds what is on hand. IMPORTANT: in a Validate workflow an alert both shows the message AND stops the submit - do not rely on that behaviour in other workflows.

// Block over-issue / over-transfer
if(input.movement_type == "Issue" || input.movement_type == "Transfer")
{
	if(input.part != null)
	{
		pm = parts_master[ID == input.part];
		onHand = ifnull(pm.current_balance, 0);
		if(input.quantity > onHand)
		{
			// in a Validate script an alert stops the record from saving
			alert "Cannot " + input.movement_type + " " + input.quantity + " - only " + onHand + " on hand for " + pm.part_name;
		}
	}
}

stock_movements — On Success - On Add

After the row is saved, delegate the four balance steps (signed_quantity, current_balance, balance_after, audit) to applyStockMovement so there is exactly one code path. input.ID is the new record id. The low-stock email workflow runs right after this.

// Post the movement to inventory
thisapp.applyStockMovement(input.ID);
// (Low-stock alert workflow runs next - see Workflows tab)

stock_movements — On Success - On Edit

When an existing movement is edited, reverse the previously applied signed_quantity and apply the new one, so the running balance stays correct. balance_after becomes a fresh snapshot (see devNotes on historical accuracy).

// Re-post an edited movement - reverse the old effect then apply the new
mv = stock_movements[ID == input.ID];
partId = mv.part;
if(partId != null)
{
	pm = parts_master[ID == partId];
	oldSigned = ifnull(mv.signed_quantity, 0);  // what this row applied last time
	newSigned = thisapp.signForMovement(mv.movement_type, ifnull(mv.quantity, 0));
	pm.current_balance = ifnull(pm.current_balance, 0) - oldSigned + newSigned;
	mv.signed_quantity = newSigned;
	mv.balance_after = pm.current_balance;
	mv.remarks = ifnull(mv.remarks, "") + "\n[audit-edit] " + zoho.loginuserid + " " + zoho.currenttime.toString() + " signed " + oldSigned + " to " + newSigned;
}

stock_movements — On Delete

Before the ledger row disappears, undo its effect on the running balance. The deleted record is available as input. Audit cannot be written on the row being deleted - log it in a separate form (see devNotes).

// Reverse this movement before it is removed
partId = input.part;
signed = ifnull(input.signed_quantity, 0);
if(partId != null)
{
	pm = parts_master[ID == partId];
	pm.current_balance = ifnull(pm.current_balance, 0) - signed;
}
// NOTE cannot audit on the row being deleted - write to a stock_audit_log form instead

parts_used — On Success - On Add

Each parts_used line becomes an Issue movement tied to its work order. QUIRK: part and work_order are MULTI-SELECT lookups here, so take the first selected id. insert into does NOT fire the stock_movements workflow, so applyStockMovement must be called explicitly.

// Turn a consumed part into an Issue stock movement
partId = null;
for each p in input.part
{
	partId = p;
	break;  // one part per usage line - take the first selected
}
woId = null;
for each w in input.work_order
{
	woId = w;
	break;
}
if(partId != null)
{
	newMvId = insert into stock_movements
	[
		SITE = input.SITE
		movement_number = thisapp.genMovementNumber()
		part = partId
		movement_type = "Issue"
		quantity = ifnull(input.quantity_used, 0)
		work_order = woId
		reference = "parts_used " + input.ID
		moved_on = zoho.currenttime
		moved_by = zoho.loginuserid
		remarks = "Auto Issue from parts consumption"
	];
	// insert does not trigger the movement workflow - post it manually
	thisapp.applyStockMovement(newMvId);
	// link the created movement back onto this row (multi-select field)
	row = parts_used[ID == input.ID];
	row.stock_movement = newMvId;
}

signForMovement

Turn a movement type plus an entered quantity into a signed number. Receipt, Return and a positive Adjustment add stock (+qty); Issue and Transfer-out remove stock (-qty). Adjustment keeps whatever sign the user typed, so a negative quantity reduces stock.

// Standalone function - call as thisapp.signForMovement(type, qty)
decimal signForMovement(string movementType, decimal qty)
{
	signed = qty;  // default increases stock - Receipt, Return, positive Adjustment
	if(movementType == "Issue" || movementType == "Transfer")
	{
		signed = qty * -1;  // consumption or transfer-out lowers this site balance
	}
	// Adjustment falls through - the sign the user entered is kept as-is
	return signed;
}

applyStockMovement

The single place all balance math lives. Given a saved stock_movements record id it computes the signed quantity, reads the part's current balance, writes signed_quantity and balance_after on the movement, updates parts_master.current_balance, and appends an audit line to remarks. Both the manual form path and the automatic parts_used path call this so balances never drift.

// Standalone function - call as thisapp.applyStockMovement(movementRecordId)
void applyStockMovement(int movementRecordId)
{
	// 1 - load the movement row that was just created or edited
	mv = stock_movements[ID == movementRecordId];
	partId = mv.part;  // single-select lookup returns the parts_master id
	if(partId == null)
	{
		return;  // nothing to post without a part
	}
	// 2 - compute the signed quantity from type + quantity
	signed = thisapp.signForMovement(mv.movement_type, ifnull(mv.quantity, 0));
	// 3 - read the current on-hand balance
	pm = parts_master[ID == partId];
	oldBal = ifnull(pm.current_balance, 0);
	newBal = oldBal + signed;
	// 4 - stamp the movement row
	mv.signed_quantity = signed;
	mv.balance_after = newBal;
	// 5 - move the running balance on the part
	pm.current_balance = newBal;
	// 6 - audit trail on the movement
	mv.remarks = ifnull(mv.remarks, "") + "\n[audit] " + zoho.loginuserid + " " + zoho.currenttime.toString() + " bal " + oldBal + " to " + newBal;
}

genMovementNumber

Produce the next human-readable movement number like MV-000042. Uses a bounded for-each to zero-pad because Creator Deluge forbids WHILE loops. See devNotes for the concurrency caveat.

// Standalone function - call as thisapp.genMovementNumber()
string genMovementNumber()
{
	// running sequence = how many movements already exist + 1
	cnt = stock_movements[ID != 0].count();
	seq = cnt + 1;
	padded = seq.toString();
	// zero-pad to 6 chars without a WHILE loop - bounded for-each
	for each idx in {1, 2, 3, 4, 5, 6}
	{
		if(padded.length() < 6)
		{
			padded = "0" + padded;
		}
	}
	return "MV-" + padded;
}

1 - Receipt increases balance

1. Storekeeper opens the Stock Movements Add form. On Load sets movement_number MV-000101, moved_on now, moved_by their login email, type Receipt, and SITE their supervisor site.
2. They pick the Part. The part On user input script shows On hand for Air Filter is 12 pcs in remarks.
3. They keep type Receipt, enter quantity 20 and Submit.
4. On Validate skips the stock check because Receipt is neither Issue nor Transfer.
5. On Success calls applyStockMovement. signForMovement returns +20, balance_after becomes 32, parts_master.current_balance becomes 32, and an audit line is appended.

2 - Issue against a work order decreases balance

1. Technician records a repair on Work Order WO-500 and needs 5 filters.
2. On the movement form they choose type Issue, part Air Filter, quantity 5 and work_order WO-500.
3. On Validate confirms 5 is not more than the 32 on hand, so the save proceeds.
4. On Success applyStockMovement returns -5, balance_after 27, current_balance 27.
5. Alternatively they log it on the parts_used form. Its On Success auto-creates the same Issue movement, calls applyStockMovement, and back-links the movement - balances end up identical.

3 - Transfer between sites

1. Site A is short so 10 filters move from Site B to Site A.
2. At Site B a movement type Transfer quantity 10 is saved. signForMovement returns -10 so Site B balance drops by 10.
3. The Inter-site Transfer workflow creates a paired Receipt at Site A for the same part_code, raising Site A balance by 10.
4. This needs the new from_site and to_site fields plus a lookup of the destination site's part by code - flagged in devNotes as not yet built.

4 - Adjustment and opening stock

1. A physical count finds 3 fewer units than the system shows.
2. A movement type Adjustment quantity -3 is entered. signForMovement keeps the sign, so current_balance drops by 3.
3. For opening stock a one-time Adjustment or Receipt equal to the counted quantity is posted so every balance change has a ledger row.
4. Until opening stock is migrated it sits directly in parts_master.current_balance and the first real movement stacks on top of it.

5 - Reorder alert fires

1. Air Filter reorder_level is 10 and after an Issue the balance lands at 8.
2. On Success finishes applyStockMovement, then the Low-stock alert workflow runs.
3. Because current_balance 8 is at or below reorder_level 10 and the part is active, a sendmail goes to the storekeeper.
4. The email names the part, current balance, reorder level and the suggested top-up to Maximum_Level.

work_orders — On Success (Create and Edit)

input.ID is the saved work order's id. Runs on both Create and Edit so attaching a template to an existing WO still builds the rows. If Creator rejects a bare function call, prefix with thisapp. (see devNotes).

// Work Orders > On Success (fires after the WO record is saved).
// If a checklist template is attached, build the technician's checklist.
if(input.checklist_template.size() > 0)
{
    // checklist_template is a multi-select lookup -> take the first template.
    templateId = input.checklist_template.get(0);

    // instantiateChecklist is idempotent, so re-saving the WO is safe.
    rowCount = instantiateChecklist(input.ID, templateId);
    info "Checklist rows ready: " + rowCount;
}

task_checklists — On user input (result field, and reading field)

On user input is a per-field event. Block A goes on result, block B goes on reading. Both write completed_on, which is what computeChecklistCompliance counts as done.

// ---- Paste block A on the RESULT field's On user input --------------------
// The moment a technician picks a result, stamp the completion time.
if(input.result != null && input.result != "Not applicable")
{
    input.completed_on = zoho.currenttime;  // zoho.currenttime is a date-time value
}

// ---- Paste block B on the READING field's On user input -------------------
// For numeric checks, grade the reading against the acceptable band.
if(input.response_type == "Numeric reading" && input.reading != null)
{
    if(input.min_acceptable != null && input.max_acceptable != null)
    {
        input.within_range = (input.reading >= input.min_acceptable && input.reading <= input.max_acceptable);
        if(input.within_range)
        {
            input.result = "Pass";
        }
        else
        {
            input.result = "Fail";
        }
    }
    input.completed_on = zoho.currenttime;  // reading entered = row done
}

checklist_templates — On Load - Create

Use zoho.loginuserid (email) - zoho.loginuser would return a name/zuid on this portal. SITE is admin-only so this only matters for admin/owner data entry; on portal it is skipped silently when siteId is 0.

// Checklist Templates > On Load (Create) - sensible defaults for a new template.
input.version = 1;
input.active = true;
input.job_type = "Preventive";
input.frequency = "Monthly";

// Default the site to the supervisor's own site on portal logins.
if(input.SITE == null)
{
    userEmail = zoho.loginuserid;      // portal login exposes the EMAIL here, not zoho.loginuser
    siteId = getSiteForUser(userEmail); // existing owner-context helper, returns 0 if none
    if(siteId != 0)
    {
        input.SITE = siteId;
    }
}

checklist_template_items — On Load - Create

Optional but recommended - keeps every line mandatory-by-default and site-consistent with its template header.

// Checklist Template Items > On Load (Create) - defaults so lines are quick to add.
input.response_type = "Yes-No";
input.mandatory = true;

// Inherit the parent template's site (template is a multi-select lookup).
if(input.SITE == null && input.template.size() > 0)
{
    for each  t in checklist_templates[ID == input.template.get(0)]
    {
        input.SITE = t.SITE;
    }
}

task_checklists — On Success (Create and Edit) [compliance rollup]

This is the code behind the "all items done -> set WO completion" workflow. Kept as On Success (not a schedule) so the WO updates the instant the last item is answered.

// Task Checklists > On Success - roll the ticked row up to the parent work order.
if(input.work_order.size() > 0)
{
    woId = input.work_order.get(0);
    pct = computeChecklistCompliance(woId);  // percent of rows done, 0 - 100

    for each  woRec in work_orders[ID == woId]
    {
        woRec.completion_percent = pct;

        // Are any MANDATORY checks still open (no completion timestamp)?
        openMandatory = task_checklists[work_order.contains(woId) && mandatory == true && completed_on == null].count();

        // Auto-complete only when fully done and not already signed off/verified.
        if(pct == 100 && openMandatory == 0 && woRec.status != "Verified" && woRec.status != "Completed")
        {
            woRec.status = "Completed";
            woRec.completed_on = zoho.currenttime;
        }
    }
}

work_orders — checklist_template (On user input) [alternative instantiate trigger]

Use EITHER this field event OR the On Success block, not both. On Success is preferred because input.ID is always present after save (covers brand-new WOs too).

// OPTIONAL alternative to the On Success trigger, if you prefer to build the
// checklist the moment a template is chosen on a SAVED work order.
// Note: input.ID is only reliable on an existing (edit) record here.
if(input.checklist_template.size() > 0 && input.ID != null)
{
    templateId = input.checklist_template.get(0);
    instantiateChecklist(input.ID, templateId);
}

instantiateChecklist(workOrderId, templateId)

Owner-context standalone function. Copies every checklist_template_items line for the given template into task_checklists rows linked to the work order, stamping the work order's SITE onto each child row. Idempotent: if the WO already has checklist rows it does nothing and returns the existing count. Returns the number of rows present for the WO. Called from the work_orders On Success event.

// instantiateChecklist(long workOrderId, long templateId)
// Standalone > runs as Application Owner so it can read/write across all 6 sites.
// Returns the number of task_checklists rows that exist for this work order.
return int
{
    createdCount = 0;

    // --- Idempotency guard ------------------------------------------------
    // work_order is a multi-select lookup, so filter with .contains(id).
    existing = task_checklists[work_order.contains(workOrderId)];
    if(existing.count() > 0)
    {
        // Already instantiated (e.g. WO re-saved) - never duplicate rows.
        return existing.count();
    }

    // --- Resolve the WO's site to stamp onto each child row ----------------
    // SITE is a single-select lookup, so the field returns the linked record id.
    siteId = 0;
    for each  wo in work_orders[ID == workOrderId]
    {
        siteId = wo.SITE;
    }

    // --- Copy the template line items -------------------------------------
    // template is a multi-select lookup on checklist_template_items.
    items = checklist_template_items[template.contains(templateId)];
    for each  item in items
    {
        insert into task_checklists
        [
            work_order = workOrderId
            SITE = siteId
            check_description = item.check_description
            response_type = item.response_type
            unit = item.unit
            min_acceptable = item.min_acceptable
            max_acceptable = item.max_acceptable
            mandatory = item.mandatory
            remarks = item.remarks
            result = "Not applicable"
        ];
        createdCount = createdCount + 1;
    }
    return createdCount;
}

computeChecklistCompliance(workOrderId)

Owner-context standalone function. Returns the percentage (0-100, 2 decimals) of task_checklists rows for the work order that are marked done, where done means completed_on is filled. Returns 0 when the WO has no checklist rows. Called from the task_checklists On Success rollup and by the PPM compliance report.

// computeChecklistCompliance(long workOrderId)
// Returns percent of checklist rows completed for a work order (0 - 100).
return double
{
    // Total rows attached to this WO (multi-select lookup -> .contains).
    total = task_checklists[work_order.contains(workOrderId)].count();
    if(total == 0)
    {
        return 0;  // no checklist on this WO
    }

    // "Done" = the row has a completion timestamp (set on user input).
    doneCount = task_checklists[work_order.contains(workOrderId) && completed_on != null].count();

    // Multiply by 100.0 (not 100) so integer division does not floor to 0.
    pct = (doneCount * 100.0) / total;
    return pct.round(2);
}

Build a reusable template with items

1. Admin opens Checklist Templates > Add. On Load defaults version=1, active=true, job_type=Preventive, frequency=Monthly, SITE from getSiteForUser.
2. Admin fills template_code (e.g. AHU-M), template_name, asset_category, adjusts job_type/frequency, sets estimated_duration_minutes, permit_required, safety_notes. Saves.
3. Admin opens Checklist Template Items > Add, selects the Template lookup. On Load defaults response_type=Yes-No, mandatory=true, SITE inherited from the template.
4. For each check: fills check_description, response_type (Yes-No / Numeric reading / Pass/Fail / Text / Photo). For Numeric reading sets unit, min_acceptable, max_acceptable. Repeats for every line.
5. Result: one template header with N line items, ready to attach to work orders.

Raise a PPM or Corrective WO with a checklist

1. User creates a Work Order, sets job_type (Preventive/Predictive/Corrective), asset, and picks the Checklist Template lookup.
2. On Save, work_orders On Success fires: input.checklist_template.size() > 0, so it reads the first template id.
3. instantiateChecklist(input.ID, templateId) runs in owner context. Idempotency guard finds no existing rows.
4. It reads the WO's SITE, fetches checklist_template_items via template.contains(templateId), and inserts one task_checklists row per line (work_order, SITE, check_description, response_type, unit, min/max_acceptable, mandatory, remarks, result=Not applicable).
5. The technician now sees a WO-specific checklist under the work order; the master template is untouched.

Technician completes items and compliance is computed

1. Technician opens each task_checklists row on the WO. For a Yes-No/Pass-Fail row they pick result; result On user input sets completed_on = zoho.currenttime.
2. For a Numeric reading row they enter reading; reading On user input sets within_range from min/max, auto-sets result Pass or Fail, and stamps completed_on.
3. On each row save, task_checklists On Success calls computeChecklistCompliance(woId) and writes work_orders.completion_percent.
4. When the last mandatory row is answered (openMandatory count = 0) and pct = 100, the WO status flips to Completed with completed_on stamped.
5. The nightly PPM compliance rollup reads completion_percent per site/job_type for the compliance report.

On Load — Buildings / Floors / Zones / Rooms (Add view)

Attach identically to all four child forms. getSiteForUser(email) already exists in owner context — do not rewrite it. This is why SITE being admin-only is safe: the code fills it.

// WHEN: form opens for a NEW record on any hierarchy child form.
// WHY: SITE is an admin-only lookup, so portal Supervisors never see or pick it.
//      It must be stamped from their assigned site or the record is orphaned.

email = zoho.loginuserid;            // portal logins expose the EMAIL here (NOT zoho.loginuser)
sid = getSiteForUser(email);         // existing owner-context function, returns SITE id or 0
if(sid != 0)
{
    input.SITE = sid;                // single-select lookup takes ONE record id
    disable SITE;                    // keep supervisors from changing it
}
// Admins (sid == 0) keep the SITE field open to choose any of the 6 sites.

On Validate — SITE (Add & Edit)

Center field link name is exactly 'Center' and already exists on the SITE form — do not add a new field. cancel is the reserved On Validate variable that stops submission.

// WHEN: a site is added or edited. WHY: Center is a short code reused in Work Order
//       numbering, so it must never be blank; also warn on a duplicate Site Code.

if(input.Center == null || input.Center.trim() == "")
{
    base = input.Site_Code;
    if(base == null || base.trim() == "")
    {
        base = input.Site_Name;
    }
    clean = base.replaceAll(" ","").toUpperCase();
    if(clean.length() >= 4)
    {
        input.Center = clean.subString(0,4);   // e.g. "Hail Hospital" -> "HAIL"
    }
    else
    {
        input.Center = clean;
    }
}

// Optional duplicate guard (comment out during bulk import)
if(input.Site_Code != null && input.Site_Code != "")
{
    dupes = SITE[Site_Code == input.Site_Code];
    if(dupes.count() > 1)
    {
        alert "Site Code " + input.Site_Code + " already exists.";
        cancel = true;      // 'cancel = true' aborts the save in an On Validate event
    }
}

On user input — SITE (Buildings form)

input.SITE is a single id here (single-select). site_or_campus is a plain text field, not a lookup.

// WHEN: the SITE lookup changes on the Buildings form.
// WHY: mirror the readable site name into the free-text "Site or Campus" label.
if(input.SITE != null)
{
    sRec = SITE[ID == input.SITE];
    if(sRec.count() > 0)
    {
        input.site_or_campus = sRec.Site_Name;
    }
}

On user input — SITE cascade reset (Floors / Zones / Rooms)

Preferred cascade = builder Lookup Filter on each child lookup, criteria SITE == this form's SITE: Floors.building filtered to buildings of SITE; Zones.floor filtered to floors of SITE; Rooms.zone filtered to zones of SITE. Deeper building->floor->zone->room chaining needs the child form to also carry the grandparent field, which it currently does not — flag to client if strict drill-down is wanted.

// WHEN: the SITE lookup changes. WHY: the parent picker is narrowed by a builder-level
//       LOOKUP FILTER (see notes); this Deluge only clears a now-invalid child chip so
//       a stale selection from the previous site cannot be saved.

// --- Floors form (clears the Building selection) ---
input.building = null;    // multi-select; null removes all chips

// --- Zones form (put this line on the Zones form instead) ---
// input.floor = null;

// --- Rooms form (put this line on the Rooms form instead) ---
// input.zone = null;

On Validate — Rooms (Add & Edit) build location_path

On Add the record has no ID yet, so we build from input.* rather than calling buildLocationPath(ID). locationPathFromZone must be saved as an app function first.

// WHEN: a room is added or edited. WHY: stamp the denormalised location_path used by
//       list views, assets and work orders. Uses the shared helper so logic stays in one place.

firstZone = null;
for each  z in input.zone            // rooms.zone is multi-select, take the first
{
    firstZone = z;
    break;
}
roomLabel = input.room_name;
if(roomLabel == null || roomLabel.trim() == "")
{
    roomLabel = input.room_number;
}
if(firstZone != null)
{
    input.location_path = locationPathFromZone(firstZone.toLong(), roomLabel);
}
else
{
    input.location_path = roomLabel;   // no zone yet -> at least show the room label
}

// Default a brand-new room to Active.
if(input.active == null)
{
    input.active = true;
}

On Success — Rooms Edited (path refresh to assets)

assets[room == input.ID] works because a multi-select lookup query matches any record that contains the id. Assigning a.location_path inside the for-each updates that record.

// WHEN: an existing room is renamed or moved (Edit success). WHY: the path snapshot on its
//       assets is now stale — push the fresh path down. Keep this light; move to a scheduled
//       function if a single room ever holds hundreds of assets.

freshPath = buildLocationPath(input.ID);
linkedAssets = assets[room == input.ID];   // '==' on a multi-select lookup matches "contains"
for each  a in linkedAssets
{
    a.location_path = freshPath;            // updates the asset row in place
}

On Success — Rooms Created / Edited (audit)

audit_log field link names are assumed (entity, record_id, action, by_user, at). Confirm against the Audit Log form before enabling; remove any field that does not exist.

// WHEN: a room is saved. WHY: light, non-blocking audit trail. Attach to BOTH the
//       Created and Edited success actions. Field names below are ASSUMED — verify audit_log.
insert into audit_log
[
    entity = "Room"
    record_id = input.ID
    action = "Room saved"
    by_user = zoho.loginuserid
    at = zoho.currenttime
];

On user input — Room (Assets form)

This is where buildLocationPath earns its keep. SITE is admin-only on Assets too, so back-filling it from the room keeps supervisor-created assets scoped correctly.

// WHEN: the Room lookup changes on an Asset. WHY: auto-fill location_path and back-fill SITE.
firstRoom = null;
for each  r in input.room            // assets.room is multi-select
{
    firstRoom = r;
    break;
}
if(firstRoom != null)
{
    input.location_path = buildLocationPath(firstRoom.toLong());
    if(input.SITE == null)
    {
        rmRec = rooms[ID == firstRoom.toLong()];
        if(rmRec.count() > 0)
        {
            input.SITE = rmRec.SITE;      // single-select copy from the room
        }
    }
}

On user input — Asset (Work Orders form)

work_orders metadata shows has_on_load=true and has_on_user_input on SITE. Coordinate with those existing scripts.

// WHEN: the Asset lookup changes on a Work Order. WHY: a WO inherits the location_path
//       already stamped on its asset (cheaper than re-walking the tree).
firstAsset = null;
for each  a in input.asset           // work_orders.asset is multi-select
{
    firstAsset = a;
    break;
}
if(firstAsset != null)
{
    aRec = assets[ID == firstAsset.toLong()];
    if(aRec.count() > 0)
    {
        input.location_path = aRec.location_path;
        if(input.SITE == null)
        {
            input.SITE = aRec.SITE;
        }
    }
}
// NOTE: this form ALREADY has On Load + On user input on SITE — ADD this block,
//       do not overwrite the existing SITE logic.

On Delete — Buildings / Floors / Zones (referential integrity)

There is no 'cancel' in an On Delete event. Real protection = remove hard-delete from the Supervisor profile and retire via the Active box instead; this script is the safety net if a hard delete slips through.

// WHEN: a parent level is deleted. WHY: Creator CANNOT block a delete from Deluge, so we
//       protect the tree by cascade-retiring orphaned children (and by permissions, see notes).

// Example on the BUILDINGS delete event — retire floors that pointed at it:
orphanFloors = floors[building == input.ID];   // multi-select match = "contains this building"
for each  f in orphanFloors
{
    f.active = false;                            // soft-retire so they drop out of pickers
}
// Repeat the same shape on Floors delete (zones[floor == input.ID])
// and Zones delete (rooms[zone == input.ID]).

locationPathFromZone

Shared helper. Given a saved Zone id (and an optional room label) it walks Zone -> Floor -> Building -> Site and returns a readable path such as "HAIL > Tower A > L3 > HVAC Zone > Room 312". Handles the multi-select lookups by taking the first linked parent at each level and skips any blank segment. Called by both buildLocationPath() and the Rooms On Validate script so the path logic lives in one place.

string locationPathFromZone(long zoneId, string roomLabel)
{
    // Builds "Center > Building > Floor > Zone > Room" from a saved Zone id.
    // roomLabel is appended only when it is not blank.
    segs = List();
    zRec = zones[ID == zoneId];
    if(zRec.count() == 0)
    {
        return "";
    }
    // ---- Site segment: prefer the short Center code, else the Site Name ----
    if(zRec.SITE != null)
    {
        sRec = SITE[ID == zRec.SITE];
        if(sRec.count() > 0)
        {
            siteLabel = sRec.Site_Name;
            if(sRec.Center != null && sRec.Center.trim() != "")
            {
                siteLabel = sRec.Center;
            }
            if(siteLabel != null && siteLabel.trim() != "")
            {
                segs.add(siteLabel);
            }
        }
    }
    // ---- Building + Floor: zones.floor is multi-select, take the FIRST ----
    firstFloor = null;
    for each  f in zRec.floor
    {
        firstFloor = f;
        break;                       // WHILE is banned; first element only
    }
    if(firstFloor != null)
    {
        fRec = floors[ID == firstFloor.toLong()];
        if(fRec.count() > 0)
        {
            firstBuilding = null;
            for each  b in fRec.building     // floors.building is multi-select
            {
                firstBuilding = b;
                break;
            }
            if(firstBuilding != null)
            {
                bRec = buildings[ID == firstBuilding.toLong()];
                if(bRec.count() > 0 && bRec.building_name != null && bRec.building_name != "")
                {
                    segs.add(bRec.building_name);
                }
            }
            if(fRec.floor_name != null && fRec.floor_name != "")
            {
                segs.add(fRec.floor_name);
            }
        }
    }
    // ---- Zone segment ----
    if(zRec.zone_name != null && zRec.zone_name != "")
    {
        segs.add(zRec.zone_name);
    }
    // ---- Room segment ----
    if(roomLabel != null && roomLabel.trim() != "")
    {
        segs.add(roomLabel);
    }
    return segs.toString(" > ");
}

buildLocationPath

Primary entry point required by the module. Given a saved Room id it resolves the room, takes its first linked zone and delegates to locationPathFromZone() to return the full readable path. Returns an empty string if the room or its zone cannot be resolved. Assets and Work Orders call this to stamp their own location_path.

string buildLocationPath(long roomId)
{
    // Walks Room -> Zone -> Floor -> Building -> Site and returns a readable path.
    // Returns "" when the room or its zone cannot be resolved.
    roomRec = rooms[ID == roomId];
    if(roomRec.count() == 0)
    {
        return "";
    }
    // rooms.zone is a multi-select lookup: use the first linked zone.
    firstZone = null;
    for each  z in roomRec.zone
    {
        firstZone = z;
        break;
    }
    if(firstZone == null)
    {
        return "";
    }
    // Room label prefers Room Name, falls back to Room Number.
    roomLabel = roomRec.room_name;
    if(roomLabel == null || roomLabel.trim() == "")
    {
        roomLabel = roomRec.room_number;
    }
    return locationPathFromZone(firstZone.toLong(), roomLabel);
}

Scenario 1 — Define a hospital hierarchy top-down

1. Admin opens the SITE form and adds the hospital: Site_Code, Site_Name, and the Center short code (auto-derived to e.g. HAIL if left blank by On Validate). Fills the Address composite.
2. Admin (or a Supervisor scoped to that site) opens Buildings; On Load stamps SITE for the supervisor and locks it. They enter building_code, building_name, total_floors; site_or_campus auto-fills from the site name.
3. Opens Floors; the Building picker is filtered to that site's buildings (builder Lookup Filter). Picks the building, enters floor_code, floor_name, level_index. Changing SITE clears the stale building chip.
4. Opens Zones; picks the floor (filtered by SITE), enters zone_code, zone_name and zone_type (Office / Plant room / Riser ...).
5. Opens Rooms; picks the zone (filtered by SITE), enters room_code, room_name, room_number. On Validate walks zone -> floor -> building -> site and writes location_path, and defaults active = true. The five-level tree now exists and every level is SITE-scoped.

Scenario 2 — Place an asset in a room and auto-build its path

1. Technician/Supervisor opens the Assets form; On Load stamps and hides SITE (admin-only) via getSiteForUser(zoho.loginuserid).
2. They enter asset_id, asset_name, asset_category, then choose the Room from the multi-select Room lookup.
3. On user input of Room fires: the script takes the first selected room and calls buildLocationPath(roomId).
4. buildLocationPath resolves the room, takes its first zone, and hands off to locationPathFromZone, which walks Zone -> Floor -> Building -> Site and returns a string like "HAIL > Tower A > L3 > HVAC Zone > Room 312".
5. input.location_path is filled and SITE is back-filled from the room when it was empty. On save the asset is fully located.
6. Later, raising a Work Order and selecting that asset copies the asset's location_path straight onto the work order, so the crew sees the readable location without re-walking the tree.

On Load — Add / Create — reusable SITE auto-fill (minimal)

Drop this on the On Load (Add) event of EVERY supervisor-accessible form. Minimal version: stamp + hide for supervisors, leave the picker for admins. Use this when the form does not need the explicit role name.

// ================================================================
// REUSABLE  --  paste on the On Load (Add / Create) event of EVERY
// supervisor-accessible form. Minimal version: auto-fill + hide.
// ================================================================

// Portal AND app logins expose the real email on zoho.loginuserid.
// Do NOT use zoho.loginuser here -- on this app portals do not put the email there.
loginEmail = zoho.loginuserid;

// Resolve the hospital via the owner-context helper (0 = not a supervisor).
sid = getSiteForUser(loginEmail);

if(sid != 0)
{
	// Supervisor: stamp their hospital and remove the picker entirely.
	input.SITE = sid;   // set the lookup by SITE record id
	hide SITE;
}
else
{
	// Admin: let them choose the hospital.
	show SITE;
}

On Load — Add / Create — full three-role detection

The full version. Resolves SUPERVISOR / ADMIN_PORTAL / ADMIN_APP so downstream field logic can branch on userRole. The no-@ heuristic on zoho.loginuser separates admin portal users (handle, no @) from admin app users (full email). Remove the info line before go-live.

// ================================================================
// FULL ROLE DETECTION  --  On Load (Add / Create).
// Resolves one of three roles and scopes SITE accordingly.
// ================================================================

loginEmail = zoho.loginuserid;   // real email for portal + app users
loginName  = zoho.loginuser;     // app user = full email ; portal user = handle, usually no "@"

userRole = "";
sid = getSiteForUser(loginEmail);

if(sid != 0)
{
	// 1) SUPERVISOR  --  hospital-side portal user locked to one SITE.
	userRole = "SUPERVISOR";
	input.SITE = sid;
	hide SITE;
}
else if(!loginName.contains("@"))
{
	// 2) ADMIN PORTAL USER  --  portal login, no email-style handle, not a Supervisor.
	userRole = "ADMIN_PORTAL";
	show SITE;   // must pick the hospital manually
}
else
{
	// 3) ADMIN APP USER  --  internal Creator user with a full email -> works across all sites.
	userRole = "ADMIN_APP";
	show SITE;
}

// Handy while testing the handover; remove once verified.
info "Resolved role: " + userRole;

On Load — Edit — SITE lock variant

Paste on the On Load (Edit) event of the same supervisor-accessible forms. On edit the record already HAS a SITE, so never overwrite it -- only enforce visibility. The null check is a safety net for legacy rows saved before scoping existed.

// ================================================================
// EDIT VARIANT  --  paste on the On Load (Edit) event of the same
// supervisor-accessible forms. The record already HAS a SITE, so
// never overwrite it -- only enforce visibility by role.
// ================================================================

loginEmail = zoho.loginuserid;
sid = getSiteForUser(loginEmail);

if(sid != 0)
{
	// Supervisor: keep the picker hidden so they cannot move a record
	// to another hospital.
	hide SITE;

	// Safety net for legacy rows that were saved without a site.
	if(input.SITE == null)
	{
		input.SITE = sid;
	}
}
else
{
	// Admin: SITE stays visible / editable.
	show SITE;
}

getSiteForUser

Owner-context standalone function. Given a login email, returns the SITE record id the matching Supervisor is locked to, or 0 when the email belongs to no Supervisor (i.e. an admin). Must run in Application Owner context so portal Supervisors, who have no read access to the Supervisor form, still resolve to their hospital.

// getSiteForUser  --  STANDALONE function, set to run in Application Owner context.
// Given a login email, returns the SITE record id the Supervisor is locked to, or 0 if none.
// Owner context is required so portal Supervisors -- who have no read access to the
// Supervisor form themselves -- can still be resolved to their hospital.
int getSiteForUser(string userEmail)
{
	// Normalise: portal logins can arrive with stray case or spaces.
	cleanEmail = userEmail.trim().toLowerCase();

	// Guard empty / null input.
	if(cleanEmail == null || cleanEmail == "")
	{
		return 0;
	}

	// First matching Supervisor wins. No WHILE loops in Creator, so a bounded
	// for-each with an explicit break is used instead.
	for each  sup in Supervisor[Email == cleanEmail]
	{
		if(sup.SITE != null)
		{
			return sup.SITE;   // lookup yields the referenced SITE record id
		}
		break;                 // matched a Supervisor but no site set -> unscoped
	}

	// No Supervisor row for this email -> caller treats as admin / unscoped.
	return 0;
}

Supervisor logs in via portal and is scoped to their hospital

1. Supervisor opens the CAFM portal URL and signs in with their hospital email.
2. They open a supervisor-accessible form (Add mode); the On Load script fires.
3. loginEmail = zoho.loginuserid returns their real email even though this is a portal session.
4. getSiteForUser(loginEmail) runs in Application Owner context, finds the Supervisor row, and returns that Supervisor's SITE id (one of the 6 hospitals).
5. sid != 0 so userRole = SUPERVISOR: input.SITE is set to sid and the SITE picker is hidden.
6. The supervisor fills the rest of the form and submits; the record is saved already stamped with their hospital, with no way to pick another site.

Admin app user sees all sites

1. An internal Creator user opens the app directly (not the portal) and opens the same form in Add mode.
2. On Load runs: loginEmail = zoho.loginuserid = their full email.
3. getSiteForUser returns 0 because there is no Supervisor row for that email.
4. loginName = zoho.loginuser contains "@", so userRole = ADMIN_APP.
5. The SITE picker stays visible and enabled; the admin can select any of the 6 hospitals.
6. The admin submits with the site they chose.

Admin portal user picks a site

1. An admin-tier portal user signs into the CAFM portal and opens the form in Add mode.
2. On Load runs: getSiteForUser returns 0 (this email is not a Supervisor).
3. loginName = zoho.loginuser has no "@" (portal handle), so userRole = ADMIN_PORTAL.
4. The SITE picker is shown; the admin portal user manually selects the hospital.
5. The record is saved with the chosen site, giving admin portal users the same cross-site reach as app admins.

Vendors — On Load (Create)

Stamps + locks SITE for supervisors, defaults Active, pre-builds the vendor code. Admins with no site (0) pick SITE manually and the code fills on the SITE input event.

// Portal logins expose the user email in zoho.loginuserid (NOT zoho.loginuser)
email = zoho.loginuserid;
// Owner-context helper -> supervisor's SITE record id, or 0 for an admin with no site
siteId = getSiteForUser(email);
if(siteId != 0)
{
	input.SITE = siteId;  // a lookup is assigned by the referenced record id
	disable SITE;         // supervisors are pinned to their own site
}
// New masters open as Active (the field default is unchecked)
input.active = true;
// Pre-build the code the moment we already know the site
if(siteId != 0)
{
	input.vendor_code = genVendorCode(siteId);
}
// The code is system-owned, never hand typed
disable vendor_code;

Vendors — On User Input of SITE

Field action so an admin who picks/changes SITE gets a matching code. input.SITE holds the chosen record id.

// Fires when SITE is picked or changed on the form
if(input.SITE != null)
{
	input.vendor_code = genVendorCode(input.SITE);
}

Vendors — On Validate (Create and Edit)

Per-site uniqueness of vendor_code. Uses for-each + break (no while). Excludes the record's own row on edit.

// Guarantee the code is unique WITHIN the site (not globally)
if(input.vendor_code != null && input.vendor_code != "")
{
	matches = vendors[SITE == input.SITE && vendor_code == input.vendor_code];
	isDup = false;
	if(input.ID == null)
	{
		// Create : any existing match is a clash
		if(matches.count() > 0)
		{
			isDup = true;
		}
	}
	else
	{
		// Edit : a match on a DIFFERENT record is a clash (skip our own row)
		for each  m in matches
		{
			if(m.ID != input.ID)
			{
				isDup = true;
				break;
			}
		}
	}
	if(isDup)
	{
		alert "Vendor code " + input.vendor_code + " already exists for this site.";
		cancel = true;  // On Validate : cancel = true stops the save
	}
}

Vendors — On Success (Create)

Belt-and-braces: backfills the code for records created via import or API that arrived without one. On Success runs after save, so fetch the row by ID and set the field.

// Backfill the code if a record was created without one (import / API)
if((input.vendor_code == null || input.vendor_code == "") && input.SITE != null)
{
	newCode = genVendorCode(input.SITE);
	if(newCode != "")
	{
		rec = vendors[ID == input.ID];
		rec.vendor_code = newCode;
	}
}

Vendors — On Success (Edit)

Keeps the code immutable: if someone blanked it, rebuild from the site so downstream transactions never lose the link.

// Never let an edited vendor lose its code
if((input.vendor_code == null || input.vendor_code == "") && input.SITE != null)
{
	rec = vendors[ID == input.ID];
	rec.vendor_code = genVendorCode(input.SITE);
}

Vendors — On Delete (TEMPLATE)

Prefer soft-delete (untick Active). Hard-delete guard is a TEMPLATE: replace the placeholder transactional form + lookup once the Work Orders / Spare Parts module is handed over. Left commented so it is a safe no-op until then.

// PREFER soft-delete : untick 'active' instead of deleting so historical
// work orders keep a valid vendor link. Hard-delete guard below.
//
// Replace <WORK_ORDER_FORM> and <vendor_lookup> with the REAL names from the
// transactional module handover, then uncomment.
//
// refCount = <WORK_ORDER_FORM>[<vendor_lookup> == input.ID].count();
// if(refCount > 0)
// {
//     alert "Cannot delete : this vendor is used on " + refCount + " transactions. Untick Active instead.";
//     cancel = true;
// }

Technicians — On Load (Create)

Stamps SITE (admin-only, so supervisors do not see it) and defaults Active. employee_code is HR-issued and typed in — not generated.

email = zoho.loginuserid;
siteId = getSiteForUser(email);
if(siteId != 0)
{
	input.SITE = siteId;  // admin-only lookup - stamped even though supervisors do not see it
	disable SITE;
}
input.active = true;      // new technicians are Active by default
// employee_code is issued by HR and typed in - it is NOT auto-generated.

Technicians — On Validate (Create and Edit)

Per-site uniqueness of the HR employee_code.

if(input.employee_code != null && input.employee_code != "")
{
	matches = technicians[SITE == input.SITE && employee_code == input.employee_code];
	isDup = false;
	if(input.ID == null)
	{
		if(matches.count() > 0)
		{
			isDup = true;
		}
	}
	else
	{
		for each  m in matches
		{
			if(m.ID != input.ID)
			{
				isDup = true;
				break;
			}
		}
	}
	if(isDup)
	{
		alert "Employee code " + input.employee_code + " already exists for this site.";
		cancel = true;
	}
}

Technicians — On Success (Create and Edit): active/inactive handling

The REAL enforcement is a filtered assignee lookup at the transaction form (technicians where active is true and SITE matches). This block handles the record itself + flags downstream. The reassignment lines are a TEMPLATE with placeholder names.

// When a technician is deactivated they must drop out of assignment
if(input.active == false)
{
	// Reassign their OPEN work so nothing is stuck on an inactive tech.
	// Replace <WORK_ORDER_FORM> and <assigned_technician> with the real names.
	// openWos = <WORK_ORDER_FORM>[assigned_technician == input.ID && status != "Closed"];
	// for each  wo in openWos { wo.assigned_technician = ""; }
	info "Technician " + input.full_name + " set INACTIVE - open work should be reassigned.";
}
else
{
	info "Technician " + input.full_name + " is ACTIVE.";
}

Tools — On Load (Create)

Stamps SITE, sets sensible defaults (quantity 1, condition Good), and pre-builds the tool code. NOTE: Tools has no 'active' field, so do not set one.

email = zoho.loginuserid;
siteId = getSiteForUser(email);
if(siteId != 0)
{
	input.SITE = siteId;   // admin-only lookup
	disable SITE;
}
// Tools has NO 'active' field - do not set one.
input.quantity = 1;        // sensible default
input.condition_1 = "Good";// valid choice from the Condition dropdown
if(siteId != 0)
{
	input.tool_code = genToolCode(siteId);
}
disable tool_code;

Tools — On User Input of SITE

Regenerates tool_code when an admin picks/changes SITE.

if(input.SITE != null)
{
	input.tool_code = genToolCode(input.SITE);
}

Tools — On Validate (Create and Edit)

Per-site uniqueness of tool_code.

if(input.tool_code != null && input.tool_code != "")
{
	matches = Tools[SITE == input.SITE && tool_code == input.tool_code];
	isDup = false;
	if(input.ID == null)
	{
		if(matches.count() > 0)
		{
			isDup = true;
		}
	}
	else
	{
		for each  m in matches
		{
			if(m.ID != input.ID)
			{
				isDup = true;
				break;
			}
		}
	}
	if(isDup)
	{
		alert "Tool code " + input.tool_code + " already exists for this site.";
		cancel = true;
	}
}

Asset Categories — On Load (Create)

Minimal master: stamp SITE, default Active. There is NO prefix field on the live form, so no code is generated.

email = zoho.loginuserid;
siteId = getSiteForUser(email);
if(siteId != 0)
{
	input.SITE = siteId;   // admin-only lookup
	disable SITE;
}
input.active = true;
// NOTE : this form has only SITE + category_name + active.
// There is NO 'prefix' field, so no code is generated here.

Asset Categories — On Validate (Create and Edit)

Per-site uniqueness of category_name.

if(input.category_name != null && input.category_name != "")
{
	matches = asset_categories[SITE == input.SITE && category_name == input.category_name];
	isDup = false;
	if(input.ID == null)
	{
		if(matches.count() > 0)
		{
			isDup = true;
		}
	}
	else
	{
		for each  m in matches
		{
			if(m.ID != input.ID)
			{
				isDup = true;
				break;
			}
		}
	}
	if(isDup)
	{
		alert "Category " + input.category_name + " already exists for this site.";
		cancel = true;
	}
}

Schedule — Daily 06:00 — vendorContractExpiryReminder

Create under Workflow > Schedules, frequency Daily, target form = vendors. Compares contract_expiry against computed marker dates (addDay) instead of a daysBetween call, so it stays reliable. Uses for-each, not while.

// Reminds active vendors before a contract lapses
today = zoho.currentdate;
marks = {30, 15, 7, 1};   // send at these day-marks before expiry
for each  d in marks
{
	targetDate = today.addDay(d);
	// Active vendors whose contract expires EXACTLY on the mark date
	dueList = vendors[active == true && contract_expiry == targetDate];
	for each  v in dueList
	{
		toAddr = v.email;
		if(toAddr != null && toAddr != "")
		{
			siteName = SITE[ID == v.SITE].Site_Name;
			sendmail
			[
				from : zoho.adminuserid
				to : toAddr
				subject : "Contract expiring in " + d + " days - " + v.vendor_name
				message : "Dear " + v.contact_person + "<br><br>The contract " + v.contract_reference + " for site " + siteName + " expires on " + v.contract_expiry.toString("dd-MMM-yyyy") + ". Please start the renewal.<br><br>CAFM - Jood FM"
			]
		}
	}
}

genVendorCode

Build a per-site vendor code from SITE.Site_Code + a zero-padded running sequence (e.g. RYD-VEN-0007). Returns empty string when no site is known so the caller leaves vendor_code blank until a SITE is chosen.

string genVendorCode(int siteId)
{
	// No site yet (admin has not picked one) -> return blank, caller keeps code empty
	if(siteId == 0)
	{
		return "";
	}
	// Site_Code is the center prefix that opens every vendor code
	prefix = SITE[ID == siteId].Site_Code;
	if(prefix == null || prefix == "")
	{
		prefix = "GEN";  // safety net if Site_Code was left blank
	}
	// Running sequence = vendors this site already has + 1
	seq = vendors[SITE == siteId].count() + 1;
	// Zero-pad to 4 digits WITHOUT a while loop (while is not allowed in this app)
	seqText = "000" + seq;
	seqText = seqText.subString(seqText.length() - 4);
	candidate = prefix + "-VEN-" + seqText;
	// Guard against a code freed up by an earlier delete
	if(vendors[SITE == siteId && vendor_code == candidate].count() > 0)
	{
		candidate = prefix + "-VEN-" + seqText + "-" + zoho.currenttime.toString("HHmmss");
	}
	return candidate;
}

genToolCode

Same pattern as genVendorCode but for the Tools form: SITE.Site_Code + running sequence over Tools for that site (e.g. RYD-TL-0012). Returns empty string when siteId is 0.

string genToolCode(int siteId)
{
	// No site chosen yet -> caller keeps tool_code blank
	if(siteId == 0)
	{
		return "";
	}
	// Site_Code prefixes every tool code
	prefix = SITE[ID == siteId].Site_Code;
	if(prefix == null || prefix == "")
	{
		prefix = "GEN";
	}
	// Running sequence = tools already held by this site + 1
	seq = Tools[SITE == siteId].count() + 1;
	// Zero-pad to 4 digits without a while loop
	seqText = "000" + seq;
	seqText = seqText.subString(seqText.length() - 4);
	candidate = prefix + "-TL-" + seqText;
	// Skip a code left behind by a deleted tool
	if(Tools[SITE == siteId && tool_code == candidate].count() > 0)
	{
		candidate = prefix + "-TL-" + seqText + "-" + zoho.currenttime.toString("HHmmss");
	}
	return candidate;
}

Add a vendor

1. Supervisor opens Add Vendor. On Load stamps their SITE, locks it, ticks Active and fills vendor_code (e.g. RYD-VEN-0007).
2. An admin instead picks SITE from the lookup; On User Input of SITE regenerates vendor_code for that site.
3. Enter vendor_name; tick one or more vendor_type values (multi-select: Parts supplier / OEM / Consultant / Service contractor); enter contact_person, email, phone.
4. Enter contract_reference, contract_start, contract_expiry, response_sla_hours; paste the WorkDrive link into documents_folder (URL field).
5. Submit. On Validate confirms vendor_code is unique within the site. The daily schedule will later email reminders 30/15/7/1 days before contract_expiry.

Add a technician

1. Open Add Technician. On Load stamps SITE (admin-only, hidden from supervisors) and ticks Active.
2. Type the HR-issued employee_code (manual, not generated) and full_name.
3. Set zoho_user (their login), pick role (Supervisor / Administrator / Technician / Storekeeper), tick one or more discipline values, set phone, email, shift.
4. Submit. On Validate blocks a duplicate employee_code within the same site.
5. Later, unticking Active logs the change and flags their open work for reassignment; a filtered assignee lookup at the transaction form keeps them out of new assignments.

Add a tool to a site inventory

1. Open Add Tool. On Load stamps SITE, defaults quantity to 1 and condition_1 to Good, and fills tool_code (e.g. RYD-TL-0012).
2. Enter tool_name; pick tool_category (HVAC / Electrical / Plumbing / Civil (Carpenter) / Safety Tools); enter make.
3. Adjust quantity, pick unit (Each / Set / Pair / Box), set condition_1, type store_location.
4. Submit. On Validate ensures tool_code is unique within the site.
5. Tools has no Active field, so retire a tool by setting condition_1 to Lost or Damaged, or hard-delete via the guarded On Delete.

Add an asset category

1. Open Add Asset Category. On Load stamps SITE and ticks Active.
2. Type category_name (e.g. Chillers). This form is intentionally minimal: SITE + category_name + active only.
3. There is NO prefix field on the live form. If category prefixes/codes are required, add a prefix field first, then extend the On Load script.
4. Submit. On Validate blocks a duplicate category_name within the site.
5. Categories then classify Assets in the transactional module.

Work Orders form > Workflow > On Add AND On Edit > On Success (assignment email to the technician)

from must be a verified org sender - zoho.adminuserid resolves to the app owner (demo1redecorporativa2). input.ID is populated on On Success of both Add and Edit, so the deep-link is valid on first save. input.SITE.Site_Name dereferences the SITE single-select lookup (SITE.Site_Name). To avoid a re-send on every later edit, add the criterion status changed to "Assigned" (Edit) or gate on a sent flag.

// Fires after a Work Order is saved. When it is Assigned, email the technician.
// input.<field> = the record just saved. input.ID = this work order's record id.
// assigned_technician is a MULTI_SELECT_LOOKUP, so it is a list of technician ids.
if(input.status == "Assigned")
{
	techEmail = "";
	techName = "";
	for each  techId in input.assigned_technician
	{
		techRec = technicians[ID == techId];
		techEmail = techRec.email;      // Technicians.email (EMAIL field)
		techName = techRec.full_name;   // Technicians.full_name (SINGLE_LINE)
		break;                          // WHILE is not allowed - loop once and break
	}
	if(techEmail != null && techEmail != "")
	{
		// Record deep-link: opens this work order in the Work Orders report
		recUrl = "https://creator.zoho.com/appbuilder/demo1redecorporativa2/cafm/#Report:work_orders_Report/" + input.ID;
		subject = "Work Order " + input.work_order_number + " assigned to you";
		msgBody = "<p>Hi " + techName + ",</p>";
		msgBody = msgBody + "<p>The following work order has been assigned to you.</p>";
		msgBody = msgBody + "<p><b>WO Number:</b> " + input.work_order_number + "<br>";
		msgBody = msgBody + "<b>Site:</b> " + input.SITE.Site_Name + "<br>";
		msgBody = msgBody + "<b>Section:</b> " + input.Section1 + "<br>";
		msgBody = msgBody + "<b>Priority:</b> " + input.priority + "<br>";
		msgBody = msgBody + "<b>Target Completion:</b> " + input.target_completion.toString("dd-MMM-yyyy hh:mm a") + "</p>";
		msgBody = msgBody + "<p>Open the work order: <a href=\"" + recUrl + "\">" + input.work_order_number + "</a></p>";
		sendmail
		[
			from : zoho.adminuserid
			to : techEmail
			subject : subject
			message : msgBody
		]
	}
}

Service Requests form > Workflow > On Add > On Success (acknowledgement email to the requester)

All_Service_Requests is the list report auto-created with the new service_requests form; use it in the deep-link. requester_email is the Service Requests EMAIL field. Same verified org sender (zoho.adminuserid). Keep the body simple inline HTML (no flexbox, no base64 images) so Gmail and Outlook render it.

// Fires when a new Service Request is logged. Acknowledge it to the requester.
// service_requests is the NEW form built in this module (see dataModelRows).
if(input.requester_email != null && input.requester_email != "")
{
	recUrl = "https://creator.zoho.com/appbuilder/demo1redecorporativa2/cafm/#Report:All_Service_Requests/" + input.ID;
	subject = "We received your request " + input.request_number;
	msgBody = "<p>Hi " + input.requester_name + ",</p>";
	msgBody = msgBody + "<p>Thank you for contacting the JOOD FM facilities team. Your service request has been logged and acknowledged.</p>";
	msgBody = msgBody + "<p><b>Request Number:</b> " + input.request_number + "<br>";
	msgBody = msgBody + "<b>Site:</b> " + input.SITE.Site_Name + "<br>";
	msgBody = msgBody + "<b>Priority:</b> " + input.priority + "<br>";
	msgBody = msgBody + "<b>Logged On:</b> " + zoho.currenttime.toString("dd-MMM-yyyy hh:mm a") + "</p>";
	msgBody = msgBody + "<p>Track progress: <a href=\"" + recUrl + "\">" + input.request_number + "</a></p>";
	sendmail
	[
		from : zoho.adminuserid
		to : input.requester_email
		subject : subject
		message : msgBody
	]
}

getRecipientForWO

Return the best email address for a work order: the assigned technician, falling back to the site Supervisor. Used by the WO assignment and SLA paths.

string getRecipientForWO(int wo)
{
	toEmail = "";
	woRec = work_orders[ID == wo];
	// 1) assigned technician first (assigned_technician is a MULTI_SELECT_LOOKUP)
	for each  techId in woRec.assigned_technician
	{
		techRec = technicians[ID == techId];
		if(techRec.email != null && techRec.email != "")
		{
			toEmail = techRec.email;
		}
		break;   // WHILE not allowed - take the first technician and break
	}
	// 2) fall back to the site Supervisor (Supervisor.SITE == this WO's SITE)
	if(toEmail == "")
	{
		for each  supRec in Supervisor[SITE == woRec.SITE]
		{
			toEmail = supRec.Email;   // Supervisor.Email (EMAIL field)
			break;
		}
	}
	return toEmail;
}

getRoleEmail

Resolve an Administrator or Storekeeper email from the Technicians form by role and SITE (Technicians.role holds Supervisor/Administrator/Technician/Storekeeper). Used for SLA, low-stock and expiry alerts.

string getRoleEmail(string roleName, int siteId)
{
	found = "";
	for each  t in technicians[role == roleName && SITE == siteId && active == true]
	{
		if(t.email != null && t.email != "")
		{
			found = t.email;
			break;
		}
	}
	// Administrators may be app-wide (no site match) - fall back to any active admin
	if(found == "" && roleName == "Administrator")
	{
		for each  t in technicians[role == "Administrator" && active == true]
		{
			found = t.email;
			break;
		}
	}
	return found;
}

sendNotification

Central dispatcher. Given a kind and a record id it builds the subject and HTML body for that event, resolves the recipient, sends via zoho.sendmail from the org sender, and writes a Notification_Log audit row. Every workflow calls this.

void sendNotification(string kind, int recordId)
{
	senderAddress = zoho.adminuserid;   // verified org sender (owner demo1redecorporativa2)
	baseUrl = "https://creator.zoho.com/appbuilder/demo1redecorporativa2/cafm/#Report:";
	toEmail = "";
	subject = "";
	body = "";
	refType = "";
	if(kind == "WO_ASSIGNED" || kind == "WO_ONHOLD" || kind == "WO_DONE")
	{
		refType = "work_orders";
		wo = work_orders[ID == recordId];
		recUrl = baseUrl + "work_orders_Report/" + recordId;
		woLink = "<a href=\"" + recUrl + "\">" + wo.work_order_number + "</a>";
		if(kind == "WO_ASSIGNED")
		{
			toEmail = getRecipientForWO(recordId);
			subject = "Work Order " + wo.work_order_number + " assigned";
			body = "<p>A work order has been assigned.</p><p>WO " + woLink + " at " + wo.SITE.Site_Name + " - priority " + wo.priority + ".</p>";
		}
		else if(kind == "WO_ONHOLD")
		{
			toEmail = wo.requester_email;   // raiser of the work order
			subject = "Work Order " + wo.work_order_number + " is on hold";
			body = "<p>Your work order " + woLink + " has been placed on hold. We will update you when it resumes.</p>";
		}
		else if(kind == "WO_DONE")
		{
			toEmail = wo.requester_email;   // raiser / requester
			subject = "Work Order " + wo.work_order_number + " " + wo.status;
			body = "<p>Your work order " + woLink + " is now " + wo.status + ".</p>";
		}
	}
	else if(kind == "PPM_DUE")
	{
		refType = "ppm_schedules";
		ppm = ppm_schedules[ID == recordId];
		recUrl = baseUrl + "ppm_schedules_Report/" + recordId;
		for each  techId in ppm.assigned_technician
		{
			techRec = technicians[ID == techId];
			toEmail = techRec.email;
			break;
		}
		subject = "PPM " + ppm.schedule_code + " due on " + ppm.next_due_date.toString("dd-MMM-yyyy");
		body = "<p>Preventive maintenance " + ppm.schedule_code + " is due on " + ppm.next_due_date.toString("dd-MMM-yyyy") + ".</p><p><a href=\"" + recUrl + "\">Open schedule</a></p>";
	}
	else if(kind == "SLA_BREACH")
	{
		refType = "work_orders";
		wo = work_orders[ID == recordId];
		recUrl = baseUrl + "work_orders_Report/" + recordId;
		supEmail = "";
		for each  supRec in Supervisor[SITE == wo.SITE]
		{
			supEmail = supRec.Email;
			break;
		}
		adminEmail = getRoleEmail("Administrator", wo.SITE);
		toEmail = supEmail;
		if(adminEmail != "")
		{
			toEmail = toEmail + "," + adminEmail;   // to takes comma-separated addresses
		}
		subject = "SLA breach on Work Order " + wo.work_order_number;
		body = "<p>Work order <a href=\"" + recUrl + "\">" + wo.work_order_number + "</a> has breached its SLA. Priority " + wo.priority + ", status " + wo.status + ", target " + wo.target_completion.toString("dd-MMM-yyyy hh:mm a") + ".</p>";
	}
	else if(kind == "CONTRACT_EXPIRY")
	{
		refType = "Asset_Contract";
		con = Asset_Contract[ID == recordId];
		recUrl = baseUrl + "All_Asset_Contracts/" + recordId;
		toEmail = getRoleEmail("Administrator", con.SITE);
		subject = "Asset contract expiring at " + con.SITE.Site_Name;
		body = "<p>An asset contract ends on " + con.End_Date.toString("dd-MMM-yyyy") + ".</p><p><a href=\"" + recUrl + "\">Open contract</a></p>";
	}
	else if(kind == "WARRANTY_EXPIRY")
	{
		refType = "assets";
		ast = assets[ID == recordId];
		recUrl = baseUrl + "assets_Report/" + recordId;
		toEmail = getRoleEmail("Administrator", ast.SITE);
		subject = "Warranty expiring for asset " + ast.asset_name;
		body = "<p>Asset " + ast.asset_id + " " + ast.asset_name + " warranty expires on " + ast.warranty_expiry.toString("dd-MMM-yyyy") + ".</p><p><a href=\"" + recUrl + "\">Open asset</a></p>";
	}
	else if(kind == "LOW_STOCK")
	{
		refType = "parts_master";
		part = parts_master[ID == recordId];
		recUrl = baseUrl + "parts_master_Report/" + recordId;
		storeEmail = getRoleEmail("Storekeeper", part.SITE);
		adminEmail = getRoleEmail("Administrator", part.SITE);
		toEmail = storeEmail;
		if(adminEmail != "")
		{
			if(toEmail != "")
			{
				toEmail = toEmail + "," + adminEmail;
			}
			else
			{
				toEmail = adminEmail;
			}
		}
		subject = "Low stock - " + part.part_name;
		body = "<p>Part " + part.part_code + " " + part.part_name + " is at " + part.current_balance + ", at or below its reorder level " + part.reorder_level + ".</p><p><a href=\"" + recUrl + "\">Open part</a></p>";
	}
	else if(kind == "SR_ACK" || kind == "SR_CLOSED")
	{
		refType = "service_requests";
		sr = service_requests[ID == recordId];
		recUrl = baseUrl + "All_Service_Requests/" + recordId;
		toEmail = sr.requester_email;
		if(kind == "SR_ACK")
		{
			subject = "Service Request " + sr.request_number + " received";
			body = "<p>Hi " + sr.requester_name + ", we have received your request " + sr.request_number + " and our team is on it.</p><p><a href=\"" + recUrl + "\">Track your request</a></p>";
		}
		else
		{
			subject = "Service Request " + sr.request_number + " closed";
			body = "<p>Hi " + sr.requester_name + ", your request " + sr.request_number + " has been closed. Thank you.</p>";
		}
	}
	// Send only when a recipient was resolved, then write the audit row
	if(toEmail != null && toEmail != "")
	{
		sendmail
		[
			from : senderAddress
			to : toEmail
			subject : subject
			message : body
		]
		logInsert = insert into Notification_Log
		[
			kind : kind
			to_email : toEmail
			ref_type : refType
			ref_id : recordId
			sent_on : zoho.currenttime
		];
	}
}

Assigning a work order emails the technician

1. Supervisor opens a work order, sets Assigned Technician and changes status to Assigned, then Update.
2. Work Orders On Edit On Success fires; status == "Assigned" is true.
3. The first id in input.assigned_technician is read and technicians[ID==techId].email is fetched.
4. zoho.sendmail sends from zoho.adminuserid to that technician email with the WO deep-link (work_orders_Report/input.ID).
5. A Notification_Log row (kind WO_ASSIGNED, ref_type work_orders, ref_id = WO id) is written.

Acknowledging a service request emails the requester

1. An occupant logs a Service Request; the record is added with requester_email filled.
2. Service Requests On Add On Success fires and sets status to Acknowledged / acknowledged_on.
3. zoho.sendmail sends from zoho.adminuserid to input.requester_email with the request number and the All_Service_Requests deep-link.
4. A Notification_Log row (kind SR_ACK, ref_type service_requests) is written.

SLA breach emails the supervisor

1. The daily/several-times-daily scheduled workflow loops open work orders.
2. For a WO whose target_completion is past and status is not Completed/Verified/Cancelled, it calls sendNotification("SLA_BREACH", wo.ID).
3. The dispatcher reads Supervisor[SITE == wo.SITE].Email and adds getRoleEmail("Administrator", wo.SITE), joined by a comma.
4. zoho.sendmail sends the breach notice to the supervisor and admin with the WO deep-link.
5. A Notification_Log row (kind SLA_BREACH) records the send.

Creator Page (ZML) - Technician KPI dashboard - server block plus render

Confirm the technicians name field link name; replace technician_name if the form uses Name. The same numbers back the R4 Summary report - use whichever the reviewer prefers.

<%
// ===== Technician KPI dashboard - group-by via for-each accumulate =====
acc = Map();
wos = work_orders[status == "Completed" || status == "Verified"];
for each  wo in wos
{
    for each  t in wo.assigned_technician        // multi-select lookup
    {
        k = t.toString();
        r = ifnull(acc.get(k), Map());
        r.put("jobs", ifnull(r.get("jobs"),0) + 1);
        if(wo.on_time == true) { r.put("ot", ifnull(r.get("ot"),0) + 1); }
        r.put("res", ifnull(r.get("res"),0) + ifnull(wo.resolution_time_mins,0));
        r.put("dt",  ifnull(r.get("dt"),0.0) + ifnull(wo.downtime_hours,0.0));
        acc.put(k, r);
    }
}
%>
<table border="1" cellpadding="6">
 <tr><th>Technician</th><th>Jobs</th><th>On-time %</th><th>Avg Resolution min</th><th>Downtime h</th></tr>
 <% for each  k in acc.keys() { %>
   <% r = acc.get(k); j = r.get("jobs"); %>
   <tr>
     <td><%= technicians[ID == k.toLong()].technician_name %></td>
     <td><%= j %></td>
     <td><%= round((ifnull(r.get("ot"),0) * 100.0)/ j, 1) %></td>
     <td><%= round(r.get("res") * 1.0 / j, 0) %></td>
     <td><%= r.get("dt") %></td>
   </tr>
 <% } %>
</table>

task_checklists - On Success (Create / Edit / Delete) - checklist compliance formula logic

A native Creator formula field cannot count records in another form, so this Deluge writes the stored percent that R2 reports. On Delete, guard with a null check on input.work_order.

// ===== checklist_compliance_percent - formula-column equivalent =====
// input.work_order is a multi-select list, so one saved check can touch several WOs.
for each  woId in input.work_order
{
    total = task_checklists[work_order.contains(woId)].count();
    done  = task_checklists[work_order.contains(woId) && result != null && result != "Not applicable"].count();
    pct = 0.0;
    if(total > 0) { pct = round((done * 100.0)/ total, 2); }
    wo = work_orders[ID == woId];
    wo.checklist_items_total = total;      // done vs total for R2
    wo.checklist_items_done  = done;
    wo.checklist_compliance_percent = pct; // value R2 groups and averages on
}

computeChecklistCompliance

Return the checklist completion percent for one work order and stamp the stored count/percent fields the R2 report groups on. Called from the WO close flow and from the task_checklists On Success script.

Decimal computeChecklistCompliance(int woId)
{
    // task_checklists.work_order is a multi-select lookup -> use contains().
    total = task_checklists[work_order.contains(woId)].count();
    done  = task_checklists[work_order.contains(woId) && result != null && result != "Not applicable"].count();
    pct = 0.0;
    if(total > 0)
    {
        pct = round((done * 100.0) / total, 2);
    }
    wo = work_orders[ID == woId];
    if(wo.count() > 0)
    {
        wo.checklist_items_total = total;
        wo.checklist_items_done = done;
        wo.checklist_compliance_percent = pct;
    }
    return pct;
}

stampWorkOrderMetrics

On work-order completion, compute response/resolution durations, the priority-based SLA response flag, the target-completion SLA flag, and on_time, so R3, R4 and R5 read stored values. Wire this into the Work Order close blueprint transition or the work_orders On Edit event when status becomes Completed.

void stampWorkOrderMetrics(int woId)
{
    wo = work_orders[ID == woId];
    if(wo.count() == 0) { return; }

    respMins = 0;
    resMins = 0;
    if(wo.raised_on != null && wo.started_on != null)
    {
        respMins = wo.raised_on.minutesBetween(wo.started_on);
    }
    if(wo.raised_on != null && wo.completed_on != null)
    {
        resMins = wo.raised_on.minutesBetween(wo.completed_on);
    }

    // priority response target in minutes - change here only
    respTarget = 240;
    if(wo.priority == "Critical") { respTarget = 30; }
    else if(wo.priority == "High") { respTarget = 60; }
    else if(wo.priority == "Medium") { respTarget = 240; }
    else if(wo.priority == "Low") { respTarget = 480; }

    respMet = false;
    if(wo.started_on != null && respMins <= respTarget) { respMet = true; }

    resMet = false;
    if(wo.completed_on != null && wo.target_completion != null && wo.completed_on <= wo.target_completion) { resMet = true; }

    onTime = false;
    if(wo.completed_on != null && wo.scheduled_date != null && wo.completed_on.toDate() <= wo.scheduled_date) { onTime = true; }

    wo.response_time_mins = respMins;
    wo.resolution_time_mins = resMins;
    wo.sla_response_met = respMet;
    wo.sla_resolution_met = resMet;
    wo.on_time = onTime;
}

getTechnicianKPIs

Deluge has no GROUP BY, so technician KPIs are built by fetching completed WOs in a window and accumulating into a Map keyed by technician id. Returns a list of KPI maps used by the R4 page and by any custom export.

list getTechnicianKPIs(date fromD, date toD)
{
    out = List();
    acc = Map();
    wos = work_orders[(status == "Completed" || status == "Verified") && completed_on >= fromD && completed_on <= toD];
    for each  wo in wos
    {
        for each  t in wo.assigned_technician   // multi-select lookup
        {
            key = t.toString();
            row = ifnull(acc.get(key), Map());
            row.put("jobs", ifnull(row.get("jobs"),0) + 1);
            if(wo.on_time == true) { row.put("ot", ifnull(row.get("ot"),0) + 1); }
            row.put("res", ifnull(row.get("res"),0) + ifnull(wo.resolution_time_mins,0));
            row.put("dt", ifnull(row.get("dt"),0.0) + ifnull(wo.downtime_hours,0.0));
            acc.put(key, row);
        }
    }
    for each  k in acc.keys()
    {
        r = acc.get(k);
        j = r.get("jobs");
        m = Map();
        m.put("technician_id", k);
        m.put("jobs_completed", j);
        m.put("on_time_percent", if(j > 0, round((ifnull(r.get("ot"),0) * 100.0)/ j, 1), 0));
        m.put("avg_resolution_mins", if(j > 0, round(r.get("res") * 1.0 / j, 0), 0));
        m.put("total_downtime_hours", r.get("dt"));
        out.add(m);
    }
    return out;
}

sendMonthlyComplianceDigest

Roll up the previous calendar month per SITE, write one compliance_snapshots row per SITE for history, and email the HTML PPM + SLA summary to facilities management. Called by the Monthly Compliance Digest scheduler.

void sendMonthlyComplianceDigest()
{
    firstThis = zoho.currentdate.toStartOfMonth();   // 1st of current month
    endPrev   = firstThis.subDay(1);                 // last day prev month
    startPrev = endPrev.toStartOfMonth();            // 1st of prev month
    period    = startPrev.toString("yyyy-MM");

    html = "<h3>CAFM Compliance " + period + "</h3>";
    html = html + "<table border='1' cellpadding='6'><tr><th>Site</th><th>PPM Sched</th><th>PPM Done</th><th>PPM %</th><th>SLA Resp %</th><th>SLA Resol %</th><th>Avg Downtime h</th></tr>";

    for each  s in SITE[ID != 0]
    {
        sid = s.ID;
        ppmSched = work_orders[SITE == sid && job_type == "Preventive" && scheduled_date >= startPrev && scheduled_date <= endPrev].count();
        ppmDone  = work_orders[SITE == sid && job_type == "Preventive" && (status == "Completed" || status == "Verified") && scheduled_date >= startPrev && scheduled_date <= endPrev].count();
        ppmPct   = if(ppmSched > 0, round((ppmDone * 100.0)/ ppmSched, 1), 0);

        base     = work_orders[SITE == sid && completed_on >= startPrev && completed_on < firstThis];
        total    = base.count();
        respMet  = work_orders[SITE == sid && completed_on >= startPrev && completed_on < firstThis && sla_response_met == true].count();
        resolMet = work_orders[SITE == sid && completed_on >= startPrev && completed_on < firstThis && sla_resolution_met == true].count();
        respPct  = if(total > 0, round((respMet * 100.0)/ total, 1), 0);
        resolPct = if(total > 0, round((resolMet * 100.0)/ total, 1), 0);

        downSum = 0.0;
        for each  w in base { downSum = downSum + ifnull(w.downtime_hours, 0.0); }
        avgDown = if(total > 0, round(downSum / total, 2), 0);

        insert into compliance_snapshots
        [
            SITE = sid
            period_month = startPrev
            ppm_scheduled = ppmSched
            ppm_completed = ppmDone
            ppm_compliance_percent = ppmPct
            sla_total_count = total
            sla_response_percent = respPct
            sla_resolution_percent = resolPct
            avg_downtime_hours = avgDown
            generated_on = zoho.currenttime
        ];

        html = html + "<tr><td>" + s.Site_Name + "</td><td>" + ppmSched + "</td><td>" + ppmDone + "</td><td>" + ppmPct + "</td><td>" + respPct + "</td><td>" + resolPct + "</td><td>" + avgDown + "</td></tr>";
    }
    html = html + "</table>";

    sendmail
    [
        from    : zoho.adminuserid
        to      : "facilities.management@joodfm.example"
        subject : "CAFM Monthly Compliance - " + period
        message : html
    ];
}

Monthly compliance review

1. Open R1 PPM Compliance (Pivot) and set the column to the closed month; read compliance % per SITE and flag any SITE below the contract target.
2. Open R3 SLA Compliance (Summary) and review response % and resolution % by SITE, Section and priority; note Critical/High rows first.
3. Open R2 Checklist Compliance (Summary) and confirm high-criticality assets sit at 100% checklist completion.
4. Cross-check the Monthly Compliance Digest email and the matching compliance_snapshots rows for the same period.
5. Export or screenshot the three reports for the management pack.

Technician performance review

1. Open R4 Technician KPIs (Summary or the ZML KPI page).
2. Sort by on-time % ascending to surface laggards; read avg resolution mins and total downtime hours alongside.
3. Click a technician to drill into their completed work_orders list for the period.
4. Compare jobs_completed against workload to separate slow work from heavy load, and set next-month targets.

Upcoming expiries review

1. Open R7a Warranty Expiry (0-90 days) grouped by bucket and action the 0-30 items first.
2. Open R7b Contract Expiry (Asset_Contract) and R7c Vendor Contract Expiry and list renewals due.
3. Raise renewal POs or replacement work orders; for lapsing warranties decide extend cover or replace.
4. Re-run the reports after updates to confirm handled items drop out of the 0-30 bucket.

Page snippet — Step 1: resolve scope and facility header

Top of the dashboard snippet. Runs on every render. Portal logins expose the email in zoho.loginuserid (NOT zoho.loginuser). Sets scopeCrit used by every KPI below and opens the cards flex container.

// ===== CAFM Operations Dashboard — page snippet Deluge =====
email = zoho.loginuserid;                 // portal login exposes email here
mySite = getSiteForUser(email);           // existing fn -> SITE record id, 0 if none
isAdmin = (mySite == 0);                  // no Supervisor row -> all-sites admin view
scopeCrit = buildScopeCriteria(mySite);   // "ID != 0" (admin) or "SITE == <id>"

// --- Facility name header ---
facilityName = "All Facilities";
if(!isAdmin)
{
	for each st in SITE[ID == mySite]
	{
		facilityName = st.Site_Name + " (" + st.Site_Code + ")";
		break;
	}
}
html = "<h2 style='font-family:sans-serif;color:#263238;'>CAFM Operations — " + facilityName + "</h2>";
html = html + "<div style='display:flex;flex-wrap:wrap;'>";  // cards row (closed in step 4)

Page snippet — Step 2: Work Order KPIs (site-scoped vs global branch shown)

Open WOs uses a pure criteria count (branches on scope automatically via scopeCrit). Overdue is date-sensitive so it is computed in a for-each comparing target_completion to zoho.currenttime — safer than putting datetimes in a criteria string.

now = zoho.currenttime;

// --- Open Work Orders (criteria count; scopeCrit already carries admin-vs-site branch) ---
openCrit = scopeCrit + " && (status != \"Completed\" && status != \"Cancelled\" && status != \"Verified\")";
openCount = work_orders[openCrit].count();
html = html + render_kpi_card("Open Work Orders", openCount + "", "Not yet completed", "#1565c0", "wo");

// --- Overdue (open AND past target_completion) — date compare in a loop ---
overdue = 0;
for each wo in work_orders[openCrit]
{
	if(wo.target_completion != null && wo.target_completion < now)
	{
		overdue = overdue + 1;
	}
}
html = html + render_kpi_card("Overdue", overdue + "", "Past target completion", "#c62828", "overdue");

// --- Critical open ---
critCount = work_orders[openCrit + " && priority == \"Critical\""].count();
html = html + render_kpi_card("Critical Open", critCount + "", "Priority Critical", "#ad1457", "critical");

Page snippet — Step 3: Completed Today, On-time %, Downtime (single pass)

One for-each over completed WOs computes three KPIs at once (efficient). on_time is a Decision box (boolean). completed_on.toDate() is compared to zoho.currentdate for the today count. Reuse this loop pattern instead of 3 separate queries.

today = zoho.currentdate;
doneToday = 0;  onTimeDone = 0;  totalDone = 0;  totalDowntime = 0.0;
for each wo in work_orders[scopeCrit + " && status == \"Completed\""]
{
	totalDone = totalDone + 1;
	if(wo.on_time == true)          { onTimeDone = onTimeDone + 1; }
	if(wo.downtime_hours != null)   { totalDowntime = totalDowntime + wo.downtime_hours; }
	if(wo.completed_on != null && wo.completed_on.toDate() == today) { doneToday = doneToday + 1; }
}
onTimePct = 0;
if(totalDone > 0) { onTimePct = round((onTimeDone * 100.0) / totalDone, 0); }
html = html + render_kpi_card("Completed Today", doneToday + "", "Closed today", "#2e7d32", "done");
html = html + render_kpi_card("On-time %", onTimePct + "%", onTimeDone + " of " + totalDone + " on time", "#00838f", "done");
html = html + render_kpi_card("Downtime hours", totalDowntime + "", "Total logged", "#6a1b9a", "wo");

Page snippet — Step 4: Assets, Parts, PPM, Warranty, Contracts (then close row)

Low-stock needs a row-by-row compare (current_balance <= reorder_level) so it uses a loop; the rest are criteria counts. Date-window counts use today.addDay(n) and app date format dd-MMM-yyyy in the criteria string. Closes the cards flex container.

// --- Active assets & Under repair ---
activeAssets = assets[scopeCrit + " && status == \"In service\""].count();
underRepair  = assets[scopeCrit + " && status == \"Under repair\""].count();
html = html + render_kpi_card("Active Assets", activeAssets + "", "In service", "#00695c", "asset");
html = html + render_kpi_card("Under Repair", underRepair + "", "Status under repair", "#ef6c00", "asset");

// --- Low stock parts (balance at or below reorder level) ---
lowStock = 0;
for each p in parts_master[scopeCrit + " && active == true"]
{
	if(p.reorder_level != null && p.current_balance <= p.reorder_level) { lowStock = lowStock + 1; }
}
html = html + render_kpi_card("Low Stock Parts", lowStock + "", "At or below reorder", "#d84315", "stock");

// --- PPM due in 7 days / Warranty 90d / Contracts 60d (date-window criteria) ---
in7  = today.addDay(7);
in90 = today.addDay(90);
in60 = today.addDay(60);
ppmDue = ppm_schedules[scopeCrit + " && status == \"Active\" && next_due_date >= '" + today.toString("dd-MMM-yyyy") + "' && next_due_date <= '" + in7.toString("dd-MMM-yyyy") + "'"].count();
warr   = assets[scopeCrit + " && warranty_expiry >= '" + today.toString("dd-MMM-yyyy") + "' && warranty_expiry <= '" + in90.toString("dd-MMM-yyyy") + "'"].count();
contr  = Asset_Contract[scopeCrit + " && End_Date >= '" + today.toString("dd-MMM-yyyy") + "' && End_Date <= '" + in60.toString("dd-MMM-yyyy") + "'"].count();
html = html + render_kpi_card("PPM Due 7d", ppmDue + "", "Active schedules", "#3949ab", "ppm");
html = html + render_kpi_card("Warranty Expiry 90d", warr + "", "Assets expiring", "#5d4037", "asset");
html = html + render_kpi_card("Contracts Expiring 60d", contr + "", "Ending soon", "#455a64", "wo");
html = html + "</div>";  // close cards flex row

Page snippet — Step 5: mini-table (Overdue Work Orders) and return

Bounded to 10 rows with a for-each + break (no WHILE loops allowed). Reads the first linked asset name from the MULTI_SELECT_LOOKUP work_orders.asset via for-each+break, falling back to General_Asset. Return html (or assign to the snippet's output variable).

// ===== Mini table: Overdue Work Orders (max 10 rows) =====
html = html + "<h3 style='font-family:sans-serif;color:#263238;'>Overdue Work Orders</h3>";
html = html + "<table style='width:100%;border-collapse:collapse;font-family:sans-serif;font-size:13px;'>";
html = html + "<tr style='background:#eceff1;text-align:left;'><th style='padding:6px;'>WO</th><th>Priority</th><th>Asset</th><th>Target</th></tr>";
shown = 0;
for each wo in work_orders[openCrit]
{
	if(wo.target_completion != null && wo.target_completion < now)
	{
		assetName = wo.General_Asset;             // fallback for free-text assets
		for each a in wo.asset { assetName = a.asset_name; break; }  // first linked asset
		html = html + "<tr style='border-bottom:1px solid #eee;'>";
		html = html + "<td style='padding:6px;'>" + wo.work_order_number + "</td>";
		html = html + "<td>" + wo.priority + "</td>";
		html = html + "<td>" + assetName + "</td>";
		html = html + "<td>" + wo.target_completion.toString("dd-MMM HH:mm") + "</td>";
		html = html + "</tr>";
		shown = shown + 1;
	}
	if(shown >= 10) { break; }   // bounded loop
}
html = html + "</table>";
return html;   // render the assembled dashboard

render_kpi_card

Reusable card builder. Returns a self-contained HTML string for one KPI tile. Called once per KPI from the page snippet; keeps card styling in ONE place so all cards look identical. title=label, value=big number (stringify counts with value + ""), subtitle=small caption, color=accent hex, iconKey=short key mapped to an emoji/icon.

string render_kpi_card(string title, string value, string subtitle, string color, string iconKey)
{
	// Map a short key to an icon. Extend this list as new KPIs are added.
	icon = "🔧";
	if(iconKey == "overdue") { icon = "⏰"; }
	else if(iconKey == "critical") { icon = "🚨"; }
	else if(iconKey == "done") { icon = "✅"; }
	else if(iconKey == "asset") { icon = "🏭"; }
	else if(iconKey == "stock") { icon = "📦"; }
	else if(iconKey == "ppm") { icon = "🗓️"; }
	// Build the card. flex:1 lets cards wrap responsively in the row container.
	card = "<div style='flex:1 1 180px;min-width:180px;background:#ffffff;border-radius:12px;";
	card = card + "border-left:6px solid " + color + ";box-shadow:0 1px 4px rgba(0,0,0,0.08);";
	card = card + "padding:14px 16px;margin:8px;font-family:sans-serif;'>";
	card = card + "<div style='font-size:13px;color:#607d8b;'>" + icon + " " + title + "</div>";
	card = card + "<div style='font-size:30px;font-weight:700;color:" + color + ";'>" + value + "</div>";
	card = card + "<div style='font-size:12px;color:#90a4ae;'>" + subtitle + "</div>";
	card = card + "</div>";
	return card;
}

buildScopeCriteria

The site-scoping pattern in one helper. Given mySite (0 = admin), returns the criteria-string prefix every KPI query starts from. Admin gets an always-true base (ID != 0) so all rows match; a supervisor gets SITE == <id>. Callers append their own conditions with && .

string buildScopeCriteria(int mySite)
{
	// mySite == 0 -> admin / all-sites. Base "ID != 0" is always true so we can safely append && ...
	crit = "ID != 0";
	if(mySite != 0)
	{
		// SITE is a lookup on every operational form; filter by the referenced SITE record id.
		crit = "SITE == " + mySite;
	}
	return crit;
}

getSiteForUser

EXISTING owner-context function — shown for reference only, DO NOT recreate. Takes the portal user's email and returns their Supervisor SITE record id, or 0 if the user has no Supervisor row (i.e. treat as admin/all-sites). Matches against Supervisor.Email.

int getSiteForUser(string email)
{
	// Reference implementation of the function already deployed in the app.
	siteId = 0;
	// for-each + break (Creator does not allow WHILE loops)
	for each s in Supervisor[Email == email]
	{
		siteId = s.SITE;   // lookup value resolves to the referenced SITE record id
		break;             // first match is enough
	}
	return siteId;
}

Supervisor sees only their hospital KPIs

1. Supervisor opens the Operations Dashboard page from the portal
2. Snippet reads their email via zoho.loginuserid
3. getSiteForUser(email) finds their Supervisor row and returns the linked SITE id (e.g. 342)
4. isAdmin is false so buildScopeCriteria returns SITE == 342
5. Every KPI count and the overdue mini-table is filtered with scopeCrit = SITE == 342
6. Header shows their site name and code, e.g. King Fahad Hospital (KFH)
7. They see only their hospital's numbers; the other 5 sites are never queried or shown

Admin sees all facilities

1. An admin (no Supervisor row) opens the same page
2. getSiteForUser returns 0 so isAdmin is true
3. buildScopeCriteria returns the always-true base ID != 0
4. All KPI queries aggregate across all 6 hospital sites
5. Header reads All Facilities
6. Cards show consolidated totals; a future site dropdown can re-scope by rebuilding scopeCrit

Proposed Technician-KPI dashboard (still to build)

1. New page scoped to a technician instead of a SITE
2. Jobs done = count of work_orders where assigned_technician contains the tech and status == Completed
3. On-time % = share of those completed WOs with on_time == true
4. Avg resolution = average of (completed_on minus started_on) in hours over completed WOs
5. Cards rendered by reusing render_kpi_card with a technician icon key
6. Add a date-range picker so leads can review per-technician performance by month

Scheduled function runDailyCafmJobs - runs once daily (this is the whole module)

Sub-jobs are called with thisapp.<functionName>() because they are standalone Creator functions living in their own modules. Each returns an int count; the orchestrator only reads the count. try/catch per job means one failure is logged into the summary and the remaining jobs still run - errorCount lands in the audit_log row so the FM team can spot a bad night. Idempotency is enforced INSIDE each generator (guard on source_schedule/asset + scheduled_date == today), so re-running this function by hand the same day adds nothing. e.toString() gives a readable error; e.get("message") is an alternative. reference_type and action_1 accept the custom strings because Allow-other-choices is enabled on both.

void automation.runDailyCafmJobs()
{
	runStamp = zoho.currenttime;
	runId = "CAFM-DAILY-" + zoho.currentdate.toString("yyyy-MM-dd");
	lines = List();
	errorCount = 0;

	// 1. Preventive PPM work orders
	try 
	{
		c = thisapp.generatePpmWorkOrders();
		lines.add("PPM work orders raised: " + c);
	}
	catch (e)
	{
		errorCount = errorCount + 1;
		lines.add("PPM FAILED - " + e.toString());
	}

	// 2. Predictive (runtime threshold) work orders
	try 
	{
		c = thisapp.generatePredictiveWorkOrders();
		lines.add("Predictive work orders raised: " + c);
	}
	catch (e)
	{
		errorCount = errorCount + 1;
		lines.add("Predictive FAILED - " + e.toString());
	}

	// 3. Contract / warranty / vendor expiry reminders
	try 
	{
		c = thisapp.sendContractWarrantyReminders();
		lines.add("Expiry reminders sent: " + c);
	}
	catch (e)
	{
		errorCount = errorCount + 1;
		lines.add("Reminders FAILED - " + e.toString());
	}

	// 4. Low stock re-check
	try 
	{
		c = thisapp.recheckLowStock();
		lines.add("Low stock alerts: " + c);
	}
	catch (e)
	{
		errorCount = errorCount + 1;
		lines.add("Low stock FAILED - " + e.toString());
	}

	// 5. SLA breach scan and escalation
	try 
	{
		c = thisapp.scanSlaBreaches();
		lines.add("SLA breaches escalated: " + c);
	}
	catch (e)
	{
		errorCount = errorCount + 1;
		lines.add("SLA scan FAILED - " + e.toString());
	}

	lines.add("Errors this run: " + errorCount);
	summaryText = lines.toString("\n");

	// One audit trail row per run
	insert into audit_log
	[
		reference_type = "Automation run"
		reference_id = runId
		action_1 = "Scheduled run"
		field_changed = "runDailyCafmJobs"
		new_value = summaryText
		changed_by = "System scheduler"
		changed_on = runStamp
	];
	info summaryText;
}

Scheduled function sendMonthlyComplianceEmail - runs monthly

Uses a trailing 30-day window via the documented date arithmetic (a Date minus an int subtracts days). Replace the placeholder recipient with the real FM manager address; the from address must be a verified sender in the app email settings. lowParts is fetched then .count() is called (do not chain the criteria and count in one expression). pct.round(1) trims to one decimal. Attach to a Monthly schedule (day 1).

void automation.sendMonthlyComplianceEmail()
{
	today = zoho.currentdate;
	windowStart = today - 30;
	dueCount = 0;
	onTime = 0;
	overdue = 0;
	woList = work_orders[job_type == "Preventive" && scheduled_date >= windowStart && scheduled_date <= today];
	for each  wo in woList
	{
		dueCount = dueCount + 1;
		if(wo.status == "Completed" || wo.status == "Verified")
		{
			if(wo.on_time == true)
			{
				onTime = onTime + 1;
			}
		}
		else if(wo.target_completion != null && wo.target_completion < zoho.currenttime)
		{
			overdue = overdue + 1;
		}
	}
	lowParts = parts_master[active == true && current_balance <= reorder_level];
	lowStock = lowParts.count();
	pct = 0.0;
	if(dueCount > 0)
	{
		pct = (onTime * 100.0) / dueCount;
	}
	body = "<h3>CAFM Monthly Compliance</h3>";
	body = body + "<p>Window " + windowStart.toString("dd-MMM-yyyy") + " to " + today.toString("dd-MMM-yyyy") + "</p><ul>";
	body = body + "<li>Preventive work orders due " + dueCount + "</li>";
	body = body + "<li>Completed on time " + onTime + "</li>";
	body = body + "<li>Overdue or still open " + overdue + "</li>";
	body = body + "<li>PPM compliance " + pct.round(1) + " percent</li>";
	body = body + "<li>Parts below reorder level " + lowStock + "</li></ul>";
	sendmail
	[
		from : zoho.adminuserid
		to : "fm.manager@joodfm.example"
		subject : "CAFM Compliance " + today.toString("MMM-yyyy")
		message : body
	]
}

runDailyCafmJobs

Single scheduled entry point, run once per day. Fans out to the five sub-jobs, isolates each with its own try/catch so one failure never aborts the rest, and records one summary line per job plus an error count in a single audit_log row.

void automation.runDailyCafmJobs()
{
	// Master daily orchestrator - full runnable body is in the scripts section.
	// Pattern per sub-job:
	//   try { c = thisapp.<subJob>(); lines.add("<label>: " + c); }
	//   catch (e) { errorCount = errorCount + 1; lines.add("<label> FAILED - " + e.toString()); }
	// Sub-jobs called: generatePpmWorkOrders, generatePredictiveWorkOrders,
	// sendContractWarrantyReminders, recheckLowStock, scanSlaBreaches.
	// Then: insert into audit_log [ reference_type="Automation run" ... new_value=lines.toString("\n") ];
}

generatePpmWorkOrders

Owned by the PPM module. Raises a Preventive work order for every Active ppm_schedules row whose next_due_date has arrived, guarding on schedule + scheduled_date so the same schedule cannot be raised twice in one day (idempotency).

int automation.generatePpmWorkOrders()
{
	created = 0;
	today = zoho.currentdate;
	dueList = ppm_schedules[status == "Active" && next_due_date <= today];
	for each  sch in dueList
	{
		// idempotency - skip if a WO already exists for this schedule today
		exist = work_orders[source_schedule == sch.ID && scheduled_date == today];
		if(exist.count() == 0)
		{
			insert into work_orders
			[
				SITE = sch.SITE
				job_type = "Preventive"
				asset = sch.asset
				source_schedule = sch.ID
				checklist_template = sch.checklist_template
				status = "Open"
				priority = "Medium"
				scheduled_date = today
				raised_on = zoho.currenttime
			];
			created = created + 1;
		}
	}
	return created;
}

generatePredictiveWorkOrders

Owned by the Predictive module. Raises a Predictive work order for each asset whose (current_running_hours - hours_at_last_service) has reached runtime_threshold, with the same same-day guard so it does not double-raise.

int automation.generatePredictiveWorkOrders()
{
	created = 0;
	today = zoho.currentdate;
	dueAssets = assets[track_running_hours == true && current_running_hours - hours_at_last_service >= runtime_threshold];
	for each  a in dueAssets
	{
		exist = work_orders[asset == a.ID && job_type == "Predictive" && scheduled_date == today];
		if(exist.count() == 0)
		{
			insert into work_orders
			[
				SITE = a.SITE
				job_type = "Predictive"
				asset = a.ID
				General_Asset = a.asset_name
				status = "Open"
				priority = "High"
				scheduled_date = today
				raised_on = zoho.currenttime
			];
			created = created + 1;
		}
	}
	return created;
}

sendContractWarrantyReminders

Owned by the Contracts module. Emails owners when an asset warranty_expiry, an Asset_Contract End_Date, or a vendor contract_expiry falls inside the next 30 days.

int automation.sendContractWarrantyReminders()
{
	sent = 0;
	today = zoho.currentdate;
	horizon = today + 30;
	// assets warranty
	wArr = assets[warranty_expiry != null && warranty_expiry >= today && warranty_expiry <= horizon];
	for each  a in wArr
	{
		// build message, sendmail to the site owner, then: sent = sent + 1;
	}
	// Asset_Contract[End_Date within horizon] and vendors[contract_expiry within horizon]
	// are handled the same way and add to sent.
	return sent;
}

recheckLowStock

Owned by the Inventory module. Finds parts_master rows where current_balance has dropped to or below reorder_level and raises or refreshes a low-stock alert for the stores team.

int automation.recheckLowStock()
{
	alerts = 0;
	lowParts = parts_master[active == true && current_balance <= reorder_level];
	for each  p in lowParts
	{
		// notify stores / flag part, then: alerts = alerts + 1;
	}
	return alerts;
}

scanSlaBreaches

Owned by the SLA module. Uses the new work_orders SLA fields to find overdue first responses and overdue resolutions, raises escalation_level, stamps escalated_on, and emails the site Supervisor.

int automation.scanSlaBreaches()
{
	escalated = 0;
	now = zoho.currenttime;
	// missed first response
	noResp = work_orders[first_response_on == null && response_due != null && response_due < now && status != "Completed" && status != "Verified" && status != "Cancelled"];
	for each  wo in noResp
	{
		// wo.escalation_level = ifnull(wo.escalation_level,0) + 1; wo.escalated_on = now; sendmail to Supervisor
		escalated = escalated + 1;
	}
	// missed resolution
	noRes = work_orders[resolution_due != null && resolution_due < now && status != "Completed" && status != "Verified" && status != "Cancelled"];
	for each  wo in noRes
	{
		escalated = escalated + 1;
	}
	return escalated;
}

sendMonthlyComplianceEmail

Monthly scheduled function. Summarises the last 30 days of Preventive work orders (due, completed on time, overdue), computes PPM compliance percent, counts parts below reorder level, and emails the FM manager. Full body in the scripts section.

void automation.sendMonthlyComplianceEmail()
{
	// window = last 30 days; loop Preventive work_orders counting due / onTime / overdue;
	// lowStock = parts_master[active && current_balance <= reorder_level].count();
	// pct = onTime*100.0/dueCount; build HTML body; sendmail to FM manager.
}

A daily run raises due PPM and predictive work orders and sends reminders

1. At 02:00 the Daily CAFM automation schedule fires and runs runDailyCafmJobs().
2. generatePpmWorkOrders() fetches ppm_schedules where status is Active and next_due_date is on or before today, and for each schedule that has no work order already carrying that source_schedule with scheduled_date today it inserts a Preventive work order with status Open.
3. generatePredictiveWorkOrders() fetches assets where current_running_hours minus hours_at_last_service is at or above runtime_threshold and inserts a Predictive work order for each that is not already raised today.
4. sendContractWarrantyReminders() emails owners for asset warranty_expiry, Asset_Contract End_Date and vendors contract_expiry that fall within the next 30 days.
5. recheckLowStock() flags parts_master rows where current_balance is at or below reorder_level.
6. Each sub-job returns its count; the orchestrator appends one line per job and writes a single audit_log row with reference_type Automation run, reference_id CAFM-DAILY-2026-09-18, and the full summary text in new_value.

An SLA breach is caught and escalated

1. scanSlaBreaches() runs inside the same daily orchestrator, inside its own try/catch.
2. It fetches open work_orders where first_response_on is empty and response_due is before now (missed first response), plus work_orders not Completed, Verified or Cancelled where resolution_due is before now (missed resolution).
3. For each breach it raises escalation_level by one, stamps escalated_on with the current time, and emails the site Supervisor via sendmail.
4. It returns the number of escalations and the orchestrator adds the line SLA breaches escalated N to the summary.
5. If scanSlaBreaches throws, the catch records SLA scan FAILED with the message, errorCount increments, and the other four jobs still complete - the failure is visible in the audit_log row for that run.

Assets — On Validate

Blocks a duplicate Asset ID within the same site. Bounded: the for-each breaks on the first real duplicate. cancel submit stops the save. Scoped per SITE because Asset IDs may legitimately repeat across the six hospitals; drop the SITE clause if IDs must be globally unique.

// Assets - On Validate
dupFound = false;
for each  d in assets[asset_id == input.asset_id && SITE == input.SITE]
{
	// on add input.ID is null; on edit skip the row being edited
	if(input.ID == null || d.ID != input.ID)
	{
		dupFound = true;
		break;
	}
}
if(dupFound)
{
	alert "Asset ID " + input.asset_id + " already exists at this site. Enter a unique Asset ID.";
	cancel submit;
}

Asset_Document — On Load

Stamps who/when, defaults the current flag, and previews the version. When the form is opened from an asset (asset + doc_type pre-filled via URL) nextDocVersion computes the real next number; otherwise it shows 1. On Success re-stamps the authoritative version at save time.

// Asset_Document - On Load (Created/blank record)
if(input.ID == null)
{
	input.uploaded_by = zoho.loginuserid;
	input.uploaded_on = zoho.currenttime;
	if(input.is_current == null)
	{
		input.is_current = true;
	}
	if(input.asset != null && input.doc_type != null)
	{
		input.version = thisapp.functions.nextDocVersion(input.asset.toString(), input.doc_type);
	}
	else
	{
		input.version = 1;
	}
}

Asset_Document — On Successful form submission

Authoritative revision control. Reads all prior rows for the same asset + doc_type (newest first), turns their is_current off, stamps this row's version = priorMax + 1 and is_current = true, then writes a Created audit row against the asset. Assigning input.field in On Success persists onto the just-saved record.

// Asset_Document - On Success (add)
priorMax = 0;
isFirst = true;
for each  pd in Asset_Document[asset == input.asset && doc_type == input.doc_type && ID != input.ID] sort by version desc
{
	if(isFirst)
	{
		priorMax = ifnull(pd.version,0);
		isFirst = false;
	}
	if(pd.is_current == true)
	{
		pd.is_current = false;
	}
}
newVer = priorMax + 1;
input.version = newVer;
input.is_current = true;
// resolve the asset SITE for the audit row
aSite = null;
for each  a in assets[ID == input.asset]
{
	aSite = a.SITE;
	break;
}
insert into audit_log
[
	SITE = aSite
	reference_type = "Asset"
	reference_id = input.asset.toString()
	action_1 = "Created"
	field_changed = input.doc_type + " revision"
	old_value = ""
	new_value = input.doc_type + " v" + newVer + " " + input.title
	changed_by = zoho.loginuserid
	changed_on = zoho.currenttime
];

Asset_Contract — On Successful form submission

Same current-flag pattern for contracts. The lookup field on Asset_Contract is named assets. Turns prior contracts' is_current off, stamps their superseded_by with this contract's ID, and sets this row current with the next version.

// Asset_Contract - On Success (add)
priorMax = 0;
isFirst = true;
for each  pc in Asset_Contract[assets == input.assets && ID != input.ID] sort by version desc
{
	if(isFirst)
	{
		priorMax = ifnull(pc.version,0);
		isFirst = false;
	}
	if(pc.is_current == true)
	{
		pc.is_current = false;
		pc.superseded_by = input.ID.toString();
	}
}
input.version = priorMax + 1;
input.is_current = true;

Stock Movements — On Successful form submission

Fires applyTransfer only for Transfer rows, so the existing Receipt/Issue/Return/Adjustment behaviour is untouched. All balance maths, the destination find-or-create, and the two audit legs live inside applyTransfer.

// Stock Movements - On Success (add)
if(input.movement_type == "Transfer")
{
	thisapp.functions.applyTransfer(input.ID.toString());
}

Work Orders — Acknowledge button (on click)

Who can do it: only the supervisor mapped to the ticket's site. getSiteForUser(email) returns that supervisor's SITE id (0 if the login is not a supervisor), and the button refuses unless it equals input.SITE. Only an Open or Draft ticket can be acknowledged, and only once. Structured with else-if (no bare return) so it is valid in a form button block.

// Work Orders - Acknowledge button on click
loginEmail = zoho.loginuserid;
allowedSite = thisapp.functions.getSiteForUser(loginEmail);
okStatus = input.status == "Open" || input.status == "Draft";
isSup = allowedSite != 0 && input.SITE == allowedSite;
if(input.acknowledged_on != null)
{
	alert "This ticket is already acknowledged.";
}
else if(!okStatus)
{
	alert "Only an Open or Draft ticket can be acknowledged.";
}
else if(!isSup)
{
	alert "Only the supervisor of this site can acknowledge this ticket.";
}
else
{
	input.acknowledged_by = loginEmail;
	input.acknowledged_on = zoho.currenttime;
	insert into audit_log
	[
		SITE = input.SITE
		reference_type = "Work order"
		reference_id = input.work_order_number
		action_1 = "Status change"
		field_changed = "acknowledged"
		old_value = ""
		new_value = "Acknowledged by " + loginEmail
		changed_by = loginEmail
		changed_on = zoho.currenttime
	];
}

Work Orders — On Validate

Enforces the acknowledge-before-assign gate. A ticket cannot move to Assigned until acknowledged_on is stamped. cancel submit blocks the save and shows the alert.

// Work Orders - On Validate
if(input.status == "Assigned" && input.acknowledged_on == null)
{
	alert "Acknowledge the ticket before assigning it to a technician.";
	cancel submit;
}

nextDocVersion

Returns the next version number (highest existing + 1, or 1 if none) for a given asset and document type. Called from Asset_Document On Load to preview the version. Standalone function, return type int, args: string assetId, string docType.

int nextDocVersion(string assetId, string docType)
{
	nextVer = 1;
	// newest revision first; break after the first row makes this bounded (no while loop)
	docs = Asset_Document[asset == assetId.toLong() && doc_type == docType] sort by version desc;
	for each  d in docs
	{
		nextVer = ifnull(d.version,0) + 1;
		break;
	}
	return nextVer;
}

applyTransfer

Executes an inter-site stock Transfer: decrements the source-site part balance, increments (or creates) the destination-site part record, writes balance_after/signed_quantity on the movement, and logs both legs to Audit Log. Standalone function, return type void, arg: string movementId. Called from Stock Movements On Success when movement_type is Transfer.

void applyTransfer(string movementId)
{
	mvList = stock_movements[ID == movementId.toLong()];
	for each  mv in mvList
	{
		// guard: only act on Transfer rows with a quantity and both sites set
		qty = ifnull(mv.quantity,0.0);
		fromSite = mv.from_site;
		toSite = mv.to_site;
		if(mv.movement_type == "Transfer" && qty > 0 && fromSite != null && toSite != null)
		{
			// resolve the source part (mv.part holds the parts_master ID)
			partCode = "";
			partName = "";
			uom = "";
			srcBalAfter = 0.0;
			for each  sp in parts_master[ID == mv.part]
			{
				partCode = sp.part_code;
				partName = sp.part_name;
				uom = sp.unit_of_measure;
				srcBalAfter = ifnull(sp.current_balance,0.0) - qty;
				sp.current_balance = srcBalAfter;
				break;
			}
			// find or create the destination-site part with the same code
			destBalAfter = qty;
			destFound = false;
			for each  dp in parts_master[part_code == partCode && SITE == toSite]
			{
				destFound = true;
				destBalAfter = ifnull(dp.current_balance,0.0) + qty;
				dp.current_balance = destBalAfter;
				break;
			}
			if(!destFound)
			{
				newDestId = insert into parts_master
				[
					SITE = toSite
					part_code = partCode
					part_name = partName
					unit_of_measure = uom
					current_balance = qty
					active = true
				];
			}
			// stamp the movement (source leg)
			mv.signed_quantity = -qty;
			mv.balance_after = srcBalAfter;
			// audit the out leg
			insert into audit_log
			[
				SITE = fromSite
				reference_type = "Stock movement"
				reference_id = movementId
				action_1 = "Updated"
				field_changed = "current_balance transfer out"
				old_value = (srcBalAfter + qty).toString()
				new_value = srcBalAfter.toString()
				changed_by = zoho.loginuserid
				changed_on = zoho.currenttime
			];
			// audit the in leg
			insert into audit_log
			[
				SITE = toSite
				reference_type = "Stock movement"
				reference_id = movementId
				action_1 = "Updated"
				field_changed = "current_balance transfer in"
				old_value = (destBalAfter - qty).toString()
				new_value = destBalAfter.toString()
				changed_by = zoho.loginuserid
				changed_on = zoho.currenttime
			];
		}
		break;
	}
}

Set asset ownership

1. Open Assets and click Add.
2. On Load defaults ownership to Client-supplied.
3. Change it to Leased for a rented chiller and Save.
4. The stored asset now carries ownership = Leased and reports/filters can group by owner.

Blocked duplicate Asset ID

1. At King Fahad site an asset HVAC-CH-01 already exists.
2. A junior adds a new asset, types asset_id = HVAC-CH-01, same SITE.
3. On Validate loops assets[asset_id == input.asset_id && SITE == input.SITE], finds a different ID, sets dupFound = true and breaks.
4. cancel submit fires; the alert asks for a unique Asset ID and the record is not saved.

Upload a new O&M revision that supersedes the old

1. Chiller CH-01 already has Asset_Document O&M Manual v1 with is_current = true.
2. From the asset, click Add Document; On Load stamps uploaded_by/on and previews version 2 via nextDocVersion.
3. Pick doc_type O&M Manual, attach the revised PDF, add revision_notes, Save.
4. On Success sets v1 is_current = false, stamps this row version = 2 and is_current = true, and writes a Created row to Audit Log. The asset now shows exactly one current manual.

Inter-site transfer moves balance

1. Store issues a Transfer of 10 filters from Site A to Site B: movement_type = Transfer, part = FLT-100, quantity = 10, from_site = A, to_site = B.
2. On Success calls applyTransfer(input.ID).
3. Source parts_master (FLT-100 at A) drops by 10; the movement records signed_quantity = -10 and balance_after = the new A balance.
4. Destination FLT-100 at B is found and raised by 10 (or created with balance 10 if B had none). Two Audit Log rows record the out and in legs.

Acknowledge a ticket

1. A Corrective ticket is raised (status Open) at Site A.
2. The Site A supervisor opens it and clicks Acknowledge.
3. getSiteForUser returns Site A which equals input.SITE, so the guard passes; acknowledged_by and acknowledged_on are stamped and an audit row is written.
4. When the coordinator later sets status = Assigned, On Validate sees acknowledged_on is filled and allows the save. Trying to assign an un-acknowledged ticket is blocked.