topic Re: Pagination of API endpoints with Workato in Workato Pros Discussion Board

Pagination of API endpoints with Workato

michielb — Tue, 21 Dec 2021 23:05:28 GMT

I'm running into a couple cases where I need to process a lot of data out of an API (like all projects in Jira, or all channels in Slack), and these endpoints tend to be paginated.

I've been playing around with the repeat action, but that takes a fixed length list to iterate, and with paginated data you often don't know how many interations you're going to have to do. I guess I could set up a helper list with 50000 iterations, and stop when I'm done, but it feels like there should be a better solution.

I haven't been able to find any good design documentation/tutorials on how to work with paginated data in Workato. Does anyone know where to find that?

Thanks, Michiel

Re: Pagination of API endpoints with Workato

clifford — Tue, 21 Dec 2021 23:09:34 GMT

often the API will tell you total records as well as the page size. You can use this in the create list helper step, as a formulia. total/pagesize. BTW it rounds correctly, UP.

Re: Pagination of API endpoints with Workato

jblanchett — Tue, 21 Dec 2021 23:29:20 GMT

If the API tells you how many records, or better yet, how many pages then you can do this:

1. Call the API once

2. Create a helper list with size = number of pages (or number of records/page size)

3. Iterate through the helper list with a FOR EACH loop

4. Within the for each, call the API and set the page_num = the sequence number of your for each loop

After this if you can do whatever you want, either process the records in the for each loop or add them to the end of a pre-defined list so you can process them when the for each loop completes.

If the API doesn't tell you how many records it's going to return, then that's truly unfortunate, but manageable. You can define a variable as a high number to start with to mimic the number of pages in step 2 above. But then, you can avoid calling the endpoint an unnecessary number of times by saving the latest number of records returned from the endpoint within your for each loop as a variable, and use an IF condition within the for each to only call the endpoint if the most recent number of returned records was > 0, or if the number of returned records is equal to the max page size if you know that information.

Re: Pagination of API endpoints with Workato

jblanchett — Tue, 21 Dec 2021 23:33:16 GMT

It would be great if Workato offered a WHILE loop to handle this unknown number of pages, but they don't. I suspect they did that to avoid unintentional, long running jobs

Re: Pagination of API endpoints with Workato

steven-marissen — Tue, 21 Dec 2021 23:44:39 GMT

You still got to make sure it can get processed within the allowed timeframe. In this case I think it is 90 minutes or it will just time-out.

Re: Pagination of API endpoints with Workato

mike-flynn1 — Wed, 22 Dec 2021 00:14:39 GMT

One strategy we have used for large lists that may require lots of calculations (like analysis on all POs for the entire year) is to do something like this:

Recipe 1 (Controller Recipe) - determines number of pages and kicks off the individual processes (or for an unknown number of pages monitors for when the process is complete)

Recipe 2 (Grunt Work Recipe) - callable recipe that gets each page of data and stores data in a temporary location (potentially doing some calculation/transformation)

Recipe 3 (Clean-up) - once all the pages are processed the controller recipe will start the clean-up recipe to create the final deliverable

We have built recursive-style processes using workato pub/sub and we have daisy-chained multiple processes of these together to do multiple large scale extractions and transformations.

Generating the 1099 report is a good example. This process has multiple large scale extractions that are dependent on each other. We had to get:

1. all vendors that are 1099-able out of NetSuite

2. all accounts in NetSuite that are designated as 1099-able

3. all bills for the vendors

4. all payments for those bills

5. calculate the percentage of lines on the bill that were 1099-able, and what percentage of the payment was applied to each line to get the 1099 amount

6. after all the calculations were complete - do the roll up to calculate the 1099 amount per payment, per bill, and eventually per vendor.

We didn't know the number of vendors, bill, or payments and had to track all of this independently (in addition to dealing with delays in getting data out of netsuite and doing calculations). The process was 100% automated (click "Start" and then 20 minutes later you received the report of all 1099s for vendors).

Re: Pagination of API endpoints with Workato

prerak-rustagi — Wed, 22 Dec 2021 01:10:46 GMT

The design that we use is to move the logic to a callable recipe and perform one job per page of the API call. When all the records in the page are processed, call the same recipe with the next page URL (you can call callable recipe xyz). Stop the execution when the last page is reached.

- Main Recipe: MR_1: Can run on a schedule. It will async call CR_1.

- Callable Recipe 1: CR_1 - This recipe simply calls another callable recipe (CR_2 - Async)

- Callable Recipe 2: CR_2 - This recipe will have an input parameter "page_url" that can be used to make a paginated call and then process the records.

If CR_2 recieves another page, then it will call and pass the page_url to CR_1 which will call CR_2 again. This process will continue unless you reach the last page.

Additionally, if you have huge volume of data, then create another Callable Recipe CR_3 to process individual records. CR_2 can pass the data for each record to CR_3. Pros: Use concurrency to increase 5x throughput, Better error debugging as each record will have it's own job.

Re: Pagination of API endpoints with Workato

mroldanvega — Wed, 22 Dec 2021 03:15:19 GMT

It would be great if we could have the ability to call a callable from itself. Deven Maru

Re: Pagination of API endpoints with Workato

jeremyo — Wed, 22 Dec 2021 04:09:05 GMT

I had to do this recently. Everyone's advice is great. I just wanted to point to some recipe examples that show this. This post has two good examples:

Recipe pagination examples : Workato Success Center

Re: Pagination of API endpoints with Workato

michielb — Wed, 22 Dec 2021 05:59:54 GMT

That sounds like quite the contstructions. I'm just trying to pull data on a couple thousand slack channels! 😄

What's been your transient storage system of choice for all that data?

Re: Pagination of API endpoints with Workato

mike-flynn1 — Wed, 22 Dec 2021 08:02:40 GMT

Sometimes lookup tables if the data doesn't return too many columns or multiple relationships. For more complicated use cases we use a MySQL database.

Re: Pagination of API endpoints with Workato

rbflood — Wed, 22 Dec 2021 11:04:53 GMT

We do this with a few recipes. One thing I think Workato sshold consider is building this pagination into the connectors themselves directly. With the billing model including number of actions, doing these paginations with helper lists can create some pretty huge action numbers from a recipe run making these recipes quite expensive. I can't think of any use cases outside of testing where it would make sense to not retrieve all of the results and the logic to figure these paginations out really inhibits the idea of citizen develpment as it can get pretty complex.

For example, Okta will paginate group members and you need to dig through the http header with a custom action to get the link to the next result page if it exists which is different than a lot of other APIs that simply add the next page or whatever into the body attributes. We use helper lists for this and it wrks but consumes a lot of actions.

A much more challenging use case is BigQuery. We have a recipe that runs a query that results is a relatively large file we need to SFTP to a vendor. BigQuery will paginate on number of records OR size of data. In this case we are retrieving a lot of columns so we hit the data limit first (which btw took a very long time to figure out). So if you add another column to your query the number of results will change. In this case we had to create a helper list to take the total results and divide by the number of rows returned to know how many iterations were needed to get the full results since it can be different depending on the run and the data returned. We create an entry in the helper list to represent the iteration so it's usually just a list with like 3 or 4 rows.

It's possible to do all of this inside recipes but it seems like it would be a lot more trivial to do behind the scenes in the ruby built into the connector itself and also limit the number of actions we need to consume to run these things and not make us figure it out on our own.

Re: Pagination of API endpoints with Workato

james-ng — Wed, 22 Dec 2021 12:30:46 GMT

I have a simple implementation using "flag":

(1) create variable "flag"="" (empty string)

(2) create helper list 100, assume max 100 pages

(3) iterate through helper list

(4) inside loop:

(4a) if "flag" does not equal "pagination_end", call API and process the data. For example start pagination with ([index]*50)+1 for 50 records each page.

(4b) if list size < 50, update variable "flag"="pagination_end"

(5) after pagination the recipe can continue

This will ensure that the remaining iteration will be skipped while the recipe continues.

See example: https://app.workato.com/recipes/1969723?st=7b3e58

Hope it helps!

Re: Pagination of API endpoints with Workato

michielb — Thu, 23 Dec 2021 03:43:59 GMT

Yeah, it feels like there should be some control mechanism to support pagination. I suppose you could build a custom connector to handle the case, but that's somewhat defeating the point of a low-code system.