Replacing values with other values in drop down

Hi. Kinda new to Airtable so I hope my question is not dumb.

What I need to do is to make a serial number generator of a kind. The serial number consists of 3 two-digit elements each element indicates a variable. So what I need is a document, where there are 3 dropdowns: Drop down1, Dropdown 2, Dropdown 3. In those dropdowns, there are 5 text values: Text 1-1 to Text 1-5 in the first drop-down, Text 2-1 to Text 2-5 in second and so on. There must be a numeric value assigned to each text value that the user does not see, so, for example, Text 1-1 is equal to 11, Text 1-2 is 12, Text 2-4 is 24 and so on.

Whenever a user chooses a value from the drop-down the numeric value appears on a cell marked as a complete serial number. If all dropdowns are left blank then it says 000000, if on dropdowns Text Value 1-3, Text Value 2-1 and Text Value 3-3 are selected, than the cell marked Serial number says 132133 and so on. All 6 numbers appear in one cell next to each other.

Does anyone have an idea of how to create something like this in Airtable?

If your dropdowns will follow that format (Text 1-1), you can use the following formula:

SUBSTITUTE(IF({Dropdown 1 field}, RIGHT({Dropdown 1 field},3), 00)&IF({Dropdown 2 field}, RIGHT({Dropdown 2 field},3), 00)&IF({Dropdown 3 field}, RIGHT({Dropdown 3 field},3), 00), '-', '')

What the formula is doing is looking at each dropdown, taking only the three characters at the end of the value, and writing them next to each other. If a dropdown is empty it writes ‘00’ instead. Then it substitutes every dash character with nothing to get a 6 digit serial number.

If your dropdowns don’t follow that format, you’ll have to use a formula structure like this:

SWITCH({Dropdown 1 field}, 'Possible value 1', 11, 'Possible value 2', 12, 'Possible value 3', 13)&SWITCH(repeat for second dropdown)&SWITCH(repeat for third dropdown)

2 Likes

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.