Skip to main content

I know the way I am doing this is unnecessarily complicated and even though it works, I’d be interested in learning how to do the same thing more efficiently.


I have a formula field that looks at the content of another field ({WRITER 2: SOCIETY} and is supposed to fill in a corresponding number.


{WRITER 2: SOCIETY} can contain either the names ASCAP, SESAC, BMI, or any other name.

ASCAP, SESAC, and BMI all have corresponding numbers (with leading zeros) that should be filled in if the statement is true.



  • ASCAP#: 00701163494

  • SESAC#: 00850346936

  • BMI#: 00794154022


If {WRITER 2: SOCIETY} contains any other name, fill in the BMI#: 00794154022


This is what I am doing. How can I simplify this formula?

Thank you in advance!!!


IF(

{WRITER 2: SOCIETY}=0,

‘’,

IF(

{WRITER 2: SOCIETY}=‘ASCAP’,

‘00701163494’,

IF(

{WRITER 2: SOCIETY}=‘SESAC’,

‘00850346936’,

IF(

{WRITER 2: SOCIETY}!=‘ASCAP’,

‘00794154022’,

IF(

{WRITER 2: SOCIETY}!=‘BMI’,

‘00794154022’,

IF(

{WRITER 2: SOCIETY}!=‘SESAC’,

‘00794154022’,

‘00794154022’

))))))

Seems like you could just use a SWITCH() statement since you’re comparing the same item to multiple static values:


SWITCH(

   {WRITER 2: SOCIETY},

   0, '',

   'ASCAP', '00701163494',

   'SESAC', '00850346936',

   'BMI', '00794154022',

   '00794154022'

)

Seems like you could just use a SWITCH() statement since you’re comparing the same item to multiple static values:


SWITCH(

   {WRITER 2: SOCIETY},

   0, '',

   'ASCAP', '00701163494',

   'SESAC', '00850346936',

   'BMI', '00794154022',

   '00794154022'

)

Thank you so much, @Kamille_Parks !


The much simpler formula works for the most part.

What doesn’t work is when {WRITER 2: SOCIETY} is empty.

For that case '‘00794154022’ gets filled in as well.

How can we avoid that?

Thank you so much, @Kamille_Parks !


The much simpler formula works for the most part.

What doesn’t work is when {WRITER 2: SOCIETY} is empty.

For that case '‘00794154022’ gets filled in as well.

How can we avoid that?


Add another line before the final one (the default value for ‘00794154022’:


   BLANK(), '',

Add another line before the final one (the default value for ‘00794154022’:


   BLANK(), '',

SWITCH(

{WRITER 2: SOCIETY},

0, ‘’,

‘ASCAP’, ‘00701163494’,

‘SESAC’, ‘00850346936’,

‘BMI’, ‘00794154022’,

BLANK(), ‘’,

‘00794154022’

)


When I add BLANK(), ‘’, Airtable says it’s an invalid formula code and doesn’t allow me to save it.

SWITCH(

{WRITER 2: SOCIETY},

0, ‘’,

‘ASCAP’, ‘00701163494’,

‘SESAC’, ‘00850346936’,

‘BMI’, ‘00794154022’,

BLANK(), ‘’,

‘00794154022’

)


When I add BLANK(), ‘’, Airtable says it’s an invalid formula code and doesn’t allow me to save it.


Then either replace BLANK() with '' or remove the BLANK() line and wrap the whole SWITCH() in an IF() statement: IF({WRITER 2: SOCIETY}, (insert switch])

Then either replace BLANK() with '' or remove the BLANK() line and wrap the whole SWITCH() in an IF() statement: IF({WRITER 2: SOCIETY}, [insert switch])


This one works:



IF({WRITER 2: SOCIETY}=BLANK(),’’,

SWITCH(

{WRITER 2: SOCIETY},

‘ASCAP’, ‘00701163494’,

‘SESAC’, ‘00850346936’,

‘BMI’, ‘00794154022’,

‘00794154022’

))


Then either replace BLANK() with '' or remove the BLANK() line and wrap the whole SWITCH() in an IF() statement: IF({WRITER 2: SOCIETY}, [insert switch])


I see what you’re saying. I don’t even need the BLANK().


Yes, this one works as well:



IF({WRITER 2: SOCIETY},

SWITCH(

{WRITER 2: SOCIETY},

‘ASCAP’, ‘00701163494’,

‘SESAC’, ‘00850346936’,

‘BMI’, ‘00794154022’,

‘00794154022’

))



Thank you!

Reply


Terms & Conditions