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

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

Best,

Michiel

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?

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

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

Thank you!

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.

Screen Shot 2020-05-29 at 3.32.37 PM

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

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?

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

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.

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

autoIDString
ARRAYJOIN({autoID})

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

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

image

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

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.

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

image

Here is the single formula version

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

image

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

2 Likes

This is perfect! Thanks!!

Hey @Justin_Barrett, thank you for your amazing work here !
I’m using this method to make a pallet tracking base, where there is pallet 1 of 2, 2 of 2 etc…
The issue I have is there is almost 12k pallet to track and each one is created via a script from the customer database and I won’t be on the field to do the trick and each pallet must be linked to the controler manually. (I’m fairly new to the airtable world) I tried some sort of wizzardry with zappier to automatically link every new record created, but it doesn’t works.

Is there a way to easilly link every new record created to the controler ?

1 Like

Welcome to the community, @Thibaut_FERRI! :smiley: I’m a little less inclined to use this system these days thanks to Airtable’s scripting and automation features, but it’ll still work if you want to use it. In terms of linking every new record to the control record, this can be done using Airtable’s automation system.

The trigger will be the creation of a new record. The action step will be an “Update record” action, where you update that record with the link to the desired control record. You can get the control record’s ID by right-clicking on it and choosing “Copy record URL”. In the automation update step, paste that full URL into the link field you’re updating, then delete everything except the actual record ID (e.g. recXXXXXXXXXXXXXX). Save and activate the automation, and all new records will auto-link to the control record.

1 Like

Thank you very much, great community ! :smiley:
I still have a little issue with this one :
image
I’d like to update the record in the control table when a record is created in the Etiquettes table. as you said I’ve used the control record’s ID in the record id field, but what to put in the fields “etiquette field” ?
On the etiquette table, the link to control is named “Link2Control”

Cheers guys, thank’s for the support =)

The value in the “Record ID” field of the action should be the record ID from the record that triggered the automation. Delete that current value, then click the + symbol on the right end, and pick the triggering record’s record ID.

Thank you for your time, patience and explanations.
I figured I went the other way around (tried to update the control record instead of the created one).
I’m still struggling a bit with english as it’s not my mothertongue.

Once again, thank you for your work.
Cheers from the land of the baguette.

1 Like

Hi @Justin_Barrett, this post is amazing.

I’m really new to Airtable and what you explain here is pretty advance to me. Although, I was able to understand what you explain I’m far to implement it to a specific use case I want.

I would really appreciate if you could give some directions with the following.

How can I modify your base (fixed length option) to have a sequence number that resets for every new year?

For example
2020-001
2020-002
2020-003

2021-001
2021-002

It is for an invoice generator I would like to create using Airtable and Google docs templates.

I did dive into this post as well (Sequential Number, Resetting Daily ) but, as I said, my knowledge is far from being able to update these bases with success.

Thank you very much for your help!

2 Likes

Hey @Justin_Barrett. You can omit my previous message. I figured it out but replacing the Base field in the main table with a formula that extracts the year from a “Created date” field and everything works as desired. Thanks again for sharing this post.

2 Likes

It’s been a little more than two years since I wrote the original post that kicked off this thread. While it was fun playing with that solution (and even using variations of it for other things), and gratifying to hear how it has helped other Airtable users, I feel the need to make something clear:

I no longer recommend the method described in this thread

Why not? Simply because there are far better ways of doing this now that weren’t available at the time I developed the original solution. Today I’d recommend an automation that runs a script. Yes, there will be a slight delay while waiting for the automation to run, but it’s still far more flexible and far less error-prone (provided that the script is written well) than my original solution.

One of these days I might put together a sample script that could be used in an automation to do this, but I’m afraid that’s pretty far down my priority list. If anyone else wants to share their scripts here, feel free!