Pages

Showing posts with label soql queries. Show all posts
Showing posts with label soql queries. Show all posts

Wednesday, June 13, 2018

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.

Tuesday, April 25, 2017

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

Wednesday, November 2, 2016

Visualforce vs. Lightning Components Side-By-Side 1

In my last post, I proposed converting an existing Visualforce page to use Lightning Components. I decided instead to start with a few simple "build the same thing in both environments and compare them" exercises.

Visualforce & Lightning Components are different coding environments for writing web applications, hosted in the user interface of a Salesforce database, that interact with data stored in that database.

Here is the first such exercise.


Apex Controller "BlogExample1ContactController.apxc" - used by both files:

public class BlogExample1ContactController {
    @AuraEnabled
    public static List<Contact> getCsFromServer() {
        // Do various proper security stuff and then...
        return [SELECT LastName, FirstName, Id, Email FROM Contact WHERE LastName LIKE 'LastXYZZY-%']; // <<-- DO NOT actually just do this.  Need proper security stuff.
    }
}

This server-side code, when executed, returns a list of "Contact"-typed records from the database (filtered to expose only records where LastName starts with "LastXYZZY-," and exposing only data from the LastName, FirstName, Id, & Email columns of those records).

 


The VisualForce page only takes 1 more file of code to get data displaying at https://MyCustomDomainName.na##.visual.force.com/apex/BlogExample1VFPage.

Visualforce Page "BlogExample1VFPage.vfp" - used in VisualForce:

<apex:page docType="html-5.0" controller="BlogExample1ContactController">
    <p>Hi</p>
    <apex:repeat value="{!CsFromServer}" var="cntct">
        <hr/>
        <p><apex:outputField value="{!cntct.LastName}"/></p>
        <p><apex:outputField value="{!cntct.FirstName}"/></p>
        <hr/>
    </apex:repeat>
    <p>Bye</p>
</apex:page>

 

Here's what visiting the VisualForce page looks like:


The Lightning Components app requires 4 more files of code (1 "app" + 1 "component" and 2 more JavaScript files as its "controller" & "helper" files) to get data displaying at https://MyCustomDomainName-dev-ed.lightning.force.com/c/BlogExample1LCApp.app.

Lightning Components App "BlogExample1LCApp.app":

<aura:application >
    <c:BlogExample1LCBasicComponent />
</aura:application>

This app's only job is to exist (lines 1 & 3) - it gets its own URL so I can actually browse to it and see it.

Oh, and it has to say, "go see the Component called 'BlogExample1LCBasicComponent'" (line 2).

"c" is a built-in variable that means "the code-space that is 'all Lightning Components.'"

 

Lightning Components App "BlogExample1LCBasicComponent.cmp":

<aura:component controller="BlogExample1ContactController">
    <aura:handler name="init" value="{!this}" action="{!c.initializationCode}" />
    <aura:attribute name="cs" type="Contact[]"/>
    <p>Hi</p>
    <aura:iteration items="{!v.cs}" var="con">
        <hr/>
        <p><ui:outputText value="{!con.LastName}"/></p>
        <p><ui:outputText value="{!con.FirstName}"/></p>
        <hr/>
    </aura:iteration>
    <p>Bye</p>
</aura:component>

My Lightning Components example is so simple that it's just got 1 component.

If we'd wanted fancier HTML representing each record of the database that we're displaying, we could have moved the contents of that "aura:iteration" tagset to their own component and put a reference to that component inside the tagset instead. (Although we'd have to make sure, when defining it, to give it an "attribute" that can hold a "Contact"-typed record and, when referencing the component, to "pass" that attribute the current value of the iteration's "con" variable.)

Anyway, this component's definition indicates that it's "controlled" by our Apex controller, much as you see in our Visualforce code.

Overall, this component looks a lot like our Visualforce page. The main difference is that we have to explicitly tell it to execute JavaScript code ("initializationCode(...)"). (Note: "initializationCode(...)" itself also has to be told to actually talk to the server-side Apex controller and fetch data ... we'll see that later.)

If we don't do that, there'll be no data between "Hi" & "Bye," even if there's data in the database that matches the query in our Apex controller.

The Visualforce line '<apex:repeat value="{!CsFromServer}" var="cntct">' knows to go talk to the server to fill in data summoned by the code in the "value" tag. (It also has the server loop through that data and generate HTML/CSS.)

The Lightning Components line '<aura:iteration items="{!v.cs}" var="con">' doesn't. It just says, "if this component's 'cs' variable (which lives in a web-surfer's browser) has any data in it, have the browser loop through it and generate HTML/CSS."

Line 3 establishes that the component has a variable named "cs" and that its data type is a list of "Contacts."

Line 2 ('<aura:handler name="init" value="{!this}" ... />') actually executes JavaScript that will go & talk to the server and that is responsible for setting the value of "cs" (in this case, upon page load).

 

Lightning Components JavaScript file "BlogExample1LCBasicComponentController.js":

({
 initializationCode : function(component, event, helper) {
        helper.getCons(component);
 }
})

Every JavaScript function intended to be summoned directly from "component" markup should be in the associated "Controller" file and should be defined to expect 3 parameters: a component, an event, and a helper (in that order).

Every JavaScript function intended to be summoned from other JavaScript, rather than from the "component" markup itself, should be in the associated "Helper" file.

In this case, our "initializationCode" doesn't do any real work - it just executes a JavaScript function found in the "Helper" file and passes it the value of the "component" passed to it (which would be the component from which we summoned it).

 

Lightning Components App "BlogExample1LCBasicComponentHelper.js":

({
    getCons : function(component) {
        var action = component.get("c.getCsFromServer");
        action.setCallback(this, function(response) {
            var state = response.getState();
            if (component.isValid() && state === "SUCCESS") {
                component.set("v.cs", response.getReturnValue());
            }
        });
        $A.enqueueAction(action);
    }
})

This JavaScript function inspects the "component" it's been passed, notices that that "component" has an Apex "controller" class attached to it in its definition, and goes about talking to that code (calling its "getCsFromServer()" method).

If data actually comes back from that process, it assigns that data to the component's variable "cs."

("v," by the way, is included in the framework and is a variable that an instance of a component uses to refer to itself. It also appears in the component's code above.)

 

Here's what visiting the Lightning Components app looks like, when viewed on its own:

(Notice how "vanilla" it looks - what you code is what you get!)


Finally, I have a bunch of random ways of abbreviating the word "contact" or "contacts" scattered throughout the source code to make variable scope clear. Hope it helps, sorry if it confuses!

Friday, July 29, 2016

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

Friday, June 3, 2016

Database Normalization And Salesforce - Ponderings

One of the biggest challenges in moving from a highly structured Oracle environment into Salesforce is how denormalized Salesforce's querying / reporting / data-viewing limitations encourage one's data model to be.

This particularly seems to be an issue in higher education. Like banking or healthcare, most of its data is rigid, with the typical business user's number one concern being easily expressed as, "Please don't mess up my data." Overall, higher education's business needs beg to have a university's databases insanely normalized.

Except on the fringes, that is. On the fringes, a university's interactions with the outside world stop being about individual human beings who care about "their" data "not being messed up." Prime "fringe" examples include:

  1. Email-address-based marketing, which is free and great, but hard to pin down to an actual person because people share
  2. Continuing education and seminars, where it's not really a big deal to swap attendees - if it's corporate training, who at the sending company cares who comes as long as someone gets trained?

These "fringe of the university's business" use cases, plus staff's desire to see the two sets of data married, seem to tempt universities to look at databases beyond their 20-year-old ERPs. (Of course such "data marriages" also desire the tall order of doing so without polluting vital information like the admission-graduation pipeline, employment history and paychecks, donation history!)

  • Unfortunately, this marriage means you're back to squeezing "fuzzy" data into a normalized structure. Which, in theory, you could do in your ERP if it gave you an easy way to add tables (and user interfaces to those tables).
  • Then there's the route of struggling to squeeze highly-normalized "vital" data into a "flat" structure like Salesforce encourages. Unfortunately, this can mean you're always fighting inaccuracy, lag, duplication...

Integrating Salesforce and our ERP (plus a few new data stores) is a much longer road than I was expecting, and it all keeps coming down to issues with matching a data model to the real world.

Banner is an amazing set of highly normalized tables for "don't lose my data" core university functions.
(If you get a look at the EnrollmentRx plugin for Salesforce as a Banner user, you'll be amazed - it's basically SPAIDEN/SRARECR/SAAADMS/SOATEST built into a newer database and restricted to "1 Contact/SPAIDEN record per email address." Everyone re-invents the wheel for higher ed because, like banking, some things just don't change.)

I think Sungard/Ellucian really missed the boat here.

They could have raked in the big bucks if they'd "taken care of the fringes" and:

  1. Given Banner just a few tweaks for easily adding/removing tables (and corresponding user interfaces)
  2. dded a good way to upsert the database 1-record-at-a-time
    (e.g. web forms - as FormAssembly's Salesforce connector proves, you really just need to have a user interface that lets a form-handler-configuration-managing user build complex SQL queries sorted by LastModifiedDate descending - not everything has to be "common-matched" - similar idea for fast deduplication ... DemandTools, which is to PL/SQL what Cognos is to SQL, exists because a good CRUD API to the Salesforce database exists)
  3. Engineered a great "over"-layer for email tracking and sending - especially by admissions departments.

For example, what if there were some sort of API that let you quickly create/update/delete SPACMNT records from what's otherwise basically a Cognos report? Like, a little pencil-shaped "edit" button near the list of comments, but otherwise, nothing is editable - it's just the read-only report (doing nice things like hiding details of inactive records and only showing you the most-recent SRARECR/SAAADMS/SGASTDN data available, as determined by the report-author)? Or a little "edit" button next to "status" that lets you pick from a picklist, and then when you commit it, the "interactive report" author has programmed logic to update SRARECR/SAAADMS/SGASTDN accordingly? That's basically what one of our main departments wants. A "collapsed, current, relevant" view of dispersed back-end data, with a few "edit" buttons interspersed so they don't have to surf through the normalized tables themselves to make updates.

The other major reason we tried Salesforce was because it had plugins for the web/email era and Banner didn't. (And a final reason was to accommodate those "fuzzy"-work departments that were using spreadsheets, Access databases, Rolodexes, etc.)

I often wonder where we'd be if we'd been able to do all this development straight into our highly-normalized ERP instead of half-rebuilding Banner inside of Salesforce.

What would we have done if we could have web'd/emailed/custom-tabled/easy-writeback'ed our existing database + data model instead of replicating our tried-and-true data model inside of Salesforce? What could we have done if Ellucian had tacked on the user-friendly concepts that Salesforce made popular instead of leaving their customers in the 1990s?

Salesforce's good luck and Ellucian's loss, I suppose. But I wish I often wish I could have my cake and eat it too - full normalization support and modern-database flexibility.

Since I can't, I still believe that the ideal for universities is to roll out Salesforce "from Rolodex to ERP," not "from ERP to Rolodex."

Thursday, February 4, 2016

The Singleton Pattern And "Commonly Used Data"

If you hadn't noticed, Salesforce stored-procedure & trigger programming is basically using Java wrapped around crippled SQL. Which means it's a good idea to be up on your object-oriented design patterns.

One of those that I just used to avoid copying the same SOQL query into a 5th trigger-handler's source code is the Singleton pattern.
(I'll be going back and fixing the 4 older trigger-handlers. Yes, I let it get that bad before I had time to fix it.)


Quick refresher for newer programmers: in object-oriented programming, a "class" is a "cookie cutter" and an "object" is each individual cookie you cut. So, a "car cookie cutter" would say, "Make sure that every car has a number of wheels, a number of doors, a make, a model, a paint color, and a unique serial number. It also needs to be driveable." A "car object" would be the "the 4-wheel 4-door red Ford Taurus with serial #XYZABC12345" - these are the values stored in its "class-level" variables. Like all cars, it would be driveable (this is a "method" that's part of the definition of all "cars" and it comes with this particular car just because it's a car).

When you're defining Apex triggers, you typically don't actually do object-oriented programming with classes. Typically, you define & use "static" methods for the "car cookie cutter" and directly invoke the "drive()" method. Which is a little hard to imagine as a metaphor - driving the car factory instead of the car - so sorry about that. But just trust that, well, if it's not a method that actually needs a particular car to exist, such methods can be defined and used and that's probably what you're actually used to doing as an Apex trigger programmer.


But ... sometimes it IS useful to manufacture a particular car!

And, more specifically, if what you'd like to do is put a single car in a museum and call it "best car ever made" and have all of your code just copy its color and number of doors, you want a Singleton. The reason to do this "copy whatever's in the museum" pattern is that this keeps you from having to hard-code values like "silver" or "4-door." If you ever change your mind about these details, you just change the car in the museum.

Think of a "Singleton" class as the museum itself. It's not open to the public - all you can do is ask the guard, "What color is the car that's in there right now?" You don't even get to know whether they really have a car in there or not. You just know that the sign on the front door says you can ask the guard what color the car is, how many doors it has, etc.

(There's an extension on the "Singleton" programming pattern where, for efficiency, the museum doesn't even exist until the first visitor asks a tourist-info-kiosk for directions to it - at which point the city quickly scrambles to build a museum complete with "4-wheel 2-door silver Ford Taurus" inside before she gets there. Designing the museum this way is called "lazy instantiation." In my code below, I took this pattern a bit further for rare questions & named it "lazy data fetch" in my comments. Think of it like not bothering to paint the car until the first time someone asks the guard what color it is.)


So here's my code for accessing "commonly used info" like "the default Account ID for new Contacts," "the default Owner for records," "the Record Type ID of a given object & record-type-name," etc.

To call the "Singleton" code and ask it "the Record Type ID of a given object & record-type-name," I just write:

Id rtID = UtilityDefaultInfoOftenNeeded.getInstance().getRecordTypeId('Admissions', 'Opportunity');

The call to "getInstance()" asks for directions to the "useful settings" museum (at which point the city scrambles to build one, complete with answers to questions I can ask the guard, if it's not built yet) and gives my code directions to that museum.

For legibility if I'm asking the guard at the door lots of questions, I might instead write:

UtilityDefaultInfoOftenNeeded useful = UtilityDefaultInfoOftenNeeded.getInstance();
Id rtID = useful.getRecordTypeId('Admissions', 'Opportunity');
// (etc.)

Note that I don't say "new UtilityDefaultInfoOftenNeeded()." I made the "constructor" private on purpose to prevent that. Instead, I say "UtilityDefaultInfoOftenNeeded.getInstance()."

In fact, the inability to use "new ..." is really what makes it a "Singleton." You're not allowed to demand that a new "best car ever" or "default settings" museum be built. You can only ask for directions to it via a "public static" method like "getInstance()" and trust that one single museum will exist by the time "getInstance()" returns directions to the museum.


Here's how "UtilityDefaultInfoOftenNeeded" is written (as a "lazy-instantiation singleton"):

public class UtilityDefaultInfoOftenNeeded {
    
    // Please note that many values returned by the "getter" methods of this class could return null,
    // so be sure to check returned values for "== null" if that is important to your code calling these methods.
    
    private static UtilityDefaultInfoOftenNeeded instance = null;
    
    private Id defaultAccountId; // Fallback "Account" for new "Contact" records where not specified
    private Id defaultOwnerID; // Fallback record owner ID for records in the database
    private Id defaultLeadNurturerID; // The User ID of the "default" "Lead Nurturer" staff member

    private Map rtIDs = new Map(); // For holding data from the "Record Type" object


   
    // Private constructor - this is a Singleton class
    private UtilityDefaultInfoOftenNeeded() {
        // In this constructor, we do any computationally expensive or limit-worrisome computations
        // that should be done as soon as the object is instantiated (rather than when the data
        // is requested through a public object-level "getter" method).
        // Checking for "null" is not necessary because this is a constructor - all variables are null so far.
        // We will not populate every object-level variable in this constructor.
        // Some object-level variables' values are rarely needed and more expensive to compute, so we will "lazy data fetch"
        // them in their getter methods.
            if (Schema.getGlobalDescribe().keySet().contains('default_settings__c')) {
                // Set default Contact Account ID, Owner ID, and Lead Nurturer User ID
                // First, grab the "Default Settings" custom setting:
                Default_Settings__c cs = Default_Settings__c.getInstance();
                System.debug('Default Settings custom setting consists of:  ' + cs);
                // Next, initialize Default IDs from this setting:
                defaultAccountId = String.isBlank(cs.Account_ID__c) ? null : cs.Account_ID__c;
                defaultOwnerID = String.isBlank(cs.Owner_ID__c) ? null : cs.Owner_ID__c;
                defaultLeadNurturerID = String.isBlank(cs.Lead_Nurturer_User_ID__c) ? null : cs.Lead_Nurturer_User_ID__c;
            }
    }
    
    // There should be just 1 public method in this class that is STATIC:  "getInstance()."
    public static UtilityDefaultInfoOftenNeeded getInstance() {
        // Lazy instantiation
        if (instance == null) instance = new UtilityDefaultInfoOftenNeeded();
        return instance;
    }
    
    // These three methods may return null Id-typed values, so be sure to check the return value before using.
    // (Developer note - no need to "lazy-data-fetch" these values, as they "lazy-fetched" upon instantiation in the constructor.)
    public Id getDefaultAccountId() {return defaultAccountId;}
    public Id getDefaultOwnerID() {return defaultOwnerID;}
    public Id getDefaultLeadNurturerID() {return defaultLeadNurturerID;}
    
    // This method may return a null Id-typed value, so be sure to check the return value before using.
    public Id getRecordTypeId(String devName, String sObjName) {
        if (rtIDs.isEmpty()) {
            // Lazy data fetch of entire "RecordType" table into this object's "rtIDs" private variable
            for (RecordType rt : [SELECT Id, DeveloperName, SObjectType FROM RecordType]) {
                rtIDs.put((rt.DeveloperName + ';' + rt.SObjectType), rt.Id);
            }
        }
        // Grab the relevant ID and return it (or "null" if not found)
        Id idToReturn = null;
        if (rtIDs.containsKey(devName + ';' + sObjName)) {idToReturn = rtIDs.get(devName + ';' + sObjName);}
        return idToReturn;
    }
    
}

(If you see "string string" end tags at the end of this code, ignore them - my code formatter is inserting them.)


And here's its test class:

@isTest
private class UtilityDefaultInfoOftenNeededTest {

    static testMethod void testInfoOftenNeeded () {
        
        // Set up default custom settings (these are data in a table, so they don't exist in seeAllData=false test classes & need to be made in the test)
        DefaultTestDataAccountFactory.makeAndSetADefaultTestingAccount();
        DefaultTestDataOwnerFactory.makeAndSetADefaultTestingOwner();
        DefaultTestDataLeadNurturerFactory.makeAndSetADefaultTestingLeadNurturer();

        UtilityDefaultInfoOftenNeeded useful = UtilityDefaultInfoOftenNeeded.getInstance();
        
        Test.startTest();
        Test.stopTest();
        
        System.assertEquals(TRUE, useful.getDefaultAccountId() != null, 'getDefaultAccountId() is null.');
        System.assertEquals(TRUE, useful.getDefaultOwnerID() != null, 'getDefaultOwnerID() is null.');
        System.assertEquals(TRUE, useful.getDefaultLeadNurturerID() != null, 'getDefaultLeadNurturerID() is null.');
        System.assertEquals([SELECT Id, DeveloperName, SObjectType FROM RecordType WHERE DeveloperName = 'Admissions' AND SObjectType = 'Opportunity'].Id, useful.getRecordTypeId('Admissions', 'Opportunity'), 'getRecordTypeId(...) is null.');
    }
    
}

Monday, April 20, 2015

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

Tuesday, April 14, 2015

Link To A Primer On SOQL Queries For People Who Are Not Used To Database Query Writing

This won't be of interest if you're already an Oracle programmer / query writer, but if you know anyone who isn't, I think this introduction to SalesForce SOQL query writing is extremely easy to follow!

Thursday, April 9, 2015

Apex vs. Oracle: More Procedural Processing, Less Declarative Processing

One of the first things I read when learning Oracle PL/SQL was Donald Bales's quote, "It's almost always best to let SQL simply do its job! You can't imagine how much PL/SQL code I've seen that can be replaced by one SQL statement."

SalesForce programming, on the other hand, requires being extremely judicious with your use of SOQL queries against the database.

In SalesForce, unlike in Oracle, you have no idea what's indexed and what's not, and you don't get to ask the DBA or "EXPLAIN PLAN", so you have no idea what queries run quickly and what queries run inefficiently.

So SalesForce limits the number of queries you can run from Apex code.

"Bulkifying" Apex code, as I understand it so far, basically means "Don't put SELECT or INSERT/UPDATE/DELETE/UNDELETE inside any sort of loop."

  • For SELECT statements: Dump the entirety of a SELECT statement's results into an in-the-Apex-code collection-typed variable. Post-process that data imperatively with Apex code
    • To put it another way, if in Oracle you might define a Cursor and then Open, Loop, & Close it a few times throughout the course of your program because you know it won't really be a performance drag to do so and will keep the code easy to read...don't do that in SalesForce. Open it once, dump its contents into a PL/SQL variable, close the cursor, and never touch the cursor again - work with your PL/SQL variable's contents instead.
  • For INSERT/UPDATE/DELETE/UNDELETE operations: Imperatively build an in-the-Apex-code collection-typed variable. Perform just 1 DML operation on that collection-typed variable.

Tuesday, March 24, 2015

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.

Tuesday, March 10, 2015

SOQL Return Value Data Types When Used In Apex Code

Today in class we wrote an Apex trigger that resulted in my learning some interesting quirks about data types when embedding SOQL statements into code written in SalesForce's Apex language.

"TL; DR" summary: SOQL query results can be typecast into the object in their outermost FROM, or into a list thereof, unless the outermost SELECT involves an aggregation.

 

In class, we wrote code that looks like this:

...
for(Session_Speaker__c newItem : trigger.new {
Session_Speaker__c sessionSpeaker = [SELECT Session__r.Name, Speaker__r.First_Name__c, Speaker__r.Last_Name__C FROM Session_Speaker__c WHERE Id=:newItem.Id];
.../* Do stuff */ }
...

For anyone who can't already read this:

The above code loops through each "Session Speaker" record that has recently been inserted into the database (it's an 'after insert'-typed trigger). It uses a holding variable called "newItem" to store the entirety of the record currently being processed. Each time it executes the code inside the loop, it does an SOQL query against ALL rows of the entire database stored in the "Session Speaker" object (table) and sees if any of them have an Id with the same value as the "SessionSpeaker" record currently being examined by the for-loop. If it finds such a "Session Speaker" record, it stores a specialized copy of it in yet ANOTHER holding variable, this one called "sessionSpeaker."

I say "specialized copy" because it doesn't include all of the fields of "Session Speaker" and even throws in a few extras from an object that acts as a "master" to SessionSpeaker (the fields referred to with the MasterObjectName__r.FieldFromTheMasterRecord__c syntax).

SOQL is good at making such "specialized copies" of rows from SalesForce objects - a lot like formula fields are good at making "specialized" single fields. If you're a database programmer, I'm guessing you even try to put as much of your algorithm into the SOQL parts of your triggers as you can.

 

Anyway, both "holding" Apex variables, newitem and sessionSpeaker, have a data type of "Session Speaker object" or "Session_Speaker__c."

Handling the "data type" returned from the SOQL query, as you store it in Apex, is pretty simple.

  • When you perform a SOQL query whose outermost FROM is the "Session Speaker" object, you can store it in a "Session_Speaker__c"-typed Apex variable as long as it only returns one row.
  • If it returns more than one row, you have to store the SOQL query's output into one of Apex's typed collection classes, such as "List<Session_Speaker__c>," and iterate through or aggregate the list.

 

Only not quite.

Today, Andy Boettcher taught me that if the outermost part of your SOQL query includes an aggregation in the SELECT, the query's return value's cannot be typecast into a List<YourObjectTypeHere__c>-typed or YourObjectTypeHere__c-typed Apex variable.

SOQL queries with aggregations in the outermost query insist to any Apex code waiting to capture them that they are List<AggregateResult>-typed. Not even something like AggregateResult if there's just one row in the result. Just List<AggregateResult>.

 

Here's an example of an SOQL query with an aggregation in the outermost SELECT:

[SELECT ContactId, count_distinct(OpportunityId) FROM OpportunityContactRole WHERE IsPrimary=true AND ContactID=:c.id AND OpportunityId in
(SELECT Id FROM Opportunity WHERE RecordTypeId='087C0000000KIVR')
GROUP BY ContactId]

(I'm planning to use it to drive an "is working with the admissions department?" formula field on Contact that can be exposed to other departments.)

 

List<AggregateResult> variables require for loops and processing with a .get() method. You can't just "dot-notate" the field you want to retrieve like you can with a "Session_Speaker__c"-typed variable. Sample code coming soon.