Universities: To Break Down Silos, Implement SalesForce Starting With Your Smallest, Simplest Use Case - Not Your Highest-Revenue Use Case

If there's one thing I'm passionate about after 9 months in SalesForce, it's the idea that when implementing SalesForce at a university or in a university department, it's best to start small and "natively like SalesForce."

Don't start with your "sales" departments that already have highly established databases and processes, like the Admissions department. Despite SalesForce's name having "sales" in it.

---

1st, start with a dean + dean's secretary who want to digitize their Rolodex. Observe their struggles and refine your user training manuals to take them into account. Write all the Reports they need.

2nd, move on to an associate dean who wants to digitize their Rolodex. Train them in with your training manuals. Write all the Reports they need.

Observe the conflicts that come up from suddenly being able to "step on each other's toes."

Refine the data model (and report organization model) if you need to, so that people can tell what data is "theirs." Think about whether that data model will be scalable to 20 or 40 different "clumps" of users who want to mark records as "records they are working with.

Make some governance ("don't edit this field unless...") manuals and pass them around to all new users. Update your training manuals (including adding sections on "how to tell whether you are working with someone," "how to tell who else is working with someone," etc. and pass them around to all new users; make sure they read the new parts.

3rd, add another user with a Rolodex.

Again, observe and improve your reports, data model, governance manuals, & training manuals - and re-train all existing users.

---

Now, 4th, you're ready to try taking a group of 1-4 staff with a very simple "events" model and adding them to SalesForce (perhaps using the "Campaign" object). Teach them not only what you taught the other groups, but also to set up an event (new Campaign), say that a Contact is coming to an event (add a Campaign Membership), and update whether they came to the event (update a CampaignMembership's status field). Write all the Reports they need.

Again, observe and improve your data model, governance manuals, & training manuals.

Now try letting this 4th group - the one with events - send out a mass email about to everyone that "they are working with" (or some subset thereof). Note how you show them to make sure they ONLY send it to people that THEY are working with. WRITE THIS DOWN! This is the foundation of your "how to send mass mailings with SalesForce" training manual that you're going to write.

Use the "mass mailing" training manual you just wrote to train someone else from the 4th group if there is one. See if the training manual teaches them everything you wanted them to know. Refine and publish & make sure everyone from this group is using it (plus the governance & other training manuals). And write any Reports they need in light of now starting to send mass mailings.

5th, add another group of 1-4 staff with a very simple "events" model (as simple as the 4th group).

Observe the conflicts that come up from suddenly being able to "step on each other's toes" with respect to events management.

Refine the data model if you need to, so that people can tell what data is "theirs." Think about whether that data model will be scalable to 20 or 40 different "clumps" of users who want to mark records as "records they are working with.

Again, update your reports & training & governance manuals until all 5 groups (including 2 groups with "events") are working seamlessly in the same SalesForce instance ("org").

Repeat one more time w/ a 6th group - again, a small group with a very simple "events" model.

---

If you've gotten this far, and you've developed a successful way to document and project-manage these 6 rollouts, updates of training end users, etc. CONGRATULATIONS! You are NOW on track to start thinking about how to move past "Rolodex & simple events" departments.

---

But you're not all the way to a big, organized department like Admissions yet!

7th, try doing a department that already had its "Rolodex" in a relational database with "Person," "Company," and "Event" tables that are structured exactly the way SalesForce's "Contact," "Account," and "Campaign" tables are structured and doesn't have any additional tables beyond that. (That is, 1 person can work at 1 company at any given time. A company can have lots of people working at it. People, not companies, attend events. A person can attend lots of events over time. An event can have lots of people attending it.) It's okay if they have more fields on their "Person" & "Company" tables than your average business card would have previously made you add to Contact/Account/Campaign. This is going to be Round 1 of seeing what it's like to move people with an established database into SalesForce - people who are used to seeing and filtering by a higher level of detail!

Get them up and running with the appropriate modifications to data model, training/governance manuals, Reports, etc. and back-updating all other users of the system. Migrate their data from their old database and have them cut over to using SalesForce instead. Keep working with them until they're happy and there isn't anything they used to be able to do but now can't. DO NOT MOVE ON until they're 100% as productive as they were in their old database! And remember that they, in particular, are not used to having to "share" edit-permissions on their People/Companies with other sub-departments, so expect a LOT of governance updates with all existing users. It's going to be a while until they're happy. Convince your superiors to be okay with that - that they will waste less time doing "measuring twice and cutting once" than moving on until all 7 departments (especially this 7th) are using the same database in perfect harmony.

8th - do Step 7 again. Only now you have two "semi-mature" subdepartments in the same database. How do you need to change your data model to tell whose "extra fields" on Contact/Account are whose? Change all your reports/governance/training manuals and fill everybody in again.

Don't move on until every single current user (including this "8th" sub-department) is happy, no matter how slow your progress feels.

Bringing something like your Admissions department into SalesForce - especially if you need to keep your data synced to a central student/employee database - is going to be like building a house (or an addition to a house). You are working your way up from "building a birdhouse" through "building a shed" through "building a stand-alone garage" to "building an addition onto a house." The project management habits, documentation, etc. that you are gaining along the way are going to be vital when it comes time to build that home-addition.

Yes, you are spending a lot of money on SalesForce for very little revenue increase, if any, but a failed implementation for a high-revenue department isn't going to be any more profitable, either.

If the reason your university wants SalesForce is to get everybody having "intelligence" on everybody else's activities so that you can "work smarter across silos," you need to start small and work your way up - and let rollouts be done when they're done, not when you thought they'd be done.

---

If everything is chugging along nicely after you have 3 "Rolodex" subdepartments," 3 "Events" subdepartments, & 2 "Cut Over From Their Own Simple Database" subdepartments all playing nicely with each other, PAT YOURSELF ON THE BACK!

Ask people how they and the university as a whole have become more productive since moving from siloed data stores to a shared data store. Take notes. (If they haven't, this might be a good time to get everybody back to their old systems and pull the plug on SalesForce. It's expensive to keep going. At least you only have to tear down a birdhouse and a shed, though.)

If net productivity is up, ask them what they had to sacrifice in terms of productivity to get these overall productivity gains. Take notes.

Share your notes with decision-makers and decide whether it's better for the university to build more birdhouses/sheds (other similar departments), which you can do quickly, or to keep working upward toward building that house (more likely home-addition). It just might be the case that SalesForce is the system of your "small departments," whereas big organized departments keep using their status-quo systems and people call/email them to find out what's going on in those systems. You're still ahead of where you used to be in the cross-silo visibility arena, and perhaps getting every last birdhouse/shed-size subdepartment into the same SalesForce org is what would bring your university the most benefit.

---

If your university decides to go for "building the house," remember that you still haven't even built a garage yet.

You haven't dealt with departments with quirky event structures or departments with lots of events.

You haven't dealt with departments that sell people things.
Tip: Don't start with the "admissions" department. Yes, it sells educations, but "degree" is a weird product that a customer has to inquire about, then prove they're good enough to buy, then finally buy. That's a complicated sales model. Building a "garage" is integrating a sales department that sells things anyone can buy as soon as they express interest (such as a department that offers fee-based seminars).

And if you can find more than 1 "garage"-level subdepartment to implement at your university to work with, all the better. I always advocate for seeing how similar departments step on each other's toes and resolving issues that arise before moving up to the next level of complexity.

---

When you finally do decide to integrate a department with strong ties to an existing central database, you are going to be so experienced at bringing users more complicated than the last into your org without messing up existing users!

Pat yourself on the back. If your goal is to break down database silos, you will be much farther along than if you had started by setting up a complex system for a complex department, only to find out that adding anybody else to the SalesForce org brings your whole house of cards tumbling down.

---

In my 9 months, the most impressive/"creative"/revenue-increasing higher education projects with SalesForce I've seen involve getting Rolodex/Spreadsheet systems into a proper relational database for the first time ever - NOT reinventing the wheel for super-complicated departments that are already "getting by."

For example, Algonquin's Saudi Arabia "Student Information System" isn't an integration with their main Student Information System. According to Q&A during their presentation at the SalesForce Higher Education Summit 2015, it was the first digitization of a homegrown SIS that previously lived in a bunch of spreadsheets. I'd actually call that a "garage"-level project, but they have some really skilled programmers in their IT department.

And yes, St. Norbert's is probably the standard-setter for the "Admissions-office-first" model, so if you're on that train, call them. But it's not a train ticket I recommend buying if you're just now considering SalesForce.

It seems to me that for schools with an eventual goal of integrating lots of tiny Rolodex/Excel/Access-based sub-departments into the same database, the "start small & work up" approach is best.

If you're a university that has followed this model, please contact me - I'd love to talk further! Also, you should totally present at SalesForce Higher Ed conferences - hope to watch you on YouTube one day!

Really, 2015? Women At Technology Meetings Don't Code?

*sigh* Because it's not like women code or anything...

This is like the time I saw conflicting lunch meetings of the ASME & the SWE (mechanical engineers & women engineers) on campus where I work - in fact, I think the job recruiters were coming to the ASME meeting, and at the same time, it was the first SWE event of the year (or its wrap-up). Yet no one bothered to reschedule one meeting or the other.

And now DreamForce, of all places. A tech conference where the CEO of the sponsoring company is doing a keynote about women's "career advancement."

Yet women in a highly-paid but highly-sexist-and-gender-gapped career (programming) have to miss what the male presenters are saying about advancing their careers to go to the 1 & only keynote in their career track?

*sigh* Did no one think this through?

I've seen other sessions rescheduled - I think a reschedule is seriously in order for this one, DreamForce. (But not so other "underrepresented dual identities" conflict so insultingly instead!)

Dormant Blog, Sorry!

The more I get used to SalesForce, the less I have to gripe...I mean write...about.

Plus, I haven't been in the thick of algorithm design and programming for a few months.

So...thanks to everyone who started reading my blog, and sorry it's been dead lately...I will be back if I get confused and ranty again!

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

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!

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.

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.

Away Message

I'm still here! Just not doing any fun programming this week. Yay, data cleanup.

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

)

My First Working Apex Test Class

Finally - after much-needed help & Google - a working Apex test class surrounding the working SOQL query I made weeks ago.

It's not a full test suite - it doesn't deal with updates where an Opportunity record type gets changed or an Opportunity Primary Contact Role gets changed - but it's a start. It definitely compares a value against the results of my SOQL query.

My SOQL query counts the # of "Admission"-typed Opportunities hanging off of a Contact.

The purpose is to enable us to use a Formula Field to let all SalesForce users know that Admissions is working with a Contact. Then Admissions doesn't have to manually click buttons saying so. They just have to do their day-to-day work.

@isTest private class TestCountAdmOppsForContact {

static testMethod void test() {

Account a = new account(Name='Fake Account');

insert a;

Contact c = new Contact(AccountId=a.id,LastName='Daikoku');

insert c;

Opportunity o1 = new Opportunity(AccountId=a.id,RecordTypeId='087C0000000KIVR',Name='Graduate Admissions 1',CloseDate=System.today(),StageName='Lead');

insert o1;

OpportunityContactRole ocr1 = new OpportunityContactRole(opportunityid=o1.id,contactid=c.id,IsPrimary=true);

insert ocr1;

Test.startTest();

delete o1;

Test.stopTest();

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

Integer aoppcnt;

if(expectedResult.size() > 0) {

aoppcnt = (Integer)expectedResult[0].get('expr0');

} else {

aoppcnt = 0;

}

system.assertEquals(aoppcnt,c.Adm_Opp_Count__c,'Adm Opportunity Count did not populate correctly');

}

}

 

Next I'll work on writing an Apex Class with a method that populates Contact.Adm_Opp_Count__c with the value in my SOQL query (or null if it returns empty/0).

Then, I'll write an AFTER trigger for INSERT/UPDATE/DELETE on Opportunity to fire my method above.

Finally, I'll loop back around to beefing up my test class.

 

Oh...and then I guess I have to figure out this whole deployment thing if it works. But that's the champagne pop.

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.

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.

I Love Code!

❤ ❤ ❤

I just went to a day-long training where I programmed in 8 SalesForce systems I had little to no experience in.

Everybody talked really fast like the entire audience had been through Computer Science 101.

This was my favorite SalesForce day ever. Including the day SalesForce fed me multiple swanky meals outside where it was 70 degrees in the winter.

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.

Welcome To "Oracle 2 SalesForce!"

Welcome!  My name is Katie, and I began my journey as a SalesForce system administrator in November 2014.

I work in a large academic department of a mid-size university.  We were the first department to purchase a SalesForce org, although the school at large has now purchased one as well.

Before taking my current position, I worked at the same university managing data entry into, and Cognos Report Studio reporting out of, our Entrprise Resource Planning (ERP) system, Banner, on top of an Oracle back-end.

Although I have not written PL/SQL to control DML (INSERT, UPDATE, DELETE) professionally, I am familiar with it through graduate school and personal experimentation.  In other words, I'm just dangerous enough to think I can talk about my blunders through enterprise-grade database systems without having the chops to really impress people who come here for the word "Oracle."  Hope you enjoy your visit anyway!

The university's ERP is still a major source of information in our SalesForce org, but not the only one.  As of the founding of this blog in March 2015, we have some interesting constraints on our work (such as working around a fixed-structure daily import until we have JitterBit to replace the PL/SQL behind it, and such as the rest of the university still doing primary data entry into the ERP).

I have found it quite a paradigm shift to design solutions to problems integrating our ERP data into our SalesForce org for the Admissions department--one I'm still "shifting" through every day.

SalesForce Success Communities, YouTube videos, and StackExchange boards have been invaluable, but sometimes I feel a bit lonely as an "Oracle person in a SalesForce world."

So...if you do too, welcome!  As I come up with "SalesForce-y" solutions to problems I used to know how to solve in Oracle, I plan to share them here.  May you find them useful in your quest to master your new world as well.  :-)

-Katie