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.
