Formula To Have Dynamic Persistent Location Based On View

[Matthias_F]

This may be outright impossible based on how Airtable is designed to be purely dynamic however, I would love to know if anyone has any solutions.

Here’s the Problem:
I am using Airtable as a Series Bible for my Fantasy Book series. It is a massive world with many plots and subplots, characters, themes etc.
As I am writing the scenes and beats, I’m noticing Airtable is not persistent with the sorting of its records. In other words, I set up a series of scenes, then I filter and sort. When I remove the filter and sort, the scenes are no longer in the order they were created. This is maddening, effectively removing the usefulness of Airtable due to it resorting all records.
Furthermore, I am aware of ‘Autonumbering’, however this isn’t much use when you decide to re-organize scenes or add a scene in between two existing scenes. The numbering immediately becomes irrelevant.
How can I maintain the order of my scenes and still use sorting, but rest knowing it will be restored to the default timeline.

Existing strategies are extremely cumbersome; Numbering each scene/beat manually, or setting a time to each scene or beat. This really gets in the way of the process and if I miss setting the number or time once, the record disappears and I have to go searching for it and setting its numbers.

I am looking for a solution to number the record automatically based on its current place in a view. That way I can re-arrange them and the number will update instead of having to update every record, or I can go into a different view/filter/sort without the number changing.
Is this at all possible?

[Matthias_F]

More testing, and adding new views also resorts records. It’s odd, and might be a bug, but adding a new view resorted the existing view. Very strange behavior. To replicate, I created a new table, then used an auto number field to populate 10 records, then I sorted, filtered, then removed those filters and sorts. Like it should, all data was in the correct order. Then I created a new view. This new view, I assume after the filter and sort, changed the order of all records in both views. Is this normal?

[ScottWorld · Accepted solution]

Yeah, sorting behavior can definitely be a bit strange in Airtable. There are definitely some inconsistencies and/or some bugs with it. (Although I have not experienced your bug of one view changing the sort order of another view.)

In general, if you sort records and choose the “Keep sorted” option, when you unsort your records, your records snap back to their original creation order (you can visually see this by adding a “Creation Date” field). And if you sort your records but DON’T choose the “Keep sorted” option, then they stay in their current order when you unsort your records.

But here’s a little-known trick that many people don’t know, which may help you with your auto-numbering issue. People probably don’t know about this because it doesn’t work 100% of the time. It only works under certain circumstances, which I think is a bug in Airtable.

Under certain circumstances, when you create a brand new Autonumber field, Airtable will (sometimes, when it’s working) automatically number all of your records based on their current order.

So what you can do is this:

1. Sort your records and/or drag your records into the order that you’d like for them to be in. You can drag records around by grabbing the record’s handle on the far left of every row. Note that you can only drag records if the choice to “keep sorted” isn’t selected.

2. Once you get all of your records in the exact order that you want them in, create a brand new autonumber field. Airtable will (sometimes, when it’s working) automatically number all of your records in their current order, which gives you a “snapshot in time” of your current sort order.

3. Then you can go back to that “snapshot in time” whenever you want, by sorting on that autonumber field.

4. BONUS: You can create several of these autonumber fields, if you’d like! So, since you can have many different autonumber fields, you can have “many different snapshots in time”… and you can even name those fields however you’d like.

So when does this trick seem to work? I can’t tell. It has something to do with how recently you sorted your records using that “sort” button, and whether or not you had the “Keep Sorted” toggle turned on or off at the time. It’s rather inconsistent, and I can’t figure out rhyme or reason to when this trick works and when this trick doesn’t work.

Outside of attempting to use this trick, you could also try creating a JavaScript script using the new scripting block that can manually apply numbers to a number field for you by looping through the records from top to bottom. I don’t know JavaScript, so someone else would have to help you with that. That might be your most consistent solution.

And of course, you could always fall back on just manually typing in numbers into a number field to manually order your records. You could use decimal points to squeeze records in between other records, so you don’t need to keep renumbering every record in your entire table.

[kuovonne]

There are many ways of managing sorting and filtering. ScottWorld has one suggestion, and here is another. Hopefully one of these methods will work for your particular use case.

---

How are you keeping track of the scene for each record/row?

Is one row a single scene? You could use single-select field to indicate the scene, and have Airtable sort records based on the scene. When you want to try a different sort, you can re-arrange the sort by customizing the single-select field.

If a single scene is split across multiple rows, you can use an alternate method of sorting within a scene–either a different multi-select field or a number field. When setting the sort, you can sort on one field, and then sub-sort on others. You can also add grouping.

If you turn on the “keep sorted” toggle, you can re-arrange your scenes by changing the value in the scene field, instead of manually dragging the scene where you want it.

---

If this answers your problem, could you please mark this post as the solution?
If not, could you please give a bit more details on your use case and a screen capture?

[Justin_Barrett]

I’ll chime in to address some things that the others didn’t.

Because Airtable is a database, record order is largely irrelevant (as far as Airtable is concerned) because records have no inherent relationship. Records have no connection to other records. In a specific situation, you may want to sort records in a specific way, but as you’ve seen, Airtable doesn’t keep track of record order when they’re not sorted. You can manually drag records into a specific order all you want, but don’t assume that Airtable is tracking any of that for the long haul. Unfortunately the only way to reliably control record order is to create one or more fields where you specify that order somehow, and then apply a sort based on the field(s) you made.

[Matthias_F]

Appreciate the clarity!

[Matthias_F]

Awesome! Yeah that works! A bit of a manual task but simple enough.
I do my work, sort manually, add the auto number and it holds them in that position. When I go and do more work, rearrange, I can essentially reset that sort by changing it back and forth and it renumbers things. Just what I needed!

[ScottWorld]

@Matthias_F Yay! Fantastic! Glad I could help! :slightly_smiling_face: If you don’t mind, could you please mark my comment above as the solution to your question? This will help other people who have a similar question in the future. :slightly_smiling_face: