Skip to main content

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!

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}

& " )"

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}

& " )"

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?

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

@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

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}

& " )"

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}

& " )"

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?

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?



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.

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 - e25 Pfizer #1]a12 Pfizer #2]a10 Janssen] - HL Provider: TM9 - (Team 9)


09-06-21 Airtable Clinic Name Test - t20 Pfizer #1]a5 Moderna #3]a20 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!

Reply


Terms & Conditions