Skip to main content

Hi, I am fairly new to Airtable, so I am trying to learn how to do things more efficiently.

 

I have a question about formulas.
Is there a way to select a field based on an input without using the SWITCH() function? I’m thinking something like a FIELD_SELECTOR() (← this a made up function as far as I know, but maybe something like it exists?), where I input the text display_name of a field, and output the value for that field. Or is this something where I need to start learning how to integrate python, and airtable built-in functions are not powerful enough?

Example:
I am making a database with taxonomic information (ie. latin names of living things). I have all the taxonomic ranks from Phylum to Species as separate fields. I have a field called taxonomic_rank that indicates the lowest taxonomic rank in the row. I want to make a function to create a display name that outputs “taxonomy_name taxonomic_rank”. So functionally, A row that says Arthropoda in Phylum and nothing in the lower ranks would output “Arthropoda Phylum”, and a row that says Arthropoda in Phylum and Arachnida in Class but nothing in the lower ranks would output “Arachnida Class”, and so on.

I think I could do this with the following formula:

SWITCH(

  {taxonomic_rank},

  'Phylum',{Phylum},

  'Class',{Class},

  'Order',{Order},

...

  'Species',{Species},

  'invalid') 

  & ' ' & {taxonomic_rank}

But wow, it would save me so much time if I could do something like this (see above for description of my made up function):

FIELD_SELECTOR({taxonomic_rank}) & ‘ ‘ & {taxonomic_rank}

Something like this might work.  {classification} should be the field that holds which level of taxonomy the record is for.

 

IF(
  {classification} = "Species",
  {Kingdom} & " > " & {Phylum} & " > " & {Class} & " > " & {Order} & " > " & {Family} & " > " & {Genus} & " > " & {Species},

  IF(
    {classification} = "Genus",
    {Kingdom} & " > " & {Phylum} & " > " & {Class} & " > " & {Order} & " > " & {Family} & " > " & {Genus},

    IF(
      {classification} = "Family",
      {Kingdom} & " > " & {Phylum} & " > " & {Class} & " > " & {Order} & " > " & {Family},

      IF(
        {classification} = "Order",
        {Kingdom} & " > " & {Phylum} & " > " & {Class} & " > " & {Order},

        IF(
          {classification} = "Class",
          {Kingdom} & " > " & {Phylum} & " > " & {Class},

          IF(
            {classification} = "Phylum",
            {Kingdom} & " > " & {Phylum},

            IF(
              {classification} = "Kingdom",
              {Kingdom},

              "No classification selected"
            )
          )
        )
      )
    )
  )
)
 


Thanks! I think this would work as well, but the SWITCH function would be more concise in my opinion. Do you know of any other ways to do this besides the IF or SWITCH functions?

Something like this might work.  {classification} should be the field that holds which level of taxonomy the record is for.

 

IF(
  {classification} = "Species",
  {Kingdom} & " > " & {Phylum} & " > " & {Class} & " > " & {Order} & " > " & {Family} & " > " & {Genus} & " > " & {Species},

  IF(
    {classification} = "Genus",
    {Kingdom} & " > " & {Phylum} & " > " & {Class} & " > " & {Order} & " > " & {Family} & " > " & {Genus},

    IF(
      {classification} = "Family",
      {Kingdom} & " > " & {Phylum} & " > " & {Class} & " > " & {Order} & " > " & {Family},

      IF(
        {classification} = "Order",
        {Kingdom} & " > " & {Phylum} & " > " & {Class} & " > " & {Order},

        IF(
          {classification} = "Class",
          {Kingdom} & " > " & {Phylum} & " > " & {Class},

          IF(
            {classification} = "Phylum",
            {Kingdom} & " > " & {Phylum},

            IF(
              {classification} = "Kingdom",
              {Kingdom},

              "No classification selected"
            )
          )
        )
      )
    )
  )
)
 

 


Not really given Airtables formula limitations, could try using the available REGEX commands but at the end of the day its a formula so takes no overhead. The difference of streamlining this kind of thing has no ROI.  Simplifying an automation does because those take resources and time.


Airtable formulas are stored internally using field IDs, not field names. This makes it possible to change a field name and have all the formulas that reference that field update with the change. It also means that there is no existing Airtable formula function that provides the feature that you want. Your SWITCH() formula is the way to go.

 

You are welcome to use the new product suggestion form to submit a new product idea/suggestion. You ca read more about the new form here.


Reply