Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.
Apr 30, 2020 07:29 AM
Hi all,
Trying to wrap my head around this one. In my table I have 4 overarching categories: say A, B, C, D. Underneath each of those I have a variety of sub categories, around 5-10. Those two categories are single select fields. And each of those sub categories has a number of items attached to them, which are just longform text. What I’m trying to achieve is a numbering system that looks like this:
1.2.5 which translates to
Category 1, subcategory 2, item 5
Is it possible to set something like this up? I’ve been playing around with formulas but have gotten stuck.
Solved! Go to Solution.
May 01, 2020 02:29 AM
The easiest & best way would be for you to just type up a number before each one of those drop-down choices. Then, Airtable will be able to extract the numbers with the formula I wrote above.
Otherwise, the only other solution for this would be significantly more complex, and totally unnecessary for your small amount of categories to choose from. It would take too long for me to type up in complete detail, but in a nutshell: Each category/subcategory column would need to become its own linked table — with a number in one of its fields. This number could even be an outnumber field. Then, you would link all the new tables back to the original table. In the original table, you would perform lookups on the 3 number fields, and then combine those 3 number fields into a formula.
But again, with the small number of categories/subcategories you have, just type in the number before each one of the drop-down choices.
Apr 30, 2020 07:37 AM
You can just create a formula that looks like this:
Category & "." & Subcategory & "." & Item
In your example, that would result in: 1.2.5
Apr 30, 2020 08:00 AM
thanks. this outputs to the text labels I’m using for the categories. would it be possible to turn that into numbers?
Apr 30, 2020 08:52 AM
VALUE(Category) & "." & VALUE(Subcategory) & "." & VALUE(Item)
Apr 30, 2020 10:58 PM
That gets me a little bit further. Except my subcategories and items are only text, so it’s not able to pull any numbers there. So for the moment that’s returning a lot of zeros. Is there any way to do it with a COUNT?
Apr 30, 2020 11:58 PM
Why don’t you attach a screenshot of your system.
May 01, 2020 01:30 AM
True, that will probably help make easier to understand what I’m try to get at. Basically what I want to do is to have 1.2.5 in the ID.
Product and client… is my first building block. Onboarding of clients is the second category and clients can create user accounts is the fifth item in that category
May 01, 2020 02:29 AM
The easiest & best way would be for you to just type up a number before each one of those drop-down choices. Then, Airtable will be able to extract the numbers with the formula I wrote above.
Otherwise, the only other solution for this would be significantly more complex, and totally unnecessary for your small amount of categories to choose from. It would take too long for me to type up in complete detail, but in a nutshell: Each category/subcategory column would need to become its own linked table — with a number in one of its fields. This number could even be an outnumber field. Then, you would link all the new tables back to the original table. In the original table, you would perform lookups on the 3 number fields, and then combine those 3 number fields into a formula.
But again, with the small number of categories/subcategories you have, just type in the number before each one of the drop-down choices.
May 01, 2020 02:41 AM
You have to assign a number for each category and subcategory. So you can use the SWITCH function to change the category to a number, then using this number you can input it in an ID like @ScottWorld explained.