Iterative Marco for paginated API responses with FIRST variable

[mshatek]

Hi all,

I have looked through many of the articles on using iterative macros to retrieve paginated results from a API, however, my API does not seem to have a next page, or next cursor with it.  The instructions say to use $$FIRST so the first three calls would look like this: 

1.  https://XXX.my.workfront.com/attask/api/v14.0/hour/search?entryDate_Mod=gte&entryDate=$$TODAYby&$$FIRST=1&$$LIMIT=2000&fields=*&ID_Sort=asc

2.  https:/XXX.my.workfront.com/attask/api/v14.0/hour/search?entryDate_Mod=gte&entryDate=$$TODAYby&$$FIRST=2001&$$LIMIT=2000&fields=*&ID_Sort=asc

3.  https://XXX.my.workfront.com/attask/api/v14.0/hour/search?entryDate_Mod=gte&entryDate=$$TODAYby&$$FIRST=4001&$$LIMIT=2000&fields=*&ID_Sort=asc

and so on!

I am trying to compose my url by incrementing a variable but I'm getting stuck on the variable being of type Byte, not integer.  

This is my input to the macro:

Macro Input

There is also no way to know how many iterations there should be in the API, so I created a flow that counts the possible number of records, but I don't know how to bring that into the iterative macro.  Here is the flow with both paths.

Current Flow

Also, do I need to pivot and transform the data BEFORE I output to the macro or can that be done after?  Ultimately I will need to pull in over 500K records with 50+ columns of associated data each.  I don't need all 50 but I probably need 15.  Then I need to feed it all to power bi!  Newbie here!  

Any help would be GREATLY appreciated.  Thank you!

[apathetichell]

Run Count first. divide by your limit.

Using Count

You can use count to return the number of results that match your query. This can be useful when you don’t need the data in the results. By returning only the count, the server can process the request more quickly and save bandwidth. For example, the request

GET /attask/api/v15.0/project/count?status=CUR

{
    "count": 3 
}

Count gets you count. divide count by limit - dynamically create successive calls via generate rows/etc. 

[mshatek]

Thanks, I'm able to get the count, but then how do I use it in the macro?  

[apathetichell · Accepted solution]

let's say [count] returns 500000 

you know your api structure is: 

1.  https://jlltransformational.my.workfront.com/attask/api/v14.0/hour/search?entryDate_Mod=gte&entryDate=$$TODAYby&$$FIRST=1&$$LIMIT=2000&fields=*&ID_Sort=asc

you use your first download tool just for count. you now have count. you use generate-rows to generate a row for ceil([count]/2000) entries. you add a record id tool. you now modify your query so that first=tostring(([RecordID]-1)*2000)+1) LIMIT=tostring(RecordID*2000)) in your modified api call. you then add a new download tool. it runs for N entries where N is the ceil of [count]/2000.

you do not need a macro.

[mshatek]

Thank you that worked!