Stumped - Trying to call an API for every record in a table

[JustinBatt]

I have a list of records that represent a unique identifier that I'd like to turn in to a URL, call an API, parse JSON and commit to a local database. I've tried a number of different methods, but the best I've been able to manage is inserting 1 record to my database.

This is my latest more successful workflow, but it only commits the last record.  

I've done pagination but the API in question doesn't give me a "next record".  I'm actually trying to work around a limitation of the API

Any help would be much appreciated

Justin

[BrandonB · Accepted solution]

If you were to run each URL through the download tool individually does it return those results as you would expect? If so, I might suggest turning this workflow into a macro and passing the URLs in using a control parameter. Having worked with quite a few APIs, what is most likely happening is the download tool is calling all of the results, the JSON parse may be working as expected, but crosstabbing the data may be aggregating each set of results within the same cells either with a sum or concatenation, whatever is chosen. If you were to instead run one URL through at a time, each set of results would be parsed independently 

[JustinBatt]

It's on my to-do to learn batch macros

But I'm happy to report my problem is solved.  I didn't have an appropriate group by, the earlier version of this workflow had a unique identifier in the API.  When that was removed, the crosstab aggregation took 100 records down to 1.  

Thanks!

[BrandonB]

Awesome, great work!