Help

Formula Help - Contingency and nested if/then?

Topic Labels: Formulas
Solved
Jump to Solution
4133 11
cancel
Showing results for 
Search instead for 
Did you mean: 
Sara_Stahlberg
6 - Interface Innovator
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!

11 Replies 11

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
6 - Interface Innovator
6 - Interface Innovator

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