Split Table Every 500 Rows
I shared my initial setup image below for feedback on this building approach.
Questions:
- Have you encountered the need to split a table by a set row count?
- Have you used a similar building approach or a different one?
Background:
For every 500 rows in the trigger attachment spreadsheet, I need 1 data table. 10k rows is the max spreadsheet row count, totaling 20 output tables at most in some cases.
- First we save the trigger attachment file to a data table, then apply a row index table formula.
- Second, with some basic math and rounding, we identify the number of times we must copy 500 rows.
- Third, we start a subprocess for looping.
Looping Subprocess:
In this loop, we use a dynamic filter to filter the trigger attachment table. (First loop filters rows 1-500, second loop filters rows 500-1000, etc. until the loop completes.) Every time we produce a 500 row table, we save it to a separate data table. We then increment a loop counter field, and re-run the loop. Once the loop completes, the output should be a data table that contains all the data tables output from the loops.
Best Answer
-
@Jozef_783863 What about using Tables: Apply formulas to columns to group rows, as you've done? You could do something like:
if ((columns['rowIndex'] > 0) && (columns['rowIndex'] < 500)) { columns['Row Group'] = 1 }
Once you've done that, you can use Tables: Create table for each unique value from the Row Group column. Should solve for the problem with fewer steps. Hope that helps!
6
Answers
-
@Thomas_937381 We will give this a shot. Thank you!
0 -
Hi Jozef, hi Thomas,
thank you for this great solution approach! How do i reference the right data table in the helper bot?
With Create table for each unique value you get a new data table with three columns: unique value, count and Table ID. The last one contains the link to the table I want to reference in the helper bot. By using {{Table ID}} as reference I get an incorrect handlebar error mail, because its just a link or the table ID but not the required data table it self.0 -
Dear both,
I found the solution by trying somethings out by my self. Just had to use {{table-id}} as field reference in the helper bot. This way the helper bots automatically uses the correct data table.1