Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Sep 06, 2021 01:16 PM
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:
Is there a way to accomplish what I’m looking to do?
Any assistance would be helpful. Thanks so much!
Solved! Go to Solution.
Sep 07, 2021 11:55 AM
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.
Sep 06, 2021 03:11 PM
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}
& " )"
Sep 06, 2021 03:50 PM
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:
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?
Sep 06, 2021 03:54 PM
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.
Sep 06, 2021 04:07 PM
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), ", "))
Sep 06, 2021 04:23 PM
@Sara_Stahlberg – edited my formula above based @kuovonne’s fix
Sep 06, 2021 04:56 PM
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
Sep 06, 2021 08:58 PM
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}
& " )"
Sep 07, 2021 11:47 AM
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?
Sep 07, 2021 11:55 AM
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.