All very possible & a common use case.
your name field would be a formula which took all that into account.
Hi @Matej_Skarka,
You can absolutely do this in Airtable. I am making a few assumptions, if I am incorrect let me know and I can adjust.
The year is the date the order was created, not just the current year.
If you do not have it already you might want a field of type Single Select and add, Karen, Bob and whoever else, name this what ever you want but in my code I named it Office.
Create a new field of type: Autonumber and name it autonumber, this field can now be hidden if you dont want to see it.
Create a new field of type: Formula and paste this into the formula editor and name this Order Number
'11'&
DATETIME_FORMAT(CREATED_TIME(),'YY')&
SWITCH(
Office,
'Karen', 5,
'Bob', 6
)&
REPT(
'0',
5-LEN(
{autonumber}&''
)
)&
{autonumber}
The autonumber field will increment for every new row that is created and will not deduct if a row is deleted. If you need to reset it at any time (after testing) delete it and recreate it to reset the counter.
Hi @Matej_Skarka,
You can absolutely do this in Airtable. I am making a few assumptions, if I am incorrect let me know and I can adjust.
The year is the date the order was created, not just the current year.
If you do not have it already you might want a field of type Single Select and add, Karen, Bob and whoever else, name this what ever you want but in my code I named it Office.
Create a new field of type: Autonumber and name it autonumber, this field can now be hidden if you dont want to see it.
Create a new field of type: Formula and paste this into the formula editor and name this Order Number
'11'&
DATETIME_FORMAT(CREATED_TIME(),'YY')&
SWITCH(
Office,
'Karen', 5,
'Bob', 6
)&
REPT(
'0',
5-LEN(
{autonumber}&''
)
)&
{autonumber}
The autonumber field will increment for every new row that is created and will not deduct if a row is deleted. If you need to reset it at any time (after testing) delete it and recreate it to reset the counter.
That looks like the direction I am looking for. I will explore this bit more.
Next questin - Can I pair the “Colleague names” automatically to sign in user in Airtable?
But even without it looks promising. Thank you very much
Yes, will the colleague be added to the order manually by someone else, or will the colleague be the person that creates the order?
If the later we will adjust some of the formula. First create a new field of type Created By. This will give us the person that creates the record. Since a person can put in any name and change it too and there can be more than one Karen, it might be a good idea to use their email address to rule out those possibilities. I do not know of another way to get the User email from Airtable other than automation or scripting. So create another empty Short Text field and name it Creator Email
Now we need to set up an automation to get the address:
Trigger - When a record is created in our table
Action - Update a record, on the same table, use the Record ID from step 1, Field Creator Email place the Created By Email.
SWITCH(
Office,
'Karen', 5,
'Bob', 6
)
to this
SWITCH(
{Creator Email},
'karen@company.com', 5,
'bob@company.com', 6
)
Yes, will the colleague be added to the order manually by someone else, or will the colleague be the person that creates the order?
If the later we will adjust some of the formula. First create a new field of type Created By. This will give us the person that creates the record. Since a person can put in any name and change it too and there can be more than one Karen, it might be a good idea to use their email address to rule out those possibilities. I do not know of another way to get the User email from Airtable other than automation or scripting. So create another empty Short Text field and name it Creator Email
Now we need to set up an automation to get the address:
Trigger - When a record is created in our table
Action - Update a record, on the same table, use the Record ID from step 1, Field Creator Email place the Created By Email.
SWITCH(
Office,
'Karen', 5,
'Bob', 6
)
to this
SWITCH(
{Creator Email},
'karen@company.com', 5,
'bob@company.com', 6
)
Hey Vivid-Squid,
you are great. This is the way 
Just need to play with it a bit.
The user who is logged in is the one who is creating the order and is the one who i need to get to the table. So I think your suggestion is correct.
Thank you very much!
Hey everyone,
I hope you're all doing well. I wanted to chime in and share some thoughts regarding the use of Air table as an ERP system and Best ERP Software Development Companies. While I'm not an expert on Air table, I do have some experience with ERP systems in general.
You can design a table with a form for sales to fill out with new order details, which is a great start.
As for the unique ID generation you mentioned, Airtable provides formulas and automations that can help achieve this. Using formulas, you can concatenate fixed numbers, year digits, office numbers, and a unique order number that increases by 1 with each new order. With some logic and formula-based calculations, you can generate the desired unique order numbers.
However, I recommend exploring Airtable's documentation or reaching out to their support team for more specific guidance on implementing this unique ID generation logic.
I wish you the best of luck in your exploration of Airtable as an ERP solution, and I hope you have a productive and successful day!
Hi @Matej_Skarka,
You can absolutely do this in Airtable. I am making a few assumptions, if I am incorrect let me know and I can adjust.
The year is the date the order was created, not just the current year.
If you do not have it already you might want a field of type Single Select and add, Karen, Bob and whoever else, name this what ever you want but in my code I named it Office.
Create a new field of type: Autonumber and name it autonumber, this field can now be hidden if you dont want to see it.
Create a new field of type: Formula and paste this into the formula editor and name this Order Number
'11'&
DATETIME_FORMAT(CREATED_TIME(),'YY')&
SWITCH(
Office,
'Karen', 5,
'Bob', 6
)&
REPT(
'0',
5-LEN(
{autonumber}&''
)
)&
{autonumber}
The autonumber field will increment for every new row that is created and will not deduct if a row is deleted. If you need to reset it at any time (after testing) delete it and recreate it to reset the counter.
I need to build an ERP in Airtable can you help?
