Formulas - Generating Row Numbers

There are often times when we want to generate and display row numbers against records. As an example, it can be useful to display a sequential number against a list of customer invoices like so:


Let's assume that our underlying data source is called Invoices. In the OnVisible property of our screen that contains a gallery control, we can use this formula to add a row number field.


Clear(colNumberedInvoices);
ForAll(Invoices,
   Collect(colNumberedInvoices,
          Last(FirstN(AddColumns(Invoices,
"RowNumber",
CountRows(colNumberedInvoices)+1
),
        CountRows(colNumberedInvoices)+1
)
         )   
)
)

This formula calls the ForAll function to loop through the Invoices data source. During each iteration, the code collects a row into a collection called colNumberedInvoices. It collects a row from the Invoices data source, and calls the AddColumns function to add the RowNumber field.

Here's how this works more precisely.The purpose of the Last(FirstN syntax is to select the full row during each iteration of the loop. Since we're collecting rows into colNumberedInvoices one at a time, the number of rows in colNumberedInvoices at any time in the loop provides a count of the number of records that we've already looped through. Calling the FirstN function with a row count of CountRows(Invoices)+1 returns all rows in Invoices that we've already looped through, plus one. Passing this result to the Last function therefore returns the current row.

On our screen, we can now then add a gallery control and set the Items property to colNumberedInvoices. To display the row number, we can add a label and set the Text property to RowNumber.


This is the pattern that most app builders use to number records. However, there is a caveat to this code. It's important to note that the ForAll function isn't guaranteed to run serially. To increase performance, PowerApps can execute the commands inside a ForAll loop in parallel, so this approach may not generate the row numbers in the expected sequence if we were to sort the input data source by a particular column. In practice however, this pattern does appear to work reliably.