Today I encountered a puzzle that I was surprised I had not encountered sooner in training. I wanted to create a recordID that only increases every x rows.

Tools required:

To create this type of field first we want to use a recordID tool configured to start from 0 - this should become clearer in the next step:

Now we want to create a field that identifies the position of the row within the desired groups of x. We use the MOD function in a formula tool:

Here is where we set the desired group size. Now you will notice that whenever we get to a multiple of 5 we get a 0 then the numbers following 5. This leverages the fact that the MOD function returns the remainder of a division of recordID divided by the number you specify after the comma. Leaving us with something like this.

We now want to calculate the groupID and will have to refer to multiple rows thus we need a multi-row formula.

In the multi-row we want to create a new field called groupID, we then want to say if for the recordID (now indicating the position within a group) = 0 then add a 1 to the previous groupID otherwise just copy the groupID from the previous row.

The result:

This can then be used in a later tool that requires grouping information together.

A business use-case of this technique is webscraping and wanting to keep multiple name value pairs together within a group. For example the webscraped information could return 5 name value pairs for each town and you eventually want each record to be a town.