Skip to main content
Solved

Numbering based on single select categories

  • April 30, 2020
  • 8 replies
  • 53 views
M

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.

Best answer by ScottWorld

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


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.

8 replies

ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • April 30, 2020

You can just create a formula that looks like this:

Category & "." & Subcategory & "." & Item

In your example, that would result in: 1.2.5

M
  • mayorcesar
  • Author
  • New Participant
  • April 30, 2020

You can just create a formula that looks like this:

Category & "." & Subcategory & "." & Item

In your example, that would result in: 1.2.5


thanks. this outputs to the text labels I’m using for the categories. would it be possible to turn that into numbers?

ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • April 30, 2020

thanks. this outputs to the text labels I’m using for the categories. would it be possible to turn that into numbers?


VALUE(Category) & "." & VALUE(Subcategory) & "." & VALUE(Item)

M
  • mayorcesar
  • Author
  • New Participant
  • May 1, 2020

VALUE(Category) & "." & VALUE(Subcategory) & "." & VALUE(Item)


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?

ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • May 1, 2020

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?


Why don’t you attach a screenshot of your system.

M
  • mayorcesar
  • Author
  • New Participant
  • May 1, 2020

Why don’t you attach a screenshot of your system.


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

ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • Answer
  • May 1, 2020

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


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.

Mohamed_Swella1
Forum|alt.badge.img+17

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


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.

Terms & ConditionsAccessibility statement