Trigger/Process/Workflow or Scheduled Script/Tool?

I often get asked to write triggers to detect the existence of certain types of data in Salesforce and, when such data exists, make a modification to some other data.

The only problem is, writing "a trigger" isn't always easy to do in a well-normalized database (that is, a database that leverages master-detail and lookup relationships to avoid redundant data entry).

Take, for example, an architecture where "App Document" records have a master-detail relationship to a parent "Admissions Application" and the "Admissions Application," in turn, has a master-detail relationship to a parent "Contact" record.

The other day, I was asked to automatically flip the "Status" to "Waived" on any "App Document" records that meet the following criteria:

• The record is of type "English Proficiency" and is in blank/"Required" status
• The record's parent "Application" has an "application citizenship category" of "International" and a "level" of "Undergraduate"
• The record's grandparent "Contact" has a "foreign citizenship country" of "United Kingdom," "Ireland," "Canada," "Australia," "New Zealand," (etc.)

This is ill-suited to a Trigger/Process/Workflow because I would actually need THREE "insert"/"update" automations, each with relatively redundant code:

1. one for all Contacts
   (in case the citizenship country changes ... then go looking for appropriate "child" apps and "grandchild" documents)
2. one for all Applications
   (in case the "application citizenship category" or "level" changes ... then double-check the parent Contact's citizenship and go looking for "child" documents)
3. and one for all App Documents
   (check the parent & grandparent details and change self if appropriate)

Yikes! That's a lot of redundant trigger code, and some of those operations aren't very efficient against Salesforce governor limits.

Especially since just one SOQL query can easily fetch the ID of all "App Document" records whose "Status" needs to be set to "Waived":

SELECT Id
FROM AdmDocument__c
WHERE Type__c='English Proficiency'
AND (Status__c = NULL OR Status__c = 'Required')
AND Application__r.Level__r.Name='Undergraduate'
AND Application__r.Citizenship_Category__c='International'
AND Application.Contact__r.Foreign_Citizenship_Country__c IN 
   ('Australia','Canada','Ireland','New Zealand','United Kingdom')

When someone asks you to "write a trigger" or "write a process builder" or "write a workflow" to automate data migration inside of Salesforce, be sure to ask yourself, "How many tables' values changing could cause a scenario to arise that would make this data need to be modified as requested?"

If the answer is "2 or more," and especially if it's "3 or more," think hard about whether "every 15 minutes" / "daily" is frequent enough for your end users to see "automatic fixes," and whether a SOQL query could extract the IDs of the records that need to be changed.

If you can come up with a SOQL query that represents the "problem records," you should be able to use a schedulable ETL tool or scheduled Apex to "extract and load back" with much lower overhead against governor limits than triggers/processes/workflows would incur. Your future self will also thank you when someone (inevitably) asks for a change to your script.

Re-Parented Child Objects Don't Fire Triggers In Parent Record Merges

Today I learned, via the Salesforce forums:

• In Salesforce, if you merge 2 "Contact" records, and if you have a bunch of "child"-table records pointing to the "about-to-be-deleted" Contact record as a foreign key, Salesforce will automatically change the foreign key cross-reference in the "child" records to the ID of the "surviving" Contact record, and it will update those child records' "last modified" timestamp, BUT it will not allow any "UPDATE" triggers from those child tables to fire.
• The only thing you can latch onto to detect that a "Contact merge" has just happened is the "AFTER DELETE" trigger-context against the "Contact" table (you can detect a "deleted but merged" Contact in that context from an ordinary "deleted" Contact because it has a "MasterRecordId" value).
  You can't "detect" that a "child" record has just been "re-parented" in the context of a "merge."
• Annoyingly, once you're that far into the merge ("after contact delete" trigger context), you can no longer tell which "child" records were pointing to the old "parent" because that's long since been "fixed" by Salesforce.
  All you can see is which "child" records are now cross-referencing the surviving "Contact" – which includes all "child" records that were already pointing to the surviving "Contact" in the first place.
• Finally, if you use that knowledge to kick off DML against surviving Contacts' "child" records, you must be careful, because "after Contact delete" is still in the context of the initial Contact merge, and you have to avoid loops (Salesforce will yell at you if you don't).
  If your DML against the "child" records kicks off triggers that result in more DML against one of the very Contacts involved in the merge, you need to make sure you kick off the DML against the "child" records in a "@future" context to force a brand new "execution context" (roughly like a transaction, but a little different in some ways – see Dan Appleman's Advanced Apex book).

Loving my "Just Push Play" Run-Specified-Tests Script

Power tools are fun.

THIS PYTHON SCRIPT HAS CHANGED MY LIFE.

Whipped it out twice this week trying to get code written under tight deadlines.

Having a nearly-instant, low-clicking-around answer to, "Would it probably work in production?" so I can easily watch how every single change to just 1 line of code behaves, before doing a full-on deploy of code that "seems to work" with a proper deployment tool and "Run All Tests," has been AMAZING.

Dreamforce 2017 Lessons Learned & Takeaways

Lucky me, I got to attend Dreamforce again. Here's a summary.

Misc

1. "Machine learning" tools are getting point-and-click enough that feeding hundreds of pieces of historical student data (e.g. "days before schoolyear applied," "GPA," "time between inquiry & admission," "time of year applied," etc.) into them and letting a computer look for trends (e.g. "likely to submit a confirming deposit?") isn't all that out of reach. Drag-and-drop tools are coming into prominence that make dimensionality reduction & linear regression problems like this intuitive & easy to play with.
    
2. There's something called "macros" I should look into more. At a glance, not sure it's anything I can't do a lot faster w/ DemandTools, but worth a glance.
    

Org configuration change management

1. One of the concepts behind "DX scratch orgs'" mere 7-day lifespan is to encourage you to put the result of all your coding time & effort into a source code version control system.
    
2. Two guiding philosophies behind development with "DX" are that: 1) you can make all the changes you like by hand in them -- just be sure to use the DX command-line tool to pull those changes down to your computer so you can promptly get them into your version control system, and 2) you should never again be making any changes to configuration in production / normal-sandbox orgs by hand (only via deploying from a copy of whatever you did in a "DX scratch org" into one of those orgs via something like the DX command-line tool).
    
3. On a related note, I still haven't heard a really admin-geared lecture about DX and "please don't mess around with things by hand in production/staging sandboxes, and please don't deploy anything via change set."
    
4. Gearset looks like an absolute miracle for shops where those kinds of "no change sets, please" disciplined version-control practices aren't yet in place (e.g. shops transitioning from Salesforce as an "interesting side project" with a no-code solo admin). It's an enterprise-class-(seeming?) tool that simply lets you log into Salesforce and a cloud Git repository and say, "Hey, synchronize my entire org's metadata once a day -- kthxbye." It also seems to be able to facilitate rollbacks. In a small shop where communication lines are open enough that once you know what changed since things were working, sending out a few emails to figure out who changed it and if they could kindly fix it is easy, this "daily snapshot" seems well worth $3600/year. Even after you have "version control" in place -- when it's this easy, what's the harm in a "side repository" of "CYA snapshots?" (Such Git-stored snapshots could also, potentially, be mined by local scripts that transform them into things like SQL commands for dropping & rebuilding tables & SOQL queries for a local daily cache of Salesforce data.) And that's without all the other things Gearset does. (Please stay just $3600/year, Gearset!) Getting a tour from a staffer makes their product look even cooler than their summary on their web site -- apparently they can give you one over the web. *drools* I want.
    

Lightning Experience

1. Although there's a lot that's still missing from "Lightning Experience" (e.g. "bucketing" & "formulas" in native reporting tools), by the Winter '19 edition, there might be enough LE-only functionality in Salesforce's native reporting tools to justify a switch--for example, something called "joined reports," field-to-field filtering, subfoldering, easier permissions management, and bookmarking of reports. (So far, we haven't found it worthwhile.) I'm hoping to get a lot of data integration projects done & stable over the next few months so I have time to play around with Lightning Experience in a sandbox.
    
2. Apparently, having a really nice "Lightning Experience" home page for a user doesn't automatically translate into a really nice mobile version of the same page -- that has to be set up separately.
    
3. There's a thing called the "Lightning Data Service" that might make Lightning Components require almost as little code as Visualforce for simple "put this SOQL query's results on a page" use cases, unlike last year when I took a hands-on training. It also looks like it has something to do w/ getting Lightning Components on the same page to auto-update when data from the database that one of them just edited changes.
    

SOQL query performance & data models

1. OpportunityContactRole isn't anywhere close to becoming a first-class object (a table against which you can write triggers). *sigh*
    
2. The "affiliations" package might be replaceable with the "multiple accounts for contacts" feature & a few triggers similar to ones from the "affiliations" package. That said, the "affiliations" package doesn't really seem to be hurting anything. But Pardot and other vendors are working to get on board with the native version of the feature, so it's worth keeping an eye on.
    
3. Having a single "Generic Account" record for people whose account you don't know the value of slows down most SOQL queries. It improves performance to give everyone their own accounts named after them, as in the HEDA model.
    
4. Salesforce objects/tables aren't really tables. They're sets of tables (e.g. indexes are a table, fields are a table, etc.). That's why Salesforce doesn't call them "tables" and impacts SOQL query performance. We don't seem to have hit issues yet, but there's also something Salesforce can do with their back-end data model and materialized views for storing your org's data called skinny tables if you ask -- it can speed up SOQL queries.
    
5. Reminder: "=" is fast in a query that can leverage a highly selective index, "!=" is not -- "!=" always requires a table scan. Query optimization 101, but so easy to overlook when you get busy and don't write queries daily where you have to worry about it.
    
6. There's a technology called "big objects," paired with "asynchronous SOQL," coming along, but it doesn't yet look useful for us. You've got to be really sure about data not changing, because right now, you can't even drop such a table once it's created.
    

Other programming-related notes

1. Stay on top of the "metadata in Apex" project, but overall, it's on purpose that you can't change your schema & change your data via the same programming language / execution environment, so don't get too excited.
    
2. Hopefully coming within a year: the ability to easily make Apex respect CRUD permissions & field-level security permissions (when you don't want your triggers to "play God").
    
3. If you're a web developer, you can write your Visualforce/Apex to include a Visualforce Boolean variable as a toggle that switches whether Visualforce pages rely on Salesforce-hosted "static resources" (e.g. CSS, JavaScript) or "localhost"-hosted ones. This can make testing a lot easier & less fragile & faster than actually trying to update the contents of Salesforce-hosted "static resources" when you're not yet even sure if they're correct. Talk to Jon Schleicher for more info about how.
    
4. Via the reporting API (warning: the JSON to parse "is a doozy"), you can wrap Reports from Salesforce's native reporting tool in Lightning Components and therefore make them easy to embed in other pages. (Natively, only dashboards can be drag-and-dropped into Lightning Components.)
    
5. Google Docs are a form of "cloud-hosted info storage with an API" -- which means that you can include info from them in custom-programmed user interface elements like Lightning Components. Possibly overlooked form of integration of results from a cheap-and-easy aggregate-reporting tool (spreadsheets) with business users' main daily work environment (Salesforce).
    
6. Personal goal: blast through data integration projects so I can free up time to work on Trailhead modules and learn to be better able to leverage a lot of the technologies I learned about "tips & tricks" for. There's a lot out there that I'd like to do, but at 8 hours apiece, one really has to work to free up the time. But they're so well-written.

Recursion-Reduction Tips For Apex Trigger Code

I wanted to share with you some lessons I learned while working on a trigger.

---

Because a trigger against a "just-saved" record can kick off another "save" operation on that record (typically the point of a trigger), the very same trigger can get re-fired during what's known as the same "execution context."

In Apex trigger programming, it's considered best-practice to make sure that any subsequent re-firings of the same trigger against the same record don't waste valuable CPU cycles when this happens (because Salesforce limits them within an "execution context").

Therefore, when writing a trigger that "does ____ for a just-saved record," it's important to make sure that, at some point, the trigger saves the ID of that record into a "I already did _____ on all of these records" flag that's viewable across all of these recursions (usually a class-level "Static"-flagged "Set<Id>"-typed variable in the trigger handler).

And, of course, you need to program your trigger to pay attention to its own flags and avoid running expensive "consider doing ____" code against records already in that "already-did-____" set of IDs.

---

The interesting question is: When do you set the "I already did _____" flag?
 

• In certain cases, one can trust that all field-values contributing to a trigger's yes/no decision of "should I do _____ to this just-saved record?" will be set the moment that the record is first saved.
   
  In those cases, the most efficient place in your trigger code to set the "I already did _____ on this record" flag is "as soon as the trigger has seen the record for the first time, no matter whether it ends up qualifying for 'doing ____' or not."
   
  That's how I usually write my triggers if I can, since it's the most efficient way to write the trigger.
   
   
• However, in certain cases (often discovered when people test your newly-written trigger and tell you that it fails under normal usage circumstances through 3rd-party record-editing environments like external portals), the values contributing to the answer to "should I do ____ to this record?" change so that the answer goes from "no" to "yes" in the middle of the "execution context."
   
  For example, other "triggers" or equivalent pieces of code do some post-processing to the just-saved record, and it's only after those pieces of code re-save the record that the answer flips to "yes."
   
  In those cases, the most efficient place in your trigger code to set the "I already did ______" on this record" flag is "as soon as the trigger has determined that it needs to do ______ to the record."
   
  This, unfortunately, will make the trigger run "Should I do _____?" checks all the way through the execution context for records that remain "no" throughout. That's why it's less efficient.
   
  But sometimes, it's simply necessary in cases where the answer can flip from "no" to "yes" mid-execution-context.
   
   

---

If one is comfortable authoring / editing the triggers/processes/workflows that are responsible for such impactful mid-execution-context value-changes, sometimes it's possible to refactor them so that they're simply part of the same "trigger handler" code as the one you're in the middle of writing.
You could precisely control the order of code execution "after a record is saved" and author these actions in a way that ensure there will never be any "mid-execution-context surprise value-changes."
That might let you use the more efficient recursion-reduction pattern instead.

Sometimes, though, there's nothing you can do but choose the 2nd option.

Python for Salesforce Developers: "Just Push Play" Run-Specified-Tests Code Deployment

Warning: the attached Python script makes you put a password straight into the code and helps you make an end-run around all sorts of change-management best practices.

Basically, if you're not a little horrified to see this script publicly shared, you probably don't understand what it's capable of enough to be using it -- so please dont!

That said, for circumstances in which you were going to ignore a lot of change-management issues anyway, or just want to do a "check only" deploy of code to a Salesforce org, etc., this code basically lets you type a handful of classes / pages / etc. into a Python script, type in your username & password, say which tests you want to run and how you want to deploy it, and see the results really quickly.

The idea is to be almost as handy, to a developer, for small changes, as right-clicking on code files in Eclipse and "deploying" from there -- the problem with Eclipse being that it doesn't have a "Run Specified Tests" option.

At some point I might create something similar to this that lets you log into two Salesforce orgs, download a smattering of code from one to your local hard drive (instead of already having to have downloaded it with Eclipse), and proceed with the deploy from there. That really feels like playing with laziness/sloppiness fire, though (somehow seems to take out the "Would I really have deployed this just with Eclipse, anyway?" factor).

This code is probably best just for "checkOnly=TRUE" deploys. For real deploys, it's probably still best to Run All Tests, and using a "Change Set" is a lot better for anyone else who might have to stumble into your org a few weeks later and see what's been happening as far as code deploys. (And that's just a bare minimum of version control for simply-maintained orgs.)

A few notes on the code:

• Change "#'''" to "'''" around blocks of code to quickly toggle them off (no point, for example, re-logging in if your Python IDE already has your session ID in memory, and you don't want to fire up a new "deploy" just to run the "check deployment status" code again).
• "thingsToAdd" is where most of the "what you need to type" exists.
• You'll also need to set "username" & "password" values (including your security token appended to your password) in "toOrgLoginEnvelope" -- I recommend remembering to change it back by saving this script to your hard drive (if you do save it) with a filename that includes something like "INCLUDES PASSWORD" so, as you exit the IDE, you remember to change things back.
• "inputstr" will need the actual path to where you've used Eclipse to download the files you have "ready to deploy" to on your hard drive.
• "outpbase" should be somewhere easy to find and delete later, like a sub-folder on your desktop.
• "deployEnvelope" will need "checkOnly" set inside the XML itself, "testLevel" set just above it, and a (brackets-and-comma-delimited) list of tests to run towards the end of the "runtests" parameter inside the parentheses that follow the XML (if n/a, use "[]").

import os
import shutil
import base64
from xml.etree import ElementTree
import xml.dom.minidom
import requests
import re   

def getUniqueElementValueFromXmlString(xmlString, elementName):
    #Extracts an element value from an XML string.
    #For example, invoking getUniqueElementValueFromXmlString('<?xml version="1.0" encoding="UTF-8"?><foo>bar</foo>', 'foo') should return the value 'bar'.
    elementsByName = xml.dom.minidom.parseString(xmlString).getElementsByTagName(elementName)
    elementValue = None
    if len(elementsByName) > 0: elementValue = elementsByName[0].toxml().replace('<' + elementName + '>', '').replace('</' + elementName + '>', '')
    return elementValue

metadataAPIVer = '40.0'

#'''
toOrgLoginEnvelope = """<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"><soapenv:Body><login xmlns="urn:partner.soap.sforce.com"><username>{username}</username><password>{password}</password></login></soapenv:Body></soapenv:Envelope>""".format(
username='username', password='password')
tor = requests.post('https://login.salesforce.com/services/Soap/u/'+metadataAPIVer, toOrgLoginEnvelope, headers={'content-type':'text/xml','charset':'UTF-8','SOAPAction':'login'})
tosessid = getUniqueElementValueFromXmlString(tor.content, 'sessionId')
tohost = getUniqueElementValueFromXmlString(tor.content, 'serverUrl').replace('http://', '').replace('https://', '').split('/')[0].replace('-api', '')
toorgid = re.sub(r'^.*/([a-zA-Z0-9]{15})$', r'\1', getUniqueElementValueFromXmlString(tor.content, 'serverUrl'))
toapiver = re.sub(r'^.*/([0-9.]+)/[a-zA-Z0-9]{15}$', r'\1', getUniqueElementValueFromXmlString(tor.content, 'serverUrl'))
#'''

# EXAMPLE CODE:  thingsToAdd = {'classes':[''],'pages':['']}
thingsToAdd = {'classes': {'singCaps':'ApexClass','ext':'cls','toUpl':['OpportunityETLHandler','OpportunityETLTest']}, 'pages': {'singCaps':'ApexPage','ext':'page','toUpl':['OpportunityETLPage']}}
inputstr = 'C:\\EXAMPLEFOLDER\\EclipseWorkspace\\My Sandbox\\src\\'
outpbase = 'C:\\EXAMPLETEMPFOLDER\\temppkgtouploadfromeclipse\\'
outpfiles = outpbase + '\\filesbeforezip\\'
outpzip = outpbase + 'uploadme'

#'''
# BEGIN:  Code to create package
if not os.path.exists(outpbase): os.makedirs(outpbase)
if not os.path.exists(outpfiles): os.makedirs(outpfiles)
pkgroot = ElementTree.Element('Package', attrib={'xmlns':'http://soap.sforce.com/2006/04/metadata'})
for folder in thingsToAdd.keys():
    innerDict = thingsToAdd[folder]
    typesElem = ElementTree.Element('types')
    if not os.path.exists(outpfiles+folder+'\\'): os.makedirs(outpfiles+folder+'\\')
    for item in innerDict['toUpl']:
        membersElem = ElementTree.Element('members')
        membersElem.text = item
        typesElem.append(membersElem)
        shutil.copy(inputstr+folder+'\\'+item+'.'+innerDict['ext'], outpfiles+folder+'\\'+item+'.'+innerDict['ext'])
        shutil.copy(inputstr+folder+'\\'+item+'.'+innerDict['ext']+'-meta.xml', outpfiles+folder+'\\'+item+'.'+innerDict['ext']+'-meta.xml')
    namesElem = ElementTree.Element('name')
    namesElem.text = innerDict['singCaps']
    typesElem.append(namesElem)
    pkgroot.append(typesElem)
verElem = ElementTree.Element('version')
verElem.text = metadataAPIVer
pkgroot.append(verElem)
with open(outpfiles+'package.xml', 'w', newline='') as fw:
    dom_string = xml.dom.minidom.parseString(ElementTree.tostring(pkgroot)).toprettyxml(encoding='UTF-8', indent='    ')
    dom_string = '\n'.join([s for s in dom_string.decode('UTF-8').splitlines() if s.strip()]) + '\n'
    fw.write(dom_string)
# END:  Code to create package
#'''

#'''
# BEGIN:  Code to create ZIP
shutil.make_archive(base_name=outpzip, format='zip', root_dir=outpfiles, base_dir='./')
zipString = None
with open(outpzip+'.zip', 'rb') as f: zipString = base64.b64encode(f.read()).decode('UTF-8')
# END:  Code to create ZIP
#'''

# TO DO:  Figure out how to run several tests.  Maybe it's just a comma-separation?  I think it it's that in the point-and-click Change Set UI.

#'''
# BEGIN:  Code to deploy ZIP
testLevel = 'RunSpecifiedTests' # Most common values will be 'RunSpecifiedTests' or 'RunLocalTests'
deployEnvelope = """<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:met="http://soap.sforce.com/2006/04/metadata">
      <soapenv:Header>
         <met:SessionHeader>
            <met:sessionId>{sessionid}</met:sessionId>
         </met:SessionHeader>
      </soapenv:Header>
      <soapenv:Body>
         <met:deploy>
             <met:zipFile>{zipfile}</met:zipFile>
             <met:deployOptions>
                 <met:checkOnly>true</met:checkOnly>
                 <met:rollbackOnError>true</met:rollbackOnError>
                 {runtests}
                 <met:singlePackage>true</met:singlePackage>
                 <met:testLevel>{testlev}</met:testLevel>
             </met:deployOptions>
         </met:deploy>
      </soapenv:Body>
      </soapenv:Envelope>""".format(sessionid=tosessid, zipfile=zipString, testlev=testLevel, runtests=''.join(['<met:runTests>'+x+'</met:runTests>' for x in ['OpportunityETLTest1','OpportunityETLTest2']]) if testLevel=='RunSpecifiedTests' else '')
deploytor = requests.post('https://'+tohost+'/services/Soap/m/'+toapiver+'/'+toorgid, deployEnvelope, headers={'content-type': 'text/xml', 'charset': 'UTF-8', 'SOAPAction': 'deploy'})
# END:  Code to deploy ZIP
#'''

#'''
# BEGIN:  Code to check deploy
checkDeployStatusEnvelope = """<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:met="http://soap.sforce.com/2006/04/metadata">
      <soapenv:Header>
         <met:SessionHeader>
            <met:sessionId>{sessionid}</met:sessionId>
         </met:SessionHeader>
      </soapenv:Header>
      <soapenv:Body>
         <met:checkDeployStatus>
             <met:asyncProcessId>{process_id}</met:asyncProcessId>
             <met:includeDetails>true</met:includeDetails>
         </met:checkDeployStatus>
      </soapenv:Body>
      </soapenv:Envelope>""".format(sessionid=tosessid, process_id=getUniqueElementValueFromXmlString(deploytor.content, 'id'))

checkdeploytor = requests.post('https://'+tohost+'/services/Soap/m/'+toapiver+'/'+toorgid, checkDeployStatusEnvelope, headers={'content-type': 'text/xml', 'charset': 'UTF-8', 'SOAPAction': 'checkDeployStatus'})

#print(checkdeploytor.content)
#print()
print('Id: ', getUniqueElementValueFromXmlString(checkdeploytor.content, 'id'))
print('Done: ', getUniqueElementValueFromXmlString(checkdeploytor.content, 'done'))
print('Success: ', getUniqueElementValueFromXmlString(checkdeploytor.content, 'success'))
print('Status: ', getUniqueElementValueFromXmlString(checkdeploytor.content, 'status'))
print('Problem: ', getUniqueElementValueFromXmlString(checkdeploytor.content, 'problem'))
print('NumberComponentsTotal: ', getUniqueElementValueFromXmlString(checkdeploytor.content, 'numberComponentsTotal'))
print('RunTestResult: ', getUniqueElementValueFromXmlString(checkdeploytor.content, 'runTestResult'))
print('NumberTestsTotal: ', getUniqueElementValueFromXmlString(checkdeploytor.content, 'numberTestsTotal'))
#print(getUniqueElementValueFromXmlString(checkdeploytor.content, 'details'))
# END:  Code to check deploy
#'''

DemandTools MassImpact equivalent of UPDATE...SET...WHERE SQL

The CRMFusion company makes powerful Salesforce-record-editing software called DemandTools.

Below are some screenshots of setting up its "MassImpact" (single-table-editing) module to do a job equivalent to running an "UPDATE...SET...WHERE" DML SQL statement against a traditional database, for data cleansing within a specific single table.

For example, to turn all values of table Contact, field Home_Phone__c that are filled with nothing but 10 digits in a row, no punctuation, into a properly formatted US phone number, you might traditionally use the following Oracle-database-friendly DML SQL:

UPDATE Contact
SET Home_Phone__c = REGEXP_REPLACE(Home_Phone__c,'^(\d{3})(\d{3})(\d{4})$','(\1) \2-\3')
WHERE REGEXP_LIKE(Home_Phone__c,'^(\d{3})(\d{3})(\d{4})$')

In DemandTools, you would set up a MassImpact "scenario" as follows:

• Step 1: Tell DemandTools that you want to operate on the "Contact" object/table, and say which fields you want to be able to see the values of while you screen go/no-go on potential updates in the 3rd step.
  
  
  
   
• Step 2: Tell DemandTools that you want to potentially-update all records where "Home_Phone__c" isn't null (unfortunately, you can't use a regular expression in the "WHERE" with DemandTools – but step 3 has some dummy-proofing to get around too wide a selection),
  and say that you want to propose a parentheses-and-dashes-formatted replacement value for any of the returned values that consist of nothing but a string of 10 bare digits in Home_Phone__c.
  
  
  
   
• Step 3: Ensure that you aren't pushing "UPDATE" calls for any records where there is no change to the value of Home_Phone__c, or where the new value of Home_Phone__c would be blank,
  and skim the records to make sure your logic is doing what you thought it would,
  and click "Update Records."
  
  
  
  

---

P.S. Just for geekiness, and to compare ease of use, here's some "Execute Anonymous" Salesforce Apex code along the same idea.
(Note: not tested at scale. Depending on your trigger/workflow/process builder load, might not actually work since it probably all runs in 1 "execution context" of post-DML CPU usage "governor limits," whereas DemandTools will run in truly separate "execution contexts" per 200 records to UPDATE.)

Map<Integer, List<Contact>> csToUpdate = new Map<Integer, List<Contact>>();
Integer csToUpdateCount = 0;
Integer currentBatch = 0;
List<Contact> cs = [SELECT Phone FROM Contact WHERE Phone <> null];
Pattern p = Pattern.compile('^(\\d{3})(\\d{3})(\\d{4})$');
for (Contact c : cs) {
 Matcher m = p.matcher(c.Phone);
 if(m.matches() == true) {
        csToUpdateCount++;
        currentBatch = (csToUpdateCount/200)+1;
        if (!csToUpdate.containsKey(currentBatch)) { csToUpdate.put(currentBatch, new List<Contact>()); }
        c.Phone = m.replaceFirst('($1) $2-$3');
        (csToUpdate.get(currentBatch)).add(c);
 }
}
if (csToUpdate.size() > 0) {
    for (List<Contact> csToU : csToUpdate.values()) {
        UPDATE csToU;
    }
}