Help

Auto Number rearranging based on row index?

Solved
Jump to Solution
340 11
cancel
Showing results for 
Search instead for 
Did you mean: 

I’m not very sure this is achievable or not.
Basically I want to automatically order all records sequentially all the time even i Reorder them.

I have auto-number field.
Screenshot 2022-07-19 at 7.25.11 AM

when I insert new record anywhere or move existing record I want to maintain the sequence, but that was not happening in Airtable.
image

Am I missing any formulae or anything else?

1 Solution

Accepted Solutions

So it sounds like you’re able to sort records via API. Otherwise, this seems to be a good blog article explaining it: The right sort of API updates

When records are sorted, they are in the correct order I suppose. If you have 100 records and you want to add a 101st in 44th place, then I would splice() that record into the array at that place. Now you have 101 items in the array and it sounds like that you want to change the “order number” every time you do this. So given your sorting order, the order number is the index in the array.

You will then have to update all records accordingly. I’d create an updateRecordsArr first by mapping over your sorted records array ({id: …}, fields: {“field 1”: …, “field 2”: …}).

Does that make sense to you?

See Solution in Thread

11 Replies 11

Hi @Manohar_Manu,

auto-number gives a new record the next higher number and that number is not changed if the sort order of the table changes. That’s how auto-number is supposed to work. It creates a unique identifier so to say for that created record.

You want to change the number every time the sort order of a table changes. This is only possible with a script in my opinion, but also not really necessary as you have the number already based on the “row number” on the left.

Why do you want to do that? I think that would make it clearer.

@Rupert_Hoffschmidt
I want to access that row number in my API, I believe that default row number is cant be accessed through API. If yes let me know

and I guess script only way I can achieve this. Please let me know if there was any script available RN?

Thanks in advance.

Via scripting API it’s easy to get the “row number” because it’s just the index number of the item in the array.

What’s your use case? You’re rearranging these records constantly or is there a specific sorting order to them?

Have your API request the records from the specific view, not just from the table. Then the records will be in the view order and you will not need a separate field to determine the order.

@kuovonne
@Rupert_Hoffschmidt

So I have set of Records, I want to retrieve them in the order that I placed in the table, but API records response giving me in random order so, I’m using this field to sort them when I requesting the API using sorting method. That is the usecase, so that is why I’m using a separate field. Its working fine I have no issue.

But let say I have 100records all order sequentially in the table, now if I have to add lets say a new record in 44th place in the table, as Im use auto number that new record will be having OrderID as 101, so when I call the api I will receive that new record in 101th place instead of 44th place.

That was the challenge I was facing.

If you specify the view in your request, the records are not in random order. They are in view order.

Does this also work if the records are grouped on the view?

I don’t know. If the REST API works the way the scripting used to work, records are not grouped. When getting records via scripting, the records are sorted correctly compared to others in the same group, records in the same group are not necessarily together.

So it sounds like you’re able to sort records via API. Otherwise, this seems to be a good blog article explaining it: The right sort of API updates

When records are sorted, they are in the correct order I suppose. If you have 100 records and you want to add a 101st in 44th place, then I would splice() that record into the array at that place. Now you have 101 items in the array and it sounds like that you want to change the “order number” every time you do this. So given your sorting order, the order number is the index in the array.

You will then have to update all records accordingly. I’d create an updateRecordsArr first by mapping over your sorted records array ({id: …}, fields: {“field 1”: …, “field 2”: …}).

Does that make sense to you?

Yes It make sense, that should work.

Thanks.

Keep in mind that you can update only 10 records at a time using the REST API, with a rate limit of 5 requests per second. If you have a lot of records, that will take a while. Plus, you can get only 100 records at a time to begin with, so if your table might have several hundred records, this whole process could take a really long time.