API -Iterate every 1000 records and Automate

Question

Hi,

Please can someone help me.

I am trying to create an API get command to Iterate every 1000 records from a SAP database.

My Get command has 3 tables in the URL:

These tables are related to each other with an ID number, the ID is unique and looks at this for the next table

tableC – ID number

tableM – ID number

tableS – ID number

I want to automate this where tableC  loops (looks at all IDS matches this to the next tableM and then matches to tableS) this continues until all records have been linked and collected.

The maximum of records is 1000 per GET call.

My URL looks like this XML and not JSON - the xml is set to auto detect child and returns child entries.

https://api.company.com/server/tableC/RKQ3UZs2ss41/tableM/4ZJ6I7W-Pg01/tableS/FIkeC4f0XLA1

The connection works is a manually add the IDs which only retrieves information in bits.

I want to automate this, any advice or guidance would be appreciated.

ChBurger · Answer

Hey dbhamber,

I would recommend utilizing the skip token within the data if available. I integrate with a SuccessFactors oData API and I was able to create an Iterative macro, which will run one or many times and process the 1000 records returned at each iteration. It then checks for the skip token and if found processes the next feed of data using the Engine.IterationNumber to keep track of the iterations (pages of data) we've processed. Once there is no skip token it returns the entirety of the dataset.

I would recommend getting all table data, then merging the data later using a join tool. Remember, if you need all data from one side of the join and the data joined in you must union those two tracks or you will lose data you want/need.

Cheers,

Chad

rkapoor · Answer

@dbhamber I am wondering if you can wrap the API call within a macro, and leverage pagination in the workflow.  This will allow you to read in your data in batchs and iterations (if done as a batch macro or as an iterative macro) and ensure you read all of your data in too.  Here is a useful article on API calls and Pagination:

https://www.thedataschool.co.uk/laine-caruzca/api-pagination-in-alteryx#:~:text=This%20is%20especially%20useful%20when,the%20rest%20of%20the%20data.

This article may be helpful for you too:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Iterative-macro-for-API-pagination-retrieval/m-p/34480#M13730