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.

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.

Try the method listed in this topic:

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.

Do you want the order number to reset each year?

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