I had a database from another location that had an existing {Item#} field. I want to keep and continue from the last existing Item# with autonumbering. The problem is that when I change the Item# field to autonumber, it renumbers from 1. Is there anyway to change the field to autonumber and then revert the original numbers?
Solved
Change Field to Autonumber but keep existing input
+1Best answer by ScottWorld
There are several different ways of accomplishing this in Airtable.
One method of accomplishing this requires an automation, but since Airtable limits you to only 50 automations, I’m going to show you a very simple, easy, and straightforward way of doing this that doesn’t eat up one of your limited automations.
You will need 4 fields to accomplish this:
- “Autonumber” - use Airtable’s autonumber field type for this.
- ”Autonumber adjusted” - this is a formula field. Formula is below.
- “Item # from old system” - make sure that this is a number field type. All of your old item #’s must appear in this field as numbers, not text. This field is ONLY used for old items that you have carried over from your old system. This field will be left empty for any new items that you create in your new system.
- ”Item # for new system” - this is a formula field. Formula is below. From this point forward, this is the field that you will ALWAYS refer to whenever you want to see the item # for ANY of the items in your system, either old items or new items. From this point forward, you will only reference this field to see the item # of any item.
So, let’s say you want your future item #’s to start at 810 and increment from there.
First, you will need to create an autonumber field called “Autonumnber” which will automatically start at 1.
That autonumber field will end at some random number, depending on how many total records currently exist in your table (i.e. depending on how many records you’ve deleted in the past).
So, for the sake of example, let’s pretend that your autonumber field ends at 775. That would mean that your next new item in Airtable would become 776, which is NOT what you want because you want your next new item to become 810.
So you need your existing records to end with 809, so your next new record can start with 810. That means that you need to increase the autonumbers by 34 to get the last existing record to become 809.
So, in your “Autonumber adjusted” formula field, you would use this formula:
{Autonumber} + 34
Finally, in your “Item # for new system” formula field, you will determine whether or not you need to use the item # from the old system or use the adjusted autonumber as your item #.
This is your formula for “Item # for new system”:
IF(
{Item # from old system},{Item # from old system},
{Autonumber adjusted}
)
And that’s it!
From this point forward, you will refer to this formula field for ALL of your items in your system, regardless of whether they are an old item or a new item.
Hope this helps!
If you have a budget and you’d like to hire the world’s best Airtable consultant to help you with this or anything else that is Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld
+32I’d suggest creating a formula field and adding the difference to the new Autonumber field!
And so if the original number was 1000, then in this new base we’d:
- Create an Autonumber field
- Create a Formula field with ‘{Autonumber} + 1000’
This would make the first record of the new base 1001, does that make sense?
We could also add 1000 records and then delete them too, just need to make sure there aren’t any automations that might get triggered by that
+1Thanks so much for the response but I'm really sorry apparently I've explained this poorly.
What I have right now is a database that had an existing Item# number for the current entries, the first one starts at number seven and ends at somewhere around 809 (Because several have been deleted since the database was started).
When the database was created in Airtable Item# was created as a regular number field. I want it to be an autonumber field instead. The problem is when I set Item# type to autonumber it resets all of the numbers in that field to start from number 1. I want to keep the existing entries and then have it autonumber any new entries starting from the existing last number.
Is there anyway that I can change the item number field to be an autonumber type but keep the existing entries with their existing numbers and then when a new entry is added it just starts at number 810 and continues on from there?
+35
There are several different ways of accomplishing this in Airtable.
One method of accomplishing this requires an automation, but since Airtable limits you to only 50 automations, I’m going to show you a very simple, easy, and straightforward way of doing this that doesn’t eat up one of your limited automations.
You will need 4 fields to accomplish this:
- “Autonumber” - use Airtable’s autonumber field type for this.
- ”Autonumber adjusted” - this is a formula field. Formula is below.
- “Item # from old system” - make sure that this is a number field type. All of your old item #’s must appear in this field as numbers, not text. This field is ONLY used for old items that you have carried over from your old system. This field will be left empty for any new items that you create in your new system.
- ”Item # for new system” - this is a formula field. Formula is below. From this point forward, this is the field that you will ALWAYS refer to whenever you want to see the item # for ANY of the items in your system, either old items or new items. From this point forward, you will only reference this field to see the item # of any item.
So, let’s say you want your future item #’s to start at 810 and increment from there.
First, you will need to create an autonumber field called “Autonumnber” which will automatically start at 1.
That autonumber field will end at some random number, depending on how many total records currently exist in your table (i.e. depending on how many records you’ve deleted in the past).
So, for the sake of example, let’s pretend that your autonumber field ends at 775. That would mean that your next new item in Airtable would become 776, which is NOT what you want because you want your next new item to become 810.
So you need your existing records to end with 809, so your next new record can start with 810. That means that you need to increase the autonumbers by 34 to get the last existing record to become 809.
So, in your “Autonumber adjusted” formula field, you would use this formula:
{Autonumber} + 34
Finally, in your “Item # for new system” formula field, you will determine whether or not you need to use the item # from the old system or use the adjusted autonumber as your item #.
This is your formula for “Item # for new system”:
IF(
{Item # from old system},{Item # from old system},
{Autonumber adjusted}
)
And that’s it!
From this point forward, you will refer to this formula field for ALL of your items in your system, regardless of whether they are an old item or a new item.
Hope this helps!
If you have a budget and you’d like to hire the world’s best Airtable consultant to help you with this or anything else that is Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld
+1This seems to have worked very well. Thank you so much for your help I can't tell you how much I really appreciate this!
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.