Automatic Sequential Numbering of Non-Sequential Items (READ JUN 9 2021 FOLLOW-UP)

[Justin_Barrett]

Summary
This setup allows Airtable to automatically number collections of items that may not be in sequential order. Item length can be either fixed or variable, with only minor changes needed to the core system to accommodate the latter.

Example
To borrow from the thread that inspired this endeavor, say you’re building product codes that have a variant sub-code, and want that sub-code to automatically increment each time an item of a given product code is added in a new record. However, it needs to auto-increment each product’s sub-code separately, even when product codes are added out of order.

For example, the first product code is 001, and its initial sub-code is 1, so the full combined code is 001-1. When you add a new variation of product 001, the sub-code bumps up by 1, creating 001-2.

Now a new product is introduced with code 002. Because it’s the first of its kind, its full combined code is 002-1. If we then introduce a third variant of product 001, it would end up with a combined code of 001-3.

So far our full product codes, in the order they were created, look like this:

001-1
001-2
002-1
001-3

For a small list, creating these manually might not be a problem, but most people don’t use Airtable with small lists. :winking_face: It would be much easier to manage if the variant codes would auto-increment each time we add a new product code.

This independent sequential numbering requires some knowledge of how many of each item came before the current one, no matter how far back in the list those previous items might live. Because each Airtable record is independent, not having any “knowledge” of any other records in the same table, there’s no direct way to reference other records to make this count. However, with a little table and field trickery (inspired by the work of @W_Vann_Hall), this can be done. The core method requires items of a fixed length, but with a minor adjustment it will also work to track items of varying lengths.

Here’s a sample base that you can copy if you wish to dig into the guts of the system, which are described below.

Airtable

Sequential Numbering of Non-Sequential Items - Airtable

An example of how to automatically add sequential numbering to non-sequentially listed items. More details in the following community post:

Core Solution
Only two tables are required:

1. The main table recording the items to be tracked.
2. A [Control] table with a single record.

The data requirements for this setup are:

1. The primary field in the main table must be an Autonumber field.
2. Every record in the main table must be linked to the lone record in the [Control] table.
3. The character length of the data in the field containing the base items to be tracked must be the same for every record. The exact length isn’t important, but it must be consistent. For example, if you’re going to use a five-character base code, the content of that code can be pretty much anything as long as it’s unique (obviously), and always five characters long in every record. The good news is that the field type can be any number of things, including (but not limited to) Single line text, Single select, Date, Duration, Phone Number, or even a link from another table

Here’s a cropped look at the [Fixed Length] table in the sample base:

Here’s a rundown of how the system works. All field names are from [Fixed Length]:

• First, a unique character is added to the base item (see the {Unique Base} field), in order to prevent accidental crossover between base items when a SUBSTITUTE is run later in the process. My sample base uses a vertical bar, but anything else can be used instead as long as it’s not part of any of the base codes.
• These unique items are looked up in the [Control] table, and that collection is then looked up back in the main table (see the {Lookup} field).
• That lookup is then condensed into a string, and only the leftmost portion kept, with the length based on multiplying the record’s autonumber value by the {Unique Base} length (see the {Truncated} field). For example, if {Unique Base} is four characters long, this field will only keep the first four characters of the mashed-together lookup for the first record. For the second record, it will keep the first eight characters, and so on. This creates a record-by-record tally of everything that has been added.
• The {Substitute} field then takes that tally and removes all instances of {Unique Base}, leaving everything else except that item.
• The length of {Substitute} is subtracted from the length of {Truncated}, then divided by the length of {Unique Base}, and we have the unique number to assign to the listed item (see the {Variant} field)
• The variant number is combined with the base and any desired separators in the {Final} field.

The four helper fields in the main table can be hidden once everything is set up. Because the numbers assigned in the Autonumber field are maintained even when reordering records, the variant numbers remain properly assigned even after records are moved around.

Variable Length Items
The same basic system also works with items of varying lengths. However, because of that variation, we can’t use the items themselves to track their count. Instead, we need to track another fixed-length item that’s tied to that primary item. For this, I created a new table, with the item names in the primary field, an autonumber field adjacent to that, and a formula field that padded the autonumber with leading zeroes to create a unique fixed-length ID for each item. Because these IDs are only going to be used in various formulas, I also added the vertical bar character here instead of doing so in the main table.

Back in the main table, the base items became links to records in that other table, with a lookup field pulling in each item’s unique ID, and then the rest of the system flows as outlined above, operating on that ID instead of the item itself. Once the item’s unique number is calculated, it’s combined with the original item.

Here’s a sample lookup table for this variable-length system (the [Presidents] table in the sample base):

And here’s the main portion of the [Variable Length] table:

[Michiel_Haverla]

No rush at all! You are a true king. Thx Justin.

Best,

Michiel

[Justin_Barrett]

Sorry for the delayed response. Work has been keeping me very busy, and I haven’t had time to put into working on the solution you’re seeking. Have you been able to get it working on your own, or do you still need assistance?

[Michiel_Haverla]

Yes, please. If you have time to have a look, would be very helpful. Thx!

[Paul_Warren]

Is there any easy way to adapt this to repeating unto 999? SO 1000 starts at 1 again?

Thank you!

[Justin_Barrett]

That’s definitely doable, although mathematically it doesn’t make sense to have 1000 turn into 1, and I couldn’t get this to work cleanly in a formula considering how the numbering system is currently created. However, 1001 could become 1, as could 2001, 3001, and so forth; i.e. resetting to 1 after each multiple of 1000 has passed.

If that works for you, read on. If not…um…I guess you could read something else? :winking_face:

This is accomplished by tweaking the formula in the {Variant} field. The current formula is:

(LEN(Truncated) - LEN(Substitute)) / LEN({Unique Base})

Here’s the modified formula that cycles back to 1 after each multiple of 1000:

MOD((LEN(Truncated) - LEN(Substitute)) / LEN({Unique Base}) - 1, 1000) + 1

However, that cycling behavior kind of defeats the purpose of the setup, which is to create a reliable count of specific items. If you’re tracking something so plentiful that you’ve got 1000 of them, why would you want the count for that item to restart at 1? Are these items disposable. so that by the time you get to 1000, whatever was numbered 1 doesn’t exist any more, and it’s safe to reuse that number?

[Paul_Warren]

Justin_Barrett:

If that works for you, read on. If not…um…I guess you could read something else? :winking_face:

Haha, it doesn’t quite work, but I read on anyway :grinning_face_with_big_eyes:

Justin_Barrett:

However, that cycling behavior kind of defeats the purpose of the setup, which is to create a reliable count of specific items. If you’re tracking something so plentiful that you’ve got 1000 of them, why would you want the count for that item to restart at 1? Are these items disposable. so that by the time you get to 1000, whatever was numbered 1 doesn’t exist any more, and it’s safe to reuse that number?

The point is to not get to a four digit number. I just realized, I probably could just take the right 3 digits using the RIGHT() function. I don’t mind having a 000 number. Also, you are exactly right, by the time I get to 1000, I don’t expect to be needing the first iteration of 001. This is being concatenated to the name of the linked record and I expect the linked record to only go up to 10, but am trying to be robust. The problem is, I don’t want to have an infinite number of IDs to concatenate.

If I have 1000 records, then link 10 records to each of them, then the Auto ID would get to 10,000. However, I really only need it to go to 100. It’s not a big enough deal for me to be willing to implement another table to iterate the record numbers like another example you have created. So I figured this was a reasonable and robust enough work around to keep each record name unique and readable.

[Paul_Warren]

Using RIGHT() wasn’t quite that simple, haha. I had to first convert the nubmer to a string using the Arrayjoin() funciton. Then use a combination of MIN() and LEN(), because RIGHT() doesn’t work if the input string is shorter than the number of characters you are asking for. But I for the result I was looking for :slightly_smiling_face:

autoIDString
ARRAYJOIN({autoID})

Number
RIGHT(autoIDString,MIN(LEN(autoIDString),3))

IF({Number}, IF({Number}<10, “00” & {Number}, IF({Number}<100, “0” & {Number}, {Number})))

[Justin_Barrett]

There are a few hiccups in your attempt to add leading zeroes…

Paul_Warren:

autoIDString
ARRAYJOIN({autoID})

The ARRAYJOIN() function is designed to work on arrays, but the {autoID} field returns a number. I get the impression you just want to convert that number to a string, so all you need for that is to concatenate the number with an empty string:

autoID & ""

The rest of the process to add leading zeroes to the number can also be greatly simplified. You only need a single formula. Here’s what I often use when that’s needed.

REPT("0", 3-LEN(autoID & "")) & autoID

Broken down, that takes the autoID value, converts it to a string, subtracts the length of that string from 3 to see how many leading zeroes are needed, repeats the “0” character that many times, and concatenates that with the original number.

[Paul_Warren]

You show great refinement in your work! I never considered using the REPT() function. I did have to make one adjustment. You can’t just feed autoID in, because once the numbers get above 5 digits long the REPT function returns an error. I am guessing this has to do with it’s ability to take negative values. Instead I used the Number field, which already reduces the auto ID to a maximum of 3 digits. Of course, this could be done in a single formula, but I like to break them out at first:

autoIDString
{autoID} & “”

Number
RIGHT(autoIDString,MIN(LEN(autoIDString),3))

REPT(“0”, 3-LEN(Number& “”)) & Number

Here is the single formula version

REPT(“0”, 3-LEN(RIGHT({autoID} & “”,MIN(LEN({autoID} & “”),3))& “”)) & RIGHT({autoID} & “”,MIN(LEN({autoID} & “”),3))

[Justin_Barrett]

I get where you’re going now, and can offer one more optimized version of your formula.

REPT("0", 3 - LEN(MOD(autoID, 1000) & "")) & MOD(autoID, 1000)

The MOD(autoID, 1000) portion automatically reduces the number—no matter how large—to no more than three digits: the remainder after dividing the number by 1000.

On a side note, you used Number & "" in part of your formula, but the concatenation with the empty string isn’t necessary there. That’s only required to take a non-string value and turn it into a string, but your {Number} field already created a string (the clue: left justification of the output means it’s a string; right-justification, as in the {autoID} field, means it’s a number).