Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

# Re: Formula Help - Contingency and nested if/then?

Solved
246 0
cancel
Showing results for
Did you mean:
6 - Interface Innovator

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!

1 Solution

Accepted Solutions
14 - Jupiter

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.

11 Replies 11
14 - Jupiter

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}
& " - ( "
& " )"
``````
6 - Interface Innovator

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?

14 - Jupiter

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.

18 - Pluto

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

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

6 - Interface Innovator

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

14 - Jupiter

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}
& " - ( "
& " )"
``````
6 - Interface Innovator

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

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?

14 - Jupiter

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.

18 - Pluto

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} & ")",
)
``````
6 - Interface Innovator

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