Python for CSV Files - Deduplicating "Some States, But Not Others"

(Version française en dessous)

I encountered a lovely "Excel to Python" business problem this morning. Many thanks to my colleague for explaining it to me, and apologies for snooping.

He had a spreadsheet containing 20,000 American high schools (column B), what state they were in (column C), and which admissions counselor was assigned to the school (column A).

His university assigns individual admissions counselors to schools within the states of Delaware (DE) and Maryland (MD), but elsewhere, each counselor covers an entire state. Nevertheless, this particular spreadsheet had every single high school listed out, with its state and counselor, no matter where it was located.

What my colleague wanted to do was reduce the size of the spreadsheet so that Delaware and Maryland schools would be listed individually, but so that the rest of the spreadsheet would simply contain a list of the remaining 48 U.S. states and the counselors assigned to them.

The input CSV file looked like this:

Counselor,School,State
Ana,Dover HS,DE
Leia,Anapolis HS,MD
Ana,Baltimore HS,MD
Leia,Best Prep,DE
Mo,Denver HS,CO
Ana,Howard HS,DE
Mo,Boulder HS,CO
Mo,Boise HS,ID
Leia,Fairfax HS,VA
Mo,Excel Academy,ID
Mo,Mann HS,ID
Leia,Roanoke HS,VA

He wanted the output CSV file to look like this:

Counselor,School,State
Ana,Dover HS,DE
Leia,Anapolis HS,MD
Ana,Baltimore HS,MD
Leia,Best Prep,DE
Ana,Howard HS,DE
Mo,STATE-CO,CO
Mo,STATE-ID,ID
Leia,STATE-VA,VA

Because we know exactly which states are treated "one way" versus "another way," this is a very straightforward "split the dataset horizontally, do something to one part of the split, and concatenate it back together" operation.

In fact, we don't even really need Python for this. In Excel, in 2 minutes, we could:

1. Filter the spreadsheet to "everything but DE & MD"
2. Extract those rows to a new tab of the spreadsheet
3. Do "Remove Duplicates" on column C
   • (or, if we wanted to dummy-check for accidental territory splits in the "other 48" states, on columns A & C)
4. Alter the contents of column B to be a concatenation of the word "STATE-" and the contents of column C
5. Un-filter our original (now shorter) worksheet
6. Cut-paste our "temporary" worksheet back onto the end of it

But if we did want to use Python, the script would be pretty straightforward. It would imitate those same steps in Excel:

import pandas
df = pandas.read_csv('c:\\example\\HighSchools.csv', dtype=object)
dfdemd = df[df['State'].isin(['DE','MD'])]
dfoth48 = df[~df['State'].isin(['DE','MD'])]
dfoth48 = dfoth48.drop_duplicates(subset=['State'], keep='first')
dfoth48['School'] = 'STATE-'+dfoth48['State']
df = pandas.concat([dfdemd,dfoth48])
df.to_csv('c:\\example\\FilteredHighSchools.csv', index=0, quoting=1)

Note that if this were an Excel XLSX file, we would use read_excel and to_excel instead (eliminating the ", quoting=1" option).

---

Where the problem gets really interesting is that we can let the computer tell us which states have "just 1 counselor for the whole state" and which states "should be listed school-by-school, because they seem to be split."

In Excel, we would:

1. Add a "column D" counting the number of unique "column A" values per value in "column C."
   • (There are various tips and tricks available on the internet to accomplish this in Excel.)
2. Then we would do the same "split, alter part of the data, and re-concatenate" trick we did earlier, only we would "split" based on "column D" instead of based on "column C" (looking for "1" vs. "2 & up").

Again, in Python, we'll imitate our steps in Excel:

import pandas
df = pandas.read_csv('c:\\example\\HighSchools.csv', dtype=object)
df['Counts'] = df['State'].map(df[['Counselor','State']].drop_duplicates(keep='first')['State'].value_counts())
df_mult_couns = df[df['Counts'] > 1]
df_1_couns = df[df['Counts'] == 1]
df_1_couns = df_1_couns.drop_duplicates(subset=['State'], keep='first')
df_1_couns['School'] = 'STATE-'+df_1_couns['State']
df = pandas.concat([df_mult_couns,df_1_couns])
df = df.drop(['Counts'], axis=1)
df.to_csv('c:\\example\\SmartFilteredHighSchools.csv', index=0, quoting=1)

This script is a lot like the previous one, only I renamed "dfdemd" to "df_mult_couns" because we are presuming that we don't yet know it's Delaware and Maryland that have multiple counselors per state. Similarly, I renamed "dfoth48" to "df_1_couns."

The new lines of code are the 3rd line and the 2nd-to-last line. Also, the 4th & 5th lines have been altered to filter on "Counts" instead of filtering on "State." Still, once we have split our data, we no longer need the "Counts" column (we could have "dropped" it from each fragment of our dataset here instead of from the re-concatenated dataset right before exporting to CSV), and we do our "dropping of duplicates" in the "1-counselor-per-state" states as usual, by "State."

Line 3 is our most complicated line of code. Let's work from the middle, starting inside the ".map()" command.

1. "df[['Counselor','State']]" produces a new DataFrame that is a copy of our original DataFrame, but with data from only those 2 columns.
2. Appending "drop_duplicates(keep='first')" to it produces a new DataFrame that is a copy of it, only using "Counselor" and "State" together (that is, all 2 columns of our input DataFrame) as a matching key.
3. Appending "['State'].value_counts()" to that "deduplicated" DataFrame produces a new "Series" (fancy list-like thing) where the "item IDs" are the states from our data set, and the values are the number of unique "Counselor" values for that "State" value.

Now that we know what's inside the ".map()" command (a "Series" of states and their "unique Counselor value counts"), let's take a look at Line 3 of our code, starting after the "=."

4. "df['State'].map(ourSeriesOfStatesWithCounselorCounts)" produces a new "Series," with the same set of "item IDs" as the "row IDs" of our original DataFrame we imported from CSV, and with values populated by numbers indicating the number of "distinct counselor values" for the "State" found in that row of our data.

Its output looks like this:

0     2
1     2
2     2
3     2
4     1
5     2
6     1
7     1
8     1
9     1
10    1
11    1
Name: State, dtype: int64

5. Finally, all we do by putting this to the right of "df['Counts'] = " is to add a new "Counts" column to our original DataFrame and fill it in with these numbers.

Voila! We've added "column D!"

It was a little fancy getting there, but save this script to your computer -- or bookmark this blog post -- and you can copy/paste/tweak it any time it seems useful, whether or not you understand it enough to write it from scratch. (Don't worry -- I had to Google it and copy/paste/modify other people's code myself. 😊)

---

---

🇫🇷 - en français

Aujourd'hui j'ai rencontré un beau problème Excel bien adapté à résoudre avec Python.

Un collègue travaillait sur une feuille de calcul contenant 20 000 lycées américains (colonne B), l'état des E-U où chacun se trouve (colonne C) et le nom du recruteur qui se charge de l'école (colonne A).

L'université où travaille mon collègue divise la charge de travail du Maryland ("MD") et du Delaware ("DE"). Aux autres 48 états, un seul recruteur gère tous les lycées de l'état.

Mon collègue voulait réduire la taille de cette feuille de calcul. Il ne voulait changer aucun enregistrement du Delaware ni du Maryland. Pour les 48 autres états, il voulait éliminer la liste des écoles et garder uniquement 48 enregistrements, contenant chaque état et le recruteur qui s'en charge.

Sa feuille de calcul ressemblait à celle-ci:

Counselor,School,State
Ana,Dover HS,DE
Leia,Anapolis HS,MD
Ana,Baltimore HS,MD
Leia,Best Prep,DE
Mo,Denver HS,CO
Ana,Howard HS,DE
Mo,Boulder HS,CO
Mo,Boise HS,ID
Leia,Fairfax HS,VA
Mo,Excel Academy,ID
Mo,Mann HS,ID
Leia,Roanoke HS,VA

Il souhaitait une feulle de calcul de sortie comme celle-ci:

Counselor,School,State
Ana,Dover HS,DE
Leia,Anapolis HS,MD
Ana,Baltimore HS,MD
Leia,Best Prep,DE
Ana,Howard HS,DE
Mo,STATE-CO,CO
Mo,STATE-ID,ID
Leia,STATE-VA,VA

Comme on sait déjà comment diviser la feuille pour traiter les données de telle ou telle manière, le travail est très simple. Cela consiste à diviser les données en "DE + MD" et "ailleurs", à dédupliquer la partie "ailleurs", et à concaténer les deux divisions.

En fait, on n'a pas vraiment besoin de Python. C'est un travail de 2 minutes avec Excel.

1. filtrer la feuille de calcul pour extraire les enregistrements "ailleurs"
2. extraire ces lignes dans un nouvel onglet
3. dédupliquer sur la colonne "C"
   • (ou, pour vérifier qu'il n'y ait pas d'erreurs de saisie de données, sur "A" et "C" ensemble, cherchant plus que 2 enregistrements par état)
4. modifier le contenu de la colonne "B" pour qu'il soit une concaténation du mot "STATE-" et du contenu de la colonne "C"
5. supprimer le filtre de notre onglet de feuille de calcul d'origine (où rien ne reste que les données "Maryland" et "Delaware")
6. couper-coller les contenus du nouvel onglet à la fin de l'originale

Mais le code Python ne serait pas trop compliqué. Le script imiterait nos étapes Excel:

import pandas
df = pandas.read_csv('c:\\example\\HighSchools.csv', dtype=object)
dfdemd = df[df['State'].isin(['DE','MD'])]
dfoth48 = df[~df['State'].isin(['DE','MD'])]
dfoth48 = dfoth48.drop_duplicates(subset=['State'], keep='first')
dfoth48['School'] = 'STATE-'+dfoth48['State']
df = pandas.concat([dfdemd,dfoth48])
df.to_csv('c:\\example\\FilteredHighSchools.csv', index=0, quoting=1)

(Pour un fichier XLSX et non CSV, on se servirait des commandes "read_excel" et "to_excel" et on supprimerait le code ", quoting=1" de la fin du script.)

---

Mais notre problème devient vraiment intéressant car nous pouvons laisser Python calculer quels états n'ont qu'un seul recruteur et quels états sont la charge de plusieurs recruteurs.

Avec Excel, il s'agit de:

1. Ajouter une "colonne D" qui compte le nombre de valeurs uniques du "colonne A" pour chaque valeur dans "colonne C."
   • (Il y a des astuces en ligne pour y arriver.)
2. Répéter notre opération de "scinder, modifier une partie, concaténer" de plus tôt. Sauf que nous divisons par la colonne "D" ("1" et "2 ou plus").

Encore une fois, le code Python va imiter nos étapes Excel:

import pandas
df = pandas.read_csv('c:\\example\\HighSchools.csv', dtype=object)
df['Counts'] = df['State'].map(df[['Counselor','State']].drop_duplicates(keep='first')['State'].value_counts())
df_mult_couns = df[df['Counts'] > 1]
df_1_couns = df[df['Counts'] == 1]
df_1_couns = df_1_couns.drop_duplicates(subset=['State'], keep='first')
df_1_couns['School'] = 'STATE-'+df_1_couns['State']
df = pandas.concat([df_mult_couns,df_1_couns])
df = df.drop(['Counts'], axis=1)
df.to_csv('c:\\example\\SmartFilteredHighSchools.csv', index=0, quoting=1)

Ce script ressemble beaucoup au précédent. J'ai renommé "dfdemd" en "df_mult_couns" car dans ce cas, nous ne savons pas encore que "Delaware" et "Maryland" auront plusieurs recruteurs par état. De même, j'ai renommé "dfoth48" en "df_1_couns".

Les nouvelles lignes de code sont la 3ème ligne et l'avant-dernière ligne.

En outre, les lignes 4 et 5 ont été modifiées pour filtrer sur "Counts" au lieu de filtrer sur "State".

Apres les lignes 4 et 5, nous n'avons plus besoin de la colonne "Counts". (Nous aurions pu le supprimer de chaque scinde après les lignes 4 et 5, mais j'ai décidé plutôt de supprimer cette colonne des données reconcaténées dans l'avant-dernière ligne).

On fait toujours notre déduplication principale par "State."

La ligne 3 du code est la ligne la plus compliquée. Inspectons-la en commençant au milieu, à l'intérieur de la commande ".map()".

1. "df[['Counselor','State']]" génère un nouveau "DataFrame" qui est une copie de notre DataFrame d'origine, mais avec des données provenant uniquement de ces 2 colonnes.
2. L'ajout de ".drop_duplicates(keep = 'first')" le déduplique par recruteur et état, pour générer encore un nouveau "DataFrame".
3. L'ajout de "['State'].value_counts()" à ce DataFrame "dédupliqué" génère une nouvelle "Série" (élément de type liste ... amélioré ... plutôt tableau associatif) où les clés sont nos états et les valeurs sont le nombre de recruteurs uniques pour chaque état.

Maintenant que nous comprenons l'intérieur de ".map()" ("Série" avec les états comme clé), examinons la partie de la ligne 3 qui suit le "=".

4. "df['State'].map(notre_Tableau_Associatif)" produit une nouvelle "Série" avec le même ensemble de clés que sont les clés des enregistrements de notre "DataFrame" d'origine (de notre fichier CSV) -- c'est-à-dire les numéros qui identifie les lignes du fichier. Les valeurs de cette nouvelle "Série" sont les nombres de recruteurs qui correspondent à l'état trouvé sous "State" pour chaque enregistrement.

La sortie de cette commande ressemble à:

0     2
1     2
2     2
3     2
4     1
5     2
6     1
7     1
8     1
9     1
10    1
11    1
Name: State, dtype: int64

5. Finalement, "df['Counts'] = " ne fait qu'ajouter cette liste nombres de recruteurs au DataFrame d'origine comme nouvelle colonne (nommée "Counts").

Et voilà! On a ajouté la "colonne D" ! Après, ce n'est qu'une question de finir les filtres.

Comme j'ai dit, la ligne 3 était peut-être un peu compliqué, mais enregistrez ce script dans un fichier sur votre ordinateur -- ou ajoutez cet article à vos favoris -- et vous pouvez le copier-coller-modifier au moment qu'il vous convient (que vous le compreniez bien ou non). (Ne vous inquiétez pas, moi aussi j'ai Googlé pour écrire cet article ! 😊)

---

---

"Python for Salesforce Administrators" Table of Contents

• Introduction & Table of Contents
• Before You Begin Programming
• Installing Python 3 on Windows
• First Scripts - Import/Export CSVs, Filter Out Rows
• Rename/Drop/Add Columns
• Recognizing Pandas DataFrames vs. Series (Important!)
• Fancier Row-Filtering and Data-Editing
• Combining Multiple Tables
• Filtering Rows By Maximum Date Per Group
• Introduction to XML and JSON

Salesforce Custom Metadata vs. Custom Objects: Where Should I Put My Configuration and Validation Settings?

(Version française en dessous)

Yesterday, a colleague asked me the difference between "custom metadata" and "custom objects" for storing "configuration and validation" information in Salesforce.

My answer is:

• Custom metadata if you can. Salesforce says so.
  And it survives sandbox refreshes!
• Data tables (custom objects) if you truly need it to be part of your data
  (e.g. people will want to include it, hyperlinked to other data, in reports).

When I wrote an Apex trigger that determined which User should "own" each "Admissions Application" in our org, I ended up splitting the configuration data in two.

Here's why:

Custom Metadata

Used for a table that helps Apex code ask, "Who does this?"

• key: the code for a graduate degree we offer
• value: a username responsible for recruiting people to that graduate degree

Data (Custom Objects)

Used for a list of U.S. states, their full spellings, and fields with "lookup" links to the User table indicating who does what work for that state.

• There was strong demand to generate native Salesforce reports per User showing all the states they're responsible for and the high schools in those states. It made sense to ensure that the "high school" table could have a "lookup" field to this "states" table.
• Custom metadata can have "master-detail" and "lookup" relational links to other custom metadata, but it can't link to ordinary data.
  • This meant we needed to store the the "states" as data (custom objects), even though we would also be using it as configuration information for Apex triggers.

---

UI & Editability Considerations

I'll let you in on a dirty little secret about another reason I used data tables ("custom objects") for most of the Undergraduate Admissions counselor assignment configuration data.

Undergraduate Admissions tweaks their "recruiter assignment" business rules several times a year. The real-world configuration data for their business is a lot more complex than a simple "list of U.S. states."

I'll be honest: Salesforce's user interfaces for hand-editing, viewing, and bulk-editing data are a lot more end-user-friendly than their user interfaces for the same operations on custom metadata, and setting granular "edit" permissions is a lot more sysadmin-friendly for data. I wanted to make sure end users, not sysadmins, were the ones whose time was spent tweaking the configuration several times a year!

I was thoroughly scolded at Dreamforce. Actually, I stand by my decision to use "data" tables, because there truly is a business need to report on the configuration data alongside normal data. But ... building your own user interfaces (typically using Lightning Components) to help end users edit custom metadata was a big theme. You have been warned:

1. Dan Appleman's "Build Awesome Configuration Pages with Lightning Components & Custom Metadata"
2. Gustavo Melendez & Krystian Charubin's "Crafting Flexible APIs in Apex Using Custom Metadata"
3. Beth Breisness & Randi Wilson's "Create Guided User Experiences for Managing ISV Custom Metadata"

---

---

🇫🇷 - en français

Une collègue, peu familière avec Salesforce, m'a demandée quelle était la différence entre « métadonnées personnalisées » et « objets personnalisés » pour stocker des données de configuration et de validation dans une organisation Salesforce.

J'ai répondu:

• Selon Salesforce, s'il est possible, utilisez des métadonnées personnalisées.
  (Elles survivent l'actualisation d'un environnement sandbox.)

• Stockez vos informations aux tables de bases de données (objets personnalisés) s'il faut créer des relations aux autres objets de votre organisation.
  (Par exemple, s'il y a des utilisateurs qui vont générer des rapports sur ces données.)

Mon approche

Lorsque j'ai écrit un déclencheur Apex pour attribuer les enregistrements de notre objet « demande d'admission » aux utilisateurs corrects, j'ai fini par utiliser les deux approches:

Métadonnées personnalisées

J'ai choisi une type de métadonnées personnalisées pour stocker des données simples concernant « qui gère chaque spécialisation ? »

• clé: le code qui indique un diplôme d'études supérieures que l'université offre
• valeur: un nom d'utilisateur chargé de recruter des étudiants au diplôme

Objets personnalisés

J'ai choisi un objet personalisé pour stocker une liste d'états américains (abréviation et nom), avec quelques champs de référence indiquant les utilisateurs qui y gèrent divers aspects du recrutement d'étudiants en license.

• Nos utilisateurs ont besoin de générer des rapports, regroupés par utilisateur, avec les états qu'ils gèrent et les lycées qui y sont situés. Donc il fallait pouvoir créer une relation de l'objet « lycée » à l'objet « état ».
• On peut créer des relations entre métadonnées personnalisées, mais pas entre les types de métadonnées personnalisées et les objets personalisés.
  • Donc j'ai choisi un modèle « objet personnalisé » pour stocker les informations à propos des états, même si ces informations servent aussi comme données de configuration pour un déclencheur Apex.

Considérations au sujet de l'interface utilisateur et au contrôle d'accès

Je vais vous révéler un secret : ce n'est pas qu‘à propos des besoins relationnels que j'ai choisi le modèle « objet personnalisé » pour les informations à propos de la gestion du recrutement en licence.

Le service admission en license changent les règles de « qui gère quoi » plusieurs fois par an. Et les données de configuration sont, en réalité, beaucoup plus complexes qu'une seule liste d'états.

Actuellement, les interfaces utilisateur de Salesforce pour voir et enregistrer des données aux objets personnalisés sont supérieures à celles pour les métadonnées personnalisées – surtout si l'on est utilisateur ordinaire. Le contrôle d'accès, aussi, est plus facile à gérer pour les administrateurs. Je voulais m'assurer que ça soit des utilisateurs ordinaires qui font plusieurs fois par an cette saisie de données, et non pas les administrateurs !

On m'a fait honte de cette position à Dreamforce. Ben, je maintiens ma choix d'objet personnalisé pour raisons de relations entre objets. Mais … on a beaucoup répété qu'il est de bonne pratique de construire ses propres interfaces utilisateur pour autoriser des utilisateurs ordinaires à modifier les métadonnées personnalisées en toute sécurité. Je vous aurai prévenus !

Vidéos pertinentes de Dreamforce

1. « Build Awesome Configuration Pages with Lightning Components & Custom Metadata » de Dan Appleman
2. « Crafting Flexible APIs in Apex Using Custom Metadata » de Gustavo Melendez & Krystian Charubin
3. « Create Guided User Experiences for Managing ISV Custom Metadata » de Beth Breisness & Randi Wilson

Trigger/Process/Workflow or Scheduled Script/Tool?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

---

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

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

---

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

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

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

---

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

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

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

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

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

)

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.