I have an airtable we use for my wife’s online resale business. Our item numbering consists of a letter to indicate the department (Men-M, Women-W, Kids-K, Electronics-E). We also have a number to indicate the category (1- Accessories, 2-Shoes, etc…). Lastly is a number that is added which I can do with autonumber. I would like to use a formula to generate this number automatically, but need help turning the category into the corresponding number as the form is set up with a single select that has the category names. Since the the Letter I use for each department is always the first letter, I used the left formula to get the letter I need. I can use concatenate to put the pices together, but need to convert my category names into the appropriate number that I use for them in the item #. An example is W12-2 is Women’s department, 12 stand for the shoes category, and the 2 is the 2nd pair of shoes that were entered into inventory. I feel like make the number at the end relate only to shoes would be too complicated, so I want to make the number after category autonumber as I enter items. For example, if I have 2000 items I have purchased for inventory all time, I would want the next item I purchase and enter to generate 2001. So, a pair of mens shoes would get M12-2001. Any assistance or input is greatly appreciated.
This is a bit complicated to follow without screenshots.
From what I understand, the best option you have is to have a table for the categories and convert the single select field into a Linked Record field instead so you can use rollups to count the items then use this count (using an automation) to add it in a separate field to concatenate it into your formula.