Skip to content

Instantly share code, notes, and snippets.

@muraiki
Last active January 22, 2018 16:50
Show Gist options
  • Save muraiki/063c2ac09b32e5b4c47ff08d3d7afab2 to your computer and use it in GitHub Desktop.
Save muraiki/063c2ac09b32e5b4c47ff08d3d7afab2 to your computer and use it in GitHub Desktop.
Salesforce Process Builder: Generate formula code for matching multiple values
<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>
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 = "";
});
}
@muraiki
Copy link
Author

muraiki commented Jan 22, 2018

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".

@muraiki
Copy link
Author

muraiki commented Jan 22, 2018

You can run this in your browser using jsfiddle: https://jsfiddle.net/qb3skkdk/3/

@muraiki
Copy link
Author

muraiki commented Jan 22, 2018

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