Using the formula field type to automatically fill out Field B based on Field A, for more than 150 unique values

[Senan_D_Souza]

Hi,
So I’m trying to have a single select field type of small mammal species of the world, that should have a list of more than 300 species to choose from; so when i select an option from this field A, i need a formula field B to be automatically filled out with the relevant ‘Genus name’ based on field A. I have tried using SWITCH, but I am allowed to enter a maximum of only 150 species. Is there a solution that would let me add 300+ options to the formula field, without having to create or link multiple tables?I have no experience in coding, so don’t know if this can be done using the scripting app?

Here’s what the formula in Field B-‘Genus name’ looks like:

SWITCH({FIELD A},"Hairy-footed Flying Squirrel","Belomys",
"Namdapha Flying Squirrel","Biswamoyopterus",
"Grey-bellied Squirrel","Callosciurus",
"Pallas’ Squirrel (Red-bellied Squirrel)","Callosciurus",
"Grey-headed Flying Squirrel","Petaurista",
"Large Red Flying Squirrel","Petaurista ",
"Spotted Giant Flying Squirrel","Petaurista ",
"Hodgson's Giant Flying Squirrel","Petaurista ",
"Mechuka Giant Flying Squirrel","Petaurista ",
"Mishmi Giant Flying Squirrel","Petaurista ",
"Bhutan Giant Flying Squirrel","Petaurista ",
"Temminck's Flying Squirrel","Petinomys",
"Vordermann's Flying Squirrel","Petinomys ",
"Grizzled Giant Squirrel (Sri Lankan Giant Squirrel)","Ratufa",
"Black Giant Squirrel (Malayan Giant Squirrel)","Ratufa ",
"Indian Giant Squirrel (Malabar Giant Squirrel)","Ratufa ",
"Himalayan Striped Squirrel","Tamiops",
"Swinhoe's Striped Squirrel","Tamiops",
"Callosciurus sp.","Callosciurus",
"Dremomys sp.","Dremomys ",
"Funambulus sp.","Funambulus",
"Hylopetes sp.","Hylopetes ",
"Marmota sp.","Marmota ",
"Ochotona sp.","Ochotona",
"Petaurista sp.","Petaurista ",
"Petinomys sp.","Petinomys",
"Ratufa sp.","Ratufa ",
"Tamiops sp.","Tamiops",
"Unidentified","Unidentified")

Thanks in advance.

[kuovonne · Accepted solution]

Airtable formulas do have limits. This thread and this thread both are examples of formulas that had limits. On the other hand, I was able to make a quick test of a switch statement with 400 choices, and it worked as expected, so something else might be the issue.

• Is it possible that your formula was invalid for a different reason, such as a repeated option, an extra/missing quote, or an extra/missing comma?

• You could use multiple formula fields. One formula field with a switch statement for the first 150 options. A second formula field with a switch statement for the second 150 options. A third formula field that consolidates the information from the first two formula fields.

[Senan_D_Souza]

Hi Kuovonne,
Thank you so much for your response. Turned out that there was a duplicate option in my species list.Changed it up and the issue was resolved.
Thanks again.