Nested IF formula help [Solved]


#1

Can anyone point out what i’m doing wrong here?

The goal is to create a list from 30 different fields.
Each field should be on a new line
We want to skip any fields that are empty. E.g don’t add a newline for empty fields.

The latest version of my nested IF formula looks like this. Any help greatly appreciated!

IF ({filename_1}!=BLANK(),filename_1 & “\n”,IF ({filename_2}!=BLANK(),filename_2 & “\n”,IF ({filename_3}!=BLANK(),filename_3 & “\n”,IF ({filename_4}!=BLANK(),filename_4 & “\n”,IF ({filename_5}!=BLANK(),filename_5 & “\n”,IF ({filename_6}!=BLANK(),filename_6 & “\n”,IF ({filename_7}!=BLANK(),filename_7 & “\n”,IF ({filename_8}!=BLANK(),filename_8 & “\n”,IF ({filename_9}!=BLANK(),filename_9 & “\n”,IF ({filename_10}!=BLANK(),filename_10 & “\n”,IF ({filename_11}!=BLANK(),filename_11 & “\n”,IF ({filename_12}!=BLANK(),filename_12 & “\n”,IF ({filename_13}!=BLANK(),filename_13 & “\n”,IF ({filename_14}!=BLANK(),filename_14 & “\n”,IF ({filename_15}!=BLANK(),filename_15 & “\n”,IF ({filename_16}!=BLANK(),filename_16 & “\n”,IF ({filename_17}!=BLANK(),filename_17 & “\n”,IF ({filename_18}!=BLANK(),filename_18 & “\n”,IF ({filename_19}!=BLANK(),filename_19 & “\n”,IF ({filename_20}!=BLANK(),filename_20 & “\n”,IF ({filename_21}!=BLANK(),filename_21 & “\n”,IF ({filename_22}!=BLANK(),filename_22 & “\n”,IF ({filename_23}!=BLANK(),filename_23 & “\n”,IF ({filename_24}!=BLANK(),filename_24 & “\n”,IF ({filename_25}!=BLANK(),filename_25 & “\n”,IF ({filename_26}!=BLANK(),filename_26 & “\n”,IF ({filename_27}!=BLANK(),filename_27 & “\n”,IF ({filename_28}!=BLANK(),filename_28 & “\n”,IF ({filename_29}!=BLANK(),filename_29 & “\n”,IF ({filename_30}!=BLANK(),filename_30 & “\n”))))))))))))))))))))))))))))))


#2

Try removing all instances of !=BLANK() in your formula, but leave everything else as is.

That piece is superfluous and doesn’t always work. See here:


#3

Thanks @Jeremy_Oglesby

I tried that but still getting an error. Here’s the update:

IF ({filename_1}(),filename_1 & “\n”,IF ({filename_2}(),filename_2 & “\n”,IF ({filename_3}(),filename_3 & “\n”,IF ({filename_4}(),filename_4 & “\n”,IF ({filename_5}(),filename_5 & “\n”,IF ({filename_6}(),filename_6 & “\n”,IF ({filename_7}(),filename_7 & “\n”,IF ({filename_8}(),filename_8 & “\n”,IF ({filename_9}(),filename_9 & “\n”,IF ({filename_10}(),filename_10 & “\n”,IF ({filename_11}(),filename_11 & “\n”,IF ({filename_12}(),filename_12 & “\n”,IF ({filename_13}(),filename_13 & “\n”,IF ({filename_14}(),filename_14 & “\n”,IF ({filename_15}(),filename_15 & “\n”,IF ({filename_16}(),filename_16 & “\n”,IF ({filename_17}(),filename_17 & “\n”,IF ({filename_18}(),filename_18 & “\n”,IF ({filename_19}(),filename_19 & “\n”,IF ({filename_20}(),filename_20 & “\n”,IF ({filename_21}(),filename_21 & “\n”,IF ({filename_22}(),filename_22 & “\n”,IF ({filename_23}(),filename_23 & “\n”,IF ({filename_24}(),filename_24 & “\n”,IF ({filename_25}(),filename_25 & “\n”,IF ({filename_26}(),filename_26 & “\n”,IF ({filename_27}(),filename_27 & “\n”,IF ({filename_28}(),filename_28 & “\n”,IF ({filename_29}(),filename_29 & “\n”,IF ({filename_30}(),filename_30 & “\n”))))))))))))))))))))))))))))))


#4

There are two possible issues here:
The first is that you might be hitting a limit to formula nesting depth.
The second is that your formula isn’t structured quite right.
Given that the IF function has the syntax
IF([condition],[if true],[if false])
you would only ever get two lines of output: the first non-blank field, and a blank line producted by the \n.
I think what you’re trying to do could be accomplished by the following structure:

IF( {filename_1} , {filename_1} & "\n" ) &
IF( {filename_2} , {filename_2} & "\n" ) &
...
IF( {filename_30} , {filename_30} )

Alternatively, the ARRAYCOMPACT and ARRAYJOIN functions were designed for this purpose:

ARRAYJOIN( ARRAYCOMPACT( [ {filename_1} , {filename_2} , ... , {filename_30} ] ) , "\n" )

Note, however, that I’ve never used Airtable’s array functions outside of a rollup so I have no idea if this will actually work.


#5

Thanks @Andy_Lin1.

The first one works! The key was to remove any spaces :-/

Here’s the final version that works:

IF({filename_1},{filename_1}&"\n") &
IF({filename_2},{filename_2}&"\n") &
IF({filename_3},{filename_3}&"\n") &
IF({filename_4},{filename_4}&"\n") &
IF({filename_5},{filename_5}&"\n") &
IF({filename_6},{filename_6}&"\n") &
IF({filename_7},{filename_7}&"\n") &
IF({filename_8},{filename_8}&"\n") &
IF({filename_9},{filename_9}&"\n") &
IF({filename_10},{filename_10}&"\n") &
IF({filename_11},{filename_11}&"\n") &
IF({filename_12},{filename_12}&"\n") &
IF({filename_13},{filename_13}&"\n") &
IF({filename_14},{filename_14}&"\n") &
IF({filename_15},{filename_15}&"\n") &
IF({filename_16},{filename_16}&"\n") &
IF({filename_17},{filename_17}&"\n") &
IF({filename_18},{filename_18}&"\n") &
IF({filename_19},{filename_19}&"\n") &
IF({filename_20},{filename_20}&"\n") &
IF({filename_21},{filename_21}&"\n") &
IF({filename_22},{filename_22}&"\n") &
IF({filename_23},{filename_23}&"\n") &
IF({filename_24},{filename_24}&"\n") &
IF({filename_25},{filename_25}&"\n") &
IF({filename_26},{filename_26}&"\n") &
IF({filename_27},{filename_27}&"\n") &
IF({filename_28},{filename_28}&"\n") &
IF({filename_29},{filename_29}&"\n") &
IF({filename_30},{filename_30}&"\n")