Blog

Formulas - Generating Row Numbers - Part 3

A common requirement is to generate sequential row numbers. In this post, I highlight a great technique that Randy Hayes demonstrates, which carries out this task.

A common and important requirement is to generate sequential row numbers. This is a topic that I have posted about previously.


Historically, this task has been complex, but the introduction of the Sequence function in 2020 makes it much simpler. A great demonstrate is the following video by Randy Hayes (which I recommend watching).
https://www.youtube.com/watch?v=v66-rwPkVvA

In this video, Randy describes how how to apply alternate row colours to items in a gallery control. This process requires a sequential row number to provide a basis on which we can define odd and even rows. We can then the apply the row background colour based on whether the row is odd or even.

https://www.youtube.com/watch?v=v66-rwPkVvA

The great thing about this technique is the short and succinct formula to number the rows in the data source.

With({data: DataSource},
     ForAll(Sequence(CountRows(data)),
            Patch(Last(FirstN(data,Value)),
                  {rowNumber:Value}
            )
      )
)



We can use this formula in the items property of a data control (such as a gallery), or we can incorporate it with a call to Collect/ClearCollect to fill a collection.

In essence, this formula calls the sequence function to generate a sequence of numbers which matches the number of items in the data source.The call to ForAll loops over the sequence, and produces an output table that includes a rowNumber field against each record.

In cases where we need to generate row numbers and we're not too concerned about sequencing the output in a precise manner (because ForAll can process rows in parallel), this offers a great solution that is short and simple.
Previous
Next