Python & Oracle SQL Hash + Regexp Replace Equivalents -- random note to self

Oracle SQL, for pulling from ERP:

standard_hash(regexp_replace(COLUMNNAMEGOESHERE, '\s', ''), 'SHA1')

Python, for cleaning up old values already loaded from ERP into Salesforce:

[hashlib.sha1(str(val).encode('utf-8')).hexdigest().upper() for val in (PANDASDATAFRAMEVARIABLENAMEGOESHERE['COLUMNNAMEGOESHERE'].str.replace(r'\s', ''))]

Note to self, lest I forget. This strips the whitespace (including line breaks) out of text, then condenses it into a 40-character hash of itself. Used for turning a big chunk of text that I expect to be unique in a table into a shorter Salesforce "unique external ID" text field when the table containing the text doesn't have a standard "primary key."

REST APIs and Salesforce - A Primer

A non-programmer colleague was getting advice to integrate an external database with Salesforce by building a "REST API" inside of Salesforce, and I chimed in because for a long time, 3 different ways of referring to "REST APIs" and "Salesforce" confused me.
 

Here's a breakdown of them that I hope will facilitate discussions with developers and help you learn the right kind of programming for your needs, if you're teaching yourself:
 

1. When people mention "building REST APIs inside of Salesforce," they mean using the "Apex" language to program a specific Salesforce database ("org") to ACCEPT requests FROM THE OUTSIDE WORLD to engage in a computer-to-computer data-transferring conversation over the web.
   
   The data can potentially flow both ways (more in a second), but only via the outside world knocking on your Salesforce org's door and saying, "Hey, I've got another communications request!" in the exact manner that you've said you want your Salesforce org to be communicated with.
   
   That's what "writing an API" means -- it means using code to define how a computer is willing to have its door knocked on and be talked to.
   
   When you "build a REST API" inside of Salesforce, you're building a door in your fortress and posting a guard by it who's cautious about how she interacts with strangers on the other side.
   • You might program your Salesforce org to know what to do with data from the outside world, but only when delivered correctly (e.g. "If you have something for the fortress, bring it in a series of pink 12x12x12 boxes, only paint the first one green and the last one purple, and put your name and return address on all of them -- if we like them, we'll figure out what to do with the stuff inside the boxes from there, and after we're done, we'll post a receipt on the door telling you whether we handled them neatly or burned them").
   • Or you might program your Salesforce org to hand out data from inside the fortress to the outside world, if asked just right ("Tap out 'shave-and-a-haircut-two-bits,' then the morse code for your name and return address, and a description of the data you want, and we'll FedEx a list to you").
   Or you might set up communications protocols for each, to really facilitate 2-way conversation--yet note that every piece of that conversation is initiated by some "outsider" piece of software knocking on your fortress's door in a specific way, no matter which way the "data you care about" is flowing.
    
    
2. There's ALSO a general-purpose API, built into every Salesforce database ("org") in existence, that uses the "REST" communications protocol. Salesforce defines and maintains how it works. It's a door that Salesforce the corporation cut into your fortress and posted a guard at.
   
   This "API" is what the authors of "ETL" tools like Apex Data Loader & dataloader.io & Jitterbit & Zapier study carefully before writing such tools.
   
   Technically, you can write your own tool taking advantage of this protocol, but most people don't bother, since other companies have done such great work.
   
   Also, if you do option #1, you can sometimes INSERT more data in fewer "API calls" than by using tools that leverage the general-purpose Salesforce "REST" data API.
   
   Both option #1 & option #2 give you the chance to write code inside your Salesforce database that post-processes incoming data according to your business's needs -- although in the case of approach #1, you might bundle that logic into the back end of the API for efficiency, whereas in the case of approach #2, you have to use "triggers" and such.
    
    
3. Finally, you can write Apex code within your Salesforce org that goes knocking on the doors of OTHER fortresses.
   
   Doing so might involve writing Apex that uses the "REST" protocol and someone else's "API," but it's not at all the same thing as the 1st concept we discussed.
   
   When learning how to do this, you'll often hear terms like "writing asynchronous Apex" and "making HTTP requests with Apex."

---

Thoughts:

Most admins' approaches to connecting an outside database land in the realm of approach #2, using someone else's "ETL" tool -- maybe with some "developer" help to write "triggers" to post-process incoming data according to your business needs. It's the easiest approach by far.

Some "apps" and "packages" and "plugins" might have done #1 as part of the software package they deliver to you (that is, they might cut a hole in your fortress and post a guard).

Personally, I've barely dabbled in "approach #2, roll-your-own-ETL-tool," and I've only been to classes and read books on approaches #1 & #3.

Watching from the outside, it seems to me that "approach #1" & "approach #2, roll-your-own-ETL-tool" achieve pretty similar business-need ends and require similar levels of developer skill.

• It's just a question of whether you prefer to be constrained to programming in Salesforce's language, yet have more control over Salesforce database performance (approach #1),
• Or whether you prefer to be a little less efficient, but have more control over the language in which you program ("approach #2, roll-your-own-ETL-tool").

(Although even in the case of approach #1, you'll probably be writing some sort of "ETL" software in your favorite language that does the actual knocking on your Salesforce database's ("org's") door -- it'll just probably be a lot simpler to write than whatever you would've written that relies solely upon Salesforce's standard, built-in API.)

---

Finally, a brief analogy for any Oracle developers following along:

REST-based data transmission : data flow in & out of Salesforce databases :: ODBC/JDBC-based data transmission : data flow in & out of traditional relational databases

Specifically, the built-in "approach #2" to using REST might be most comparable -- not sure how many database management systems let you write your own APIs into them (#1) or make callouts to internet services from them (#3).

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

Alphabetize CSV Column Headers With Python

Today I need to dump a 200-column CSV out of a "central university" Salesforce org, give it to a business user, and ask her which columns she needs me to set up a "landing spot" for in our "subschool" org.

The least I can do is have it in alphabetical order for her.

Here's a Python script that can do just that (plus move ID to the front), after dataloader.io exported the columns in a seemingly random order.

If you're doing inter-data-store integration and you're not using Python (especially the "CSV" & "Pandas" modules) to help you with the CSV files, try it. You'll like it.

import pandas
import csv

inpath = 'C:\\WHEREVER_YOU_PUT_YOUR_FILE\\'
infilename = 'WHATEVER_YOU_NAMED_YOUR_FILE.csv'
infilepath = inpath + infilename
outpath = 'C:\\WHEREVER_YOU_WANT_YOUR_NEW_FILE\\'
outfilename = 'NAME_FOR_YOUR_NEW_FILE.csv'
outfilepath = outpath + outfilename

# CSV to Pandas:
df = pandas.read_csv(infilepath, encoding='latin-1',  header=0)

# Make a list of alphabetized column headers (with ID at the front):
sortedColumnList = sorted(df.columns)
sortedColumnList.insert(0, sortedColumnList.pop(sortedColumnList.index('Id')))

# Make a new Pandas object arranged alphabetically:
df2 = df.reindex_axis(sortedColumnList, axis=1)

# Write the new Pandas object to disk:
df2.to_csv(outfilepath, index=False, quoteing=csv.QUOTE_ALL)

You can replace the last line of code with this code if you want to remove columns where all of the rows are empty, anyway:

# Drops blank columns.  (Nothing to do with alphabetization.)
df3 = df2.dropna(axis=1, how='all')
# Write the new Pandas object to disk
df3.to_csv(outfilepath, index=False, quoteing=csv.QUOTE_ALL)

Actually, I think you can just add ".dropna(axis=1, how='all')" to the end of the "reindex_axis()" call and still print df2 - and actually, you can also just set df to equal itself with something done to it and print df (e.g. "df = df.reindex_axis().dropna()"), but I decided to favor legibility over style.

To execute this, you can just paste it into a plain-text file you call alphabetizecolumnheaders.py, install WinPython on your computer, bring up your ".py" file in the "Spyder" python IDE, and click "Run."

Credit to StackExchange here & here & here.

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!

My First VisualForce: A Table To Simulate A List View

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

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

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

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

Only SalesForce wouldn't let us.

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

So here's the code

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

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

The VisualForce Page

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

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

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

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

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

My awful test class

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

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

VisualForce!

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

Update: Posted here

6 Ways To Code Field Value Changes / DML In SalesForce

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

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

 

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

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

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

 

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

 

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

 

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

 

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

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

 

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

An Update On The "Winner Picker" Formula Field

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

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

 

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

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

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

 

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

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

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

 

The updated code is:

IF(

Term_Lookup__r.Future__c ,
IF(

OR(

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

)
, null
,
(

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

)

) , null

)

Buggy Booleans

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

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

 

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

 

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

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

 

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

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

 

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

 

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

Rolling Up A Single Record To Its Master Object

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

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

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

 

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

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

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

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

 

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

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

 

However:

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

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

 

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

 

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

 

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

A Formula Field To Pick The Best Custom Object Among Many

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

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

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

 

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

 

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

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

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

 

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

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

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

 

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

 

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

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

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

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

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

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

Here is the math behind this formula:

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

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

IF(
Term_Lookup__r.Future__c ,


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


, null
)

 

 

The Result:

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

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

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

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

 

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

Background: Our Recruiters Rely On Opportunity Objects

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

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

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

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

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

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

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

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

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

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

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

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

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



We're an "Opportunity shop" because:

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

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

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

Stay tuned.

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