Skip to main content

Hi all,



I’m using Airtable to create a mood tracker. I have a form question which asks which of the following 20 emotions I’m feeling, multiple answers = ok.



:green_square: Balanced


:green_square: Calm


:green_square: Cozy


:green_square: Loving


:green_square: Fulfilled


:yellow_square: Energised


:yellow_square: Focused


:yellow_square: Happy


:yellow_square: Inspired


:yellow_square: Motivated


:blue_square: Bored


:blue_square: Drained


:blue_square: Lonely


:blue_square: Sad


:blue_square: Tired


:red_square: Angry


:red_square: Anxious


:red_square: Hungover


:red_square: Restless


:red_square: Stressed


:red_square: Tense



What’d I’d like to achieve then is to have a pie chart look at these emotions and display an ‘average’ with each of the 4 colours. My plan was to use IF(FIND) to create a kind of mood average column in the database to direct the pie chart to, but I’m having a bit of difficulty writing the script.



I was going down the track of:



   IF


   ( FIND(


        'Balanced', Mood


        ),


    FIND(


        'Calm', Mood


        ),


    FIND(


        'Cozy', Mood


        ),


    FIND(


        'Loving', Mood


        ),


        'Green',


        'Not green'


    )


But then I don’t think I can nest FIND objects inside the IF statement like this. Could anybody help me, please? Am I on the right track?

Welcome to the community, @Ciaran_Duffy!



Yes, you can nest, but in Airtable, you are required to keep repeating the IF() function over & over again, like this:



IF(


FIND('Balanced',Mood),'Green',


IF(


FIND('Calm',Mood),'Green',


IF(


FIND('Cozy',Mood),'Green',


IF(


FIND('Loving',Mood),'Green',


'Not Green'


))))

Welcome to the community, @Ciaran_Duffy!



Yes, you can nest, but in Airtable, you are required to keep repeating the IF() function over & over again, like this:



IF(


FIND('Balanced',Mood),'Green',


IF(


FIND('Calm',Mood),'Green',


IF(


FIND('Cozy',Mood),'Green',


IF(


FIND('Loving',Mood),'Green',


'Not Green'


))))

That set me on the right path! I’ve got it now, thanks Scott!

Alternatively, you could use the OR() logical function, like this:



IF(


  OR(


    FIND('Balanced', Mood),


    FIND('Calm', Mood),


    FIND('Cozy', Mood),


    FIND('Loving', Mood)


  ),


  'Green',


  'Not Green'


)

Yet another possibility is to use regular expressions. Because multiple answers can apply, you can concatenate everything together instead of using nested functions:



IF(REGEX_MATCH(Mood, "Balanced|Calm|Cozy|Loving|Fulfilled"), "🟩") &


IF(REGEX_MATCH(Mood, "Energised|Focused|Happy|Inspired|Motivated"), "🟨") &    


IF(REGEX_MATCH(Mood, "Bored|Drained|Lonely|Sad|Tired"), "🟦") &


IF(REGEX_MATCH(Mood, "Angry|Anxious|Hungover|Restless|Stressed|Tense"), "🟥")


Alternatively, you could use the OR() logical function, like this:



IF(


  OR(


    FIND('Balanced', Mood),


    FIND('Calm', Mood),


    FIND('Cozy', Mood),


    FIND('Loving', Mood)


  ),


  'Green',


  'Not Green'


)

Thanks for the refinement, @Jeremy_Oglesby! This is much cleaner than my formula!

Yet another possibility is to use regular expressions. Because multiple answers can apply, you can concatenate everything together instead of using nested functions:



IF(REGEX_MATCH(Mood, "Balanced|Calm|Cozy|Loving|Fulfilled"), "🟩") &


IF(REGEX_MATCH(Mood, "Energised|Focused|Happy|Inspired|Motivated"), "🟨") &    


IF(REGEX_MATCH(Mood, "Bored|Drained|Lonely|Sad|Tired"), "🟦") &


IF(REGEX_MATCH(Mood, "Angry|Anxious|Hungover|Restless|Stressed|Tense"), "🟥")



Ooh, @Justin_Barrett, that’s cool!

Reply


Terms & Conditions