Text calculated custom fields CONCAT separator options

[DanBl]

I see these examples listed here. But is there a more complete list? Specifically a way to force a line break between fields? Especially blank fieds?

CONCAT

This expression concatenates the string and is formatted as follows: CONCAT(string1,"separator", string2) The following are examples of separators that you can include: a space: " " a dash: "-" a slash: "/" a comma: "," a word: "or", "and"

[William--]

To insert a line break into a valueexpression, insert the unicode character for newline as shown below. 
This example inserts a line break between the record's owner and the record owner's email address. 

CONCAT({owner}.{name},"\u000A",{owner}.{emailAddr})

(original comment edited to display the best solution above)

[DanBl]

Thanks. Conceivably, I could work around by adding a line break to the calculated field between CONCAT expressions? What's the line break character that calculated fields can read?

[laurence_jayawardane]

I don't know the answer to your specific question Dan, but I do simply use an extra column that I share (sharecol=true) that has the separator there, in some instances. For example merging a name and a description and adding a line in between... I'd add two columns, one for name, one for description, then use the method here to add a third column under the subheader "Merge data from two columns with a line break" to then merge the two initial columns together. Except instead of using value=<br> I'd use value=<hr> for a horizontal line.

[William--]

To insert a line break into a valueexpression, insert the unicode character for newline as shown below. 
This example inserts a line break between the record's owner and the record owner's email address. 

CONCAT({owner}.{name},"\u000A",{owner}.{emailAddr})

(original comment edited to display the best solution above)

[Doug_Den_Hoed__AtAppStore]

Brilliant @William--, 

Thanks for sharing: it’s in the vault!

Regards,

Doug

[CoreyBe]

I was referred to this thread last week by a colleague, and it seems like it would solve an issue we have worked around for some time. 

I have a question about Step 2 though - how would I go about "entering" the invisible unicode into the custom paragraph field? Pasting in the text "200B 000A 200B" causes those characters to be rendered into the new string. Is there an text entry method for unicode that will keep it invisible?

Thank you!

[CoreyBe]

Got it working. Very creative solution! Thank you!

[William--]

For anyone else referencing this in the future, you'll need to look for a unicode reference website to look for those particular unicode characters, copy them from that site, then paste into your calculation. 

Since they're invisible, it doesn't do much good to paste them in this thread. 

https://unicode-table.com/en/200B/

https://unicode-table.com/en/000A/

[ElizaBa]

This isn't working for me - did Adobe remove this functionality?

[Melinda_Layten_Freeman]

Note that you may have to type the slash twice in a row whenever you edit the field since text mode editor likes to eat slashes as special characters.   So if "\u000A"  doesn't work try "\\u000A"

[KatherineLa]

Building on this lovely helpful thread with my discovery for the day. Workfront will require, but remove, the duplicate slashes if this formula is used in a calculated field on a report making editing a giant pain. However, using the "\u000A" trick in a calculated field on a custom form will not require extra trickery. 

It won't necessarily render very nicely when displaying on the form, but subsequently using that field in a report will get the expected formatting. 

As always, calculated fields on custom forms don't recalculate consistently, so use this with care or you may wind up with stale data in your reports. 

[William--]

Notifying anyone following this thread that a discovery has been unearthed!

@skyehansen was sharing this trick by @The_Real_Melinda_Layten in a larger discussion about multi-select fields. I then realized this is how we can insert unicode control characters into a value expression, without having to rely on the clunky workaround that was previously described. 

CONCAT({owner}.{name},"\u000A",{owner}.{emailAddr})

This makes it very easy to insert a line break in a calculated field or column, by simply putting in the unicode \u000A in your expression where a line break is needed.

Even more importantly, this can be used to replace column sharing in many, many situations. A single calculated column can replace multiple shared columns using this approach - and will also be easier to re-arrange within a view since there are no column number prefixes that have to be considered. 

[Doug_Den_Hoed__AtAppStore]

Thanks for sharing @William-- 

I'm going to use this new approach rather than column sharing going forward: it is lighter, more functional, and easier to maintain...provided I remember to always re-supply the extra leading \ symbols that are sacrificed whenever such calculations are edited via the front end (vs pushing such calcs in via the API).

Regards,

Doug

[NeetuKanwarMokawat]

Hi All!
I want to know the escape character here, requirement is to keep the owner name and email address in quotes, how can we achieve it?

CONCAT("Name":"{owner}.{name}","'","Email":"{owner}.{emailAddr}")

[Doug_Den_Hoed__AtAppStore]

Hi @NeetuKanwarMokawat,

One way is to use ‘ for the concat components, and “ for the delimiters around the contained terms, like this;

CONCAT(‘“Name”:”’,{owner}.{name},””’,…)

Regards,

Doug