I need look into a long text field and determine if a any words from a list are contained in the long text. I can do this using a UI only formula field for one word, but what would be the best way to compare it to a list? Salesforce Formula fields have a “Case” function, but it can’t be used on Long Text Fields. I don’t see a Case type of option in UI ONLY forumlas. Is it possible?
Question
Do UI only formulas support something like Case" in salesforce formulas?"
+17This topic has been closed for replies.
+7At the risk of sending you down an arduous rabbit hole, have you considered using javascript’s RegEx?
RegEx expression (?:^|W)dog(?:$|W)|(?:^|W)brown(?:$|W) will match both dog and brown in “The quick brown fox jumped over the lazy dog”
You can play with it at: https://regex101.com/
Please note, I know next to nothing about regex, but use it after much trial and error on occasion.
https://stackoverflow.com/questions/432493/how-do-you-access-the-matched-groups-in-a-javascript-regu… has a snippet on one of the answers:
var myString = “something format_abc”;So your formula would be like (HAVE NOT TESTED IN SKUID AT ALL, and really guessing on actual syntax)
var myRegexp = /(?:^|s)format_(.*?)(?:s|$)/g;
match = myRegexp.exec(myString);
while (match != null) {
// matched text: match[0]
// match start: match.index
// capturing group n: match[n]
console.log(match[0])
match = myRegexp.exec(myString);
}
if(/(?:^|W)dog(?:$|W)|(?:^|W)brown(?:$|W)/.exec(string) !=null) …
Presumably you’ll need to actually understand the regex, I just cribbed a sample and added an or (|) to handle multiple words, and you’ll probably want to use case insensitive
+7The alternative method i’d try is to split your search string into an array of words then search that. But it would probably require your formula field to call a function.
+18You can also use skuid.formula.Formula() at add any formula you want to write in javascript to the skuid pagebuilder. Just use the javascript switch/case function.
I keep all my formulas on a single static resource added to my master page, like this:
//Custom Formulas<br />(function (skuid){<br />//////////////////////////////////////////////<br />//Shortcuts & Global Variables //////////////////////////////////////////////<br />var $ = skuid.$;<br />//////////////////////////////////////////////<br />//Formulas<br />//////////////////////////////////////////////<br />var formulas = {<br />'REMOVE_TEXT': [<br /> function (string_to_search, string_to_remove) {<br /> return string_to_search.split(string_to_remove).join(''); },<br /> {numArgs: 2, returnType: 'text'}<br />]<br />};<br />//////////////////////////////////////////////<br />//Register Formulas<br />//////////////////////////////////////////////<br />$.each(formulas,function(name,formulaArray){ skuid.formula.Formula(name, formulaArray[0], formulaArray[1]); });<br />})(skuid);
Raymond,
In Skuid’s Q4 release we are adding some new Formula Functions that should help with this: CONTAINS_ANY, CONTAINS_ALL, and CASE
CONTAINS_ANY will allow you to check whether a given input string contains any of a list of other strings, e.g.
CONTAINS_ANY({{Favorite_Food__c}}, “Spaetzle”, “Sauerkraut”, {{Dynamic_Food_Choice__c}})
CONTAINS_ALL() will allow you to check whether a given string contains ALL of a list of other strings.
And the CASE() statement is a convenience method to avoid a million nested IF() functions, e.g.
CASE({{Status}}, 0, “Open”, 1, “In Progress”, 2, “Closed”, “Invalid Status”)
to replace
IF({{Status}} == 0, “Open”, IF({{Status}} == 1, “In Progress”, IF({{Status}} == 3, “Closed”, “Invalid Status”)))
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.