Can you customize auto numbering with different prefixes

[Roxanne_C_Hare]

Hello everyone, I am really impressed with Airtable and want to roll it out to our team. However, I want to get some questions answered before presenting.

Every job we do has an assigned number to it. I would like to use the autonumber feature in order to assign numbers to our jobs. However, we need to be able to customize this. For example, not starting the autonumbering with 1.

Also, we have different prefixes for each type of job. Some of them include MISC, R, and P, and some have dashes. The jobs look something like this: P3765, MISC-377, R451. We need a numbering system to create new numbers so that we’re not duplicating numbers, as this would be a nightmare. Regarding that, I’d also like some sort of error message to pop up if someone tries to manually enter a number that’s already been used.

Can the autonumber feature handle something like this, and if so, how?

Thank you so much in advance.

[kuovonne]

You can use an autonumber field in conjunction with a formula field. The formula can add an offset to the base autonumber and also tack on a prefix.

{prefix} & ( {autonumber} + 300)

If you choose this method, all the numbers will be unique across prefixes. If you delete a record, you will end up with a skipped number. This bothers some people, but not others.

If you want each prefix group to have its own running number, you must use an editable field and automations to manage the numbering. There are various techniques for setting up these systems. Usually they also involve having the number and prefix in different fields and an automation to manage the numeric part. Some involve scripting and others involve a special control table to manage the number. A key limitation of these systems is that if two records are created at the exact same time, you can end up multiple records with the same number.

[Roxanne_C_Hare]

Thank you! I really don’t think two things would be created at the same time, however, as of now, I am the only person assigning numbers to our jobs. I’m wondering if it would make sense to continue to have one person controlling this aspect of our job flow, or to allow everyone on the team the ability to assign their own job numbers. Food for thought.

[kuovonne]

Roxanne_C_Hare:

I’m wondering if it would make sense to continue to have one person controlling this aspect of our job flow, or to allow everyone on the team the ability to assign their own job numbers.

The answer to this depends on how much do you trust everyone on the team to follow the rules when creating their job numbers. It will be less work for you if they create their job numbers properly. It will be more work for you if they mess up and you have to fix things.

If you don’t trust others to create proper job numbers but need an editable field to hold the job number, you can set the field permissions to prevent others from changing the editable field. (Note that even though people may be highly competent, if they have many responsibilities, it might be easier to take the task of generating job numbers off their plate.)

Roxanne_C_Hare:

I really don’t think two things would be created at the same time

Then an automated system involving an editable field is probably the best, assuming that you have enough automation runs to spare for this. Set the field permissions so that only you and the automation can change the editable field. This way others cannot accidently change it, but you can fix things in the unlikely case that two are created at the same time.

[Roxanne_C_Hare]

hi kuovonne, i love your detailed response. going back to your suggestion about using an autonumber field in conjunction with a formula field, if there are different prefixes, would this technique need to be applied to separate columns, or could it be applied within the same column? also, since we already have numbers created, could the formula start being applied after the latest number created, or would it have to start from “the beginning”? i hope these questions make sense! thank you in advance!

[kuovonne]

Roxanne_C_Hare:

i love your detailed response.

Thanks!

Roxanne_C_Hare:

if there are different prefixes, would this technique need to be applied to separate columns, or could it be applied within the same column?

A formula can reference several different columns. You might need some nested IF statements, and maybe some SWITCH statements.

Roxanne_C_Hare:

since we already have numbers created, could the formula start being applied after the latest number created, or would it have to start from “the beginning”? i hope these questions make sense! thank you in advance!

The formula itself will apply to all records in the table. However, you can have the formula look at the created date/time of the record and output something different for records with an earlier date/time. This can get a bit ugly bit it does work.