Hi all. I'm a non-Developer trying to do a nested loop in a Job but can't get it to work correctly. Here's what I need to do:

  1. Call a REST API to get a list of Account records and add the rows to the Result
  2. For each Account record in the Result, call a 2nd REST API and retrieve up to 500 rows
  3. If there are still more rows to retrieve from the 2nd API for the Account record, call the REST API again using the "NEXT PAGE URI" that came back from the previous request
  4. Continue retrieving rows from the 2nd API for the Account record (500 at a time) until there are no more to retrieve
  5. Repeat the process for the next Account record in the Result.

So far I have a single Job that calls 2 transformations (1 to get the list of records for the result, 1 to retrieve rows for each record in the result). The job is structured to loop for each Transformation if the previous call didn't return all of the rows available. The 2nd transformation in my Job is also set to execute for every input row from the previous transformation. It's not working correctly whenever the 2nd Transformation needs to make multiple calls to retrieve all of the Records for a single Account. I think the problems are from trying to do a loop back to a Transformation that already has the "Execute every input row" option selected.

Is there a better way to do this? Any help would really be appreciated.