Greetings fellow Gristers!
Would anyone know of a method to get and set the row ID (1…6 in the image)?
In the image, the table has been filtered and sorted to give 6 records (1…6). I am looking to manually change the order in which these records are displayed.
The $id maps to the records. But if filtering/ordering is used, the table has a unique set of Table Row display IDs (1…6).
The solution i have presently, is to have a column with integers ($Order), where the table is sorted using the $Order entered here. It works fine, until I need to insert a record in the middle of records (e.g. between 2 and 3). At this point I will need to change the value in the Order field for all records starting from 3 to 6.
The row IDs are permanent (no way to change them). But Grist does have a way to rearrange rows: when rows are not sorted by any field, you can drag them to move relative to each other.
Internally, this order is maintained using a special column called manualSort, which contains floating-point numbers (not always integers), so that rearranging doesn’t require updating that helper column for many rows. But that column isn’t helpful if you need incrementing integers.
What you may need is a feature that’s in the works, to support ordered lookups in formulas better (e.g. PREVIOUS(rec, sort_by="Date")). It may include a function like RANK(rec, sort_by="Date") or RANK(rec, sort_by="manualSort"), which would return the integer index of a row in a sorted list. Would that be what you are looking for?