I want to add an additional column to a query that shows the row number of each row in the query.
In access I used a function in a macro as follows (adapted to Access from here https://www.experts-exchange.com/questions/20492559/Display-record-row-number-in-a-query.html):
Function GetRowNum(strQueryName As String, strIDField As String, varID As Variant) As Long
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("SELECT [" & strIDField & "]" & " FROM " & strQueryName, dbOpenSnapshot)
Select Case rst(strIDField).Type
Case dbBigInt, dbBinary, dbBoolean, dbByte, dbCurrency, dbDecimal, dbDouble, dbFloat, dbInteger, dbLong, dbSingle, dbVarBinary:
rst.FindFirst "[" & strIDField & "]=" & varID
Case dbChar, dbText, dbMemo:
rst.FindFirst "[" & strIDField & "]='" & varID & "'"
Case dbDate, dbTime:
rst.FindFirst "[" & strIDField & "]=#" & Format(varID, "mm/dd/yyyy hh:nn:ss") & "#"
End Select
GetRowNum = rst.AbsolutePosition + 1
rst.Close
Set rst = Nothing
End Function
Then in the Query, I add a column with the following SQL:
GetRowNum("MyQuery","MyIDField",[MyIDField]) AS SortA
Where MyQuery is the name of the query you are calling the function from and MyIDField is the name of the primary key field.
How do I achieve this in Base? Is there a more straightforward method of adding a row number to a query?