Skip to main content

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.







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 rControl] 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 oFixed Length] table in the sample base:





Here’s a rundown of how the system works. All field names are from iFixed 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 oPresidents] table in the sample base):





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



@Timo_Germer Not without using a script. Scripts have no limit on how many records they can retrieve (aside from memory limits, of course, but that’s not an issue I’ve heard of anyone encountering).


Ah I see. Is it easy to set up such a script that runs within an automation? I have tried your provided script. It worked perfectly - but it would have to be triggered manually each time.


Ah I see. Is it easy to set up such a script that runs within an automation? I have tried your provided script. It worked perfectly - but it would have to be triggered manually each time.


As I wrote in the post just above yours from earlier today:




For a non-scripting way of doing this (i.e. low-code), I would highly recommend using Integromat. I use Integromat every single day for doing all sorts of advanced automations in Airtable, with no JavaScript necessary. There is a little bit of a learning curve with Integromat, but once you get over the learning curve, it’s extremely powerful:




Just a note of thanks @Justin_Barrett this suits my needs perfectly - for a WBS after I manually change or assign L1,2,3 - 7 level to an item with it’s own id. An automation not suited to my needs so many thanks - to note for newer users, I didn’t use Airtable in before 2021, now a self referencing table (can be filtered and a dropdown limited to reference only ‘that’ view - for example ‘the parent task’ - so perhaps this is simpler now with Airtable advances - thus look up the ‘id’ of the parent easily forms a WBS character base. I use levels to drag up and down and grab the sequence to assign the WBS


Hello, it’s great but how can I see the actually formula ? Thanks


Hello, it’s great but how can I see the actually formula ? Thanks


Which actual formula are you referring to? There are multiple formulas listed above.


For a non-scripting way of doing this (i.e. low-code), I would highly recommend using Integromat. I use Integromat every single day for doing all sorts of advanced automations in Airtable, with no JavaScript necessary. There is a little bit of a learning curve with Integromat, but once you get over the learning curve, it’s extremely powerful:






You didnt actually explain any approach to solving it using integromat so this is quite useless..


@Justin_Barrett Is there any chance you would you be able to look at my topic and help me? This topic is exactly what I'm needing, but I was given a formula to use to get a certain setup, and it isn't working: https://community.airtable.com/t5/base-design/line-item-support-help/td-p/166098 and the person who helped me is no longer around, it seems. Any guidance would be incredible!


@Justin_Barrett Is there any chance you would you be able to look at my topic and help me? This topic is exactly what I'm needing, but I was given a formula to use to get a certain setup, and it isn't working: https://community.airtable.com/t5/base-design/line-item-support-help/td-p/166098 and the person who helped me is no longer around, it seems. Any guidance would be incredible!


@ebefort_DDP The techniques I outlined in my original post should still work even after all the changes that Airtable has gone through since then, though frankly it's a bit of a clunky hack that I wouldn't recommend for a production environment. These days I would lean towards something code-based in an automation, but unfortunately I'm no longer able to provide direct help with individual issues, so I can't be of any help putting that code together. I'm sorry.


@ebefort_DDP The techniques I outlined in my original post should still work even after all the changes that Airtable has gone through since then, though frankly it's a bit of a clunky hack that I wouldn't recommend for a production environment. These days I would lean towards something code-based in an automation, but unfortunately I'm no longer able to provide direct help with individual issues, so I can't be of any help putting that code together. I'm sorry.


Ah! I just realized the date on this! And that is perfectly fine, I so appreciate the response!! Thank you!!


@Justin_Barrett Genius, thank you! I've pretty much duplicated this solution but for some reason, I dont get the same result in truncated field. Would really appreciate any help!

 

 


@Justin_Barrett Genius, thank you! I've pretty much duplicated this solution but for some reason, I dont get the same result in truncated field. Would really appreciate any help!

 

 


Sorry for the delayed reply, @Dollie . I've been navigating a cross-country move for the past several weeks, and haven't been checking my notifications for a bit. That and I almost never visit this forum any more for various reasons.

Anyway, the only thing that sticks out is that your autonumber field has some lines deleted. It starts with 4 instead of 1. One of the things I learned in my testing is that deleted lines can mess up the system. I recommend changing that to a regular number type, manually fix the numbers to start with 1, then convert it back to autonumber again and Airtable will recognize the change.

Let me know if that fixes it. If not I might be able to dive deeper, but frankly my schedule these days seldom allows me to do that.


Sorry for the delayed reply, @Dollie . I've been navigating a cross-country move for the past several weeks, and haven't been checking my notifications for a bit. That and I almost never visit this forum any more for various reasons.

Anyway, the only thing that sticks out is that your autonumber field has some lines deleted. It starts with 4 instead of 1. One of the things I learned in my testing is that deleted lines can mess up the system. I recommend changing that to a regular number type, manually fix the numbers to start with 1, then convert it back to autonumber again and Airtable will recognize the change.

Let me know if that fixes it. If not I might be able to dive deeper, but frankly my schedule these days seldom allows me to do that.


I found the answers in one of your responses to others. Thank you @Justin_Barrett . This solution is brilliant 


After experimenting with both the formula-based approach (using SUBSTITUTE + string manipulation) and another method of counting linked records in a separate base table, I’ve found that leveraging Airtable automations can simplify the process. The original SUBSTITUTE-based solution is clever and works great, but it involves linking all records to a “control” record or table, which may not feel intuitive for some users.

What Worked Best for Me:

A Single-Table Setup Using Automations:
By setting a trigger that fires when a record is fully ready (e.g., once {Base Code} is entered and confirmed for expected char length etc), the automation finds all other records with the same base code and updates the new record with a permanent, incremental code. This removes the need for complex formulas, record linking, and multiple tables.

Why I Like This Solution:

  • Simplicity: Avoids complex formulas like ARRAYJOIN and SUBSTITUTE.
  • Maintainability: Adjusting an automation is imo easier than modifying formulas.
  • User Experience: Team members simply fill in a required field, and the numbering is handled automatically.

Downsides:
This solution will consume some monthly automation runs and may introduce a short delay (a few seconds) before the final code appears.


After experimenting with both the formula-based approach (using SUBSTITUTE + string manipulation) and another method of counting linked records in a separate base table, I’ve found that leveraging Airtable automations can simplify the process. The original SUBSTITUTE-based solution is clever and works great, but it involves linking all records to a “control” record or table, which may not feel intuitive for some users.

What Worked Best for Me:

A Single-Table Setup Using Automations:
By setting a trigger that fires when a record is fully ready (e.g., once {Base Code} is entered and confirmed for expected char length etc), the automation finds all other records with the same base code and updates the new record with a permanent, incremental code. This removes the need for complex formulas, record linking, and multiple tables.

Why I Like This Solution:

  • Simplicity: Avoids complex formulas like ARRAYJOIN and SUBSTITUTE.
  • Maintainability: Adjusting an automation is imo easier than modifying formulas.
  • User Experience: Team members simply fill in a required field, and the numbering is handled automatically.

Downsides:
This solution will consume some monthly automation runs and may introduce a short delay (a few seconds) before the final code appears.


Definitely a superior solution. At this stage there's no reason to use that old hack that I came up with. It was an interesting experiment at the time and helped folks to get things done, but I definitely don't recommend it any more.


Reply