Expand my Community achievements bar.

SOLVED

Concat - remove dash if optional field left blank

Avatar

Level 4

I am trying to remove dashes if an optional field is not filled out.  

 

Here is my current formula:

CONCAT({DE:Region - Requesting Business Area},"-",{DE:Primary Requesting Business Area},"-", {DE:Secondary Business Focus},"-",RIGHT(YEAR({entryDate}),2),"-",{DE:Rally Feature Number},"-",{DE:TruBrand Family})

 

The first and third fields are optional (Region Requesting Business Area and Secondary Business Area) so I would like to remove the "-" if those fields are blank. 

 

I've tried to use the If IsBlank formula a few different ways and have not been successful. Can anyone help?  ty! 

 

Here's my last attempt: 

 

IF(ISBLANK({DE:Region - Requesting Business Area}," ","-",

IF(ISBLANK({DE:Primary Requesting Business Area}," ","-",

IF(ISBLANK({DE:Secondary Business Focus}," ","-",

CONCAT({DE:Region - Requesting Business Area},"-",{DE:Primary Requesting Business Area},"-", {DE:Secondary Business Focus},"-",RIGHT(YEAR({entryDate}),2),"-",{DE:Rally Feature Number},"-",{DE:TruBrand Family})))))))

1 Accepted Solution

Avatar

Correct answer by
Level 5

Hey @TCarey, give this a try:

 

displayname=My Calculated Field
textmode=true
valueexpression=CONCAT(IF(ISBLANK({DE:Region - Requesting Business Area}),"",CONCAT({DE:Region - Requesting Business Area}," - ")),{DE:Primary Requesting Business Area},IF(ISBLANK({DE:Secondary Business Focus}),"",CONCAT(" - ",{DE:Secondary Business Focus}))," - ",RIGHT(YEAR({entryDate}),2)," - ",{DE:Rally Feature Number}," - ",{DE:TruBrand Family})
valueformat=HTML

View solution in original post

4 Replies

Avatar

Level 5

I often have trouble with ISBLANK depending on the type of field I'm using in the formula. What seems to work better for me is checking the length of the field. So something like:

IF(LEN({DE:Region - Requesting Business Area}>0,"-","")

So if there's any value in that field, it should get a -, otherwise it'll get nothing.

Avatar

Level 4

thanks for your response!  However, I still can't seem to get it to work.  I'm sure it's how I'm phrasing it, I've tried a number of different ways and the system keeps highlighting IF LEN as errors. 

 

Concat all the chosen fields. If the length of a chosen field is less than 0 characters do not use a dash. 

 

CONCAT(IF(LEN({DE:Primary Requesting Business Area}>0,"-","",IF(LEN({DE:Secondary Business Focus}>0,"-","", RIGHT(YEAR({entryDate}),2),"-",{DE:Rally Feature Number},"-",{DE:TruBrand Family})))))

 

IF(LEN({DE:Primary Requesting Business Area}>0,"-",""+

IF(LEN({DE:Secondary Business Focus}>0,"-",""+RIGHT(YEAR({entryDate}),2),"-",{DE:Rally Feature Number},"-",{DE:TruBrand Family})))))

Avatar

Correct answer by
Level 5

Hey @TCarey, give this a try:

 

displayname=My Calculated Field
textmode=true
valueexpression=CONCAT(IF(ISBLANK({DE:Region - Requesting Business Area}),"",CONCAT({DE:Region - Requesting Business Area}," - ")),{DE:Primary Requesting Business Area},IF(ISBLANK({DE:Secondary Business Focus}),"",CONCAT(" - ",{DE:Secondary Business Focus}))," - ",RIGHT(YEAR({entryDate}),2)," - ",{DE:Rally Feature Number}," - ",{DE:TruBrand Family})
valueformat=HTML

Avatar

Level 4

Amazing!  Thank you so much @J_Mas !

 

I had to tweak it a little bit - but it worked!  Here's my final formula that I used in our custom form: 

 

CONCAT({DE:Primary Requesting Business Area},IF(ISBLANK({DE:Secondary Business Focus}),"",CONCAT("-",{DE:Secondary Business Focus})),"-",RIGHT(YEAR({entryDate}),2),"-",{DE:Rally Feature Number},"-",{DE:TruBrand Family})