Hey I am trying to set up a formula for the below problem. I am provided with a list of names in the 'home' column and certain entries I need to change to end up like the 'bot name'.
I have tried switch/If functions and I just get errors.
Formula If or Switch Help
+2
+25Switch(Home,
'Team ABC FC', 'ABC FC',
....
'Team Hij', 'Hij')
+29How many different values are there in the {Home} text field? Do you have a complete list of all possible values? If you have only a limited number of possible values, you can use the pattern in the SWITCH() function that Alexey pointed out. However, if you have many values, the values might change, or new values might be added, you may need a different approach so you don't have to constantly update the formula whenever a new name is entered.
I can't see the logic used to determine the {Bot Name}. It seems like most of the time you strip out the word "Team" from the beginning and ensure that "FC" is at the end. This logic could be implemented with a formula, but it would be formula with multiple nested functions, not a simple SWITCH() or IF(). However, then you have "Team DEF" which doesn't fit that pattern, so you may need something more complex.
Does a single value in the {Home} text field occur in multiple rows? If this is commonplace, consider using a linked table with one field for the {Home} field value and another field for the {Bot Name} and use a lookup field for the {Bot Name}. Do the three parts of the {Home} field value each have meaning? If so, consider using three different fields for the individual parts, and then consolidate the info in a formula field.
+2How many different values are there in the {Home} text field? Do you have a complete list of all possible values? If you have only a limited number of possible values, you can use the pattern in the SWITCH() function that Alexey pointed out. However, if you have many values, the values might change, or new values might be added, you may need a different approach so you don't have to constantly update the formula whenever a new name is entered.
I can't see the logic used to determine the {Bot Name}. It seems like most of the time you strip out the word "Team" from the beginning and ensure that "FC" is at the end. This logic could be implemented with a formula, but it would be formula with multiple nested functions, not a simple SWITCH() or IF(). However, then you have "Team DEF" which doesn't fit that pattern, so you may need something more complex.
Does a single value in the {Home} text field occur in multiple rows? If this is commonplace, consider using a linked table with one field for the {Home} field value and another field for the {Bot Name} and use a lookup field for the {Bot Name}. Do the three parts of the {Home} field value each have meaning? If so, consider using three different fields for the individual parts, and then consolidate the info in a formula field.
I have potential 20 in these base but have about 80 or more across bases, They only change once a year as they are sporting teams.
Your lookup option may be easier to manage. How would that work, I have only been using airtable in very basic functionality.
Thanks for your help so far.
+2Switch(Home,
'Team ABC FC', 'ABC FC',
....
'Team Hij', 'Hij')
Thanks, I was struggling how to type it out, that has worked but going to also look at the other option as there are a lot of names I need to add across all bases.
+29I have potential 20 in these base but have about 80 or more across bases, They only change once a year as they are sporting teams.
Your lookup option may be easier to manage. How would that work, I have only been using airtable in very basic functionality.
Thanks for your help so far.
Here are the Airtable support pages on linked record fields and lookup fields. (They work together.)
This support page has a video on creating a new linked table by converting an existing field to a linked record field.
Login to the community
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.