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

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. :wink: 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 [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:

53%20PM

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):

40%20PM

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

54%20PM

12 Likes

@Justin_Barrett – terrific work! I’d simultaneously been working on a sequential numbering routine that restarted automatically each day, allowing such things as ‘03/31/2019.1’, ‘03/31/2019.2’, ‘03/31/2019.3’, and so on, but you beat me to the punch. (I went ahead and posted it, anyway — but it appears Discourse can’t easily differentiate between self-deprecating humor and other-deprecating invective [unsurprisingly], so my post awaits moderator approval… .) Your solution is better, though, being more general-purpose.


Edit: A-ha! Here 'tis.

3 Likes

Just looked at the example table and this is EXACTLY what we need! Is there any way you would be willing to help me set this up? I’d be happy to share my work e-mail address so we don’t have to keep coming back to this thread.

Thank you! I am so excited about this!

I’d be happy to help you set this up, Sara. Send your email address via PM and we’ll get rolling!

@Justin_Barrett I found this show and tell-and it is AMAZING! I made a copy of your base and played with it to make sure it would work if I used dates as the base-and it did. But when I incorporated it into my company’s CRM-it didn’t work :frowning:

Instead of the variant being unique it just states the total number of of records with that date. I have tried recopying the formulas but to no avail.

Would you or someone be willing to take a look at it for me?

Thank you!

Devonna

Yes, I’d be happy to take a look at it, though I won’t have time to do so until later tomorrow afternoon. I’ll let you know what I found after reviewing what you shared.

1 Like

Thank you! I poked around and noticed that some records are responding as expected while others are not. Which makes it more confusing. The screen shot below shows both cases.

The issue with @Devonna_Wolfe’s base, and with another base that I fixed a while ago, has to do with the autonumber field that’s a required part of the system. I’m not able to edit my original post any more, so I’m adding the relevant details here.

Summary
Deleting a record in the main table will break the automatic sequencing of any items after that point. Thankfully there’s a simple fix: Change the autonumber field to a single line text field, save the change, then change it back to autonumber. This will force Airtable to rebuild the numbering sequence, and the sequence numbering will be correct.

The Long Version
When I was initially testing this system, I somehow didn’t think to test how deleting records would affect the calculations. Some of those calculations are based on the number of records in the table, and some are based on the hidden autonumber field that’s integral to the system. In theory those should always stay in sync. However, once a record is deleted, the numbering will be out of sync from that point on. For example, if your main table contains 200 records, but you delete record 90, records 91 onward won’t be synced properly. Thankfully the above fix takes just a few seconds, and will correct the problem.

One way to be alerted automatically when the numbering scheme gets out of sync is to make a field named {Sync} containing the following formula (replacing {Unique Base} with your own field name if necessary):

IF(LEN(Truncated) != Autonumber * LEN({Unique Base}), "❌")

I have not been able to get past this step of the system. It seems to break whenever I add the {Unique Base} into the substitute formula in place of the old text variable. This is the formula I am using which results in an error. Is there a different formula that should be used here?

SUBSTITUTE(Truncated,{Unique Base},"")

Welcome to the community, @Weston_Beaumont! :smiley: That’s the correct formula, assuming that you used those exact names for your fields when building this system in your own base. What kind of error message are you getting? Does Airtable allow you to save that formula, or is the error appearing in the formula field setup dialog?

Note: for the next 48 hours I’ll be largely unable to respond as we drive across the country to our new home. I’ll reply as soon as I can after that.

The field names match and I have been using the drop down from the formula field in order to make sure the field names are correct. The error shows up in the cells after the formula field is saved. This has to be a problem related to the field format.

This formula works (in a limited sense since the ID needs to be dynamic with the record type)
SUBSTITUTE(Truncated,“001|”,"")

This formula does not work, The brackets around the Unique Base name are interesting.
SUBSTITUTE(Truncated,{Unique Base},"")

Can you be more specific? What kind of error do you see?

When referencing a field that’s more than just a single word, or one that has emoji or other non-alphabetic characters in its name, the name must be wrapped in curly braces for the formula to operate properly.

Hi there! I have been looking for a good solution to my issue and I see many posts linked to this article. But looking at @Justin_Barrett instructions, I am unable to solve this.

I’m using Airtable for an administration system where people add their receipts. Every receipt needs to have a unique number. Preferably I have a SETTING view where I can set the code of the receipt and give a starting number of the sequence.

For example I will have TB1-001 (where TB1 is the code) and the 001 is set due to my starting number of the sequence, which is 1.

I can share my table to give you an insight to my challenge. Thx

Hi, there! Total spreadsheet / Airtable n00b here. :sweat_smile:

First, let me say that if anyone would like to reach out to help me out with this, I’d be more than happy to compensate them for their time.

I think what I’m trying to accomplish is along these lines.

Goal: A table that adds a new record numbered 1-10 and RESETS to number 1 once 10 is reached.

Reason: I run a real estate team and I need to assign them leads as they come in to our system in an equitable manner.

Background: I’m fairly fluent in Zapier and could easily send these records to Airtable but I can’t seem to find a way to distribute them to my 10 person team evenly as they come in.

Question: Is this actually a good use case for my problem or am I barking up the wrong tree? Is there a much easier solution I’m not seeing?

Spreadsheets are my kryptonite so thanks in advance for any / all insight / advice.

PS: I will totally pay you to set this up for me!

-Keith

Welcome to the community, @Michiel_Haverlag! :smiley: If you’re able to share a link to your base, feel free to post that here. I’m a lot busier these days with my new job, and can’t always do deep dives into problem setups, but I’m hoping this will be an easy fix.

The setup you want can be done with a much simpler setup than what I described above. In your case, you would begin by adding an Autonumber field. I often name these with a number/pound sign so I can shrink it down fairly small:

Add a formula field with this formula:

MOD({#} + 9, 10) + 1

Which will give you this:

Hi Justin,

That would be amazing. I have duplicated my base where you could play around:

https://airtable.com/invite/l?inviteId=invyZBCb87e7y50cA&inviteToken=5411d318faed83d12f36c4ca47cb30855985d64d7defcec5960e9c5adccd3ab1

So basically there is just one column that needs to be a sequence which needs to be plussed by one. Example: if I have 4 receipts in the table and I add a new one, the field should say 5.

If I delete no. 5 and add a new one, this integer should say 5 again. Hope it is clear to you. I don’t think it is hard mathematics for you.

Thanks in advance :slight_smile:

Michiel

Michiel,

I was about to dive into this, but after reading through your comments, one thing stuck out:

This is a known issue with the system I came up with. In a follow-up post above, I pointed out:

This is because a core element of the system is an Autonumber field, and Autonumber fields never repeat numbers, even if a record is deleted. The only way to “fix” this is to tweak the Autonumber field (as I outlined above) to reset it. This wouldn’t be much of a bother if you seldom delete records. However, If you foresee yourself deleting records regularly, you’ll have to apply this reset to the Autonumber field every time you do so, which would become more than a bit annoying IMO.

Hi @Justin_Barrett,

Ok. In that case we will change our workaround and won’t delete the records but change them :slight_smile: If we would do that, would that change things and could you proceed with your magic?

That will work if you don’t delete records, but just know that it may be a day or two before I can work on it. I’ve already got a very full plate at the moment.