re-calculating the primary key column when inserting a new record to retain the ordering

[Liam_Garrod]

Hi there,

I would like to use a formula in the primary key column of an airtable data set to create a unique ID, or autonumber, but i want that number to recalculate when i insert a new row anywhere in the master data set so that the ordering is retained. 

if you use the autonumber field type, it will create the record using the next available number, then all my views start to loose the ordering i want.

is there a way to write a formula that will re-calcuate the primary key column to retain the existing order, with the new record in place. 

For example:

1 TestA

2 TestB

3TestC

If i insert a row between TestB and TestC, the primary key would show as follows:

1 TestA

2TestB

3Testnew record

4TestC

[TheTimeSavingCo]

I don't think so I'm afraid; the only way I can think of doing this is with a script

[Liam_Garrod]

Is there any examples of a script for this? 

Ideally, id like to be able to use one tables ordering to dictate another tables order based on a linked field. 

I have a table that has the master set of requirements, then i have another table that uses those requirements via a linked field, and within this second table there are multiple records depending on a country and how they use the requirement. Id like my second table to retain the ordering of the requirements from the first table. so that when i add new entries to this table via a form, it retains the same order as the master table. this is becuase i output views onto confluence pages and it would be easier reading to stay in order

[kuovonne · Accepted solution]

Each grid view can have its own sort order. Because formulas have the same value across all views, formulas are not aware of the sort order of a particular view.

One option is to have an editable numeric field for the {sort order} and include that in the formula of your primary field. You would need to manage updates to the {sort order} field manually, with a script, or with an automation.

However, you should also consider why you want to have this sort order number in your primary field. The fact that you want one makes me think that you are not taking advantage of Airtable's ability to have different views with data sorted in different ways so that you can look at the data from different perspectives. While it is good to have a unique primary field, it is not strictly necessary and there are many ways of getting a unique primary field.

[Liam_Garrod]

thank you so much for explaining that! i was being stupid and over thinking this problem.. i was able to create a "SORT ORDER" column in my requirements table as a numeric and then use that to filter all my views without over complicating it. thank you!