Overview
What was this project?
A solar EPC (Engineering, Procurement & Construction) company was managing their entire sales and project pipeline through disconnected spreadsheets, manual emails, and a patchwork of standalone tools. As projects grew in complexity — with multiple teams (BD, Design, Procurement, Finance, Construction) each owning a different phase — data was getting lost, timelines were slipping, and there was no single place anyone could look to understand where a project actually stood.
They needed a fully custom application — not an off-the-shelf CRM — because their process was too specific. The system had to handle everything: lead capture → site survey → BOM design → vendor procurement → quotation → invoicing → project planning → construction handover.
I built this as a 15-module Zoho Creator application using Deluge for backend logic, JavaScript widgets for complex UI interactions, and custom HTML/CSS for reports and PDF generation.
💡
The company name is kept confidential. All business context, modules, and logic described here are real and based on the actual implementation.
System Architecture
15-Module Process Flow
Each module represents a stage in the project lifecycle. A record progresses linearly through these stages, with approval gates, revision loops, and automated notifications at every step.
Lead Generation
BD team captures project lead → BD Head approves or rejects → unique Lead ID (LD-001 format) assigned
Site Visit (Pre-Sales)
Field team visits site, captures GPS location, conducts survey — auto-linked to lead record
Design (Pre-Sales)
Design team builds BOM (Bill of Materials) based on plant type — auto-populated line items per plant type selection
Costing & Estimation
Procurement fills pricing and GST on the shared BOM widget → Admin approval gate → costing locked
Quotation
Multiple quotation variants per lead → PDF generation → client sends CPO → all other variants locked
Proforma Invoice
Finance creates split invoices against quotation amount — balance auto-calculated per invoice
Mark Payment
Installment-based payment tracking per invoice — delete & auto-recalculate on error correction
Project Planning
Multi-team planning (Design + Procurement + Construction) with planned vs actual timelines via custom widget
Site Visit (Post-Sales)
Post-order site revisit — pre-sales data auto-filled, changes isolated to post-sales only
Design (Post-Sales)
Post-order design updates — BOM changes propagate to project planning, isolated from pre-sales BOM
Purchase Requisition
Side-by-side comparison of pre-sales vs post-sales BOM for procurement visibility
Vendor Invitation (RFQ)
Bulk-select BOM line items → auto-generate vendor invitation emails with item details
Vendor Quotes
Up to 5 rate entries per vendor per item — circular override with modulo logic after limit
Vendor Quote Comparison
Widget-based comparison — auto-select minimum rate vendor or manual selection → Admin approval → Purchase Requisition updated
Final Billing
Extra cost invoicing post-construction — same installment + payment tracking as Proforma, separate from quotation
The Problems
Core technical challenges
This project had four distinct areas of extreme complexity — each requiring custom logic well beyond what Zoho Creator handles out of the box.
📋
BOM at Scale
500–700 line items per project, with complex grouping, sorting, and deduplication logic across multiple plant types. Standard subform handling would hit Zoho's code limits.
🔄
Pre vs Post Sales Isolation
Same BOM data needed to appear in both pre-sales and post-sales modules — but changes in post-sales must never overwrite pre-sales records.
🧾
Split Invoice & Payment Tracking
Invoices split across multiple installments, with auto-recalculating balances. Deleting any payment mid-chain had to cascade correctly across all remaining records.
🏭
Multi-Team Shared Module
Design and Costing teams used the same BOM form simultaneously — with different field visibility, different button states, and strict sequential control over who fills what.
Challenge 1 — BOM
Solving BOM at scale
The Problem
A solar EPC project can have 500–700 BOM line items spread across multiple plant types (e.g. RCC, Metal Sheet). Items needed to be grouped by component category, sorted by WBS code within each group, deduplicated across plant types, and the entire order needed to be user-rearrangeable. On top of this, recording change history on a 500-row subform would require 500×500 = 250,000 loop iterations — which crashes Zoho's 10,000-line code limit instantly.
Solution: Custom sort field + bidirectional plant type tracking
Instead of sorting in code, I created a computed field called Sort_WBSCode that concatenates the user-assigned S.No (group order) with the numeric suffix of the WBS code. This turns the sort problem into a simple subform column sort — which Zoho handles natively with zero code.
Deluge — Sort field logic
// S.No = 1, WBS Code = "SIPL.ABC.01"
// Extract last 2 digits of WBS: "01"
// Concatenate: 1 + 01 = "101"
wbs_suffix = right(input.WBS_Code, 2);
sort_val = input.S_No.toString() + wbs_suffix;
input.Sort_wbscode = sort_val.toLong();
// Result: rows sort as 101, 102, 201, 202...
// Groups stay together, items sorted within group
For deduplication across plant types — where two plant types like RCC and Metal Sheet both share a "Module" component — I stored all plant type names in a backend text field per BOM line. When a plant type is removed or added, the code filters and re-renders only the affected rows instead of rebuilding the entire BOM.
Solution for Subform History
Instead of comparing 500 rows against 500 historical rows, I created a dedicated blank subform that acts as a real-time event log. On every field input, the field name, form name, record ID, and new value are written to this subform. History comparison now requires only one loop through this log — completely bypassing the O(n²) problem.
BOM Field: No Script Run — preventing infinite loops
A critical bug: when a script removes a component from the "Select Component" multi-select field, Zoho auto-triggers the user input handler for that field — which re-runs the BOM refresh, creating an infinite loop.
I solved this with a boolean flag field No_Script_Run. Whenever the script programmatically changes the component field, it sets this flag to true first. The input handler checks this flag at entry — if true, it skips all BOM code and resets the flag to false. The next genuine user input runs normally.
Deluge — Loop prevention flag
// In the script that modifies Select Component programmatically:
input.No_Script_Run = true;
input.Select_Component = updated_components;
// In the user input handler for Select_Component:
if(input.No_Script_Run == true)
{
input.No_Script_Run = false;
return; // skip BOM refresh — triggered by script, not user
}
// else: user actually changed the field — run BOM logic
refreshBOM(input);
Challenge 2 — Data Isolation
Pre-sales vs post-sales data separation
The Problem
After a client places a purchase order, the project enters post-sales. The same BOM, site visit data, and design data from pre-sales needed to appear in post-sales modules — but any changes made in post-sales (site re-survey, updated BOM, revised design) must never touch the original pre-sales records. Both versions needed to be visible, comparable, and independently editable.
1
Normal fields — direct copy on handover
When a record moves from Project Planning to Site Visit Post-Sales, all regular field values from pre-sales are copied directly into the post-sales record fields. Since they're now separate records, edits to post-sales fields never affect pre-sales data.
2
Subform rows — bidirectional lookup without duplication
For subform rows (which can have hundreds of entries), copying them would be wasteful. Instead, I created a bidirectional lookup between both the pre-sales and post-sales parent forms and the shared subform. Each subform row stores IDs for both parents. The same physical row displays in both modules — zero duplication.
3
On post-sales edit — smart row splitting
When a user edits a row in the post-sales module, validation checks whether this row has been modified. If yes, a brand-new post-sales-only row is created with the updated values, tagged as "post-sales." The original row continues to serve the pre-sales view unchanged. Only rows that actually differ between pre and post sales exist as separate records — identical rows are shared.
Result
Pre-sales data is permanently preserved for audit and analytics. Post-sales teams get full edit freedom. Both views stay in sync on shared data. The Purchase Requisition module (Module 11) uses this setup to show a clean side-by-side comparison of what was designed pre-sale vs what was actually ordered post-sale.
Challenge 3 — Billing
Split invoicing & installment payments
The Problem
A single project quotation (e.g. ₹10,00,000) needs to be invoiced in parts — sometimes 4 or 5 separate proforma invoices. Each invoice is then paid in installments. If any installment is entered incorrectly and deleted, every other installment under that invoice must auto-recalculate. The system also needed to prevent over-invoicing: total invoices must never exceed the quotation amount.
Proforma Invoice → Payment Flow
₹1,00,000
Quotation Amount
↓
↓ each invoice accepts multiple payments
Balance Amount field — the key to auto-recalculation
Every invoice record carries a Balance_Amount field. When a new invoice is created, this field stores the remaining payable amount (Quotation Amount minus all previously raised invoices). This makes over-invoicing impossible — the system validates against this field before saving.
For payments: when a payment record is deleted, a Deluge workflow fires on the delete event, fetches all remaining payment records for that invoice, sums them up, and recalculates installment numbers and running balances in a single loop — keeping the chain consistent.
Final Billing — post-construction extras
Module 15 uses the same invoice + payment architecture, but completely independent of the original quotation. Any additional costs that arise during construction (scope additions, material changes, site complications) are billed here — with their own invoice series and payment tracking — without touching the original pre-sales financials.
Challenge 4 — Procurement
Vendor RFQ & quote comparison
The Problem
With 500+ BOM line items, the procurement team needed to send RFQ invitations to multiple vendors per item, collect up to 5 rate quotes per vendor, compare them visually, and select the best rate — either automatically (minimum rate) or manually. After selection, an admin approval gate had to push the approved rates back into the Purchase Requisition and hide the approved items from the RFQ report so they're never double-processed.
1
Bulk RFQ generation from BOM report
I created a duplicate report of the post-sales BOM where each line item appears as a separate selectable record. The team uses a bulk action button to select multiple items and trigger the RFQ form — all selected items auto-populate into the new RFQ form. Selected vendors receive automated email notifications with the item list in the body.
2
5-rate circular override in Vendor Quotes
Each vendor quote record tracks an Edit_Record_Count field starting at 1. First edit fills Rate 1, second edit fills Rate 2, and so on up to Rate 5. After 5, the system applies modulo arithmetic: edit count 6 → (6 % 5 = 1) → overwrites Rate 1 again. This gives procurement a simple, bounded history of vendor negotiations without unbounded record growth.
Deluge — Circular rate override logic
edit_count = input.Edit_Recode_Count;
rate_slot = edit_count % 5;
if(rate_slot == 0) { rate_slot = 5; } // mod 5 gives 0 on 5th, 10th...
if(rate_slot == 1) { input.Rate_1 = new_rate; }
else if(rate_slot == 2) { input.Rate_2 = new_rate; }
// ... and so on
input.Edit_Recode_Count = edit_count + 1;
3
Widget-based comparison & approval flow
The Vendor Quote Comparison module is built as a custom JavaScript widget — giving the team a spreadsheet-style view of all vendors side by side per line item. They can click "Auto-select minimum rate" or manually tick their preferred vendor. On submission, the record goes for admin approval. Once approved, the final rate updates the Purchase Requisition and the RFQ_Generated flag hides that line item from the open RFQ report — preventing duplicate invitations.
System Features
Cross-cutting system features
Automated reminder notifications
Every module has an automated email reminder system. If a record is pending in any module for more than 2 days without action, a reminder email is sent — and continues every 2 days until the record moves forward. Each email includes a running count of how many reminders have been sent for that record. Three Deluge fields manage this: Next_Trigger (date of next email), Count (reminder counter), and Stop (boolean flag that kills the loop when the record advances).
Full audit trail — edit history & status history
Every action in the system — record status changes, field edits, approvals, rejections — is logged with the timestamp, user email, module name, and the value before and after the change. This is stored in a dedicated Status History subform per module. Managers can pull the complete timeline of any lead record at any point.
Super admin override with controlled propagation
The super admin can edit any record at any stage without restriction. However, those changes don't propagate automatically — they only become visible downstream after the relevant module team re-submits the record to the next step. This prevents accidental mid-process disruptions while still giving the admin full correction capability.
Current status dashboard
A Current_Status field in the Lead Generation module is updated every time a record moves to a new module (e.g. "Design Team", "Procurement", "Finance"). Combined with month/year fields, this powers a live dashboard showing where every active project stands across the pipeline — without any manual reporting.
Outcome
What this system delivered
15
Custom modules built end-to-end
0
Manual cross-team data handoffs
100%
Audit trail on every record action
500+
BOM line items handled per project
5
Teams unified on one platform
∞
Invoice splits with auto-balance
Key Outcome
What previously required 5 separate teams working across spreadsheets, emails, and phone calls is now a single traceable system. Every lead, every BOM change, every vendor quote, every rupee invoiced and collected — all in one place, with a full history of who did what and when.