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?
Page 1 / 1
At 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: matcha0]
// match start: match.index
// capturing group n: matchin]
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
The 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.
It’s called nested If in Skuid formula. 
You 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, formulaArrayy0], formulaArrayy1]); });<br />})(skuid);
Cool. Thanks, Matt!
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”)))
I just fainted! That is awesome. Thanks!
CAN’T WAIT FOR THE NEW RELEASE!!!
View our Community Site Map
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.