Split Table Every 500 Rows

Jozef_783863
Jozef_783863 Posts: 331 admin
edited April 2020 in Questions

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.

  1. First we save the trigger attachment file to a data table, then apply a row index table formula.
  2. Second, with some basic math and rounding, we identify the number of times we must copy 500 rows.
  3. 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

Answers

  • Jozef_783863
    Jozef_783863 Posts: 331 admin

    @Thomas_937381 We will give this a shot. Thank you!

  • 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.

  • 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.