Assigning Unique Numbers to Record Group

[ian_lyngklip]

I am trying to create a formula that creates a unique ID number based on a concatonated value. Value should reflect year, customer number and the order number for that customer. It should look like this,

YYYY-CustNo-OrdeNo.

So the order for customer that we sight up in 2022 as the first customer of the year and makes their fifth order will have the following number

2022-001-005.

I have already generated the Year and customer number from our accounting program, so I have the first two pieces. (2022 and 001) but I need a formula to generate the order number relative to the client.

Can anyone help with some code or a reference to the knowledgebase? Thanks.

[Vivid-Squid]

Hi @ian_lyngklip
It sounds like you want to concatenate the three fields into one text string.

With concatenate, you join each item with a comma you can also add text like the hyphen by surrounding it in quotes:

CONCATENATE({year},"-",{customer number},"-",{order number})

Replace the text in my formula in the {} with the names of your fields.

[kuovonne]

Welcome to the Airtable community!

Automatically generating a unique number for a sub-set of records in a table typically requires having an editable field and using a automation to fill that number. You cannot do this with a formula field alone.

Here is one common technique:

1. Trigger the automation based on when a record meets conditions. The conditions are when the year and customer number are not blank, but the order number is blank.

2. Do a “Find records” action to find records that match the year and customer number of the triggering record.

3. Update the triggering record to set the {order number} field equal to the length of the records from the “Find records” action.

Note that this technique has some limitations:

• It will not work if someone has more than 100 orders
• If someone deletes the last order, the next order will repeat the same number (which may not be a problem)
• If someone deletes an order before the last one, the next order will repeat the same number (because there will be fewer matching orders).
• If two orders are created and meet the triggering conditions at the same time, they could end up with the same order number.

There are other techniques that can be used to workaround some of these issues, but they typically involve either having additional fields in your base or involve scripting.

[Kamille_Parks]

Try the method listed in this topic:

Update counter when action is triggered Automations

Set up your tables to have fields like these: [Clients] table: {Max Order #}: a rollup field pointing to the [Client orders] table with the MAX(values) aggregation formula to find the last order number associated with that client. {Next Order #}: a formula field that just adds 1 like this {Max Order #} + 1 [Client orders] table: {Client Order #}: a number field {Client Next Order #}: a lookup field pointing at the [Clients] table’s {Next Order #} field. Then set up your Automation to…

You’ll be concatenating multiple fields as Vivid Squid mentions. Some limitations from Kuovonne’s suggestion also still apply namely “if two orders are created within a quick succession” and “if someone deletes the last order” but will work for customer s who have more than 100 orders per year.

[kuovonne]

ian_lyngklip:

Value should reflect year, customer number and the order number for that customer. It should look like this,

Do you want the order number to reset each year?

[Kamille_Parks]

Good Question. The conditional Rollup mentioned in my scenario could be modified to also only find the max order number for the current year