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!

[Jeremy_Oglesby]

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

[Sara_Stahlberg]

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 😎

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

[Jeremy_Oglesby]

Sara_Stahlberg:

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.

[kuovonne]

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

[Jeremy_Oglesby]

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

[Sara_Stahlberg]

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

[Jeremy_Oglesby]

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

[Sara_Stahlberg]

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?

[Jeremy_Oglesby · Accepted solution]

Sara_Stahlberg:

& IF({Pfizer #1 - Allocated} > 0, {Pfizer #1 - Allocated} & " Pfizer #1" & IF({Pfizer #2 - Allocated} > 0, {Moderna #3 - Allocated} > 0, {Janssen - Allocated} > 0), ", “)

That line needs to be:

& IF({Pfizer #1 - Allocated} > 0, {Pfizer #1 - Allocated} & " Pfizer #1" & IF(OR({Pfizer #2 - Allocated} > 0, {Moderna #3 - Allocated} > 0, {Janssen - Allocated} > 0), ", "))

You were just missing the OR() function. That last IF() with the OR() function is just determining whether or not to show a comma, depending on if there are other dose-allocations to account for after this one.