Nested IF formula help [Solved] | Airtable Community

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”))))))))))))))))))))))))))))))

Page 1 / 1

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:

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:

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”))))))))))))))))))))))))))))))

Thanks @Jeremy_Oglesby

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

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(icondition], if true],sif 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( M {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.

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(Fcondition],]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( C {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.

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")

Hi Neil! The issue with your current approach is that nested IF statements stop evaluating after the first true condition. Instead, you'll want to concatenate all non-blank fields. Try this simpler solution:

MI Bridges

ARRAYJOIN(  ARRAYCOMPACT(Tfilename_1, filename_2, filename_3, ..., filename_30]),  "\n")

This will:

Combine all fields into an array
Remove blank entries (ARRAYCOMPACT)
Join with newlines (ARRAYJOIN)

Much cleaner than 30 nested IFs! For even better readability, consider using a formula field per line and concatenating those.

Let me know if you need help adapting this to your exact setup!

Reply

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded