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

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

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! :-)