Skip to main content
Solved

Conditional Auto-Numbering (Multiple Autonumber sequences per base)?

  • March 29, 2021
  • 8 replies
  • 586 views
S
Forum|alt.badge.img+12

Wondering if it is possible to run the auto-number column, but conditional on another column. We are setting up a purchase order tracking log, and I would like things to autonumber based on the field for who is doing the ordering. So if Steve goes in to create 3 separate orders, he picks “Steve” from the single-select dropdown field “Ordered By” and then he gets a sequence of auto-numbers. For his 3 orders, he would get 1, 2, and 3 (I would have the key column by a formula that creates a unique ID based on “Ordered By” and the Autonumber, so it would probably be “Steve001”, Steve002", “Steve003”, etc etc).

If Dave then goes in and enters an order for himself, what we would like is for the auto-numbering to restart for the new single-select entry “Dave” so that Dave’s first entry would get an autonumber “1” again, and the key field would be “Dave001”. If steve then goes to do another entry, he would get “Steve004”.

So far, I am not seeing how to do this (or if it can be done). I realize that sharing the autonumber will still create unique IDs for each order, but part of the problem we are trying to solve is for people out in the field to know that they have their own set of numbers and if they need to place an order with their vendor but are not near the computer, then just have to remember the last available number in sequence to give a PO # to the vendor. If everyone shares, then you never really know what the next available number is since anyone could take it.

Any help on this appreciated.

Thanks

Best answer by kuovonne

I wrote up a solution to this a couple of years ago:

That said, I would probably approach the problem slightly differently now that Airtable has both scripting and automation features.

Instead of having users pick from a single select field as you described, build a [Users] table and let users link to their record. Then you could use a count field in the [Users] table to count the number of links from orders, use a rollup field in your [Orders] table to bring that number back over to the order record, and a formula field to pad the leading zeroes. When the record is added from the form, trigger an automation (“When record created”) that copies this formula-created number into whatever field you want to use to store the unique ID. Not a line of code required, and much easier to manage than my original setup.


I agree with @Justin_Barrett 's automation solution, and suggest a few tweaks:

Instead of using a count field in the [Users] table, use a rollup of the MAX(values) of the numbering field. If you use a count field and someone deletes a record, the count will be off and you will end up repeating a number.

IF(MAX(values), MAX(values) + 1, 1)

I would put the padding of leading zeros in the formula for the unique ID that combines the numbering field with the user name. That way the numbering field can be a numeric field and take advantage of the MAX(values) feature.

Instead of using the “when record created” trigger, I recommend using the “when record meets conditions” trigger. Have the conditions be when the user is not empty but the numbering field is empty. This way, if a user ever manually creates a record in the user interface (not in the form), the autonumbering system will still work.

You may still have a slight problem with telling the user in the field what his last number is if he doesn’t remember it, as the last number will not be calculated until after the record is created. However, the information could be easily looked up on a mobile device with internet access by using either Airtable itself or a shared view.

Calvin_Young2
T

8 replies

Justin_Barrett
Forum|alt.badge.img+21

I wrote up a solution to this a couple of years ago:

That said, I would probably approach the problem slightly differently now that Airtable has both scripting and automation features.

Instead of having users pick from a single select field as you described, build a [Users] table and let users link to their record. Then you could use a count field in the [Users] table to count the number of links from orders, use a rollup field in your [Orders] table to bring that number back over to the order record, and a formula field to pad the leading zeroes. When the record is added from the form, trigger an automation (“When record created”) that copies this formula-created number into whatever field you want to use to store the unique ID. Not a line of code required, and much easier to manage than my original setup.

kuovonne
M
kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • Answer
  • March 30, 2021

I wrote up a solution to this a couple of years ago:

That said, I would probably approach the problem slightly differently now that Airtable has both scripting and automation features.

Instead of having users pick from a single select field as you described, build a [Users] table and let users link to their record. Then you could use a count field in the [Users] table to count the number of links from orders, use a rollup field in your [Orders] table to bring that number back over to the order record, and a formula field to pad the leading zeroes. When the record is added from the form, trigger an automation (“When record created”) that copies this formula-created number into whatever field you want to use to store the unique ID. Not a line of code required, and much easier to manage than my original setup.


I agree with @Justin_Barrett 's automation solution, and suggest a few tweaks:

Instead of using a count field in the [Users] table, use a rollup of the MAX(values) of the numbering field. If you use a count field and someone deletes a record, the count will be off and you will end up repeating a number.

IF(MAX(values), MAX(values) + 1, 1)

I would put the padding of leading zeros in the formula for the unique ID that combines the numbering field with the user name. That way the numbering field can be a numeric field and take advantage of the MAX(values) feature.

Instead of using the “when record created” trigger, I recommend using the “when record meets conditions” trigger. Have the conditions be when the user is not empty but the numbering field is empty. This way, if a user ever manually creates a record in the user interface (not in the form), the autonumbering system will still work.

You may still have a slight problem with telling the user in the field what his last number is if he doesn’t remember it, as the last number will not be calculated until after the record is created. However, the information could be easily looked up on a mobile device with internet access by using either Airtable itself or a shared view.

ScottWorld
Justin_Barrett
M
S
Forum|alt.badge.img+12
  • Spencer_Mullane
  • Author
  • Known Participant
  • April 2, 2021

So I’m about 95% through this, setup was pretty easy and this is the first time I’ve had time to actually check out the automation functions, so far so good though.

The one part I am stuck on is how to pass the “IF(MAX(Values), MAX(Values)+1,1) value from my "
Users” table to my “Orders Table”. Being the first use of automation, I’m sure I’m just missing the right way to collect/copy the record/field from Users as I am able to enter a static number into the “Order Number” field and get the automation to enter a pre-defined static number.

Attached are two screenshots of the basic test tables I setup with the automation on the right, just can’t figure out how to pass the “Next Available PO Number” field (for the relevant user) which holds the “MAX(Values” formula over to the “Order Number” field in the Orders table.

Any help on that last little bit would be awesome (currently re-reading the documentation on automations now to figure out what part I missed).

Thanks again!

kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • April 2, 2021

So I’m about 95% through this, setup was pretty easy and this is the first time I’ve had time to actually check out the automation functions, so far so good though.

The one part I am stuck on is how to pass the “IF(MAX(Values), MAX(Values)+1,1) value from my "
Users” table to my “Orders Table”. Being the first use of automation, I’m sure I’m just missing the right way to collect/copy the record/field from Users as I am able to enter a static number into the “Order Number” field and get the automation to enter a pre-defined static number.

Attached are two screenshots of the basic test tables I setup with the automation on the right, just can’t figure out how to pass the “Next Available PO Number” field (for the relevant user) which holds the “MAX(Values” formula over to the “Order Number” field in the Orders table.

Any help on that last little bit would be awesome (currently re-reading the documentation on automations now to figure out what part I missed).

Thanks again!


As Justin suggested, pass it as another rollup field in the [Orders] table. It will be a rollup of a rollup. You can use the SUM(values) or MAX(values) rollup formula. It doesn’t really matter as there is only one linked record.

Then the automation can copy the number from the rollup field to the numbering field. Note that I recommend using a “when record meets conditions” trigger instead of “when created” trigger, to ensure that the rollup has a value. If you use the “when created” trigger, there is a tiny chance that the automation could trigger before the rollups have time to calculate.

S
Forum|alt.badge.img+12
  • Spencer_Mullane
  • Author
  • Known Participant
  • April 2, 2021

As Justin suggested, pass it as another rollup field in the [Orders] table. It will be a rollup of a rollup. You can use the SUM(values) or MAX(values) rollup formula. It doesn’t really matter as there is only one linked record.

Then the automation can copy the number from the rollup field to the numbering field. Note that I recommend using a “when record meets conditions” trigger instead of “when created” trigger, to ensure that the rollup has a value. If you use the “when created” trigger, there is a tiny chance that the automation could trigger before the rollups have time to calculate.


Missed that roll-up of a roll-up part and thought that the “passing” of the value was going to occur in the automation (will still be curious to see if there is a way to do it that way as well).

This closed the loop for me though, functions exactly as need! Thank you both!

Justin_Barrett
kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • April 2, 2021

Missed that roll-up of a roll-up part and thought that the “passing” of the value was going to occur in the automation (will still be curious to see if there is a way to do it that way as well).

This closed the loop for me though, functions exactly as need! Thank you both!


If you have a scripting automation action, you could have the script do everything without any rollup fields.

Justin_Barrett
G
Forum|alt.badge.img+3
  • Giovanni_Berb
  • New Participant
  • December 9, 2024

Hi @kuovonne  , i found this very usefull!

But, how about I can have have an automatic sequential number per Year? I have a field "created time" but how I can count the number of record created in the same year? Maybe it is easier than how it looks for me.

Thank you a lot

kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • December 9, 2024

Hi @kuovonne  , i found this very usefull!

But, how about I can have have an automatic sequential number per Year? I have a field "created time" but how I can count the number of record created in the same year? Maybe it is easier than how it looks for me.

Thank you a lot


One option is to have a table of years, link records to years, and follow the same process outlined in this thread.

However, the more I work with Airtable, the less I like having numbering sequences that restart like this. If you need to know the number of items in a year, it is better to look at the actual number of records. If you need to know the sequence of records, it is better to look at the underlying date/time field. Usually the actual need is just for a unique identifier.

k_pkae
Terms & ConditionsAccessibility statement