Formula Help - Contingency and nested if/then?

[Sara_Stahlberg]

Ok, I am working on a project and have been staring at my formula for quite a while now. Hoping somebody can help me out! Essentially, I would like to combine several fields into one, but include certain fields only when they are filled. Here is my formula:

DATETIME_FORMAT({Clinic Date},‘MM-DD-YY’)
&" “&{Clinic Name + Activity}&” - “&{Blue - Allocated}&” Blue"&", “&{Red Allocated}&” Red “&” , “&{Yellow - Allocated}&” Yellow"&" - HL Provider: “&{HL Provider}&” - ( “&{Lead Team}&” )"

In this scenario, the following are true:

• Blue, Red, and Yellow allocations are numeric fields that may or may not be filled in on each row. I’d like them to only appear if they have a number greater than 0 populating that field on a given row (otherwise, I don’t need to see it in the formulated text)
• HL Provider, Clinic Name, Lead Team are all just other fields that I am interested in seeing in the text, and I’ve tried to format it accordingly.

Is there a way to accomplish what I’m looking to do?

Any assistance would be helpful. Thanks so much!

[kuovonne]

Part of the difficulty with this formula is the logic for having tidy commas between items without any extra commas. Often in situations like this, I find that the extra work involved in managing commas simply isn’t worth the effort and difficulties in maintaining the formula. Instead I put beginning/ending delineators around the list items such as …

---

09-06-21 Airtable Clinic Name - [25 Pfizer #1][12 Pfizer #2][10 Janssen] - HL Provider: TM9 - (Team 9)

09-06-21 Airtable Clinic Name Test - [20 Pfizer #1][5 Moderna #3][20 Janssen] - HL Provider: TM8 - (Team 😎

---

This formula is written in my personal style that combines both CONCATENATE and & to make it easier to identify individual items being put together.

CONCATENATE(
    DATETIME_FORMAT({Clinic Date}, 'MM-DD-YY') & " ",
    {Clinic Name + Activity} & " - ",
    IF( {Blue - Allocated} > 0,  "[" & {Blue - Allocated} & " Blue]" ),
    IF( {Red - Allocated} > 0,  "[" & {Red - Allocated} & " Red]" ),
    IF( {Yellow - Allocated} > 0,  "[" & {Yellow - Allocated} & " Yellow]" ),
    IF( {Green - Allocated} > 0,  "[" & {Green - Allocated} & "Green]" ),
    " - HL Provider: " & {HL Provider},
    " - (" & {Lead Team} & ")",
)

[Sara_Stahlberg]

Thank you @kuovonne nd @Jeremy_Oglesby! You both truly saved me with figuring this out!