Skip to main content

I've tried to work my way around this problem several ways and I can't seem to figure it out... I have a nested IF statement that uses a value from a text lookup field that just doesn't want to play nice. 

Essentially, I have a formula title for a record that should work like this
IF {single select}= "A", {text1} & " - " & {text2}, IF {single select} = "B" & {lookupfield} = Not Empty, {text1} & " - " & {lookupfield}, {text1}))

I can't get the lookup text to cooperate and express itself as not empty (or empty). I tried T(), BLANK(), and COUNTA(), and even though in a separate formula field COUNTA() gives me 1 and 0, trying to incorporate that into the overall formula doesn't work. I get that the lookup field isn't actually text... I'm going crazy trying to figure this one out.

Could you provide screenshots of your data setup?  I tried replicating this but couldn't, so I figure my setup's different somehow! 

 

   

Could you provide screenshots of your data setup?  I tried replicating this but couldn't, so I figure my setup's different somehow! 

 

   


Sure, image 1 here includes all relevant fields, and image 2 is the current formula that I can't seem to improve. The issue I'm trying to eliminate is the bottom record in image 1 " One Time Little Wish Program - ". Because that address doesn't have an alt name (i.e. The Richards Center or whatever) in the linked address record, it should just display the service name and nothing else, getting rid of that dash. I left out from the original post that this setup lives under a total override so when necessary the name can be changed entirely, but that part should be ancillary (it just makes it 4 nested IFs rather than 3). 

I'm aware that the current formula isn't written that way (I'm collabing on this project) but I'm having a devil of a time writing something that essentially says "If Virtual, service - org name. If In-Person with alt name, service - alt name. If In-Person no alt name, service".  

Sure, image 1 here includes all relevant fields, and image 2 is the current formula that I can't seem to improve. The issue I'm trying to eliminate is the bottom record in image 1 " One Time Little Wish Program - ". Because that address doesn't have an alt name (i.e. The Richards Center or whatever) in the linked address record, it should just display the service name and nothing else, getting rid of that dash. I left out from the original post that this setup lives under a total override so when necessary the name can be changed entirely, but that part should be ancillary (it just makes it 4 nested IFs rather than 3). 

I'm aware that the current formula isn't written that way (I'm collabing on this project) but I'm having a devil of a time writing something that essentially says "If Virtual, service - org name. If In-Person with alt name, service - alt name. If In-Person no alt name, service".  


Hmm, does this look right?

IF(
Type = "Virtual",
Service & ' - ' & {Org name (from Service)}
) &
IF(
Type = "In-person",
IF(
{alternate name (from physical_address)} ,
Service & ' - ' & {alternate name (from physical_address)},
Service
)
)

Link to base 

If not, could you provide a read-only link to a duplicate of your base with no data in it?

Reply


Terms & Conditions