Skip to content

Approval Workflow Documentation

This document serves as the live record for the Pre-Sales Order Approval Workflow. It only includes components, schemas, and logic that have been officially implemented. It will be updated progressively as new features are added.

Table of Contents

  1. High-Level Business Flow & Lineage
  2. Database Schema (Implemented Tables)
  3. ToolJet Data Queries
  4. Data & Filtering Flow Architecture
  5. UI Component Configuration & Maintenance
  6. Address Management Flow
  7. Pending Items Resolution Flow & State Management
  8. Document Upload Architecture & Troubleshooting
  9. 8.1 The Upload Pipeline (How it works)
  10. 8.2 Troubleshooting & Adding New Upload Types
  11. 8.3 Advanced Unified Client License Upload Flow (Dynamic Modal Architecture)
  12. Advanced Debugging & Development Patterns
  13. Flowable API Integration (Task Completion)

1. High-Level Business Flow & Lineage

Before diving into the code, it is critical to understand the real-world business process this ToolJet application supports. This workflow manages the "Approval Hold" phase of Pre-Sales orders.

What happens after an order is created?

  1. Order Creation: A sales agent creates an order in the main ERP system. The order lands in the tbl_order_Details table with a status of ORDER_CREATED.
  2. The Approval Hold: Orders often cannot be instantly processed because they are missing critical compliance documents (e.g., a Manufacturing Agreement, Drug License, Food License, or Client Payment). Instead of just putting the order "On Hold" generally, the system inserts specific missing items into tbl_PRE_SALES_ORDER_APPROVAL_ITEMS.
  3. The ToolJet Dashboard (This App): Approvers and admins log into this ToolJet app. The main table (orderApprv_get_orders) shows them exactly which orders are waiting, and critically, what they are waiting for (the PENDING_ITEMS column).
  4. Resolution (Document Upload): The admin clicks on an order and uses the Right-Side Panel to resolve the holds. For example, if the Agreement is missing, they switch to the "Agreement" tab. They fill in the Agreement Date, Expiry Date, and upload the physical PDF/Image.
  5. Lineage of a Document Upload:
  6. The file is uploaded to the backend via REST API (orderApprv_upload_document) -> Saved to Google Cloud Storage.
  7. The metadata is saved in a specific table (e.g., tbl_mfg_agreements or vw_client_compliance_licenses).
  8. The missing requirement in tbl_PRE_SALES_ORDER_APPROVAL_ITEMS is marked with a RESOLVED_TS timestamp.
  9. Final Release: Once all pending items for an order have a RESOLVED_TS, the order is fully approved and released to the manufacturing floor.
flowchart TD
    A[Order Created in ERP] --> B{Missing Compliance Docs?}
    B -- Yes --> C[Insert Pending Items into tbl_PRE_SALES_ORDER_APPROVAL_ITEMS]
    C --> D[Order appears in ToolJet Dashboard with 'Pending Items' count]
    D --> E[Admin opens order and uploads missing docs]
    E --> F[API Uploads to GCS & Saves Metadata]
    F --> G[Mark Pending Item with RESOLVED_TS]
    G --> H{All Items Resolved?}
    H -- No --> D
    H -- Yes --> I[Order Fully Approved]
    B -- No --> I
    I --> J[Release to Manufacturing Floor]

This decoupled architecture ensures we have a strict audit trail of why an order was held, who resolved it, and what document was provided as proof.

2. Database Schema (Implemented Tables)

erDiagram
    tbl_order_Details ||--o{ tbl_PRE_SALES_ORDER_APPROVAL_ITEMS : "ORDER_NO_C (1:N)"
    tbl_order_Details }o--|| TBL_CLIENT : "CLIENT_ID_I (Billing)"
    tbl_order_Details }o--o| TBL_CLIENT : "MKT_CLIENT_ID_I (Marketing)"

    TBL_CLIENT ||--o{ tbl_mfg_agreements : "BILLING_CLIENT_ID_I / MARKETING_CLIENT_ID_C"

    vw_client_compliance_licenses ||--|| TBL_CLIENT : "CLIENT_ID_I"

    tbl_PRE_SALES_ORDER_APPROVAL_ITEMS {
        UUID ID PK
        VARCHAR ORDER_NO_C FK
        VARCHAR ITEM_NAME_C
        TIMESTAMPTZ RESOLVED_TS
    }

    tbl_mfg_agreements {
        UUID ID PK
        VARCHAR BILLING_CLIENT_ID_I FK
        VARCHAR MARKETING_CLIENT_ID_C FK
        VARCHAR ORGANIZATION_C
        DATE EXPIRY_DATE_D
    }

2.1 Pending Approval Items (tbl_PRE_SALES_ORDER_APPROVAL_ITEMS)

Purpose: This table tracks the specific missing documents, licenses, or requirements (e.g., DL, FL, GST, Client Payment) that are currently holding up an order's approval. Workflow Logic: Instead of relying on a single "Hold Reason" dropdown in the main orders table, this table allows multiple items to be marked as pending simultaneously for a single order. When an item is resolved (e.g., the DL is uploaded), the RESOLVED_TS is stamped, providing a full audit trail of what was missing and when it was fixed.

CREATE TABLE SAAR_BIOTECH.tbl_PRE_SALES_ORDER_APPROVAL_ITEMS (
    "ID" UUID PRIMARY KEY DEFAULT UUIDV7(),
    "ORDER_NO_C" VARCHAR(255) NOT NULL,
    "ITEM_NAME_C" VARCHAR(100) NOT NULL,
    "CREATED_BY" VARCHAR(255),
    "CREATED_TS" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    "RESOLVED_BY" VARCHAR(255),
    "RESOLVED_TS" TIMESTAMPTZ,
    CONSTRAINT UQ_PRE_SALES_ORDER_APPROVAL_ITEM UNIQUE ("ORDER_NO_C", "ITEM_NAME_C")
);

CREATE INDEX IDX_PRE_SALES_ORDER_APPROVAL_ITEMS_ORDER ON SAAR_BIOTECH.tbl_PRE_SALES_ORDER_APPROVAL_ITEMS (ORDER_NO_C);
CREATE INDEX IDX_PRE_SALES_ORDER_APPROVAL_ITEMS_PENDING ON SAAR_BIOTECH.tbl_PRE_SALES_ORDER_APPROVAL_ITEMS (ORDER_NO_C) WHERE RESOLVED_TS IS NULL;

2.2 Agreements (tbl_mfg_agreements)

Purpose: This table acts as the master record for unique agreement contracts. It bridges the three core entities: Billing Client, Marketing Client, and Manufacturing Organization. Workflow Logic: By extracting agreements into this table, we avoid duplicate uploads. When a new order is placed, the system checks this table for an active agreement matching the client.

CREATE TABLE SAAR_BIOTECH.tbl_mfg_agreements (
    "ID" UUID PRIMARY KEY DEFAULT UUIDV7(),

    -- Physical Document Tracking
    "FILE_NO_C" VARCHAR(255) NOT NULL,
    "SERIAL_NO_C" VARCHAR(255) NOT NULL UNIQUE,

    -- The Core 3-Way Relationship
    "BILLING_CLIENT_ID_I" VARCHAR(255) NOT NULL, 
    "MARKETING_CLIENT_ID_C" VARCHAR(255) NOT NULL,
    "ORGANIZATION_C" VARCHAR(255) NOT NULL,

    -- Validity Dates
    "ISSUE_DATE_D" DATE NOT NULL,
    "EXPIRY_DATE_D" DATE, 

    -- Status and Tracking
    "CREATED_BY" VARCHAR(255),
    "CREATED_TS" TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    "UPDATED_BY" VARCHAR(255),
    "UPDATED_TS" TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Prevents overlapping agreements for the exact same combination
CREATE UNIQUE INDEX IDX_ACTIVE_AGREEMENT 
ON SAAR_BIOTECH.tbl_mfg_agreements ("BILLING_CLIENT_ID_I", "MARKETING_CLIENT_ID_C", "ORGANIZATION_C");

-- Secondary indexes for faster UI search and filtering
CREATE INDEX IDX_AGREEMENTS_FILE_NO ON SAAR_BIOTECH.tbl_mfg_agreements ("FILE_NO_C");
CREATE INDEX IDX_AGREEMENTS_MARKETING_CLIENT ON SAAR_BIOTECH.tbl_mfg_agreements ("MARKETING_CLIENT_ID_C");
-- (Note: SERIAL_NO_C automatically gets an index because it is marked UNIQUE in the table definition)

2.3 Client Compliance Licenses View (vw_client_compliance_licenses)

Purpose: This view pivots the active client documents (GST, DL, FL) from the vw_active_documents module into a single, flat row per client. Workflow Logic: Instead of writing complex JOINs in ToolJet, the UI can execute a simple SELECT * FROM vw_client_compliance_licenses WHERE "CLIENT_ID_I" = 'XYZ' to instantly get all preview URLs and expiry dates for a client.

CREATE OR REPLACE VIEW SAAR_BIOTECH.vw_client_compliance_licenses AS
SELECT 
    c.entity_id_c AS "CLIENT_ID_I",

    -- GST Information
    MAX(CASE WHEN c.document_type_c = 'GST_CERTIFICATE' THEN c.document_id::TEXT END)::UUID AS "GST_DOCUMENT_ID",
    MAX(CASE WHEN c.document_type_c = 'GST_CERTIFICATE' THEN c.preview_url END) AS "GST_PREVIEW_URL",
    MAX(CASE WHEN c.document_type_c = 'GST_CERTIFICATE' THEN c.issued_d::TEXT END)::DATE AS "GST_ISSUE_D",

    -- Drug License (DL) Information
    MAX(CASE WHEN c.document_type_c = 'DRUG_LICENSE' THEN c.document_id::TEXT END)::UUID AS "DL_DOCUMENT_ID",
    MAX(CASE WHEN c.document_type_c = 'DRUG_LICENSE' THEN c.preview_url END) AS "DL_PREVIEW_URL",
    MAX(CASE WHEN c.document_type_c = 'DRUG_LICENSE' THEN c.issued_d::TEXT END)::DATE AS "DL_ISSUE_D",
    MAX(CASE WHEN c.document_type_c = 'DRUG_LICENSE' THEN c.expiry_d::TEXT END)::DATE AS "DL_EXPIRY_D",

    -- Food License (FL) Information
    MAX(CASE WHEN c.document_type_c = 'FOOD_LICENSE' THEN c.document_id::TEXT END)::UUID AS "FL_DOCUMENT_ID",
    MAX(CASE WHEN c.document_type_c = 'FOOD_LICENSE' THEN c.preview_url END) AS "FL_PREVIEW_URL",
    MAX(CASE WHEN c.document_type_c = 'FOOD_LICENSE' THEN c.issued_d::TEXT END)::DATE AS "FL_ISSUE_D",
    MAX(CASE WHEN c.document_type_c = 'FOOD_LICENSE' THEN c.expiry_d::TEXT END)::DATE AS "FL_EXPIRY_D"

FROM SAAR_BIOTECH.vw_active_documents c
WHERE c.entity_type_c = 'TBL_CLIENT'
GROUP BY c.entity_id_c;

3. ToolJet Data Queries

3.1 orderApprv_get_orders (Master Order List)

Purpose: This is the primary query driving the ToolJet Master Table. It fetches all orders and calculates their real-time dashboard status by joining the tbl_PRE_SALES_ORDER_APPROVAL_ITEMS table. It utilizes backend-enforced security variables to ensure users only see data they are authorized to view.

Security Constraints: - Organization Isolation: Only returns orders matching {{variables.user.ORGANIZATION_C}}. - RBAC Bypass: If the user holds the table:order:view_all permission, the organization check is completely bypassed. - Status Filtering: Normal users only see orders with specific statuses (ORDER_CREATED, ORDER_REVISED, ORDER_REJECTED) and non-HOME_PARTY clients. Users with the table:order:approve_sale_order permission bypass these status checks entirely.

WITH PENDING_ITEMS AS (
    SELECT
        "ORDER_NO_C",
        COUNT(*) FILTER (WHERE "RESOLVED_TS" IS NULL) AS PENDING_COUNT,
        STRING_AGG("ITEM_NAME_C", ', ' ORDER BY "ITEM_NAME_C")
            FILTER (WHERE "RESOLVED_TS" IS NULL) AS PENDING_ITEMS
    FROM tbl_PRE_SALES_ORDER_APPROVAL_ITEMS
    GROUP BY "ORDER_NO_C"
)
SELECT
    O."ORDER_NO_C",
    O."PAYMENT_STATUS",
    COALESCE(O."PAYMENT_AMOUNT_C"::TEXT, '') AS "PAYMENT_AMOUNT_C",
    COALESCE(O."PART_APPROVED_STATUS", '') AS "PART_APPROVED_STATUS",
    COALESCE(O."PART_APPROVED_COMMENTS", '') AS "PART_APPROVED_COMMENTS",
    TO_CHAR(O."PAYMENT_TS", 'DD Mon YYYY, HH12:MI AM') AS "PAYMENT_TS",
    COALESCE(O."PAYMENT_ASSIGN", '') AS "PAYMENT_ASSIGN",
    O."MARKETING_PERSON_C",
    O."CLIENT_ID_I",
    O."ENQ_PERSON_C",
    O."ENQ_EMAIL_C",
    O."ENQ_PHONE_C",
    O."FRESH_OR_REPEAT_B",
    O."BRAND_C",
    O."BRAND_OWNERSHIP",
    O."DOSAGE_FORM_C",
    O."DRUG_TYPE_C",
    O."COMPOSITION_C",
    O."PACKING_DIMENSIONS_C",
    O."PACKING_TYPE_C",
    O."BRAND_VERIF_ASSIGN",
    O."QUANTITY_I",
    O."RATE_I",
    O."MRP_I",
    O."INVENTORY_CHARGES_B",
    O."PLATE_CHARGES_B",
    O."CYLINDER_CHARGES_B",
    O."ORDER_DATE",
    O."RATE_PROVIDER_REF",
    O."O_STATUS_C",
    O."PRE_INT_STATUS",
    O."PRE_INT_TS",
    O."HOLD_REASON_C",
    O."ORGANIZATION_C",
      COALESCE( O."MKT_CLIENT_ID_I"::TEXT, '') AS "MKT_CLIENT_ID_I",

    C."CLIENT_NAME_C", -- Note: CLIENT_ID_I and CLIENT_NAME_C refer to the Billing Client
    COALESCE(MC."CLIENT_NAME_C", '') AS "MKT_CLIENT_NAME_C",
    PR."PRODUCT_NAME_C",

    COALESCE(P.PENDING_COUNT, 0) AS "PENDING_COUNT",
    COALESCE(P.PENDING_ITEMS, '') AS "PENDING_ITEMS"
FROM tbl_order_Details O
LEFT JOIN PENDING_ITEMS P ON P."ORDER_NO_C" = O."ORDER_NO_C"
LEFT JOIN TBL_CLIENT C ON O."CLIENT_ID_I" = C."CLIENT_ID_I"
LEFT JOIN TBL_CLIENT MC ON O."MKT_CLIENT_ID_I" = MC."CLIENT_ID_I"
LEFT JOIN tbl_product PR ON O."COMPOSITION_C" = PR."PRODUCT_ID_I"
WHERE (
        '{{variables.user.permissions["table:order:view_all"]}}' = 'true'
        OR O."ORGANIZATION_C" = '{{variables.user.ORGANIZATION_C}}'
    )
    AND O."PAYMENT_STATUS" IS NOT NULL
    AND O."PAYMENT_STATUS" <> 'NS'
    AND (C."CLIENT_TYPE_C" IS NULL OR C."CLIENT_TYPE_C" <> 'HOME_PARTY')
    AND (
        O."O_STATUS_C" IN ('ORDER_CREATED', 'ORDER_REVISED', 'ORDER_REJECTED')
        OR '{{variables.user.permissions["table:order:approve_sale_order"]}}' = 'true'
    )
ORDER BY O."PAYMENT_TS" DESC, O."PRE_INT_TS" DESC;

3.2 orderApprv_get_licenses (Detail Panel - Client Licenses)

Purpose: Fetches the aggregated compliance rows for both the Billing and Marketing clients for the selected order from the vw_client_compliance_licenses view. It includes a dynamic CLIENT_ROLE column to differentiate between the two clients. This populates the "Client Licenses" tab when an admin clicks on a specific order.

ToolJet Query Execution: Runs when a row in tblOrders is clicked.

SELECT 
    *, 'Billing' AS "CLIENT_ROLE"
FROM SAAR_BIOTECH.vw_client_compliance_licenses 
WHERE "CLIENT_ID_I" = '{{components.tblOrders.selectedRow.CLIENT_ID_I}}'

UNION ALL

SELECT 
    *, 'Marketing' AS "CLIENT_ROLE"
FROM SAAR_BIOTECH.vw_client_compliance_licenses 
WHERE "CLIENT_ID_I" = '{{components.tblOrders.selectedRow.MKT_CLIENT_ID_I}}'
  AND '{{components.tblOrders.selectedRow.MKT_CLIENT_ID_I}}' IS NOT NULL;

3.3 orderApprv_get_agreements (Detail Panel - Agreements)

Purpose: Fetches the active agreements that match the exact Billing Client, Marketing Client, and Organization triad for the selected order. Joins vw_active_documents to also return the preview URL of the uploaded PDF.

ToolJet Query Execution: Runs when a row in tblOrders is clicked.

SELECT 
    A.*, 
    D.document_id,
    D.display_name_c AS doc_name,
    D.preview_url,
    D.expiry_d
FROM SAAR_BIOTECH.tbl_mfg_agreements A
LEFT JOIN SAAR_BIOTECH.vw_active_documents D
    ON D.entity_id_c = A."ID"::TEXT   
   AND D.entity_type_c = 'MFG_AGREEMENT'    
WHERE A."BILLING_CLIENT_ID_I" = '{{components.tblOrders.selectedRow.CLIENT_ID_I}}'
  AND A."MARKETING_CLIENT_ID_C" = '{{components.tblOrders.selectedRow.MKT_CLIENT_ID_I}}'
  AND A."ORGANIZATION_C" = '{{components.tblOrders.selectedRow.ORGANIZATION_C}}';

3.4 orderApprv_get_pending_items (Detail Panel - Pending Items)

Purpose: Fetches the detailed history of compliance requirements missing for the selected order. Displays exactly what is missing, who marked it, and whether it has been resolved.

ToolJet Query Execution: Runs when a row in tblOrders is clicked to populate the pending items details table.

SELECT 
    "ID",
    "ITEM_NAME_C" AS "Pending Requirement",
    "EXPECTED_DATE_D" AS "Due Date",
    "CREATED_BY" AS "Added By",
    TO_CHAR("CREATED_TS", 'DD Mon YYYY, HH12:MI AM') AS "Added On",
    "RESOLVED_BY" AS "Resolved By",
    TO_CHAR("RESOLVED_TS", 'DD Mon YYYY, HH12:MI AM') AS "Resolved On",
    CASE 
        WHEN "RESOLVED_TS" IS NULL THEN 'Pending'
        ELSE 'Resolved'
    END AS "Status"
FROM SAAR_BIOTECH.tbl_PRE_SALES_ORDER_APPROVAL_ITEMS
WHERE "ORDER_NO_C" = '{{components.yourOrdersTable.selectedRow.ORDER_NO_C}}'
ORDER BY "Status" ASC, "CREATED_TS" DESC;

3.4 orderApprv_insert_agreement (Create / Update Agreement Record)

Purpose: Inserts a new agreement record into tbl_mfg_agreements. If an agreement for the same BILLING_CLIENT_ID_I + MARKETING_CLIENT_ID_C + ORGANIZATION_C combination already exists, it updates the metadata instead (upsert pattern). Returns the full row using RETURNING * so the upload step can immediately reference the generated ID.

ToolJet Query Execution: Triggered by the "Submit" button inside the modalUploadAgreement modal.

JSON Form Component: form_agreement (Raw JSON mode). Field values accessed as components.form_agreement.data.FIELD_NAME.value.

Date Null Handling: ToolJet sends either "" (empty string) or "null" (literal string) when a date picker is empty. Use NULLIF(NULLIF(value, ''), 'null')::DATE to safely convert these to proper SQL NULL.

INSERT INTO SAAR_BIOTECH.tbl_mfg_agreements (
    "FILE_NO_C", 
    "SERIAL_NO_C", 
    "ISSUE_DATE_D", 
    "EXPIRY_DATE_D", 
    "BILLING_CLIENT_ID_I", 
    "MARKETING_CLIENT_ID_C",
    "ORGANIZATION_C",
    "CREATED_BY",
    "UPDATED_BY"
) VALUES (
    '{{components.form_agreement.data.FILE_NO_C.value}}', 
    '{{components.form_agreement.data.SERIAL_NO_C.value}}',
    '{{components.form_agreement.data.ISSUE_DATE_D11.value}}', 
    NULLIF(NULLIF('{{components.form_agreement.data.EXPIRY_DATE_D1.value}}', ''), 'null')::DATE,
    '{{components.tblOrders.selectedRow.CLIENT_ID_I}}',
    '{{components.tblOrders.selectedRow.MKT_CLIENT_ID_I}}',
    '{{components.tblOrders.selectedRow.ORGANIZATION_C}}',
    '{{variables.user.userId}}',
    '{{variables.user.userId}}'
)
ON CONFLICT ("BILLING_CLIENT_ID_I", "MARKETING_CLIENT_ID_C", "ORGANIZATION_C") 
DO UPDATE SET
    "FILE_NO_C" = EXCLUDED."FILE_NO_C",
    "SERIAL_NO_C" = EXCLUDED."SERIAL_NO_C",
    "ISSUE_DATE_D" = EXCLUDED."ISSUE_DATE_D",
    "EXPIRY_DATE_D" = EXCLUDED."EXPIRY_DATE_D",
    "UPDATED_BY" = EXCLUDED."CREATED_BY",
    "UPDATED_TS" = NOW()
RETURNING *;

3.5 orderApprv_upload_document (Global Document Upload - REST API)

Purpose: A single, reusable REST API query that handles ALL document uploads across the app (agreements, GST, DL, FL, etc.). It uses ToolJet parameters so the same query can be called with different values for each document type.

Data Source: infinity-plus-data-source (REST API) Method: POST URL: /api/internal/tooljet/storage/documents/upload Body (Raw JSON): 

{
  "fileName":  "{{parameters.fileName}}",
  "mimeType":  "{{parameters.mimeType}}",
  "base64Data":"{{parameters.base64Data}}",
  "entityId":  "{{parameters.entityId}}",
  "documentType": "{{parameters.documentType}}",
  "issuedDate": "{{parameters.issuedDate}}",
  "expiryDate": "{{parameters.expiryDate}}",
  "customMetadata": {{ JSON.stringify(parameters.customMetadata) }}
}

How to trigger it (On Success of orderApprv_insert_agreement):

In the orderApprv_insert_agreement query → Settings → On Success → Run Query → orderApprv_upload_document, pass these parameter values:

Parameter Value
fileName {{components.filepicker1.file[0].name}}
mimeType {{components.filepicker1.file[0].type}}
base64Data {{components.filepicker1.file[0].base64Data}}
entityId {{queries.orderApprv_insert_agreement.data[0].ID}}
documentType MFG_AGREEMENT
issuedDate {{queries.orderApprv_insert_agreement.data[0].ISSUE_DATE_D}}
expiryDate {{queries.orderApprv_insert_agreement.data[0].EXPIRY_DATE_D}}
customMetadata { "BILLING_CLIENT": "{{components.tblOrders.selectedRow.CLIENT_ID_I}}", "MARKETING_CLIENT": "{{components.tblOrders.selectedRow.MKT_CLIENT_ID_I}}", "ORGANIZATION": "{{components.tblOrders.selectedRow.ORGANIZATION_C}}" }

Crucial Note on customMetadata Formatting: Do NOT wrap the JavaScript object in single quotes (') inside the Event Handler parameters. It must be passed as a raw JavaScript object. The REST query body then uses {{ JSON.stringify(parameters.customMetadata) }} to safely escape it into a valid JSON string before sending it to the backend.

File Picker Note: The file picker component (e.g., filepicker1) must have Parse Content toggled ON in its properties. This is mandatory for ToolJet to encode the file as Base64 (.base64Data) before sending.

On Success of orderApprv_upload_document: Run orderApprv_get_agreements to refresh the agreements table and display the newly uploaded document.

3.4 orderApprv_get_client_details

Purpose: Fetches comprehensive client details (both Billing and Marketing clients) for the currently selected order, including their respective addresses, GST numbers, and Drug License numbers. Uses a self-join on TBL_CLIENT and TBL_CLIENT_ADDRESS.

SELECT 
    UPPER(BC."CLIENT_NAME_C") AS "BILLING_CLIENT_NAME",
    BC."DRUG_LICENSE_NO_C" AS "BILLING_DL_NO",
    BC."GST_NO_C" AS "BILLING_GST_NO",
    BC."BILLING_ADDRESS_C" AS "CLIENT_BILLING_ADDRESS_PREF",

    BCA."ADDESS_ID_C" AS "BILLING_ADDRESS_ID",
    BCA."ADDRESS_TYPE_C" AS "BILLING_ADDRESS_TYPE",
    BCA."C_ADDRESS_DISPLAY" AS "BILLING_ADDRESS_DISPLAY",

    UPPER(MC."CLIENT_NAME_C") AS "MARKETING_CLIENT_NAME",
    MC."DRUG_LICENSE_NO_C" AS "MARKETING_DL_NO",
    MC."GST_NO_C" AS "MARKETING_GST_NO",
    MC."MARKET_BY_ADDRESS_C" AS "CLIENT_MARKETING_ADDRESS_PREF",

    MCA."ADDESS_ID_C" AS "MARKETING_ADDRESS_ID",
    MCA."ADDRESS_TYPE_C" AS "MARKETING_ADDRESS_TYPE",
    MCA."C_ADDRESS_DISPLAY" AS "MARKETING_ADDRESS_DISPLAY"

FROM TBL_CLIENT BC

LEFT JOIN TBL_CLIENT_ADDRESS BCA 
    ON BCA."CLIENT_ID_I" = BC."CLIENT_ID_I" 
   AND BCA."ADDRESS_TYPE_C" = BC."BILLING_ADDRESS_C"

LEFT JOIN TBL_CLIENT MC 
    ON MC."CLIENT_ID_I" = '{{components.tblOrders.selectedRow.MKT_CLIENT_ID_I}}'

LEFT JOIN TBL_CLIENT_ADDRESS MCA 
    ON MCA."CLIENT_ID_I" = MC."CLIENT_ID_I" 
   AND MCA."ADDRESS_TYPE_C" = MC."MARKET_BY_ADDRESS_C"

WHERE BC."CLIENT_ID_I" = '{{components.tblOrders.selectedRow.CLIENT_ID_I}}'

3.5 orderApprv_get_address_details

Purpose: Fetches the full raw record of a specific address from TBL_CLIENT_ADDRESS so it can be populated into an edit modal or form. Trigger: Typically triggered when an admin clicks an "Edit Address" button, passing the target Address ID into a ToolJet query parameter.

SELECT *
FROM TBL_CLIENT_ADDRESS
WHERE "ADDESS_ID_C" = '{{parameters.addressId}}'

3.6 orderApprv_upsert_address

Purpose: Inserts a new address or updates an existing address in tbl_client_address. This query uses Postgres ON CONFLICT to perform an upsert based on the composite unique key ("CLIENT_ID_I", "ADDRESS_TYPE_C") to ensure a client never has duplicate address types. If the address is new, it generates a UUID. Trigger: Triggered when the user submits the form_edit_address form component.

INSERT INTO SAAR_BIOTECH.tbl_client_address (
    "ADDESS_ID_C",
    "CLIENT_ID_I",
    "ADDRESS_TYPE_C",
    "ADDRESS_LINE_1",
    "ADDRESS_LINE_2",
    "ADDRESS_LINE_3",
    "ADDRESS_LINE_4",
    "CITY_C",
    "STATE_C",
    "PIN_CODE_C",
    "COUNTRY_C",
    "CREATED_BY",
    "CREATED_TS",
    "UPDATED_BY",
    "UPDATED_TS"
)
VALUES (
    COALESCE(NULLIF('{{parameters.addressId}}', ''), gen_random_uuid()::text),
    '{{parameters.clientId}}',
    '{{components.form_edit_address.data.address_type.value}}',
    '{{components.form_edit_address.data.address_line_1.value}}',
    '{{components.form_edit_address.data.address_line_2.value}}',
    '{{components.form_edit_address.data.address_line_3.value}}',
    '{{components.form_edit_address.data.address_line_4.value}}',
    '{{components.form_edit_address.data.city.value}}',
    '{{components.form_edit_address.data.state.value}}',
    '{{components.form_edit_address.data.pin_code.value}}',
    '{{components.form_edit_address.data.country.value}}',
    '{{variables.userId}}',
    NOW(),
    '{{variables.userId}}',
    NOW()
)
ON CONFLICT ("CLIENT_ID_I", "ADDRESS_TYPE_C") 
DO UPDATE SET 
    "ADDRESS_LINE_1" = EXCLUDED."ADDRESS_LINE_1",
    "ADDRESS_LINE_2" = EXCLUDED."ADDRESS_LINE_2",
    "ADDRESS_LINE_3" = EXCLUDED."ADDRESS_LINE_3",
    "ADDRESS_LINE_4" = EXCLUDED."ADDRESS_LINE_4",
    "CITY_C" = EXCLUDED."CITY_C",
    "STATE_C" = EXCLUDED."STATE_C",
    "PIN_CODE_C" = EXCLUDED."PIN_CODE_C",
    "COUNTRY_C" = EXCLUDED."COUNTRY_C",
    "UPDATED_BY" = EXCLUDED."UPDATED_BY",
    "UPDATED_TS" = NOW()

RETURNING NULL;

3.7 orderApprv_get_all_client_addresses

Purpose: Fetches all available addresses for a specific client. This makes the query highly reusable (e.g., you can trigger it for the Billing client or the Marketing client by just passing their respective ID). Trigger: Pass the target Client ID into the ToolJet query parameter when triggering this query.

SELECT 
    "ADDESS_ID_C",
    "CLIENT_ID_I",
    "ADDRESS_TYPE_C",
    "C_ADDRESS_DISPLAY"
FROM TBL_CLIENT_ADDRESS
WHERE "CLIENT_ID_I" = '{{parameters.clientId}}'
ORDER BY "ADDRESS_TYPE_C";

3.8 orderApprv_set_active_address

Purpose: Sets an existing address as the active "Billing" or "Marketing" address for a client. This dynamically updates BILLING_ADDRESS_C or MARKET_BY_ADDRESS_C in TBL_CLIENT based on the currently active tab (e.g. billing-client vs t0). It also dynamically targets the correct Client ID (Billing vs Marketing) from the main tblOrders list. Trigger: Triggered when the user checks the "Mark as Active" checkbox in the address List View. 

UPDATE TBL_CLIENT
SET 
  "BILLING_ADDRESS_C" = CASE WHEN '{{components.tabs1.currentTab}}' = 'billing-client' THEN '{{parameters.addressType}}' ELSE "BILLING_ADDRESS_C" END,
  "MARKET_BY_ADDRESS_C" = CASE WHEN '{{components.tabs1.currentTab}}' = 't0' THEN '{{parameters.addressType}}' ELSE "MARKET_BY_ADDRESS_C" END

WHERE "CLIENT_ID_I" = CASE 
  WHEN '{{components.tabs1.currentTab}}' = 'billing-client' THEN '{{components.tblOrders.selectedRow.CLIENT_ID_I}}'
  WHEN '{{components.tabs1.currentTab}}' = 't0' THEN '{{components.tblOrders.selectedRow.MKT_CLIENT_ID_I}}'
END;

3.9 orderApprv_fetch_row_details (JavaScript Master Query)

Purpose: A master JavaScript query (RunJS) that orchestrates fetching all related data when an order row is selected. Because all these queries rely purely on the selectedRow and do not depend on each other, they are executed in parallel using Promise.all for maximum dashboard speed. Trigger: Triggered by the On row selected event on the main tblOrders table component.

// Run all independent queries simultaneously for maximum speed
await Promise.all([
  queries.orderApprv_get_client_details.run(),
  queries.orderApprv_get_licenses.run(),
  queries.orderApprv_get_agreements.run()
]);

console.log("All row details fetched successfully!");

4. Data & Filtering Flow Architecture

The Approval Workflow uses a highly optimized "Fetch Once, Filter Locally" architecture to minimize database load and provide an instantaneous UI experience. 

sequenceDiagram
    participant DB as PostgreSQL DB
    participant API as ToolJet (orderApprv_get_orders)
    participant UI_DD as Dropdowns (drpOrderStatus, etc.)
    participant UI_TBL as ToolJet Data Table (tblOrders)

    API->>DB: Fetch all allowed orders (Single query on load)
    DB-->>API: Returns Full Dataset
    API->>UI_TBL: Populate table with all data

    API->>UI_DD: JS maps & counts unique statuses (e.g., "PAID (5)")

    Note over UI_DD,UI_TBL: User selects a filter option
    UI_DD->>UI_TBL: JS injects compound filter (e.g., AND Payment='PAID')
    UI_TBL->>UI_TBL: Table instantly hides non-matching rows
    Note over DB,UI_TBL: ZERO round-trips to database during filtering!

Here is the exact step-by-step flow of how data moves from the backend to the user's screen:

Step 1: Initial Data Fetch (Server-Side)

When the ToolJet app loads, the orderApprv_get_orders query runs. It passes through our strict backend security logic (variables.user.*) to ensure the user only downloads the rows they are allowed to see. This is the only time the main database is queried.

Step 2: Dynamic Dropdown Population (Client-Side)

Instead of running separate SQL queries to populate the filter dropdowns (like Order Status, Payment Status, and Pending Items), the dropdowns use JavaScript {{ ... }} to inspect the data that was already downloaded in Step 1. * They scan the dataset. * They dynamically group and count the unique statuses (e.g., creating the label "Active Orders (42)"). * If new statuses are introduced in the database, the dropdowns adapt automatically without any code changes.

Step 3: User Interaction & Event Handling (Client-Side)

When an admin selects an option from any of the three dropdowns (drpOrderStatus, drpPaymentStatus, or msPendingItems), an On Select event is fired. * The event uses a JavaScript array spread syntax [...(existing filters)] to grab all the filters currently active on the table. * It removes the old filter for that specific dropdown, and appends the newly selected value. * This ensures that multiple dropdowns (e.g., Payment Status = "PAID" AND Pending Items = "Design") can compound together without overwriting each other.

Step 4: Table Rendering (Client-Side)

The dropdown triggers a native Set filters action on the tblOrders component. ToolJet instantly hides the rows that don't match the compound filters. Because this happens entirely in the browser's memory, the filtering is visually instantaneous and requires zero round-trips to the PostgreSQL database!

5. UI Component Configuration & Maintenance

The client-side filtering relies on specific ToolJet component configurations. When maintaining or modifying this workflow, refer to the following mappings:

Where is the SQL?

  • Location: ToolJet Query Manager -> orderApprv_get_orders.
  • Important: Do NOT try to add UI filters (like {{components.dropdown.value}}) into the SQL WHERE clause. This query is strictly for Security (Organization isolation) and fetching the raw baseline data. All user-driven UI filtering happens entirely in JavaScript.

Where is the Dropdown Option Logic?

  • Location: Click on any Dropdown (e.g., drpPaymentStatus) -> Properties Panel -> Option data.
  • How it works: You will find an Immediately Invoked Function Expression (IIFE) written in JavaScript {{ (function() { ... })() }}. This script parses queries.orderApprv_get_orders.data to extract unique statuses and calculate the row counts (e.g., "PAID (5)").
  • To Debug: If a dropdown is missing an option, check this script to ensure it is mapping the correct column name from the database.

Where is the Filtering Logic?

  • Location: Click on any Dropdown -> Properties Panel -> Events -> On select -> Set filters.
  • How it works: You will see a JavaScript array spread syntax: 
    [
  ...(components.tblOrders.filters || []).filter(f => f.column !== "TARGET_COLUMN"),
  {"column": "TARGET_COLUMN", "condition": "equals", "value": "NEW_VALUE"}
]
  • Why we do this: This ensures that when a user selects a payment status, it doesn't accidentally wipe out their order status filter. It acts as a compounded AND filter.
  • To Debug: If filtering breaks, open the ToolJet debugger (bottom panel) and inspect the components.tblOrders.filters array to see exactly what JSON objects are being applied to the table. Ensure you are using doesNotEqual instead of notEquals, as ToolJet is strict about condition operators.

Right Bar Detail Layout & Formatting

  • Structural Separation: The selected row details are split into two distinct ToolJet HTML widgets:
  • Top HTML: Houses the core business and immediate approval data (Order No, Client info, Product/Composition, and current Payment status).
  • Bottom HTML: Houses technical and financial details (Quantity/MRP, secondary statuses, charges, timestamps, and pending items). This ensures critical information is always visible without scrolling past large tabs.

6. Address Management Flow

The Address Management UI (accessed via the Billing or Marketing tabs) uses a highly advanced, variable-free architecture to manage adding, editing, and selecting addresses.

Here is how the end-to-end flow operates:

1. Tab-Based Routing

The entire address flow is heavily context-aware based on the active tab (e.g., billing-client vs t0). * When opening the modal, the orderApprv_get_all_client_addresses query dynamically fetches addresses for either the Billing Client ID or the Marketing Client ID based entirely on the currentTab property.

2. Setting the Active Address

Within the List View, users see a dynamic Checkbox next to each address. * Dynamic State: The checkbox evaluates if the current List View row's ADDRESS_TYPE_C exactly matches the client's official BILLING_ADDRESS_TYPE or MARKETING_ADDRESS_TYPE. If it matches, the checkbox is automatically checked and disabled. * Trigger: Clicking an unchecked box runs orderApprv_set_active_address, which uses the tab name to know exactly which column in TBL_CLIENT to update (BILLING_ADDRESS_C or MARKET_BY_ADDRESS_C), seamlessly switching the active address for the order.

3. Add & Edit Mode Intelligence

The address form handles both Adding and Editing seamlessly without relying on risky UI state variables. * State Detection: The JavaScript behind the form properties strictly checks if the orderApprv_get_address_details query has an ID in its data array. If it does, the form switches to Edit Mode and populates the fields. If the query data is empty/reset, it switches to Add Mode. * Smart Validation (No Duplicates): The form maps all existing addresses for the client. The "Address Type" dropdown is dynamically filtered: if a client already has a "Billing" address, it is removed from the dropdown options. The only exception is if the user is currently in Edit Mode modifying that exact "Billing" address. * Execution: Clicking Submit runs the orderApprv_upsert_address query. It uses a PostgreSQL ON CONFLICT ("ADDESS_ID_C") clause to either elegantly update the existing row, or insert a newly generated UUID. * Layout Constraints: ToolJet's native HTML widgets wrap content in a flex container that can unintentionally center content vertically. To enforce strict top-alignment and a robust 2-column layout, the outermost div of these widgets relies on position: absolute; top: 0; left: 0; right: 0; coupled with display: grid; grid-template-columns: 1fr 1fr;. * Date Formatting (SQL vs UI): To minimize JavaScript processing overhead inside ToolJet widgets, complex string manipulation (like date/time formatting) is pushed down to the database layer. For example, PAYMENT_TS is formatted using TO_CHAR(O."PAYMENT_TS", 'DD Mon YYYY, HH12:MI AM') directly in the SQL query. The UI then safely defaults to simple bindings like {{components.tblOrders.selectedRow.PAYMENT_TS || 'Pending'}}, relying on JavaScript's native truthy/falsy fallback if the database returns a null.

Agreement Tab Detail View (Dynamic Expiry UI)

To display the fetched active agreement beautifully without needing a table, we use an HTML Component in ToolJet.

Where to find the logic: * Visibility: The HTML component is only visible when queries.orderApprv_get_agreements.data.length > 0. * Expiry Logic: Inside the HTML component's code, there are embedded JavaScript blocks ({{ (function() { ... })() }}) that calculate the difference between today's date and the EXPIRY_DATE_D. * How to change the warning threshold: If you want to change the warning period (currently set to 60 days), look for if (diff <= 60) inside the HTML component's code and change the 60 to your new threshold. It handles turning the text Amber for warnings and Red for expired.

Client Details Tabbed Layout

To display the comprehensive Billing and Marketing client details (fetched via orderApprv_get_client_details) within a limited UI space, we utilize ToolJet's native Tabs component.

Where to find the logic: * Data Source: The tabs rely on the orderApprv_get_client_details query being triggered upon selecting a row in the main orders table. * Billing Tab: Contains a native ToolJet Text component mapping the Billing fields (e.g., {{queries.orderApprv_get_client_details.data[0].BILLING_CLIENT_NAME}}, GST, DL, and Address). * Marketing Tab: Contains a native ToolJet Text component mapping the identical Marketing fields (e.g., {{queries.orderApprv_get_client_details.data[0].MARKETING_CLIENT_NAME}}). * Design Note: To maintain performance and avoid inline JavaScript overhead, the text components use simple HTML formatting (like <b> and <br>) combined with the || 'N/A' fallback operator to handle null values gracefully.

7. Pending Items Resolution Flow & State Management (Variable-Backed ListView Pattern)

Managing complex nested states within a ToolJet ListView component (such as toggling visibility of child components based on other child components' inputs) requires a specific architectural pattern. Relying on native component state (e.g., trying to read components.buttongroup1.selected directly from another component in the same row) is unreliable due to ToolJet's strict scoping rules and occasional bugs with listItem.

To solve this, we use the Variable-Backed ListView Pattern. This pattern decouples the UI from the database and establishes a ToolJet Page Variable as the single source of truth for the list's state.

stateDiagram-v2
    [*] --> DB_Fetch: User Selects Order
    DB_Fetch --> GlobalVariable: Maps to page.variables.pendingList

    state GlobalVariable {
        [*] --> PENDING: If missing
        [*] --> RESOLVED: If completed
        [*] --> NA: If not required

        PENDING --> NA: User clicks 'NA' (Deep Copy UI Update)
        NA --> PENDING: User clicks 'Pending' (Deep Copy UI Update)

        note right of PENDING: Requires Due Date
    }

    GlobalVariable --> ValidationGate: User clicks 'Save'

    state ValidationGate {
        Valid: All PENDING items have dueDate
        Invalid: Missing dueDate for PENDING item

        Invalid --> DisabledSaveButton: Block Action
    }

    ValidationGate --> DB_Upsert: If Valid
    DB_Upsert --> [*]: orderApprv_save_pending_items CTE query runs

7.1 Initialization & Mapping

When the database query (orderApprv_get_pending_items) succeeds, it does not feed directly into the ListView. Instead, an On Success event triggers a script to map the data, inject UI-specific properties (like uiStatus), and store it globally in a variable.

Status Definitions (uiStatus): - PENDING: This requirement is currently missing and blocking the order. An Expected Date is compulsory. - RESOLVED: The requirement was previously missing but has now been fulfilled/uploaded. - NA (Not Applicable): This requirement is either not required for this specific order, or it was already present at the start of the flow (meaning it never blocked the order).

// Example: Init Script
const queryData = queries.orderApprv_get_pending_items.data || []; 
const items = ['Advance Payment', 'Drug License', 'Food License', 'GST Certificate', 'MFG Agreement'];

const mappedList = items.map(itemName => {
    const dbItem = queryData.find(row => row['Pending Requirement'] === itemName);
    return {
        name: itemName,
        ...dbItem,
        details: dbItem ? `Added by ${dbItem['Added By']} on ${dbItem['Added On']}` : '',
        dueDate: dbItem ? dbItem['Due Date'] : null,
        uiStatus: dbItem?.Status === 'Pending' ? 'PENDING' : 
                  dbItem?.Status === 'Resolved' ? 'RESOLVED' : 'NA'
    };
});

actions.setPageVariable('pendingList', mappedList);

7.2 Component Data Binding

The ListView's Data property is then bound strictly to the variable with a failsafe fallback: {{ page.variables.pendingList || [] }}

Child components strictly read from this data context rather than inspecting each other: - Button Group Default Value: {{ [listItem.uiStatus] }} - Date Picker Visibility: {{ listItem.uiStatus === 'PENDING' }}

7.3 The "Deep Copy" Reactivity Workaround

When an admin clicks a button to change a status, the script must update the global variable. However, ToolJet's reactivity engine often fails to detect nested property mutations (e.g., list[0].uiStatus = 'NEW'), causing the UI to freeze and fail to re-render.

To force a full UI re-render, the update script uses a Deep Copy of the specific row being modified. This guarantees ToolJet detects the memory reference change.

7.4 The listItem Scope Bypass

A known bug in ToolJet causes the listItem context to occasionally evaluate as undefined (or "") when used inside the Parameter fields of an Event Handler's Run Query action.

To bypass this without breaking the application, we explicitly reach outside the broken row scope and grab the name dynamically from the parent ListView using the i (index) parameter: {{ components.listview2.data[i].name }}

7.5 Complete Update Script (orderApprv_update_local_pending_items)

This reusable RunJS query is triggered by the On select event of the Button Group inside the ListView.

Parameters Passed: - itemName: {{ components.listview2.data[i].name }} (Bypasses listItem bug) - newStatus: {{ components.buttongroup1.selected[0] }} - newDate: {{ components.datepicker1[i].value }}

Script Logic: 

// 1. Grab current list (Shallow copy of array)
const currentList = [...(page.variables.pendingList || [])];

// 2. Find the index using the passed parameter
const itemIndex = currentList.findIndex(item => item.name === parameters.itemName);

if (itemIndex !== -1) {
    // 3. FORCE REACTIVITY: Create a brand new object for this row instead of modifying the old one
    currentList[itemIndex] = {
        ...currentList[itemIndex],
        uiStatus: parameters.newStatus !== undefined ? parameters.newStatus : currentList[itemIndex].uiStatus,
        dueDate: parameters.newDate !== undefined ? parameters.newDate : currentList[itemIndex].dueDate
    };

    // 4. Save it back to the variable to trigger instant UI updates
    actions.setPageVariable('pendingList', currentList); 
}
This architecture ensures 100% deterministic UI state, eliminates race conditions, and bypasses native component scoping issues inside ToolJet.

7.6 Reactive UI Validation & Save Pipeline

Instead of relying on a post-click JavaScript validation script, this architecture uses Reactive UI Validation to prevent the user from ever clicking Save if data is invalid. This guarantees that only valid data reaches the PostgreSQL query.

1. Save Button (Dynamic Disable)

The Save button's Disable property is bound to an inline array check that instantly disables the button if any PENDING item is missing its dueDate:

{{ 
  (page.variables.pendingList || []).some(item => item.uiStatus === 'PENDING' && !item.dueDate) 
}}

2. Validation Warning Text (Optional)

To ensure good UX, a red Text Component is placed near the Save button. Its Visibility property uses the exact same expression, ensuring it only appears when the button is disabled:

⚠️ Please provide an Expected Date for all PENDING items before saving.

3. Triggering the Save

Because the Save button is mathematically guaranteed to only be clickable when the payload is valid, its On click event directly triggers the orderApprv_save_pending_items PostgreSQL query.

orderApprv_save_pending_items (PostgreSQL - CTE Architecture)

Instead of relying on Javascript to build a payload, this query reads the pendingList page variable directly. It uses an advanced PostgreSQL Common Table Expression (CTE) to instantly perform both operations in a single, lightning-fast database transaction.

Why this architecture is highly robust: 1. Single JSON Parse (payload CTE): The json_to_recordset function parses the ToolJet variable exactly once into a temporary memory table called payload. 2. Surgical Deletion (deleted CTE): If a user accidentally marks an item as PENDING and later reverts it to NA, we must delete it. This CTE forces PostgreSQL to check two strict conditions: the ORDER_NO_C must match the current order, and the ITEM_NAME_C must match exactly an item marked NA. Because of the table's composite unique key, it is mathematically impossible for this to delete the wrong row or affect other orders. 3. Dynamic Bulk Upsert: Finally, it takes the remaining PENDING and RESOLVED items, dynamically calculates timestamps using COALESCE (handling items that weren't previously in the database), and upserts them.

WITH payload AS (
    -- 1. Parse the JSON exactly once
    SELECT 
        "name" AS itemName,
        "uiStatus",
        NULLIF("dueDate", '')::DATE AS dueDate,
        "Status" AS oldStatus
    FROM json_to_recordset('{{ JSON.stringify(page.variables.pendingList || []) }}') 
    AS x("name" VARCHAR, "uiStatus" VARCHAR, "dueDate" VARCHAR, "Status" VARCHAR)
),
deleted AS (
    -- 2. Delete any NA items safely
    DELETE FROM SAAR_BIOTECH.tbl_PRE_SALES_ORDER_APPROVAL_ITEMS
    WHERE "ORDER_NO_C" = '{{components.tblOrders.selectedRow.ORDER_NO_C}}'
      AND "ITEM_NAME_C" IN (SELECT itemName FROM payload WHERE "uiStatus" = 'NA')
)
-- 3. Upsert the rest
INSERT INTO SAAR_BIOTECH.tbl_PRE_SALES_ORDER_APPROVAL_ITEMS (
    "ORDER_NO_C", 
    "ITEM_NAME_C", 
    "EXPECTED_DATE_D", 
    "CREATED_BY", 
    "RESOLVED_BY", 
    "RESOLVED_TS"
)
SELECT 
    '{{components.tblOrders.selectedRow.ORDER_NO_C}}', 
    itemName, 
    dueDate, 
    '{{variables.user.userId}}', 

    -- Generate the resolvedBy on the fly
    CASE 
        WHEN COALESCE(oldStatus, '') <> 'Resolved' AND "uiStatus" = 'RESOLVED' THEN '{{variables.user.userId}}'
        ELSE NULL
    END AS resolvedBy,

    -- Generate the timestamp on the fly
    CASE 
        WHEN COALESCE(oldStatus, '') <> 'Resolved' AND "uiStatus" = 'RESOLVED' THEN NOW()
        ELSE NULL
    END AS resolvedTs

FROM payload
WHERE "uiStatus" <> 'NA'
ON CONFLICT ("ORDER_NO_C", "ITEM_NAME_C") 
DO UPDATE SET
    "EXPECTED_DATE_D" = EXCLUDED."EXPECTED_DATE_D",
    "RESOLVED_BY" = COALESCE(EXCLUDED."RESOLVED_BY", tbl_PRE_SALES_ORDER_APPROVAL_ITEMS."RESOLVED_BY"),
    "RESOLVED_TS" = COALESCE(EXCLUDED."RESOLVED_TS", tbl_PRE_SALES_ORDER_APPROVAL_ITEMS."RESOLVED_TS");

7.7 Flowable Integration (Save & Hold Automation)

To create a frictionless UI, the ToolJet approval modal is deeply integrated with the Flowable BPMN Engine. When the user clicks "Save", ToolJet automatically submits the active Flowable task to push the workflow into the "Hold" state, provided the order is in the correct status.

This is handled elegantly using ToolJet's visual Event Handlers attached directly to the orderApprv_save_pending_items SQL query.

1. The On success Event Handler

Go to the orderApprv_save_pending_items query, scroll down to Events, and add a new handler: * Event: On success * Action: Run Query * Query: restCompleteFlowableTask (or whatever your API query is named) * Run Only If: 

{{ ['PENDING', 'REQUIREMENTS_PENDING'].includes(components.tblOrders.selectedRow.PAYMENT_STATUS) }}

2. The Flowable REST API Payload

In your Flowable REST API query, you can dynamically calculate the BPMN decision and the list of missing items directly in the JSON body:

{{
  (() => {
    // 1. Generate the missing items list
    const pendingList = (page.variables.pendingList || [])
      .filter(item => item.uiStatus === 'PENDING')
      .map(item => item.name)
      .join(', ');

    // 2. Return the clean object your backend expects
    // We ALWAYS send 'hold', regardless of the active task.
    return {
      decision: 'hold',
      PENDING_ITEMS_LIST: pendingList
    };
  })()
}}

3. Architectural Rationale

  • Dynamic Routing (hold vs update): In the BPMN, the gwReview gateway only accepts hold, while the gwPending gateway only accepts update. If we blindly sent decision: 'hold' from the pendingClearanceTask, Flowable would crash with a "No outgoing sequence flow" exception. The PAYMENT_STATUS check safely routes the decision based on the active task state.
  • REQUIREMENTS_PENDING Status: When Flowable hits sqlOnHoldTask, it explicitly updates the DB PAYMENT_STATUS to REQUIREMENTS_PENDING. This clearly differentiates an order that is actively blocked by missing documents from a brand new order sitting in the initial PENDING state.

8. Document Upload Architecture & Troubleshooting

Our document upload flow integrates a low-code UI (ToolJet) with a strictly typed Java/Spring Boot backend. Because of ToolJet's template engine quirks (specifically how it evaluates and stringifies objects), we implemented a specialized pipeline to prevent JSON parsing errors and maintain data integrity.

sequenceDiagram
    participant User
    participant TJ as ToolJet UI (filepicker & form)
    participant SQL as ToolJet SQL Query (Upsert metadata)
    participant REST as ToolJet REST Query (Upload Document)
    participant Spring as Spring Boot Backend
    participant GCS as Google Cloud Storage

    User->>TJ: Uploads File & Submits Form
    TJ->>TJ: Automatically Base64 encodes file
    TJ->>SQL: Runs insert/update (e.g., orderApprv_insert_agreement)
    SQL-->>TJ: Returns DB row ID via `RETURNING *`

    Note over TJ,REST: On Success Event Handler triggered
    TJ->>REST: Passes base64 file, metadata obj, and DB ID
    REST->>REST: JSON.stringify(customMetadata)
    REST->>Spring: POST /api/.../documents/upload
    Spring->>Spring: ObjectMapper parses JSON (or uses @JsonCreator fallback)
    Spring->>GCS: Uploads physical file bytes
    GCS-->>Spring: Returns GCS URL
    Spring-->>TJ: Success Response
    TJ->>User: Renders updated UI (e.g., fetches agreements)

8.1 The Upload Pipeline (How it works)

  1. User Action: The user selects a file (e.g., PDF/JPEG) via the filepicker component and fills out a JSON form (e.g., form_agreement).
  2. ToolJet Base64 Encoding: Because the filepicker component has "Parse Content" toggled ON, ToolJet automatically encodes the physical file into a Base64 string (components.filepicker1.file[0].base64Data).
  3. Database Upsert (On Submit): The specific insert SQL query (e.g., orderApprv_insert_agreement) runs first. It inserts/updates the database record and returns the new row (RETURNING *).
  4. Triggering the Upload API: The On Success handler of the SQL query triggers the REST API query (orderApprv_upload_document), passing the returned database ID (entityId) and the Base64 file string as parameters.
  5. JSON Serialization Strategy: To pass nested dynamic metadata (like the Client ID and Organization) from ToolJet to the backend without breaking the JSON structure, the metadata is defined as a plain JavaScript object in the ToolJet parameters. The REST query body then uses {{ JSON.stringify(parameters.customMetadata) }} to safely escape it.
  6. Backend Deserialization (The @JsonCreator Fallback):
  7. Under normal circumstances, Spring Boot's ObjectMapper parses the valid JSON body.
  8. Failsafe: If a developer accidentally types JSON.stringify({...}) in the raw body field of ToolJet (which causes ToolJet to send the entire payload as a literal string), the backend's StorageDto.ToolJetUploadRequest class contains a @JsonCreator(mode = DELEGATING). This custom creator intercepts the string, strips the JSON.stringify( wrapper, and safely parses the inner JSON to prevent crashes.
  9. Date Parsing: ToolJet date pickers output ISO strings (YYYY-MM-DDTHH...Z). The DTO uses specialized @JsonSetter methods to trim these to standard YYYY-MM-DD before parsing them into LocalDate objects.

8.2 Troubleshooting & Adding New Upload Types

If you need to add a new document upload type (e.g., "Food License" or "Drug License") in the future, follow these exact steps to avoid breaking the JSON payload:

1. Create the specific insert/update SQL query Create a query like orderApprv_insert_food_license that upserts into your target table and includes RETURNING *; at the end.

2. Configure the "On Success" handler Trigger the global orderApprv_upload_document API query. - Pass documentType as the new enum (e.g., "FOOD_LICENSE"). - Pass entityId dynamically from the SQL return (e.g., {{queries.orderApprv_insert_food_license.data[0].ID}}). - CRITICAL: Pass customMetadata as a raw Javascript object. Do NOT wrap it in single quotes ('). Correct: { "LICENSE_NO": "{{components.form.data.LIC_NO.value}}" } Incorrect: '{ "LICENSE_NO": "..." }'

3. Leave the REST API query alone The orderApprv_upload_document query is designed to be completely reusable. Do not modify its raw JSON body. It must remain exactly as: 

{
  ...
  "customMetadata": {{ JSON.stringify(parameters.customMetadata) }}
}

Common Errors & Fixes: * Unexpected character ('B' (code 66)): was expecting comma... Cause: You accidentally wrapped the JavaScript object in single quotes (') in the Event Handler parameters. ToolJet injected '{"Key..."}' directly into the JSON body, breaking the double quotes. Fix: Remove the single quotes in the Event Handler parameters. Fix: Remove the manual JSON.stringify wrapper from the API query body. Rely on the {{ JSON.stringify(parameters.customMetadata) }} inside the specific field instead.

8.3 Advanced Unified Client License Upload Flow (Dynamic Modal Architecture)

In traditional low-code development, handling multiple document types (e.g., GST Certificates, Drug Licenses, Food Licenses) often leads to "component bloat"—creating separate modals, separate forms, and separate queries for every document type.

To eliminate this technical debt and ensure absolute consistency, the application leverages an advanced Dynamic Modal Pattern. A single, universally reusable modal (modal8) and form (form5) intelligently adapt their UI and query payloads based on a ToolJet Page Variable context.

This architecture requires tight coordination between ToolJet's frontend event handlers, the REST API payload mapping, and the PostgreSQL query structure.

Phase 1: Context Injection (The Trigger)

The flow begins entirely from the "Client Licenses" tab in the right-side detail panel. - There are specific buttons for updating specific documents (e.g., "Update Drug License", "Update GST Certificate"). - Event Handler Sequence: 1. Set Page Variable: The On click event instantly sets document_type to a strict enum string (e.g., 'DRUG_LISCENCE' or 'GST_CERTIFICATE'). 2. Show Modal: It then launches modal8. - Why this matters: The modal itself is completely "dumb". It does not know what a GST certificate is. It only knows how to read the document_type variable.

Phase 2: Dynamic UI Adaptation (The Modal)

Once modal8 opens, all child components dynamically re-render based on the document_type variable.

  • Modal Header (Title Text): Instead of hardcoding "Update GST", the title text component evaluates: 
    {{ page.variables.document_type.replaceAll('_', ' ') }} | {{ components.tabs.currentTab === 'billing-client' ? queries.orderApprv_get_client_details.data?.[0]?.BILLING_CLIENT_NAME : queries.orderApprv_get_client_details.data?.[0]?.MARKETING_CLIENT_NAME }}
    This instantly converts "GST_CERTIFICATE" into a beautiful, human-readable "GST CERTIFICATE" title, and dynamically appends the correct client name.
  • Form Structure (form5): The form uses generic labels. Instead of GST Number and DL Number, it provides a single Number text input. It also provides ISSUE_DATE_D1 and EXPIRY_DATE_D2 date pickers, and a generic filepicker2.
  • Real-time Validation: The Submit button's Disabled property contains a strict check: {{ !components.form5.isValid || !components.filepicker2.fileSize > 0 }} This mathematically guarantees that the user has provided a valid number, issue date, expiry date, and successfully attached a physical file before the queries are ever allowed to fire.

Phase 3: The Dual-Query Execution Pipeline (On Submit)

When the valid form is submitted, ToolJet fires a sequence of queries. The brilliance of this architecture is how a single form payload is split and intelligently routed to two completely different backends (PostgreSQL and Google Cloud Storage) simultaneously.

1. Database Upsert (orderApprv_update_client_details)

The SQL query must update TBL_CLIENT. However, because we only have one generic Number field in the UI, we must use JavaScript ternary operators in the Event Handler parameters to map it to the correct column, while passing null to the others:

Parameter Value (Ternary Logic)
gstNo {{ page.variables.document_type === 'GST_CERTIFICATE' ? components.form5.data.Number.value : null }}
dlNo {{ page.variables.document_type === 'DRUG_LISCENCE' ? components.form5.data.Number.value : null }}
clientName {{ components.form5.data.ClientName.value || null }}
clientId {{queries.orderApprv_get_client_details.data[0].CLIENT_ID_I}}

The PostgreSQL COALESCE and NULLIF Failsafe: To prevent the query from wiping out a client's existing Drug License when we are only updating their GST Certificate, the SQL query is explicitly wrapped in COALESCE and NULLIF. Empty strings are safely cast to NULL. 

UPDATE SAAR_BIOTECH.TBL_CLIENT
SET 
  "GST_NO_C"          = COALESCE(NULLIF(TRIM('{{parameters.gstNo}}'), ''), "GST_NO_C"),
  "DRUG_LICENSE_NO_C" = COALESCE(NULLIF(TRIM('{{parameters.dlNo}}'), ''), "DRUG_LICENSE_NO_C"),
  "CLIENT_NAME_C"     = COALESCE(NULLIF(TRIM('{{parameters.clientName}}'), ''), "CLIENT_NAME_C"),
  "UPDATED_BY"        = '{{variables.user.userId}}',
  "UPDATED_TS"        = NOW()
WHERE "CLIENT_ID_I" = '{{parameters.clientId}}'
  AND (
       ("GST_NO_C"          IS DISTINCT FROM COALESCE(NULLIF(TRIM('{{parameters.gstNo}}'), ''), "GST_NO_C"))
    OR ("DRUG_LICENSE_NO_C" IS DISTINCT FROM COALESCE(NULLIF(TRIM('{{parameters.dlNo}}'), ''), "DRUG_LICENSE_NO_C"))
    OR ("CLIENT_NAME_C"     IS DISTINCT FROM COALESCE(NULLIF(TRIM('{{parameters.clientName}}'), ''), "CLIENT_NAME_C"))
  );
If parameters.dlNo evaluates to null (because we are in the GST flow), COALESCE(null, "DRUG_LICENSE_NO_C") returns the existing database value, resulting in zero changes to the DL column! The IS DISTINCT FROM clause ensures that we don't even waste a database write if the user submitted the exact same number.

2. File Upload API (orderApprv_upload_document)

Simultaneously, the second Event Handler triggers the global Document Upload REST API. * Document Type Mapping: It passes {{page.variables.document_type}} directly into the documentType parameter, ensuring Google Cloud Storage categorizes the file correctly. * Custom Metadata Mapping: The generic Number field from the UI must be stored permanently against the file in GCS. We pass a raw Javascript object into the customMetadata parameter: 

{ "LICENSE_NUMBER": "{{components.form5.data.Number.value}}" }
(See Section 8.1 for the mechanics of how JSON.stringify safely serializes this).

Phase 4: State Cleanup & UX

After the queries successfully fire, a final Event Handler runs the Close modal action on modal8. Because the queries execute asynchronously in the background, the UI feels instantaneous to the admin.

9. Advanced Debugging & Development Patterns

When maintaining or extending this ToolJet application, keep these critical architectural patterns and gotchas in mind:

1. Handling ToolJet Empty Dates (PostgreSQL Crashes)

ToolJet date pickers output "" (empty string) or "null" (literal string) when cleared. If you pass these directly into a PostgreSQL DATE or TIMESTAMP column, the database will throw a type error and crash the query. * The Fix: Always wrap date parameters in your SQL with: NULLIF(NULLIF('{{components.datepicker.value}}', ''), 'null')::DATE.

2. The "Cannot read properties of undefined" Race Condition

If a text component or Javascript block throws this error, it is almost always because the underlying query data hasn't finished loading yet (e.g. queries.my_query.data[0] is undefined for a split second). * The Fix: Always use JavaScript Optional Chaining (?.). Change data[0].COLUMN to data?.[0]?.COLUMN. This allows the component to silently fail and render blank until the data arrives a millisecond later.

3. Prepared Statements & Single Quote Errors

ToolJet automatically uses "Prepared Statements" for all SQL queries to prevent SQL injection. This means ToolJet automatically wraps your parameters in quotes behind the scenes. * The Gotcha: If you manually add single quotes around a parameter inside a ToolJet Event Handler (e.g., setting a variable to '{{listItem.ID}}'), ToolJet sends a literal string containing curly braces to the database, resulting in 0 rows found. * The Fix: Never put single quotes around {{...}} expressions inside Event Handlers or Javascript blocks. Only use them inside the raw SQL editor.

4. The Hidden Text Component Pattern (List View Scoping)

ToolJet's List View components heavily restrict scope. A button outside the List View cannot read data from a row inside the List View, and sometimes even buttons inside the row struggle to pass multiple variables cleanly. * The Pattern: We use hidden Text components (Visibility: {{false}}) inside the List View row to expose listItem data to the broader page context. For example, if a row contains txt_addressType set to {{listItem.ADDRESS_TYPE_C}}, a button can safely pass {{components.listview1.selectedRecord.txt_addressType.text}} to a query without relying on messy global page.variables.

10. Flowable API Integration (Task Completion)

flowchart LR
    subgraph ToolJet Frontend
        A[Admin clicks Approve] --> B[Modal: Input Payment / Charges]
        B --> C[Click Submit]
        C --> D[JS strips UI metadata & adds decision variable]
        D --> E[orderApprv_complete_flowable_task API Query]
    end

    subgraph Flowable BPMN Engine
        E --> F[Complete User Task]
        F --> G{Gateway (decision)}
        G -- decision: 'approve' --> H[sqlFullApproveTask (Groovy)]
        G -- decision: 'partial' --> I[sqlPartialApproveTask (Groovy)]
        H --> J[DB data.update (PAYMENT_STATUS = APPROVED)]
        I --> K[DB data.update (PAYMENT_STATUS = PART_APPROVED)]
    end

    J --> L[Proceed to Fulfillment]
    K --> M[Send Status Email & Return to Admin]

10.1 Flowable Task Completion Query (orderApprv_complete_flowable_task)

This REST API query dynamically completes the active task for ANY process in Flowable. By fully parameterizing every field, it becomes a universal utility query that is never hardcoded.

  • Data Source: infinity-plus-data-source
  • Method: POST
  • URL: {{constants.FLOWABLE_BASE_URL}}/workflow/tasks/submit-by-key
  • Body (JSON): (Note: If ToolJet's JSON editor complains about missing quotes on arrays/objects, you can switch the Body type to Raw or Custom and paste this directly) 
    {
  "businessKey": "{{parameters.businessKey}}", 
  "processKey": "{{parameters.processKey}}",
  "taskKeys": {{JSON.stringify(parameters.taskKeys)}},
  "actingUserId": "{{parameters.actingUserId}}", 
  "variables": {{JSON.stringify(parameters.variables)}}
}

10.2 How to trigger this query (No-Code Event Handlers)

Because the query is 100% parameterized, you do not need to write custom JavaScript (Run Code) to execute it. You can trigger it natively using ToolJet's Run Query action and pass the parameters directly in the UI.

Typically, you attach an On click event handler to a form submit button (e.g., inside an approval modal).

Event Handler Configuration: - Event: On click - Action: Run Query - Query: orderApprv_complete_flowable_task - Parameters (Pass these directly in the Event Handler's UI): - businessKey: {{components.tblOrders.selectedRow.ORDER_NO_C}} - processKey: preSalesOrderApproval - taskKeys: {{ ['reviewOrderTask', 'pendingClearanceTask', 'partialApproveTask'] }}

10.3 End-to-End Flow: Full Approval with Financial Inputs

The "Full Approve" action is the most critical transition in the Pre-Sales workflow. It requires capturing financial details (like payment amounts and plate charges) from the admin before officially releasing the order. Instead of ToolJet performing a direct UPDATE query on the database, it safely delegates this to the Flowable engine.

Step 1: The Approval Modal (modal4 / form4) When an admin decides to approve an order, a modal opens containing a ToolJet Form (form4). This form contains Currency Input components linked directly to the selected order's existing values: - PAYMENT_AMOUNT_C - INVENTORY_CHARGES_B - PLATE_CHARGES_B - CYLINDER_CHARGES_B

Step 2: Dynamic Variable Mapping (On Submit) When the admin clicks Submit (button16), an onClick event triggers the orderApprv_complete_flowable_task API query. Instead of sending the raw {{components.form4.data}} object (which contains useless ToolJet UI state like isVisible, isValid, id, and formattedValue), the Event Handler uses a specific JavaScript snippet in the variables field:

{{
  (() => {
    // 1. Safely grab the data (defaults to empty object if undefined)
    const formData = components.form4.data || {};

    // 2. Map only the values
    const cleanVariables = Object.fromEntries(
      Object.entries(formData).map(([key, field]) => [key, field?.value])
    );

    // 3. Return the merged object securely
    return {
      ...cleanVariables,
      decision: 'approve'
    };
  })()
}}
This script dynamically loops through every component in the form, extracts only the raw number values, merges them with the decision: 'approve' variable, and sends a clean, flat JSON payload to Flowable.

Step 3: Flowable Engine Execution (sqlFullApproveTask) The Flowable engine receives the API call, completes the User Task, and routes the token down the "Full Approve" gateway path to a Script Task (sqlFullApproveTask).

Inside this task, a Groovy script automatically reads the mapped ToolJet variables and executes a secure data.update against tbl_order_Details: - It sets PAYMENT_STATUS to APPROVED - It saves the captured payment and charge amounts directly into the table. - It updates audit fields PAYMENT_ASSIGN, PAYMENT_TS, UPDATED_BY, and UPDATED_TS.

This architecture ensures that Flowable remains the absolute source of truth for state changes, preventing race conditions or incomplete data updates from the frontend UI.

10.4 End-to-End Flow: Partial Approval

The "Partial Approve" action follows the exact same pattern as the Full Approval, with one additional input field for the admin.

Step 1: The Partial Approval Modal (modal6 / form6) When an admin selects "Partial Approve", the UI opens modal6. form6 captures the same financial inputs as Full Approve (Payment Amount, Plate Charges, etc.), but additionally displays text inputs for the partial approve reasoning, such as PART_APPROVED_STATUS.

Step 2: Dynamic Variable Mapping (On Submit) When the admin clicks Submit (button20), it triggers the same API query (orderApprv_complete_flowable_task). The query's Event Handler uses a JavaScript snippet to strip out unnecessary UI metadata, enforce the decision variable to partial, and include the new comments field. 

{{
  (() => {
    // Grab data from form6
    const formData = components.form6.data || {};
    const cleanVariables = Object.fromEntries(
      Object.entries(formData).map(([key, field]) => [key, field?.value])
    );
    return {
      ...cleanVariables,
      decision: 'partial'
    };
  })()
}}

Step 3: Flowable Engine Execution (sqlPartialApproveTask) The Flowable engine receives the API call, completes the User Task, and routes the token down the "Partial Approve" gateway path to a Script Task (sqlPartialApproveTask).

Inside this task, a Groovy script automatically reads the mapped variables and executes a secure data.update against tbl_order_Details: - It sets PAYMENT_STATUS to PART_APPROVED - It saves the captured payment and charge amounts directly into the table. - It saves the PART_APPROVED_COMMENTS provided by the admin. - It updates audit fields PAYMENT_ASSIGN, PAYMENT_TS, UPDATED_BY, and UPDATED_TS.

This triggers the emailPartialApproveTask which reads the PART_APPROVED_COMMENTS directly from the database to send a detailed status email, before routing the token to a new User Task (partialApproveTask) where the admin must eventually finalize the order.