function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Abin Thomas 23Abin Thomas 23 

How to parse a text field into multiple variables that can be reported on?

Hello,

We have a custom text field where our users add information with hashtags. They can add as many hashtags as they want. However, we are having trouble accurately reporting on this data, as we cannot separate out the grouped hashtag data and report how many occurences by each hashtag. 

Is there a way to use a row level formula to accomplish this?

I tried using the following: 
RIGHT( Case.Hashtags__c,(LEN( Case.Hashtags__c)-FIND("#", Case.Hashtags__c)))


However, all that is doing is taking text like this: 
#HHRGD1 #LUPA
And changing it to text like this: HHRGD1 #LUPA

Im wanting the formula to split HHRDG1 and LUPA so that i can see how many times each of those pop up. 
Andrew GAndrew G
Hi Abin

I don't think you can achieve your outcome with a Formula.

You will need to look at a trigger that users something like : 
List<String> hashtagList = Record.Hashtags__c.split('#');

Regards
Andrew
Alain CabonAlain Cabon
Your delimiter is not # but the space. The formulas below only works if the delimiter is exactly one character long.

It is very difficult to split many hashtags with a formula. More and more complicated for each new hashtag. You can split about ten hashtags and the formula is huge for the last hastag. Beyond, the limit size for a formula is probably exceeded.

First_hashtag: 
IF((LEN(Case.Hashtags__c) - LEN(SUBSTITUTE(Case.Hashtags__c,' ', ''))) = 0, Case.Hashtags__c )

Second_hashtag:  
IF((LEN(Case.Hashtags__c) - LEN(SUBSTITUTE(Case.Hashtags__c,' ', ''))) = 1, RIGHT(Case.Hashtags__c, LEN(Case.Hashtags__c) - FIND(" ", Case.Hashtags__c)))

Third_hashtag: 
IF((LEN(Case.Hashtags__c) - LEN(SUBSTITUTE(Case.Hashtags__c,' ', ''))) = 2, 
RIGHT(Case.Hashtags__c, LEN(Case.Hashtags__c) - FIND(" ", Case.Hashtags__c, FIND(" ", Case.Hashtags__c)+1)))

Seventh_hashtag:  
IF((LEN(Case.Hashtags__c) - LEN(SUBSTITUTE(Case.Hashtags__c,' ', ''))) = 6,
RIGHT(Case.Hashtags__c, LEN(Case.Hashtags__c) - FIND(" ", Case.Hashtags__c, FIND(" ", Case.Hashtags__c, FIND(" ", Case.Hashtags__c, FIND(" ", Case.Hashtags__c, FIND(" ", Case.Hashtags__c, FIND(" ", Case.Hashtags__c)+1)+1)+1)+1)+1)))
The resulting formula is already huge internally because Hashtags__c is reused many times and you have only seven hashtags.
  
CASE( LEN(Last_Name__c) - LEN(SUBSTITUTE(Last_Name__c,' ', '')),
1, RIGHT(Last_Name__c, LEN(Last_Name__c) - FIND(" ", Last_Name__c)),
2, RIGHT(Last_Name__c, LEN(Last_Name__c) - FIND(" ", Last_Name__c, FIND(" ", Last_Name__c)+1)),
3, RIGHT(Last_Name__c, LEN(Last_Name__c) - FIND(" ", Last_Name__c, FIND(" ", Last_Name__c, FIND(" ", Last_Name__c)+1)+1) ),
4, RIGHT(Last_Name__c, LEN(Last_Name__c) - FIND(" ", Last_Name__c, FIND(" ", Last_Name__c, FIND(" ", Last_Name__c, FIND(" ", Last_Name__c)+1)+1)+1) ),
5, RIGHT(Last_Name__c, LEN(Last_Name__c) - FIND(" ", Last_Name__c, FIND(" ", Last_Name__c, FIND(" ", Last_Name__c, FIND(" ", Last_Name__c, FIND(" ", Last_Name__c)+1)+1)+1)+1) ),
6, RIGHT(Last_Name__c, LEN(Last_Name__c) - FIND(" ", Last_Name__c, FIND(" ", Last_Name__c, FIND(" ", Last_Name__c, FIND(" ", Last_Name__c, FIND(" ", Last_Name__c, FIND(" ", Last_Name__c)+1)+1)+1)+1)+1) ),
Last_Name__c
)

https://salesforce.stackexchange.com/questions/19348/formula-field-for-getting-a-first-and-last-word-from-the-string

A simple alternative is formula consumer by just using CONTAINS when there are not many different hastags (limited list).

Formula n°1: HAS_HHRGD1 : IF((CONTAINS( Case.Hashtags__c, ”#HHRGD1” )), true, false)
Formula n°2: HAS_LUPA :  IF((CONTAINS( Case.Hashtags__c, ”#LUPA” )), true, false)

That doesn't work if #HHRGD1 and #HHRGD12 exist.

Formula n°1: HASHHRGD1 : AND ( IF((CONTAINS( Case.Hashtags__c, ”#HHRGD1” )), true, false) ,  IF((CONTAINS( Case.Hashtags__c, ”#HHRGD12” )), false, true) )

That could be also not usable easily.

Otherwise, there is the Apex trigger that splits the hashtags and fills new fields (one by hashtag) on insert, update and delete.
Apex can split easily because the function split exists.
Andrew GAndrew G
@Alain - nice pick up that the "delimiter" is not the Hash # but the Space ' ' .  And interesting ideas for the formula, i didn't consider that solution, but yes, it does become unwiedy.

Regards
Andrew
Abin Thomas 23Abin Thomas 23
@Alain, thank you for the suggestion. I have a question. The formula consumer you mentioned can be used as a row level formula? I tried pasting and it would not validate. Thanks for all of your help!
Alain CabonAlain Cabon
@Abin Thomas

They are formulas in the fields of the object Case and not in the report indeed (without Case. , that is the problem).

A formula returning numbers is also better for countings.

Type : number (instead of checkbox)

Formula n°1: HAS_HHRGD1 : IF((CONTAINS( Hashtags__c, '#HHRGD1' )), 1 , 0)
Formula n°2: HAS_LUPA :       IF((CONTAINS( Hashtags__c, '#LUPA' )), 1,  0)

 
Alain CabonAlain Cabon
The formula for the first hashtag is too simple because the formula above works when there is only one hashtag.
The alternatives are also missing ( IF (condition, <return value if true>, <return value if false> ) ).
Here the alternatives ( <false>) are always the empty string ( '' ).

As soon as there is at leat a second hashtag, the first hastag is the left part just before the first space.

First_hashtag:
  
IF((LEN(Case.Hashtags__c) - LEN(SUBSTITUTE(Case.Hashtags__c,' ', ''))) = 0, Case.Hashtags__c,'')
+
IF((LEN(Case.Hashtags__c) - LEN(SUBSTITUTE(Case.Hashtags__c,' ', ''))) > 0, LEFT(Case.Hashtags__c, FIND(" ", Case.Hashtags__c)-1),'')

IF((LEN(Case.Hashtags__c) - LEN(SUBSTITUTE(Case.Hashtags__c,' ', ''))) = 0, Case.Hashtags__c,  ''  )
Abin Thomas 23Abin Thomas 23

@Alain, it appears that the formula you provided that can grab up to 7 hashtags is only placing the latest hashtag in the row level formula. Is there any way I can grab all of the hashtags so that I can group by unique hashtag for reporting?
Alain CabonAlain Cabon
@Abin, 

In fact, there is not a easy solution like that excepted in Apex (Andrew's response) or with a mutli-select picklist
With formulas, it is always very very difficult and very formula consuming (many formulas) because you cannot split values.

A formula always returns a simple type like string, boolean (checkbox), date, number but never a list or an array like with Apex ( List<String> ).

A multi-select picklist is more suitable with the conversion of the hashtags into the values of that picklist. 

You could create a multi-select picklist and update the values (ie HHRGD1;LUPA) with simple substitutions of spaces with ";" and # with space.

You can use the process builder and the save at each create or update of the object the text value into the multi-select picklist.

The multi-select picklist has the values HHRGD1 and LUPA

Case.Hashtags__c = #LUPA #HHRGD1

Formula :  SUBSTITUTE( SUBSTITUTE( Case.Hashtags__c , '#',''), ' ',';')  

User-added image

User-added image

User-added image

When you save or update the value, #B1 #A1 becomes B1;A1 and finally A1;B1 into the multi-select picklist.

Report based on Multi-Select Picklists
https://help.salesforce.com/articleView?id=000326275&type=1&mode=1 (https://help.salesforce.com/articleView?id=000326275&type=1&mode=1)

It is the only solution without coding Apex.