Get Past Deluge Loop and Iteration Limits in Zoho Creator

Work Around Deluge Iteration Limits in Zoho Creator

Deluge caps how many records a single loop may touch. Hit that ceiling and your script stops part-way through, often without an error that makes the cause obvious. Two patterns get around it, and both work by making each individual loop smaller rather than by making Creator faster.

The second pattern in the original never advanced. It took start and end parameters and incremented a counter inside the loop — but the query itself was hardcoded to range from 1 to 50. The window never moved, so every invocation processed the same first fifty records no matter what arguments you passed. The rewrite computes the range from the parameters. Also corrected: Month_Value = 7 used a single =, which assigns rather than compares in Deluge; a condition mixing || and && without parentheses did not mean what it looked like; and two branches of an if/else were identical.

Pattern 1 — split the query by key prefix

When records carry a structured code, iterate the code space rather than the records. Each inner query returns a handful of rows, so no single loop gets near the cap.

// Split one huge query into many small ones by prefix.
// P001..P999 becomes 1000 narrow queries instead of one wide loop.
digits = {"0","1","2","3","4","5","6","7","8","9"};

for each a in digits
{
    for each b in digits
    {
        for each c in digits
        {
            code = "P" + a + b + c;
            for each prod in Product[Code == code]
            {
                prod.July = ProductSales[Codigo == prod.Codigo && Month_Value == 7].sum(Sales);
            }
        }
    }
}

Be clear-eyed about what this does. It does not make anything faster — it trades one large loop for a thousand small queries, and the total work goes up. What it buys you is staying under the per-loop ceiling. Reach for it when you genuinely cannot restructure the data.

Pattern 2 — a paged batch function that moves its own window

More general, and the one to prefer. Process a fixed window, then call yourself with the next one.

// ============================================
// PAGED BATCH PROCESSOR
// Handles one window of records, then queues the next.
// ============================================

void Updates.processBatch(int startAt, int batchSize)
{
    endAt = startAt + batchSize - 1;

    // The window MOVES. This is the part the original got wrong.
    records = Estadisticos[ID != null] sort by ID asc range from startAt to endAt;

    processed = 0;
    for each rec in records
    {
        monthly = List();
        monthly.add(ifnull(rec.Ene,0));
        monthly.add(ifnull(rec.Feb,0));
        monthly.add(ifnull(rec.Mar,0));
        // ... the remaining months

        total = 0.0;
        filled = 0;
        for each m in monthly
        {
            total = total + m;
            if(m > 0)
            {
                filled = filled + 1;
            }
        }

        rec.Volumen  = total;
        rec.Promedio = if(filled > 0, (total / filled).round(2), 0);
        processed = processed + 1;
    }

    info "Processed " + processed + " records from " + startAt + " to " + endAt;

    // If the window came back full, there is probably more to do.
    if(processed == batchSize)
    {
        thisapp.Updates.processBatch(endAt + 1, batchSize);
    }
}

Notes

  • range from X to Y is what does the work. Fetching everything and skipping rows in Deluge still loads everything and still hits the cap. The range has to be in the query.
  • Sort ascending, and sort by something stable. Paging over an unstable or descending sort means records shift between windows — some get processed twice, others never.
  • Recursion has its own ceiling. The self-call above is convenient but stacks. For a genuinely large table, have each run write its finishing position to a settings record and let a scheduled function pick up from there. Slower in wall-clock terms, but it cannot blow the stack or the statement limit.
  • Fix the data model before reaching for either of these. Needing to iterate 50,000 rows in a form workflow is usually a sign the calculation belongs in a report aggregate, a scheduled job, or a field maintained incrementally on write.

This script is part of the free Creator Scripts Deluge Library.

All 39 Deluge scripts, the full Zoho Creator course, and every downloadable asset are now free. Get free access →