This article will explain how to fetch all records in a database's table using a recipe.
Scenario: Your table has more than 1000 rows and you wish to get all these rows in a single run of a recipe (i.e 1 job). However, Workato's "Search row" action has a result size LIMIT of 1000 rows. How do you work around this?
1. Use the "Create List" action under the "List" application.
The size of this list should be equal to 1.a (the total number of rows in your table OR a large number that will always be equal/more than the total number of rows in your table) DIVIDED by 1.b (the Limit size you wish to use in your search result) PLUS 1.
This should be done in formula mode and will hopefully make more sense in subsequent steps.
2. Use a repeat action that loops through the list you have created in step 1.
3. Within the repeat action, add a "Search row" action using a Database connector of your choice.
3i. In the Search Rows step, you need to configure the following fields: Orderby, Limit, Offset.
Orderby: This field will be used to order your search results by a particular field.
Limit: This should be set to the maximum limit of 1000 AND it should match what you've defined in 1.b above in the "Create list" step.
Offset: This should be equal to the Index from the Foreach step Multiplied by the LIMIT value (which is 1000 in this case).
As an example, assume your table has 5500 rows, so your "Create list" will have a list size = 5500/1000 + 1 = 6.5 = 6 (since we are dealing with integers, not floats).
This means your repeat action will repeat itself 6 times.
In loop 1, the search action will retrieve 1000 rows (the Limit) from Offset = Index * 1000 = 0 * 1000 = 0. i.e loop 1 will retrieve the first 1000 rows (0 - 999).
In loop 2, the search action will retrieve 1000 rows (the Limit) from Offset = Index * 1000 = 1 * 1000 = 1000. i.e loop 2 will retrieve the next 1000 rows (1000 - 1999).
This goes on until loop 6, where the Offset = 5 * 1000 = 5000. i.e loop 6 will retrieve rows from 5000 - 5999. As defined at the start of this example, the table only has 5000 rows so this loop will retrieve the last 500 rows.
In subsequent actions, you can decide what you want to do with each batch of rows you have retrieved from the database table.
CAVEAT: Do note that each job in a recipe has a runtime limit of 2000 seconds or 33 minutes. If the job processing time exceeds that, the job will fail before it completes. As such, do not attempt to loop through and process every single row that you retrieve from the search step individually as you will most likely hit the this limit.
This method of retrieving all records in a table should only be used if your target application has a Bulk action. i.e Bulk insert 1000 rows at a time to another app like Salesforce.