Iterative Macro for API pagination: Loop output ignored after first iteration (schema corr

[ybondar]

Hi Community,

I’m building an Iterative Macro for a cursor-based API (Repsly v3):

GET https://api.repsly.com/v3/export/clients/{lastTimeStamp}

Pattern:

• Start with lastTimeStamp = 0
• Each response returns MetaCollectionResult.LastTimeStamp and TotalCount
• Continue while TotalCount > 0

Macro Input schema (and expected Iterative Output schema):

• base_url (V_String512)
• api_user (V_String512)
• api_pass (V_String512)
• lastTimeStamp (Int64)

What works:

• First iteration: URL = …/clients/0, Authorization OK.
• Download returns HTTP 200 and JSON with MetaCollectionResult.LastTimeStamp = 5322764819.
• Before Iterative Output: Browse shows 1 row, 4 fields, updated lastTimeStamp = 5322764819.

Guards applied:

• Select before Loop → only 4 fields, exact types.
• Sample (First N=1) before Loop → guarantees one row.
• Output Mode = Auto Configure by Name.
• Interface Designer: Iteration Input = Macro Input, Iteration Output = Loop.

Issue: Despite all this, the second iteration sometimes starts again with the initial input row (lastTimeStamp = 0), as if the macro ignores the Loop output.

Design details:

• Upper branch uses Append Fields to bring in records_count, new_last_ts, total_count for filtering.
• Filter logic:

  records_count > 0
  AND NOT IsNull(new_last_ts)
  AND NOT IsNull(lastTimeStamp)
  AND ToNumber(new_last_ts) > ToNumber(lastTimeStamp)
  AND NOT IsNull(total_count)
  AND total_count > 0

• Formula: lastTimeStamp = new_last_ts (after filter).
• JSON Parse → Filter → Formula → Summarize → Formula → Append → Filter → Formula → Select → Loop.

Questions:

1. Can multiple rows or schema drift (e.g., V_WString from JSON) cause the engine to fallback to the initial Macro Input row, even if Browse before Loop shows 1 row / 4 fields?
2. Is the recommended pattern to enforce Select + Sample immediately before Loop (we did this)?
3. Any best practices when using Append Fields for meta logic so they don’t interfere with Loop payload?
4. How to inspect/log the actual record passed from Iterative Output to the next iteration?

Thanks in advance! Happy to share the macro or anything else if needed.

[ybondar · Accepted solution]

Solution Summary: How We Fixed the Iterative Macro Issue

After extensive troubleshooting, the root cause was schema consistency and complexity in the loop path. Even though Browse before the Loop showed the correct values, the macro sometimes fell back to the initial input because:

• Multiple rows or extra fields reached the Loop anchor due to multiple Append Fields.
• Type drift (e.g., V_WString from JSON Parse) and mismatched sizes caused silent schema mismatches.
• Iterative Output requires exactly one row and exactly the same schema as Macro Input (names + types).

What We Changed

1. Simplified the loop path:

   • Reduced three Append Fields to one.
   • Removed unnecessary summarizes and appends.
   • Ensured only meta logic flows into the loop.

2. Unified schema and types:

   • Enforced base_url, api_user, api_pass as V_String(512) and lastTimeStamp as Int64 at every stage.
   • Applied Select immediately before Loop to keep only these 4 fields.

3. Guaranteed one row per iteration:

   • Added Sample (First N=1) before Iterative Output.
   • Verified no intermediate tool could produce multiple rows.

4. Added guards:

   • Filtered HTTP_StatusCode = 200 before JSON Parse.
   • Filtered meta logic:

     [total_count] > 0 AND [new_last_ts] > [lastTimeStamp]

   • Updated lastTimeStamp = new_last_ts only after passing the filter.

5. Optimized macro structure:

   • Authorization and Accept headers generated dynamically via Formula + Base64 Encoder.
   • JSON Parse restricted to meta fields for cursor and total count.
   • Data branch outputs client records separately without interfering with the loop schema.

Final Macro Flow

• Macro Input → Formula (auth_plain, Accept) → Base64 Encoder → Formula (Authorization, URL) → Download → Filter (HTTP 200) → JSON Parse → Extract MetaCollectionResult.LastTimeStamp and TotalCount → Formula (update lastTimeStamp) → Filter (loop conditions) → Select (4 fields) → Sample (1 row) → Iterative Output (Loop).
• Data branch: JSON Parse → Filter Clients → Output.

Key Lessons

• Iterative Macro behavior depends on runtime schema integrity, not just what Browse shows.
• Always enforce:
  • One row only to Loop.
  • Exact schema match (names + types).
• Avoid unnecessary complexity in the loop path (Append/Union).
• Use guards for HTTP status and meta logic to prevent silent failures.

Result: The macro now iterates correctly:

• Iteration 1 → …/clients/0
• Iteration 2 → …/clients/5322764819 …and continues until TotalCount = 0.