Adding IF formula to a column with an existing concatenate formula

[Contact_Lights_]

Hey guys!

I’m looking to add an if formula to the formula below. What I need is that if the “fixture model #”, the “bulb model #” or the “miscellaneous” fields are empty, the output of the concatenate funtion is “N/A”

CONCATENATE({Name (from Orders)}," - “,”(",Quantity,") “,{Fixture Model #},” / “,{Bulb Model #},” / ",{Miscellaneous})

Thanks in advance!

[kuovonne]

Welcome to the Airtable community!

You can accomplish this task with a combination of the IF, OR, and NOT functions.
They are all documented in the Logical functions section of the formula field reference.

IF(
  OR(
    NOT({Fixture Model #}),
    NOT({Bulb Model #}),
    NOT({Miscellaneous})
  ),
  "N/A",
  CONCATENATE({Name (from Orders)}," - “,”(",Quantity,") “,{Fixture Model #},” / “,{Bulb Model #},” / ",{Miscellaneous})
)

[Justin_Barrett]

IF(
    AND({Fixture Model #}, {Bulb Model #}, {Miscellaneous}),
    CONCATENATE({Name (from Orders)}," - (",Quantity,") ",{Fixture Model #}," / ",{Bulb Model #}," / ",{Miscellaneous}),
    "N/A"
)

[kuovonne]

@Justin_Barrett’s formula is much better than mine. His formula checks to make sure that there are values in all three fields. Mine checks if any of the fields are blank (which is how you originally phrased your conditions.) The end result of both is the same, but his is more concise.

[Contact_Lights_]

So I guess clarity goes a long way here, lol. These are great. but what I should have said is that I still need the concatenate output of the items that ARE there. Just in the places that there is no value, I would like to insert “N/A” instead.

[kuovonne · Accepted solution]

If you want to use a different string when a field value is empty, use an IF.

Here is the general syntax:

IF({field name}, {field name}, "other text")

I also suggest that you use the & operator to concatenate your text instead of the CONCATENATE function.

{Name (from Orders)} & 
" - (" & Quantity & ") " &
IF({Fixture Model #}, {Fixture Model #}, "N/A") & 
" / " &
IF({Bulb Model #}, {Bulb Model #}, "N/A") & 
" / " &
IF({Miscellaneous}, {Miscellaneous}, "N/A")

You may also want to change the symbol you use between field values, as the / between field and the / in N/A can get confusing.

[Contact_Lights_]

kuovonne:

{Name (from Orders)} & 
" - (" & Quantity & ") " &
IF({Fixture Model #}, {Fixture Model #}, "N/A") & 
" / " &
IF({Bulb Model #}, {Bulb Model #}, "N/A") & 
" / " &
IF({Miscellaneous}, {Miscellaneous}, "N/A")

Awesome! Thanks Kuovonne. This works perfectly. I also changed the N/A to NO to get rid of the /. Thanks for the advice.

[Contact_Lights_]

So I guess I have one more question. In the formula below, I should change to the & instead of using concatenate? Also, how would I get the “-” or spaces to not show if there was no value in the field?

CONCATENATE(Manufacturer, " - “, {Product Type},” - “, {Manu. Model #},” - ",{Fixture Type}, " - “,{Fixture Material},” - “,{Finish Color},” - “,{Bulb Type},” - “,Voltage,” - “,Wattage,” - “,{Beam Spread},” - ",{Kelvin/Color} )

[kuovonne]

Since you are going to re-write the formula anyway, I recommend switching to the & operator instead of CONCATENATE. (But I wouldn’t change a working formula only for the sake of changing it.)

You can include the - in the IF formula. For example, the following formula will include the - and the field value if there is a field value. If the field is blank, it will show nothing at all.

IF({Fixture Type}, " - " & {Fixture Type})

The main issue arrises if the very first field is optional. If it is you have to get more creative. But concatenating IF statements like this one usually is sufficient.

[Emma_Garwood]

Hi! I was hoping I could piggy back on this topic, as my brain has reached max. RAM and I just can’t seem to get this right.
My original formula was

CONCATENATE(

{1st Quantity}," x “,{1st Pair},”\n",
{2nd Quantity}," x “,{2nd Pair},”\n",
{3rd Quantity}," x “,{3rd Pair},”\n",
{4th Quantity}," x ",{4th Pair})

Which works fine if there are values in the 2nd, 3rd + 4th Quantity and Pair cells. However, I only want those “x” characters to show if the cells are not empty. If they are empty, I’d rather they didn’t show!

Thanks in advance!