Last active
January 22, 2018 16:50
-
-
Save muraiki/063c2ac09b32e5b4c47ff08d3d7afab2 to your computer and use it in GitHub Desktop.
Salesforce Process Builder: Generate formula code for matching multiple values
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<form> | |
<label for="fieldName">Field name (include object):</label> | |
<input id="fieldName" name="fieldName" type="text" size="100" onkeydown="if (event.keyCode == 13) false"> | |
</form> | |
<p> | |
<input type="checkbox" id="isPicklist" name="isPicklist" value="picklist"> | |
<label for="isPicklist">Picklist?</label> | |
<input type="checkbox" id="isNot" name="isNot" value="not"> | |
<label for="isNot">NOT?</label> | |
</p> | |
<label for="matchingValues">Comma-separated list of values to match (a single space is allowed after each comma):</label> | |
<textarea id="matchingValues" name="matchingValues" rows="4" cols="80">Foo,Bar,Baz</textarea> | |
<p id="createBtn" style="color: blue; text-decoration: underline">Create</p> | |
<p id="output"></p> | |
<p id="clearBtn" style="color: blue; text-decoration: underline">Clear</p> |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
var createBtn = document.getElementById("createBtn"); | |
var clearBtn = document.getElementById("clearBtn"); | |
// Defaults | |
document.getElementById("fieldName").value = "[Contact].SomeField__c"; | |
// Handlers | |
createBtn.onclick = function() { | |
var fieldName = document.getElementById("fieldName"); | |
var isPicklist = document.getElementById("isPicklist"); | |
var isNot = document.getElementById("isNot"); | |
var matchingValues = document.getElementById("matchingValues"); | |
var fname = isPicklist.checked ? | |
"TEXT(" + fieldName.value + ")" : | |
fieldName.value; | |
var regexOr = matchingValues.value.trim().replace(/,\s?/g,"|"); | |
// Return a string "FALSE" because the Process Builder needs to make a comparison to the target field name's value, which will be a string (or a picklist coerced to string via TEXT). If we use a boolean here, process builder will reject it as it won't compare a boolean to a string. Note that if a literal "FALSE" is a valid option for your field, then you will need to change FALSE below to something else. | |
var result = isNot.checked ? | |
('"FALSE", ' + fname) : | |
(fname + ', "FALSE"'); | |
var formula = "IF(" + | |
"REGEX(" + fname + ', "' + regexOr + '"), ' + | |
result + | |
")"; | |
document.getElementById("output").textContent = formula; | |
} | |
clearBtn.onclick = function() { | |
document.getElementById("fieldName").value = ""; | |
document.getElementById("isPicklist").checked = false; | |
["matchingValues", "output"].forEach(function (id) { | |
document.getElementById(id).textContent = ""; | |
}); | |
} |
You can run this in your browser using jsfiddle: https://jsfiddle.net/qb3skkdk/3/
Also, note that Process Builder still has SOQL limits that you can run into, which it apparently does not optimize for. My process was complex enough that I had to just write Apex code anyway. Yay Salesforce!
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Since Process Builder doesn't support matching multiple values using commas (as one would in Workflow Rules by doing "foo=bar,baz"), I created this tool to generate a formula that uses regular expressions for matching multiple values. It also supports picklists and negation.
For example, to match the field [Contact].SomeField__c with values Foo, Bar, and Baz, it will generate the following formula:
IF(REGEX([Contact].SomeField__c, "Foo|Bar|Baz"), [Contact].SomeField__c, "FALSE")
See the comments in the code regarding why I return a string value of "FALSE".