Formula Help - Contingency and nested if/then?

Hi @Sara_Stahlberg,

You can wrap the conditional segments of your concatenated string in IF() functions to only show them if the conditions hold true.

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

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

Thank you @Jeremy! Unfortunately, this formula wasn’t accepted for me. Let’s try this way. Here are the actual fields that I am working with:

• Pizer #1 - Allocated
• Pfizer #2 - Allocated
• Moderna #3 - Allocated
• Janssen - Allocated

I want to be able to show, for example:
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 8)

I might have some punctuation off somewhere I’m guessing?

Perhaps… can you post the formula that is failing? I double-checked all the formula syntax in what I posted to you above and it looks sound. If all you are replacing is field names, then it should work.

@Jeremy_Oglesby

I think you are missing an & in this line before your second IF.

& IF({Blue - Allocated} > 0, {Blue - Allocated} & " Blue" IF(OR({Red Allocated} > 0, {Yellow - Allocated} > 0), ", "))

@Sara_Stahlberg – edited my formula above based @kuovonne’s fix

Thank you @Jeremy_Oglesby ! This works. Now, if I wanted to add another one in here, where would I add it? Let’s say, Green - Allocated

Because of the conditionals to determine whether or not to add a comma after the name, it’s easier to add it to the beginning of the list of conditionals:

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

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

Thanks, Jeremy! This is generating a weird output for me. Any ideas on why this might happen?

Here’s my formula:

DATETIME_FORMAT({Clinic Date}, ‘MM-DD-YY’)
& " "
& {Clinic Name + Activity}
& " - "
& IF({Pfizer #1 - Allocated} > 0, {Pfizer #1 - Allocated} & " Pfizer #1" & IF({Pfizer #2 - Allocated} > 0, {Moderna #3 - Allocated} > 0, {Janssen - Allocated} > 0), ", “)
& IF({Pfizer #2 - Allocated} > 0, {Pfizer #2 - Allocated} & " Pfizer #2” & IF(OR({Moderna #3 - Allocated} > 0, {Janssen - Allocated} > 0), ", “))
& IF({Moderna #3 - Allocated} > 0, {Moderna #3 - Allocated} & " Moderna #3” & IF({Janssen - Allocated} > 0, “, “))
& IF({Janssen - Allocated} > 0, {Janssen - Allocated} & " Janssen”)
& " - HL Provider: "
& {HL Provider}
& " - ( "
& {Lead Team}
& " )”

And here’s a sample of the output:
09-19-21 Viva Calle / Yu-Ai Kai’s Akiyama Wellness Center - 80 Pfizer #1TRUE40 Janssen - HL Provider: TM6 - ( Team 7 )

Do you know what happened with the TRUE situation there?

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

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} & ")",
)

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