Help

Using Formula to Create Autonumber

Topic Labels: Formulas
Solved
Jump to Solution
11965 10
cancel
Showing results for 
Search instead for 
Did you mean: 
Paul_Dezzutto
5 - Automation Enthusiast
5 - Automation Enthusiast

Is there a sequential order when a new record is created through a Form view? Can you define/control the order?

1 Solution

Accepted Solutions

@Paul_Dezzutto

I think the answer here is yes, based on what you are saying and the formula you showed us. I’m not sure what your {Leading Zeros} field is holding, but I assume it’s just a number representing the total length you want the numeric portion of your ID’s to be.

So if you want the numeric portion of your ID’s to always be 5 digits long…

00001
00002
02308
etc...

then you can just replace that reference to {Leading Zeros} with the number 5 – your formula will continue to behave the same way.

Is that what you were asking?

See Solution in Thread

10 Replies 10

Welcome to the community, @Paul_Dezzutto!

Yes, there is an autonumber field type that you can choose.

Thanks, I don’t think I phrased the question very well. I’ve set up my primary formula with this: ‘EV-’&REPT(‘0’,{Leading Zeros}-LEN({Autonumber}&’’))&{Autonumber}
When a user submits a new record via a form view, the formula does pick up the 'Leading Zeros value. Can I set a static value for the Leading Zero in the formula?

No, unfortunately, Airtable does not support static values (aka “global values” in other programming languages).

What are you trying to do? Just trying to set a certain amount of leading zeroes in your formula?

@Paul_Dezzutto

I think the answer here is yes, based on what you are saying and the formula you showed us. I’m not sure what your {Leading Zeros} field is holding, but I assume it’s just a number representing the total length you want the numeric portion of your ID’s to be.

So if you want the numeric portion of your ID’s to always be 5 digits long…

00001
00002
02308
etc...

then you can just replace that reference to {Leading Zeros} with the number 5 – your formula will continue to behave the same way.

Is that what you were asking?

Yep, exactly. Works like a charm even with the form submissions.

Thanks @Jeremy_Oglesby

This would be helpful for something I am doing, but obviously I’m missing something.

How are you creating the Autonumber field? That has to be a string and not a number in order to use the LEN formula, right?

Thanks.

Paul

@Paul_Burchfield

The Autonumber field is just that - an Autonumber field. Its value is an integer produced automatically, sequentially, and which cannot be edited.

In @Paul_Dezzutto’s formula, he is able to use the LEN() function on the Autonumber field because he is coercing the integer returned by that field into behaving like a string by concatenating it with an empty string inside the LEN() function.

Here’s his formula:

'EV-' &
REPT(
   '0',
   {Leading Zeros} -
   LEN({Autonumber} & '')
) &
{Autonumber}

Notice that inside the LEN() function he has {Autonumber} & '' – that & '' is concatenating an empty string onto the end of the integer returned by {Autonumber}, and doing that forces that integer into behaving like a string when it gets evaluated by LEN(). So LEN() is ultimately getting the length of the value in the {Autonumber} field if it were a string.

The latest version: ‘EV-’&REPT(‘0’,5-LEN({Autonumber}&’’))&{Autonumber}. The leading zeros will always be 5 digits.

But I can see the application where the number of digits would increase or decrease.