Do UI only formulas support something like "Case" in salesforce formulas?

  • 1
  • Question
  • Updated 1 year ago
  • Answered
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?
Photo of Skuidward Tentacles (Raymond)

Skuidward Tentacles (Raymond), Champion

  • 17,398 Points 10k badge 2x thumb

Posted 1 year ago

  • 1
Photo of Seth Vanderdrift

Seth Vanderdrift

  • 878 Points 500 badge 2x thumb
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";<br>var myRegexp = /(?:^|\s)format_(.*?)(?:\s|$)/g;<br>match = myRegexp.exec(myString);<br>while (match != null) {<br> // matched text: match[0]<br> // match start: match.index<br> // capturing group n: match[n]<br> console.log(match[0])<br> match = myRegexp.exec(myString);&nbsp;<br>}
So your formula would be like (HAVE NOT TESTED IN SKUID AT ALL, and really guessing on actual syntax)

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
Photo of Seth Vanderdrift

Seth Vanderdrift

  • 878 Points 500 badge 2x thumb
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.
Photo of mB Pat Vachon

mB Pat Vachon, Champion

  • 42,926 Points 20k badge 2x thumb
It's called nested If in Skuid formula. ;)
Photo of Matt Sones

Matt Sones, Champion

  • 31,702 Points 20k badge 2x thumb
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
(function (skuid){
//////////////////////////////////////////////
//Shortcuts & Global Variables //////////////////////////////////////////////
var $ = skuid.$;
//////////////////////////////////////////////
//Formulas
//////////////////////////////////////////////
var formulas = {
'REMOVE_TEXT': [
    function (string_to_search, string_to_remove) {
        return string_to_search.split(string_to_remove).join('');     },
    {numArgs: 2, returnType: 'text'}
]
};
//////////////////////////////////////////////
//Register Formulas
//////////////////////////////////////////////
$.each(formulas,function(name,formulaArray){ skuid.formula.Formula(name, formulaArray[0], formulaArray[1]); });
})(skuid);
(Edited)
Photo of Skuidward Tentacles (Raymond)

Skuidward Tentacles (Raymond), Champion

  • 17,398 Points 10k badge 2x thumb
Cool. Thanks, Matt!
Photo of Zach McElrath

Zach McElrath, Employee

  • 49,648 Points 20k badge 2x thumb
Official Response
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")))
Photo of Skuidward Tentacles (Raymond)

Skuidward Tentacles (Raymond), Champion

  • 17,398 Points 10k badge 2x thumb
I just fainted! That is awesome. Thanks!
Photo of mB Pat Vachon

mB Pat Vachon, Champion

  • 42,926 Points 20k badge 2x thumb
CAN'T WAIT FOR THE NEW RELEASE!!!