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.

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.

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;
    }
}

Python CSV Example - Back-Filling Nearby Zip Code Distances

Today I used the following small Python script to add a bunch of distances to a new field called "Miles_From_Us__c" on 40,000 pre-existing Contact records.

The process was as follows:

1. Export "SELECT ID, MAILINGPOSTALCODE, MILES_FROM_US__C WHERE MAILINGPOSTALCODE != NULL AND MAILINGPOSTALCODE != '' AND MILES_FROM_US__C = NULL" from our Salesforce org to a CSV file on my hard drive at "c:\tempexample\contactzipextract.csv" using the "Apex Data Loader" software.
2. Run the Python script in this blog post instead of bashing my head against the wall with Excel.
3. Import the CSV file on my hard drive at "c:\tempexample\contactzipupdate.csv" back into our Salesforce org as an "UPDATE" operation using the "Apex Data Loader" software.

 

import pandas

zips = ['11111','22222','33333','44444']

zipstodist = {'11111':0, '22222':1, '33333':9, '44444':21}

df = pandas.read_csv('c:\\tempexample\\contactzipextract.csv', dtype='object')

df = df[df['MAILINGPOSTALCODE'].str[:5].isin(zips)]

df['MILES_FROM_US__C'] = df['MAILINGPOSTALCODE'].str[:5].apply(lambda x : zipstodist[x])

df[['ID','MILES_FROM_US__C']].to_csv('c:\\tempexample\\contactzipupdate.csv', index=0)

Here's an explanation of what my script is doing, line by line, for people new to Python:

1. Says that I'll be using the "Pandas" plug-in to Python.
2. Says which U.S. postal codes ("zip codes") I care about as a Python "list" that I give a nickname of "zips."
   • I got this from the first column of a CSV file of "zip codes of interest" that a consultant sent one of my users.
3. Defines the "distance from us," in miles, of each zip code of interest, as a Python "dict" that I give a nickname of "zipstodist."
   • I got this from the first and second columns of a CSV file of "zip codes of interest" that a consultant sent one of my users.
   • I could have cross-checked the two CSV files in Python, but I had already transformed the CSV data into this list style when writing some Salesforce Apex code for future maintenance, so copying/pasting it into Python was easier in this case.
4. Reads my CSV file into Python as a Python "Pandas dataframe" that I give a nickname of "df."
5. Filters out any rows whose first 5 characters of the value in the "MAILINGPOSTALCODE" column aren't among my zip codes of interest.
6. Sets each row's"MILES_FROM_US__C" to the distance corresponding to the first 5 characters of that row's "MAILINGPOSTALCODE" value.
7. Exports the "ID" & "MILES_FROM_US__C" columns of my filtered, transformed dataset out to a new CSV file on my hard drive.

A Brief UPDATE Script: Oracle SQL vs. Salesforce Apex+SOQL

In a copy of Salesforce using EnrollmentRx, we are capturing the details of every submission-from-a-student on a table attached to "Contact" (PK-FK) called "Touch_Point__c."

When such a "Touch_Point__c" record is created, if it is the first created for a given "Contact," a trigger copies its "Lead_Source__c" value over to the corresponding "Contact" record's "LeadSource" field.

Midway through an advertising campaign, a decision was made to change the string used for a certain departmental landing page's "Lead_Source__c" value from "Normal Welcome Page" to "Landing Page."

We'd caught up on back-filling "Lead_Source__c" values on old "Touch_Point__c" table records.

However, we hadn't yet back-filled the corresponding "LeadSource" fields on "Contact" in the case where such "Touch_Point__c" records had been the first in existence for a given "Contact."
(We wanted to leave "Contact" records alone where none of the altered-after-the-fact "Touch_Point__c" were actually the first "Touch_Point__c" record for the "Contact.")

I wrote a little script that's the equivalent of a complex "UPDATE" statement and am sharing it here for colleagues from the Oracle SQL world.
(Please excuse any typos or inefficiencies -- the real data set was small, so I didn't care about performance, and I didn't actually run the Oracle.)

---

Here's some Oracle SQL that I believe would've done the job, if Salesforce were a normal Oracle database:

UPDATE Contact
SET LeadSource = (
    SELECT
        Lead_Source__c
    FROM Touch_Point__c
    INNER JOIN (
        SELECT Contact__c, MIN(CreatedDate) AS MIN_CR_DT
        FROM Touch_Point__c
        GROUP BY Contact__c
 ) qEarliestTP
    ON Touch_Point__c.Contact__c = qEarliestTP.Contact__c AND Touch_Point__c.CreatedDate = qEarliestTP.MIN_CR_DT 
    WHERE Contact.Id = Touch_Point__c.Contact__c
 AND Lead_Source__c='Landing Page'
 AND Dept_Name__c='Math'
 AND utm_source__c is not null
 AND extract(year from CreatedDate) >= extract(year from current_date)
)
WHERE Contact.Id IN (
    SELECT
        Touch_Point__c.Contact__c
    FROM Touch_Point__c
    INNER JOIN (
        SELECT Contact__c, MIN(CreatedDate) AS MIN_CR_DT
        FROM Touch_Point__c
        GROUP BY Contact__c
 ) qEarliestTP
    ON Touch_Point__c.Contact__c = qEarliestTP.Contact__c AND Touch_Point__c.CreatedDate = qEarliestTP.MIN_CR_DT 
    WHERE Contact.Contact__c = Touch_Point__c.Contact__c
 AND Lead_Source__c='Landing Page'
 AND Dept_Name__c='Math'
 AND utm_source__c is not null
 AND extract(year from CreatedDate) >= extract(year from current_date)
)
AND Contact.LeadSource='Normal Welcome Page'

---

Here's the Salesforce Apex code (with embedded SOQL) I wrote to do the job instead, since Salesforce doesn't give you a full-on SQL-type language.**

// Loop through every record in the "Touch_Point__c" table, setting it aside in a map, keyed by its foreign key to a the "Contact," if it is the earliest-created "Touch_Point__c" for that Contact
Map<Id, Touch_Point__c> cIDsToEarliestTP = new Map<Id, Touch_Point__c>();
List<Touch_Point__c> allTps = [
  SELECT Id, Contact__c, CreatedDate
  FROM Touch_Point__c
  ORDER BY Contact__c, CreatedDate ASC
 ];
for (Touch_Point__c tp : allTPs) {
 // The "ORDER BY" in allTPs should make this logic short-circuit at the first half of the "IF," but 2nd half will dummy-check if the list is, for some reason, out of order.
    if (!cIDsToEarliestTP.containsKey(tp.Contact__c) || cIDsToEarliestTP.get(tp.Contact__c).CreatedDate > tp.CreatedDate) {
        cIDsToEarliestTP.put(tp.Contact__c, tp);
    }
}

// Loop through every "landing page visit"-typed record in the "Touch_Point__c" table, updating a modified in-memory copy of the record in the "Contact" table it references to a list of "Contact" records called "csToUpdate" ONLY IF the "landing page"-related TouchPoint is also the "earliest-created" TouchPoint for that Contact record -- then call a DML operation on that in-memory list to persist it to the database.
List<Contact> csToUpdate = new List<Contact>();
List<Touch_Point__c> mathLandingTPs = [
  SELECT Id, Contact__c, Lead_Source__c, Contact__r.LeadSource, Dept_Name__c, utm_source__c, CreatedDate
  FROM Touch_Point__c
  WHERE Lead_Source__c='Landing Page'
  AND Contact__r.LeadSource='Normal Welcome Page'
  AND Dept_Name__c='Math'
  AND utm_source__c<>null
  AND CreatedDate>=THIS_YEAR
 ];
for (Touch_Point__c tp : mathLandingTPs ) {
    if (cIDsToEarliestTP.containsKey(tp.Contact__c) && cIDsToEarliestTP.get(tp.Contact__c).Id == tp.Id) {
        csToUpdate.add(new Contact(Id=tp.Contact__c, LeadSource=tp.Lead_Source__c));
    }
}
UPDATE csToUpdate;

---

Oracle programmers, I imagine your colleagues might yell at you if you used PL/SQL to hand-iterate over smaller SQL queries in Oracle rather than using native SQL do the work for you. In Salesforce, that's simply the way it's done.

**Note that more complex code might be required -- e.g. you might have to run the same code several times with a row-count limit on it -- since Salesforce is pretty picky about the performance of triggers fired in response to a DML statement. (A normal Oracle database has its limits, too, of course, but they're likely far less strict if it's your own in-house database than with Salesforce.)

Marketo Activities -> Salesforce Tasks with REST and Python

A year and a half ago, we had to turn off 2-way syncing in Marketo. It was messing up "Contact" record fields too badly.

Unfortunately, that also meant we lost the functionality of letting Marketo create a new "Task" for a Contact's "Activity History" every time we sent them an email. So we've got a year-and-a-half gap in such "Task" records in Salesforce to fill. (It's okay for it to be a one-off, because we're consolidating mass-mailing systems between departments and getting rid of Marketo.)

There's no easy "export a CSV file of every email we've ever sent every person in Marketo" feature in Marketo's point-and-click web-browser-based user interface. Tech support said we'd have to write code to query their REST API.

My basic algorithm is as follows:

1. Use Python ("requests" library) code against the Marketo API to get a "dict" of every "Email Sent" activity in Marketo (and which LeadId it belongs to and the name of the email according to Marketo)
2. Use Marketo's export functions against "All Leads" to get a CSV file containing a mapping of Marketo Lead IDs to Salesforce Contact IDs & save it to my hard drive.
3. Use Salesforce's Data Loader to get a CSV file containing all existing "Task" records from the old "Marketo Sync" email tracking & save it to my hard drive.
4. Use Python ("pandas" library) code to append Salesforce IDs from the CSV in step #2 to the "dict" in step #1, while subtracting emails Salesforce already knows about (match on "WhoId" & "Subject" in CSV from step #3). Write the result out to CSV.
5. Use Salesforce's Data Loader to import the CSV from step #4 into the "Tasks" table of Salesforce

---

The Python script is:

import requests
baseurl = 'https://xxx.mktorest.com'
clientid = 'cid'
clientsecret = 'secret'
apitaskownerid = 'xxx' #(Marketo API sf user)
activsincedatetime = 'yyyy-mm-ddThh:mm:ss-GMTOffsetHH:00'
accesstoken=requests.get(baseurl + '/identity/oauth/token?grant_type=client_credentials' + '&client_id=' + clientid + '&client_secret=' + clientsecret).json()['access_token']
firstnextpagetoken = requests.get(baseurl + '/rest/v1/activities/pagingtoken.json' + '?sinceDatetime=' + activsincedatetime + '&access_token=' + accesstoken).json()['nextPageToken']

def getactivs(pagetok):
    ems = []
    activsbatchjson = requests.get(baseurl + '/rest/v1/activities.json' + '?nextPageToken=' + pagetok + '&activityTypeIds=INSERTNUMBERHERE' + '&access_token=' + accesstoken).json()
    if 'result' in activsbatchjson:
        ems.append(activsbatchjson['result'])
    if activsbatchjson['moreResult'] != True:
        return ems
    else:
        return getactivs(activsbatchjson['nextPageToken'])

emailssent = getactivs(firstnextpagetoken)[0]

import pandas
activdf = pandas.DataFrame(emailssent, columns=['leadId', 'primaryAttributeValueId', 'primaryAttributeValue', 'activityDate'])
leaddf = pandas.read_csv('c:\\temp\\downloadedmarketoleads.csv',usecols=['Id', 'Marketo SFDC ID'])
joindf = pandas.merge(activdf, leaddf, how='left', left_on='leadId', right_on='Id')
joindf.drop(['Id'], axis=1, inplace=True, errors='ignore')
joindf.rename(columns={'Marketo SFDC ID':'WhoId'}, inplace=True)
joindf['Status'] = 'Completed'
joindf['Priority'] = 'Normal'
joindf['OwnerId'] = apitaskownerid
joindf['IsReminderSet'] = False
joindf['IsRecurrence'] = False
joindf['IsHighPriority'] = False
joindf['IsClosed'] = True
joindf['IsArchived'] = True
joindf['Custom_field__c'] = 'Marketo Sync'
joindf['Subject'] = joindf['primaryAttributeValue'].map(lambda x: 'Was Sent Email: ' + x)
joindf['Description'] = 'Marketo email history backfill'

existingtasksdf = pandas.read_csv('c:\\temp\\downloadedsalesforcetasks.csv') # SELECT ActivityDate,CreatedDate,Description,LastModifiedDate,Subject,WhoId FROM Task WHERE Custom_field__c = 'Marketo Sync' AND Subject LIKE 'Was Sent Email: %' AND IsDeleted = FALSE
existingtasksdf['matched'] = True
not_existing = pandas.merge(joindf, existingtasksdf, how='left', on=['WhoId','Subject'])
not_existing = not_existing[pandas.isnull(not_existing['matched'])]
not_existing.drop(['matched'], axis=1, inplace=True, errors='ignore')
not_existing.to_csv(path_or_buf='c:\\temp\\newtasksreadyforinsert.csv', index=False)

(Note - not a lot of through given to security with respect to this script and its use of login credentials in a GET (although HTTPS) call. It's a one-off script, we're about to shut down the system, and I deleted the credentials from Marketo's configuration shortly after running the script. Your requirements may vary - you may need a more robust way of authenticating.)

---

P.S.

Updated code coming soon - I had to change the "getactivs" section of the code from a recursive solution (not sure why that came to mind first...it just did that day...) to an interative one because it errored out when fetching half a million records 300 at a time.

I also ended up dumping the Marketo API half-million-record response's output to CSV and commenting out the post-processing part of the code, then commenting out the API-fetch and running post-processing against that CSV. The API-based fetch wasn't always getting a complete data set. (I suspect my authorization key might have been expiring. However, by the time I ran it with code to debug the problem, I was working at start-of-business, and - probably due to lower network traffic - the fetch finally ran without issue, so I'll never know. All I know is I got my data once, and once works for me.)

Finally, I ran into some snags with data Pandas couldn't read from CSV in the API output (some sort of weird em-dash in the data, I think), so the updated code will include even more low-performance kludges to work around that. (Using the CSV module and a loop to build a list-of-dicts and having Pandas read that worked.)

HTTP Callout Mocks

This summary is not available. Please click here to view the post.

Big Job: Apex vs. Manual CSV File Manipulation vs. Python-Is-Cool

In our Salesforce org, we have an object called "Extended Contact Information" that's in a "detail" relationship hanging off of "Contact."

It lets us use record-types to cluster information about Contacts that is only of interest to certain internal departments (and lets us segment, department-by-department, their answers to questions about a Contact that they might feel differently about ... such as whether they're the best way to get ahold of a company).

We also have a checkbox field for each internal department that lives on Contact, and that needs to be checked "true" if a department is working with someone. (Whenever a department attaches an "Extended Contact Information" object to someone, we have a trigger in place that checks that department's corresponding box on "Contact," but it can also be checked by hand.)

Our computers mostly care about this checkbox, but humans care about the "Extended Contact Information" record, so today I did an audit and noticed 4,000 people from our "Continuing Ed" department were flagged in the the checkbox as working with that department but didn't have "Extended Contact Information" records of that type attached.

I wrote the following Apex to execute anonymously, but due to a bazillion triggers downstream of "Extended Contact Information" record inserts, it hits CPU time execution limits somewhere between 100 at a time & 300 at a time.

List<Contact> cons = [SELECT Id, Name FROM Contact WHERE Continuing_Ed__c = TRUE AND Id NOT IN (SELECT Contact_Name__c FROM Extended_Contact_Information__c WHERE RecordTypeId='082I8294817IWfiIWX') LIMIT 100];

List<Extended_Contact_Information__c> ecisToInsert = new List<Extended_Contact_Information__c>();

for (Contact c : cons) {
    ecisToInsert.add(new Extended_Contact_Information__c(
        Contact_Name__c = c.Id,
        RecordTypeId='082I8294817IWfiIWX'
    ));
}

insert ecisToInsert;

Probably the fastest things to do are one of the following:

1. run this code manually 40 times at "LIMIT 100"
2. export the 4,000 Contact IDs as a CSV, change the name of the "Id" column to "Contact_Name__c," add a "RecordTypeId" field with "082I8294817IWfiIWX" as the value in every row, and re-import it to the "Extended Contact Information" table through a data-loading tool

But, of course, the XKCD Automation Theory part of my brain wants to write a Python script to imitate option #2 and "save me the trouble" of exporting, copying/pasting, & re-importing data. Especially since, in theory, I may need to do this again.

TBD what I'll actually go with. Python code will be added to this post if I let XKCD-programmer-brain take over.

As a reminder to myself, here's a basic Python "hello-world":

from simple_salesforce import Salesforce
sf = Salesforce(username='un', password='pw', security_token='tk')
print(sf.query_all("SELECT Id, Name FROM Contact WHERE IsDeleted=false LIMIT 2"))

Output:

OrderedDict([('totalSize', 2), ('done', True), ('records', [OrderedDict([('attributes', OrderedDict([('type', 'Contact'), ('url', '/services/data/v29.0/sobjects/Contact/xyzzyID1xyzzy')])), ('Id', 'xyzzyID1xyzzy'), ('Name', 'Person One')]), OrderedDict([('attributes', OrderedDict([('type', 'Contact'), ('url', '/services/data/v29.0/sobjects/Contact/abccbID2abccb')])), ('Id', 'abccbID2abccb'), ('Name', 'Person Two')])])])

And this:

from simple_salesforce import Salesforce
sf = Salesforce(username='un', password='pw', security_token='tk')
cons = sf.query_all("SELECT Id, Name FROM Contact WHERE IsDeleted=false LIMIT 2")
for con in cons['records']:
    print(con['Id'])

Does this:

xyzzyID1xyzzy
abccbID2abccb

Whoops. Looks like there aren't any batch/bulk insert options in "simple-salesforce," and I don't feel like learning a new package. Splitting the difference and grabbing my CSV file with Python, but inserting it into Salesforce with a traditional data-loading tool.

from simple_salesforce import Salesforce
sf = Salesforce(username='un', password='pw', security_token='tk')
cons = sf.query_all("SELECT Id, Name FROM Contact WHERE Continuing_Ed__c = TRUE AND Id NOT IN (SELECT Contact_Name__c FROM Extended_Contact_Information__c WHERE RecordTypeId='082I8294817IWfiIWX') LIMIT 2")

import csv
with open('c:\tempsfoutput.csv', 'w', newline='') as csvfile:
    fieldnames = ['contact_name__c', 'recordtypeid']
    writer = csv.DictWriter(csvfile, fieldnames=fieldnames, lineterminator="\n")
    writer.writeheader()
    for con in cons['records']:    
        writer.writerow({'contact_name__c': con['Id'], 'recordtypeid': '082I8294817IWfiIWX'})

print('done')

And then the CSV file looks like this:

contact_name__c,recordtypeid
xyzzyID1xyzzy,082I8294817IWfiIWX
abccbID2abccb,082I8294817IWfiIWX

Test-Driven Development Example

The following post is based on an email I sent a student worker who, bless his heart, is doing terribly complicated and important, but also terribly boring, grunt work. As a thank-you, I'm giving him Apex coding projects to do in a Developer Org and granting him a block of time per day to work on them.

---

This morning, I'm working on writing a new "public" Apex method that, as a parameter, takes a set of Contact IDs. If a Contact's "Account.Name" is meaningless (e.g. "No Company Assigned,"), this method then checks to see if there is a non-null string in that Contact's "Company Holding Spot" custom field. If any such strings happen to match the Name of a record in our Accounts table, my new method populates the Contact's AccountId with the appropriate value. Hopefully, I can use this to clean up a bunch of old data in one fell swoop.

I'm writing the new method in test-driven-development style, which means I have my test fully ready to go before I've put any "action" code into my new method. Here's what that looks like:

@isTest
public class ScratchPadClassTest {
    
    // Class-level static variables
    private static Boolean setupAlreadyRan = FALSE;
    private static UtilityDefaultInfoOftenNeeded useful;
    
    // The test method    
    static testMethod void testCompanyHoldingSpotToAcctId() {
        runSetup();
        
        // Set up a couple of test accounts (also remember that a 3rd "No Company Assigned" one exists thanks to "runSetup()")
        Account aMcD = new Account(Name='McDonalds');
        Account aTgt = new Account(Name='Target');
        insert new List<Account>{aMcD, aTgt};
        
        // Set up a couple of test contacts who newly claim they work at McDonald's (but one of whom already worked somewhere else)
        Contact c1 = new Contact(LastName='McTestNullAcctNowMcD', AccountId=useful.getDefaultAccountId(), Company_Holding_Spot__c = 'mcdonalds');
        Contact c2 = new Contact(LastName='McTestTgtAcctNowMcD', AccountId=aTgt.Id, Company_Holding_Spot__c = 'Mc. Donalds');
        insert new List<Contact>{c1, c2};
        
        Test.startTest();
        // Call our data-transformation method on the IDs of our two contacts
        ScratchPadClass.copyCHSToAcct(new Set<Id>{c1.Id, c2.Id});
        Test.stopTest();
        
        // Pull a fresh copy of our contacts out of the database
        Map<Id, Contact> csAfter = new Map<Id, Contact>([SELECT Id, AccountId, Account.Name FROM Contact WHERE Id IN (:c1.Id, :c2.Id)]);
        
        // PERFORM TESTS CHECKING FOR DATA QUALITY
        // Since Contact #1 had a generic "No Company Assigned" account to start with, McD's should have propagated into AccountId
        System.assertEquals(aMcD.Id,csAfter.get(c1.Id).AccountId, 'c1 Account Name is ' + csAfter.get(c1.Id).Account.Name);
        // Contact #2 was already working at a real company (Target), don't overwrite w/ McD's.  Needs human review.
        System.assertEquals(aTgt.Id,csAfter.get(c2.Id).AccountId, 'c2 Account Name is ' + csAfter.get(c2.Id).Account.Name);
    }
    
    // A private helper method
    private static void runSetup() {
        if (setupAlreadyRan == FALSE) {
            DefaultTestDataFactory.setUpCustomSettings();
            if (useful==null) { useful = UtilityDefaultInfoOftenNeeded.getInstance(); }
            setupAlreadyRan = TRUE;
        }
        return;
    }
       
}

NOTE: You might notice that I have classes called "DefaultTestDataFactory" and "UtilityDefaultInfoOftenNeeded" whose code isn't shown here. Don't worry about them. All they do that matters is to this code is:

1. Insert a "No Company Assigned" account into the database (DefaultTestDataFactory) and
2. Provide an easy way to retrieve the ID of that account (UtilityDefaultInfoOftenNeeded.getInstance().getDefaultAccountId())

The body of ScratchPadClass looks like this:

public class ScratchPadClass {

    public static void copyCHSToAcct(Set<Id> cIds) {
        return;
    }
    
}

When I "Run Test" on "ScratchPadClassTest," the first System.AssertEquals fails with the following error message:

• Assertion Failed: c1 Account Name is No Company Assigned: Expected: 00738000006UTiPEDC, Actual: 00738000006UTiOEDC

In other words, I expected c1’s Account.Name to change from "No Company Assigned" to "McDonalds," but it didn’t happen.

Well, of course it didn’t happen. ScratchPadClass.copyCHSToAcct() doesn’t even do anything yet!

Now I will go write ScratchPadClass.copyCHSToAcct(). And I’ll know I wrote it correctly when my test passes.

And THAT’s test-driven development!

Mid-Dormancy Updates

The good news is that I'm more used to Apex+SOQL than PL/SQL+SQL now. I can't say the same of SQL vs. SOQL (SOQL still drives me bonkers), but I'm not floundering enough to have to write down my daily frustrations any more. In fact, I'd say I'm starting to bang out Apex triggers rather often!

I've also had the chance to work on some other really cool projects. When I went to Dreamforce, I vacationed at the home of a hobby-acquaintance who happens to be part of San Francisco's tech boom crowd. Between hanging out in the Dreamforce dev zone and spending time with him and his friends, I got inspired to get my hands dirtier with not-purely-database Salesforce programming.

• With a friend's help, I developed a Python (Pandas & OAuth & Flask) web application and hosted it on Heroku.com. It facilitates a colleague's daily updates/inserts of external data into Salesforce using Name+Email as a matching key. (Name+Email for matching is not natively supported by Salesforce data loaders.)
  
  My colleague provides my tool a .CSV file of names, email addresses, and data to be uploaded into Salesforce.
  
  The web application downloads a list of names+email addresses+IDs from Salesforce, joins the two lists, and returns the user's .CSV file to them in a form that is ready to "upsert" into Salesforce using standard data-loading tools.
• With another friend's help, I wrote my first significant amount of JavaScript/JQuery. Our Web-To-Salesforce form handler wasn't doing a very good job, so I switched us out to another provider. Our forms now do a great deal of "onSubmit" pre-processing to alter the DOM before it goes off to the form handler.
  
  A lot of cloud form-handler providers don't let you choose your own field names, for example. They make you use names like "form143_field281." To avoid changing things like "first_name" to an obscure name/id like that in our HTML and keep our forms more portable should we have to switch form handlers again, I copy the relevant fields into hidden fields at the last minute using this JavaScript.
  
  There were also certain "to-Salesforce" behaviors that couldn't really be taken care of in the JavaScript or the form handler's connector, so I got to do plenty of Apex trigger programming as well.

Oh, and finally, I finished my software degree with straight A's. Booyah - time to check in about a raise!

It's been a fun period of blog "dormancy" - happy new year to all.

My First VisualForce: A Table To Simulate A List View

So, a month or two ago, our implementation partner wrote a trigger to keep a field on the Opportunity called "Related Application Record" populated with a Lookup to the Admission Application record it most closely corresponded to.

(Don't forget our old friend the double-sided garden rake!)

Those Admission-Application "rake tines" are actually miniature rakes in and of themselves. They have "Checklist Records" hanging off of them, indicating what documents a Contact has turned in along with their application form.

We wanted to put a Related List at the bottom of the Opportunity page layout to show any "Checklist Records" from the Admission Application record indicated in "Related Application Record."

Only SalesForce wouldn't let us.

So I wrote my first VisualForce page, which allowed me to put a component into the Opportunity page layout that looks close enough to a Related List.

So here's the code

(Please let me know if you have any ideas for making "chkls" in my wrapper class private - it seems like bad design to leave it public. Please also let me know if you see any design or security flaws in this code. I'm still a beginner and appreciate pro tips.)

(Note: Please let me know if my code doesn't seem to flow. I did some manual Find&Replace to obfuscate my org's internal structure just a wee bit, and I might have missed something.)

The VisualForce Page

<apex:page standardController="Opportunity" extensions="OppApplicationChecklistClass">
 <apex:pageBlock >
  <apex:pageBlockTable value="{!app_chkl}" var="a" id="table">
   <apex:column value="{!a.chkls.Requirement__c}"></apex:column>
   <apex:column value="{!a.chkls.Received_Date__c}"></apex:column>
   apex:column value="{!a.chkls.Comment__c}"></apex:column>
  </apex:pageBlockTable>
 </apex:pageBlock>
</apex:page>

The Apex Class (A "Standard Opportunity Controller Extension")

(Ignore the close-tags in line 52 - either Blogger or my new code formatter doesn't seem to like Apex collections and insists on closing them as if they were HTML.

public with sharing class OppApplicationChecklistClass {
 
    // Attributes possessed by all objects made out of this class
    private final Opportunity opp;
    private List chklRecords;
    
    // Constructor for this class
    public OppApplicationChecklistClass(ApexPages.StandardController stdController) {
        this.opp = (Opportunity)stdController.getRecord();
    }
 
    // Methods possessed by all objects made out of this class
    
    public Opportunity getOpportunity() {
        return opp;
    }
    
    public List getchecklist() {
  
  if (chklRecords == null) {
   
   chklRecords = new List();
    
   List tempQueryResult = [SELECT (SELECT Id FROM Opportunities__r),
    (SELECT Applicant_Last_Name__c, Requirement__c, Received_Date__c, 
    Comment__c FROM Checklist__r) FROM ApplicationRecord__c
    WHERE Id IN (SELECT Related_Application_Record__c FROM Opportunity WHERE Id = :opp.id)
    AND Id IN (SELECT RelatedApplication__c FROM Checklist__c)];

    if(tempQueryResult.size() == 1) {
    for (Checklist__c a : (tempQueryResult.get(0)).Checklist__r) {
     chklRecords.add(new wChkl(a));
    }
   }
  
  }
  
  return chklRecords;
  
    }
    
    // Another attribute possessed by all objects made out of this class...
    // ...the wrapper class wChkl surrounding a single Checklist__c object
    public with sharing class wChkl {
        public Checklist__c chkls {get; set;} // I don't seem to be able to find a way to privatize this.
        public wChkl(Checklist__c a) {
            chkls = a;
        }
    }
 
}

My awful test class

(I swear I mean to come back to it and make it meaningful...)

@isTest
private class OppApplicationChecklistTest {
  static testMethod void test() {
        
        Opportunity setupOpp = new Opportunity();
        ApexPages.StandardController sc = new ApexPages.standardController(setupOpp);
        
        // Create an instance of the page controller to test
        OppApplicationChecklistHandler testPageCon = new OppApplicationChecklistHandler(sc);
        
        // Try calling methods/properties of the controller in all possible scenarios to get the best coverage.
        Opportunity testOpp = testPageCon.getOpportunity();
        
        // OppApplicationChecklistHandler works with a blank Opportunity if it can't find a real one from the page it's on.
        System.assertEquals(null, testOpp.Id);
        // Working with a blank Opportunity, the list of Checklist records would also be blank.
        System.assertEquals(0, (testPageCon.getchecklist()).size());
    }
}

VisualForce!

It's off to vacation for me, but boy do I have some fun code to share once things settle down. I created my first VisualForce component! It's a Related-List-like block that can be inserted into an Opportunity Page Layout, and it shows a list of values from an object that ACTUALLY hangs off of "Admission" in the underlying schema.

Update: Posted here

6 Ways To Code Field Value Changes / DML In SalesForce

Back when I first started designing a solution to the problem of syncing ERP "Admission" records to SalesForce "Opportunity" records, I approached it with the idea that "synchronization" means "examining both objects" for sameness or differences.

That's what I would have recommended in Oracle. We would have written a PL/SQL stored procedure or anonymous procedure with an IF (criteria) THEN (action) structure. We would have scheduled it to run as often as we expected our data to change.

 

However, I couldn't find the equivalent of a PL/SQL stored procedure - something you can invoke just because you feel like it - in SalesForce.

Sure, there were Methods in Apex Classes, but I couldn't figure out how to invoke them at will.

I wanted this functionality because a record from either object - "Admissions" or "Opportunity" - could change in a way that creates or breaks a "match" between the two objects.
It takes two to tango when you're talking about "synchronization."
Triggers (both in Oracle and in SalesForce) can only react to one type of object changing its value. A single trigger can't be progrmamed to fire based on updates to records of either object type.

 

After I posted my dilemma on the SalesForce Success Community, James Loghry of EDL Consulting provided this wonderful summary of the ways you can programmatically / batch change the value of one field based on the value of another:
(text in {}'s mine)

 

1. Apex Triggers / {Workflow Rules} / Process Builder for handling updates / logic when a record {of a given object type} is created or updated.
    
2. Batch Apex which can be executed manually or on a scheduled, periodic basis.
    
3. Web Services (REST or SOAP) to update the records from either the same Salesforce instance or an external data source.
    
4. A middleware solution (for instance Oracle Fusion / BPEL or Jitterbit) that interacts with Salesforce.
    
5. You could also manually use tools like Dataloader / {DemandTools} to update the records manually.
    
6. Depending on your relationships and requirements, you could potentially use formula / {rollup summary} fields between the detail records and master contact record instead of performing "DML" transactions (record creates, updates, deletes, undeletes, merges, etc.)
   {How cool would it be if there were such a thing as "SOQL fields"? *alas*}

 

Batch Apex sounds like a very promising way to "keep thinking about synchronization the way I always have in Oracle," although I'm disappointed by the language implying that you can only have 5 jobs scheduled at a time. I wonder how much functionality you can safely cram into a single job...

 

In the meantime, because our consulting partners are skilled at writing Apex Triggers, we have tried to ask ourselves where a trigger could handle most of the work. In the case of synchronizing "Admission" and "Opportunity" records, we expect that 90% of the time, changes to the "match" between records will come from changes to Admission (ERP) records.

Our department's SalesForce users will simply have to be warned that they play with certain fields on "Opportunity" records at risk of their own confusion (about why the data didn't re-synchronize itself).

 

I'm daydreaming of more, but it's a good start in a fast push to go live.

An Update On The "Winner Picker" Formula Field

Two weeks ago I described a formula for a field called Winner_Picker__c that helps perform a many-to-one cardinality reduction.

After last week's immersion in deduplicating our records, today I realized that it needed some refinement to handle deduplicated Contacts.

 

When the source of duplicate Contact records in SalesForce is that there exist duplicate Person records in our ERP Banner, first we merge those records in Banner - then we merge them in SalesForce and clean up any redundancies.

Merging the "Admission" records dangling off of a single Person as the "Person" records are combined in Banner happens as part of manual data entry before the computerized merge.

In SalesForce, it makes more sense for us to do the computerized merge of Contacts first and clean up redundant dangling records that they both brought to the party afterwards.

 

My original formula only produced a unique Winner_Picker__c value within the scope of a person. Today I fixed it so that the formula does the final "coin toss" based on data unique to a SalesForce Contact, rather than data unique to a Banner person (more than one of whom might be dangling numerically identical Admissions records off of Contact until manual cleanup is performed).

Instead of pulling the least significant digits from an ERP record ID, I now pull them from an AutoNumber-typed Record ID from SalesForce called "Name." (Note: Apparently they are up to 10 digits long).

I also realized that having a 2nd Record_Type_Prioritizer__c call wasn't necessary, which is good, because my resulting number was getting too big for SalesForce..

 

The updated code is:

IF(

Term_Lookup__r.Future__c ,
IF(

OR(

ISPICKVAL(Status__c, "Deferred"),
ISPICKVAL(Status__c, "Rejected"),
ISPICKVAL(Status__c, "Withdrawn")

)
, null
,
(

(((Record_Type_Prioritizer__c * 1000000) - Term_Lookup__r.Term_Code_Numeric__c)* 10000000000)
+
VALUE(RIGHT(NAME,LEN(NAME)-4))

)

) , null

)

Buggy Booleans

I've been cleaning up a lot of address/phone/email data that got mis-synced (or not synced at all) between our ERP and SalesForce this week.

(I'm supposed to be making error reports of SalesForce/ERP data that is no longer in sync due to humans changing it in only one system or the other, but to make such reports meaningful, I've had to eliminate all the bulk mistakes.)

 

Typically, for such single-object (Contact) errors, I make a formula field to detect the error ("WHERE").
I make a List View on Contact to show the fields that help a human decide how to react to the error ("SELECT...FROM...").
Why List Views? Reports don't let you filter a list based on the value of a formula field. List Views do.

 

You'd think that "checkbox" - a Boolean - would be the perfect data type for an error-condition-checking formula field. Who doesn't love a Boolean? It's the ultimate in concreteness and unambiguity - everything a programmer loves.

SalesForce. SalesForce doesn't love a Boolean, that's who. :-(

 

Most filters in SalesForce can only do string and numeric comparisons (and "is null" & "is not null" are often missing). Don't count on anything but other formula fields being able to have a sense of "is checked/selected" or "is not checked/selected."

List View filters can't handle "is checked."
Surprisingly, neither can the filters in "power-tool" software MassImpact by DemandTools.

 

So I've made a lot of text-typed formula fields this week that return "true" and null.
(Although since I'm returning a string anyway, I have decided to return a substitute for "true" like "BannerPhoneNotInSF" so I don't get similar-looking formulas mixed up with each other when I have them pasted into two NotePad++ windows at the same time. That habit saved my bacon today.)

 

Which reminds me of another thing I've been doing this week: A lot of relying on NotePad++ to edit formula fields. I find it extremely helpful to edit nested code there and paste it back into the Formula Field editor window in my browser at the last minute. I don't know what I'd do without my indentation management and little red close-parentheses.

Rolling Up A Single Record To Its Master Object

Rollup fields are the other of the two types of "calculated value" field allowed in SalesForce.

When you have a "Master-Detail" relationship between two objects in SalesForce, you can add a Rollup Field to the "Master" object that shows the result of an aggregation (count/min/max/etc.) on the values found in a single "column" of the "Detail" object.

But you don't have to use rollup fields exactly as they were intended!

 

Today, I was asked to add a field to "Admissions" called "Application Form Received Date."

After poking around in reports from our legacy system (Banner), I discovered that we calculate this date by looking at the "Received Date" of the checklist item called "Application Form" that is attached to the a given "Admissions" record.

In both Banner and the SalesForce "dumping ground custom objects" for imported Banner data, each application document that we have received is stored in its own record of a "Checklist" object.  "Checklist" is at the "detail" end of a Master-Detail relationship with "Admissions."

It just so happens that in our data model, no "document type" may be used twice in the same set of "Checklist" records.  (Or, in our classic "tines of a garden rake" example, no color of tine may be used more than once on a given rake.)

 

So back to the analogy of children drawing on a garden rake (copying details) as they build it:  the rule here is that if you see an orange tine, you have to copy whatever is doodled on it onto the handle of the rake.

A formula field won't help you implement this rule. Formula fields don't know what to do when they have multiple rows to choose a value from, so they can only copy field values "from handle to tines" (from Master to Detail or from LookedUp to LookingUp).

 

However:

• If you want to copy NUMERIC or DATE data "from tine to handle" (from Detail to Master),
• And you know that you can specify "which color of tine" by inspecting the values of fields on the "tine" (except formula field values),
• And you know that only "1 tine of that color" will exist...

...Then doing a MAX or MIN "Rollup Field" on the "tine" (Detail) field will make an exact copy of it on the "handle" (Master).

 

After all, the solution to "What is the biggest number between 4 and 4?" is ... 4.

 

To build the "Application Form Received Date" field on the "Admissions" object, I added a Rollup Field defined as the maximum "Received Date" among all eligible Checklist items. I then filtered the definition of "eligible Checklist item" to records where "Document Type" = "Application Form" (knowing that there would be only 0 or 1 such records).

 

Yay for kludges! (I think I want to print that and hang it on my wall.)

A Formula Field To Pick The Best Custom Object Among Many

Formula fields are one of two types of "calculated value" field allowed in SalesForce. (The other type being lookup fields, which do not allow any calculations besides aggregations.)

As I mentioned, while most universities choose either custom "Admission" objects or the SalesForce native "Opportunity" object to represent the many-to-one nature of people trying to matriculate to their school, we are forced to live in, and synchronize, both models.

Keeping this data synchronized requires code to perform a cardinality reduction. I used a formula field to do the heavy lifting.

 

Note: If you want to skip the background and go straight to the formula field's source code, click here.

 

Imagine that you're building a garden rake with tines at both ends of the handle. (The handle is a particular "Contact." Each tine at 1 end of the rake represents "Admissions" records. Each tine at the other end of the rake represents "Opportunity" records.)

• You allow the neighbor's children to add tines to the "Admissions" end of the rake.
  (This is like the Central Admissions Office adding records about paperwork coming into their office from the Contact.)
  • The neighbor's children are allowed to draw pretty pictures on the "Admissions" tines before they attach them to the rake.
    (This is like the Central Admissions office recording the details of that paperwork.)
• You allow your own children add tines to the "Opportunities" end of the rake.
  (This is like your department adding records about their intuition that someone is interested in matriculating based on phone calls, event attendance, etc.)
  • Your are allowed to draw pretty pictures on the "Opportunities" tines before they attach them to the rake.
    (This is like your office recording the details of when they think the person would want to matriculate and other relevant information.)

You have a special rule for your children (working on the "Opportunities" tines): Although they're not allowed to add tines to the "Admissions" end of the rake, periodically they must look at the "Admissions" tines that have already been attached. If they see that there is an "Admissions" tine attached to the rake that is the same color as one of the "Opportunity" tines that they have attached, they must copy over all of the pretty pictures from that "Admissions" tine onto their matching "Opportunity" tine.
(This matches our business rule that SalesForce must push all data from "Admissions" objects to their matching "Opportunity" objects so that our recruiters can see everything they need to see on "Opportunity" objects.)

 

That's the kind of environment we're developing in.

There's just one more catch:
Our Central Admissions Office is allowed to add multiple tines of a given color, whereas our Departmental Recruiters must make every tine at their end a unique color. (The color of a tine is analogous to "Program Of Interest.")

The ERP requires that every Person-Program-Term combination be given its own record. Our recruiters don't care about that level of detail - they just want to see the "most relevant" information for a given Person-Program combination.

 

So we have a cardinality reduction problem on our hands. This lets the "doodle-copying" code know which "blue tine" on the "Admissions" side of the rake is the "best" so that we can copy its doodles over to the "blue tine" on the "Opportunity" side of the rake. Otherwise, the "doodle-copying" code will get confused.

 

Here's how I solved it: (update here)

First, I created a formula field on Admissions called Record_Type_Prioritizer__c.

I should explain that: I've oversimplified our problem up above. We actually have 2 similar record types coming in from the ERP - "Recruit" and "Admissions." So Record_Type_Prioritizer__c has objects label themselves 9 (if Admissions), 8 (if Recruit w/ an "app started" flag), or 7 (plain-old recruit).

Next, I created a formula field on Admissions called Winner_Picker__c. (It's also on "Recruit.")

Here is how I described Winner_Picker__c to end users so they could review my logic:

WinnerPicker is a calculated numeric field. First, it disqualifies any "past" records or "withdrawn" by being blank for such records. Then it ranks Admissions records as best, Recruit records with an Application Started? flag as 2nd-best, and other Recruit records in 3rd place. Within each of those categories, it ranks records in descending order from soonest term (best) to futuremost term (worst). If there are still any ties, it breaks them "coin toss" style by ranking them in descending order according to their unique record IDs from Banner.

Here is the math behind this formula:

• Return null for any term that has already started or any Admissions record with an inactive status
• Admissions = 9,000,000; Recruit+Flag = 8,000,000, Recruit-flag = 7,000,000.
• Subtract term code (a 6-digit #; subtraction because we care about sooner terms more than later ones as long as they’re in the future).
• Multiply that 7-digit # by 1,000,000 to add a comfortable amount of zero-padding at the right.
• Add 900 or 800 or 700 (Adm / Recr+Flag / Recr) because the last 2 digits are unique within, but not across, sets of Admissions/Recruit records for a person.
• Add the 2 unique digits not already covered in the previous code from the Admissions/Recruit record's primary key in the ERP as a tie-breaker.

Finally, here is the source code of Admissions.Winner_Picker__c:
Note that Admissions's "Term" field is a Lookup Field to a "Term" object. "Term" records are full of fun formula fields themselves! They provide data about a given term in many formats: as a sortable number, as a comparison of its start date to today, etc.

IF(
Term_Lookup__r.Future__c ,


IF(
OR(
ISPICKVAL(Status__c, "Deferred"),
ISPICKVAL(Status__c, "Rejected"),
ISPICKVAL(Status__c, "Withdrawn")
)
,
null,
(((Record_Type_Prioritizer__c * 1000000) - Term_Lookup__r.Term_Code_Numeric__c)* 1000000)
+
((Record_Type_Prioritizer__c * 100) + VALUE(RIGHT( Banner_Application_ID__c , ( (LEN(Banner_Application_ID__c) - 6) - LEN(Formula_Contact_PIDM__c) ) )))
)


, null
)

 

 

The Result:

Each "Admission" record looks at its field values and suggests its "self-worth" as a number.

This "self-worth" number is guaranteed to be a unique value among all Admissions+Recruit records with the same Person+Program combination.

Thanks to this formula field, you simply have to perform an "IS NOT NULL" and a MAX() to retrieve the best ERP-imported record for a Program.
(Or, in other words, "The best blue tine on the rake.")

It saved us a lot of time during a Requirements Analysis meeting with our Partners doing trigger development work to say, "Don't worry about the cardinality reduction logic - we got it - just MAX() this field for the Person+Program."

 

Now if only you could use a Rollup Field to MAX() Formula Fields ... Don't forget to click here and upvote! :-)

Background: Our Recruiters Rely On Opportunity Objects

A bit of background on the code we're developing:

The records storing the details of a person's interest in matriculating to a higher education program form a many-to-one relationship with that person.

What does "many-to-one" mean? Here's an example:

I can be interested in matriculating to my undergraduate studies at Harvard, {get rejected or matriculate and hopefully graduate}, 5 years after that decide I want a master's degree in English there, {get rejected or matriculate and hopefully graduate}, and 10 years after THAT decide that what I would really like is a master's degree in French there.

"Records about my interest in Harvard" are "many-to-one" because there is only 1 of me, but there are 3 separate "interactions" I've had with Harvard about my interest in it.

To track these interactions, my peers who went to DreamForce tell me that Admisisons offices using SalesForce typically break down into two categories:

1. Those that use SalesForce's native "Opportunity" object to track "interest in matriculating."
   • FYI, using "Opportunity" as if it were in a many-to-one relationship with "Contact" involves populating a field on Opportunity called "Primary Contact Role" with the "Contact" record of the person interested in matriculating.  Apex Triggers can then grab that record and treat the relationship like a "lookup" relationship.
2. Those that create custom "master-detail" objects hanging off of "Contact" objects to track "interest in matriculating." Such objects are often called something like "Admission."

We're an "Opportunity" shop (model #1).

But Banner, our ERP, works on model #2 ("Admission" records).

And we're currently in a position where the rest of the school is maintaining those records in Banner on our behalf, rather than us doing everything in SalesForce the way most "Opportunity" shops would.

So we have "Admission" detail objects hanging off of "Contact" in SalesForce AND "Opportunity" objects acting as if they hung off of "Contact."

The "Admission" objects get populated every morning by a PLSQL dump to .CSV files and automated data loading of those .CSV files into SalesForce.  (We know, we know.  We're looking into JitterBit.)

Finally, we don't have the resources we need, in the timeline we need them before "go-live," to restructure the "Admission" object or its corresponding PLSQL code into something that our recruiters can easily work off of. (We would want more "collapsing" of Banner data to be done before import so that back-to-back applications to the same program update old records rather than creating new ones.)



We're an "Opportunity shop" because:

• "Opportunity" provides us a "blank slate" in this environment where "Admission" is set in stone.
• "Opportunity" also plays better with SalesForce plugins like the mass-mailing software Pardot.

We just need to figure out how to "collapse and copy" relevant data from "Admission" records into the "Opportunity" records that are equivalent to them.

Much of the code I'll cover in upcoming posts has revolved around our need to act like an "Opportunity shop" without ignoring the contents of data feeds that think we're an "Admissions object shop."

Stay tuned.

You're Going To Need A Lot Of Formula Fields

As far as I can tell, there are only two "native" ways to extract data from one part of SalesForce and make it visible in another part of SalesForce independently of INSERT/UPDATE/DELETE data-modification events:

• Formula fields (which perform data transformations on single values - think to_number, substring, etc.)
• Rollup fields (which perform aggregations like count/min/max on a single "column" across all or selected rows of a table - or, to rephrase in SalesForce words - which perform aggregations on a single "field" across all or selected records of an object type)

If all you need to do is display a sort of "calculated value" on some user's record-editing interface, technically you could stick it onto the page with code designed for displaying custom user interfaces.  But you have to bake your own security/permissions into such code (it makes an end-run around the rules you've set up), and as far as I know, you can't grab the value and insert it into yet more code, so I haven't looked into that option.

Roll-up fields are extremely limited - especially since you can't perform them on Formula Fields.  Which means no pre-processing values and then aggregating them in real-time (what the heck, SalesForce?!  Please log in and upvote this idea, everyone - in return, you get a million brownie points from me).

Formula fields have their limits, too - for example, the fact that they can only handle single values and have no aggregation functions mean that you can only look "up the leg" of a "crow's foot" between objects - you can't look "down to the toes."

(That said, formula fields don't care if the "up the leg" is up a "master-detail" many-to-one relationship or a "lookup" many-to-one relationship.  What's the difference?  In a master-detail relationship, the "detail" record can't exist without being associated to a "master" record.  In a "lookup" relationship, it can - unless you set the field as mandatory, but SalesForce doesn't consider that to turn it into a "master-detail" relationship.)

Still, overall, formula fields are pretty powerful.

And necessary, because it seems that SalesForce lacks a way to define criteria for automated DML based on abstract conditions that you can check for any old time you feel like it (the way Oracle Stored Procedures allow you to do).

It's my understanding that SalesForce can ONLY execute code through triggers (which, like in Oracle, fire in reaction to specific data changes - NOT to invocation by a human or other scheduled code).  You can't just store a "method" and invoke it at will.**

To avoid trigger-ing yourself into loops you lost track of at line 1874, I've found that it's best to keep as much "code" as you can in formula fields.  They might even be responsible for getting data from where a trigger put it to where the next trigger is going to pick it up from.


Coming next:

• My presentation of a "rate yourself on a scale from 1 to 10 trillion" formula field I made to help evaluate "ranking" for "Admissions Record" objects.  This allows you to "MAX()" records based on textual data that is "better" or "worse" than other textual data - for example, ranking an "admitted" record higher than a "just applied" record.  (Which the lack of Rollup Fields for Calculated Objects makes less useful - don't forget to upvote! - but at least you have LESS code to put into a trigger.)
• An idea I'm still working through in my spare time that involves using triggers, then formula fields to make sure that no matter where data is changed (in Admissions Records or in Opportunities, which need to be kept in sync - but I don't really care which side changed to trigger the "sync"), it always involves an INSERT/UPDATE to Opportunity, followed by a trigger on Opportunity.

**Update 7/13/16: It turns out you can store procedures and invoke them "at will." To store the procedure, you simply write a "public" method in a "public" class, write a "test class" & "test method" to make sure you have adequate "code test coverage," and put your code into production. To actually invoke your method, you can write more code (including VisualForce) to set up a "button" that invokes it, you can call it directly from the "Execute Anonymous" area of the "Developer Console" if you're a sysadmin, or you can write more code to set up a "scheduled" job that invokes it.