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!
Best answer by kuovonne
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.
+29Welcome 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})
)
+21IF(
AND({Fixture Model #}, {Bulb Model #}, {Miscellaneous}),
CONCATENATE({Name (from Orders)}," - (",Quantity,") ",{Fixture Model #}," / ",{Bulb Model #}," / ",{Miscellaneous}),
"N/A"
)
@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.
IF(
AND({Fixture Model #}, {Bulb Model #}, {Miscellaneous}),
CONCATENATE({Name (from Orders)}," - (",Quantity,") ",{Fixture Model #}," / ",{Bulb Model #}," / ",{Miscellaneous}),
"N/A"
)
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.
+29If 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.
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.
Awesome! Thanks Kuovonne. This works perfectly. I also changed the N/A to NO to get rid of the /. Thanks for the advice.
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.
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} )
+29So 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} )
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.
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!
+21Hi! 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!
Welcome to the community, @Emma_Garwood! :grinning_face_with_big_eyes: As @kuovonne suggested above, I recommend dropping CONCATENATE() and joining the items with the & operator instead. Here’s the revision with that change applied, and each group only appearing if the relevant fields are filled:
{1st Quantity} & " x " & {1st Pair} &
IF(AND({2nd Quantity}, {2nd Pair}), "\n" & {2nd Quantity} & " x " & {2nd Pair}) &
IF(AND({3rd Quantity}, {3rd Pair}), "\n" & {3rd Quantity} & " x " & {3rd Pair}) &
IF(AND({4th Quantity}, {4th Pair}), "\n" & {4th Quantity} & " x " & {4th Pair})
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.
Thanks so much Kuovonne - it’s going to take me some time to get my head round it, but really appreciate the fix for now :slightly_smiling_face:
Welcome to the community, @Emma_Garwood! :grinning_face_with_big_eyes: As @kuovonne suggested above, I recommend dropping CONCATENATE() and joining the items with the & operator instead. Here’s the revision with that change applied, and each group only appearing if the relevant fields are filled:
{1st Quantity} & " x " & {1st Pair} &
IF(AND({2nd Quantity}, {2nd Pair}), "\n" & {2nd Quantity} & " x " & {2nd Pair}) &
IF(AND({3rd Quantity}, {3rd Pair}), "\n" & {3rd Quantity} & " x " & {3rd Pair}) &
IF(AND({4th Quantity}, {4th Pair}), "\n" & {4th Quantity} & " x " & {4th Pair})
that’s so great, thanks Justin! I don’t know how you guys work this out - i’ve got a lot of learning to do! works a dream :slightly_smiling_face:
+21that’s so great, thanks Justin! I don’t know how you guys work this out - i’ve got a lot of learning to do! works a dream :slightly_smiling_face:
Glad to know that you got the answer you were seeking! When it comes to working this stuff out, I’ll repeat something I said in another thread recently: a lot of it has to do with patterns. Learn to see the patterns in both the data you’re analyzing, and the scripting options available, and you can learn how to systematically process the data.
I’ve got a YouTube channel that’s all about Airtable, and my initial plan for it was to do a lot of deep diving into how certain parts of Airtable work. It’s taking longer than I thought to make that happen, though. While I still feel that’s useful, I felt prompted recently to shift gears slightly and start breaking down the logic behind various formula solutions and base designs I’ve shared here. It’ll be a lot more informal, but I get the feeling that it could be just as beneficial as some of the deeper topics I’ve got planned. Anyway, this formula will be in the first video of that new series, and I’ll be sure to let you know when it’s live.
Glad to know that you got the answer you were seeking! When it comes to working this stuff out, I’ll repeat something I said in another thread recently: a lot of it has to do with patterns. Learn to see the patterns in both the data you’re analyzing, and the scripting options available, and you can learn how to systematically process the data.
I’ve got a YouTube channel that’s all about Airtable, and my initial plan for it was to do a lot of deep diving into how certain parts of Airtable work. It’s taking longer than I thought to make that happen, though. While I still feel that’s useful, I felt prompted recently to shift gears slightly and start breaking down the logic behind various formula solutions and base designs I’ve shared here. It’ll be a lot more informal, but I get the feeling that it could be just as beneficial as some of the deeper topics I’ve got planned. Anyway, this formula will be in the first video of that new series, and I’ll be sure to let you know when it’s live.
That sounds like it’s going to be a great watch. I learn well by watching videos - unless it’s how to make a soufflé rise… you haven’t got a formula for that, have you?!
+21That sounds like it’s going to be a great watch. I learn well by watching videos - unless it’s how to make a soufflé rise… you haven’t got a formula for that, have you?!
Haha…nope, I’m not the one to go to for cooking help. I like a lot of oddball things, and often just make it up as I go depending on what I’m craving at the moment. That, and I’ve never made a soufflé. :slightly_smiling_face:
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.