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

Python for Salesforce Administrators - "In Both Tables?" Dummy-Check Script

I was asked how to use Python to "dummy check" that every transaction in a Salesforce log also appeared in a payment processor's log, and vice-versa.

The following are a few simple scripts that do just that. We're going to work with our "sample1" & "sample2" files, which have some people who overlap by name and email, and others who don't.
In your case, instead of "name & email" you might want to match on "transaction ID" or "product ID, customer ID, and timestamp." Whatever appears in both datasets.

Note that "Albert Howard" is going to show up as "not in the other file" for both files because he has a different email address in each file.

(Prep work: First, make sure you've created a ".CSV" file like the one described in "First Scripts". I called mine "sample1.csv". Second, make sure you've created a 2nd ".CSV" file like the one described in "Combining Multiple Tables". As in that post, in each example, we'll overwrite the contents of a piece of Python code saved to our hard drive as "script.py" and run the entire script.)

---

Code for one big "error log" output file

Code:

import pandas
pandas.set_option('expand_frame_repr', False)
df1 = pandas.read_csv('C:\\tempexamples\\sample1.csv', dtype=object)
df2 = pandas.read_csv('C:\\tempexamples\\sample2.csv', dtype=object)

df1matchfields=['First','Last','Email']
df2matchfields=['FirstName','LastName','Em']

mergedf = df1.merge(df2, left_on=df1matchfields, right_on=df2matchfields, how='outer', indicator=True)

uniquedf = mergedf[mergedf['_merge'].str.endswith('_only')]
uniquedf.to_csv('C:\\tempexamples\\output.csv', index=0, quoting=1)

Output:

"Id","First","Last","Email","Company","PersonId","FirstName","LastName","Em","FavoriteFood","_merge"
"5829","Jimmy","Buffet","jb@example.com","RCA","","","","","","left_only"
"2894","Shirley","Chisholm","sc@example.com","United States Congress","","","","","","left_only"
"30829","Cesar","Chavez","cc@example.com","United Farm Workers","","","","","","left_only"
"724","Albert","Howard","ah@example.com","Imperial College of Science","","","","","","left_only"
"","","","","","983mv","Shirley","Temple","st@example.com","Lollipops","right_only"
"","","","","","k28fo","Donald","Duck","dd@example.com","Pancakes","right_only"
"","","","","","8xi","Albert","Howard","ahotherem@example.com","Potatoes","right_only"

---

Code for two separate "error log" output files

Code:

import pandas
pandas.set_option('expand_frame_repr', False)
df1 = pandas.read_csv('C:\\tempexamples\\sample1.csv', dtype=object)
df2 = pandas.read_csv('C:\\tempexamples\\sample2.csv', dtype=object)

df1matchfields=['First','Last','Email']
df2matchfields=['FirstName','LastName','Em']

mergedf = df1.merge(df2, left_on=df1matchfields, right_on=df2matchfields, how='outer', indicator=True)

uniquedf1 = mergedf[mergedf['_merge']=='left_only'][df1.columns]
uniquedf2 = mergedf[mergedf['_merge']=='right_only'][df2.columns]
uniquedf1.to_csv('C:\\tempexamples\\output1.csv', index=0, quoting=1)
uniquedf2.to_csv('C:\\tempexamples\\output2.csv', index=0, quoting=1)

Output 1:

"Id","First","Last","Email","Company"
"5829","Jimmy","Buffet","jb@example.com","RCA"
"2894","Shirley","Chisholm","sc@example.com","United States Congress"
"30829","Cesar","Chavez","cc@example.com","United Farm Workers"
"724","Albert","Howard","ah@example.com","Imperial College of Science"

Output 2:

"PersonId","FirstName","LastName","Em","FavoriteFood"
"983mv","Shirley","Temple","st@example.com","Lollipops"
"k28fo","Donald","Duck","dd@example.com","Pancakes"
"8xi","Albert","Howard","ahotherem@example.com","Potatoes"

---

---

The code above only works if you have "Pandas version 0.17" or greater installed.

Pandas is up to version "0.23" as of this blog post, so that's a pretty safe bet.

Except that the "no admin rights, even with old installations of Windows" programming environment I walked you through installing is many years old, and it comes with "Pandas version 0.16." So here are two scripts that do the equivalent job in older versions, if you don't feel like upgrading everything.

"Old Pandas" code for one big "error log" output file

Code:

import pandas
pandas.set_option('expand_frame_repr', False)
df1 = pandas.read_csv('C:\\tempexamples\\sample1.csv', dtype=object)
df2 = pandas.read_csv('C:\\tempexamples\\sample2.csv', dtype=object)

df1matchfields=['First','Last','Email']
df2matchfields=['FirstName','LastName','Em']

mergedf = df1.assign(SourceDF='df1').merge(df2.assign(SourceDF='df2'), left_on=['First','Last','Email'], right_on=['FirstName','LastName','Em'], how='outer')
uniquedf = mergedf[mergedf['SourceDF_x'].isnull() | mergedf['SourceDF_y'].isnull()]

uniquedf.to_csv('C:\\tempexamples\\output.csv', index=0, quoting=1)

Output:

"Id","First","Last","Email","Company","SourceDF_x","PersonId","FirstName","LastName","Em","FavoriteFood","SourceDF_y"
"5829","Jimmy","Buffet","jb@example.com","RCA","df1","","","","","",""
"2894","Shirley","Chisholm","sc@example.com","United States Congress","df1","","","","","",""
"30829","Cesar","Chavez","cc@example.com","United Farm Workers","df1","","","","","",""
"724","Albert","Howard","ah@example.com","Imperial College of Science","df1","","","","","",""
"","","","","","","983mv","Shirley","Temple","st@example.com","Lollipops","df2"
"","","","","","","k28fo","Donald","Duck","dd@example.com","Pancakes","df2"
"","","","","","","8xi","Albert","Howard","ahotherem@example.com","Potatoes","df2"

"Old Pandas" code for two separate "error log" output files

Code:

import pandas
pandas.set_option('expand_frame_repr', False)
df1 = pandas.read_csv('C:\\tempexamples\\sample1.csv', dtype=object)
df2 = pandas.read_csv('C:\\tempexamples\\sample2.csv', dtype=object)
 
df1matchfields=['First','Last','Email']
df2matchfields=['FirstName','LastName','Em']
 
mergedf = df1.assign(SourceDF='df1').merge(df2.assign(SourceDF='df2'), left_on=['First','Last','Email'], right_on=['FirstName','LastName','Em'], how='outer')
 
uniquedf1 = mergedf[mergedf['SourceDF_y'].isnull()][df1.columns]
uniquedf2 = mergedf[mergedf['SourceDF_x'].isnull()][df2.columns]
uniquedf1.to_csv('C:\\tempexamples\\output1.csv', index=0, quoting=1)
uniquedf2.to_csv('C:\\tempexamples\\output2.csv', index=0, quoting=1)

Output 1:

"Id","First","Last","Email","Company"
"5829","Jimmy","Buffet","jb@example.com","RCA"
"2894","Shirley","Chisholm","sc@example.com","United States Congress"
"30829","Cesar","Chavez","cc@example.com","United Farm Workers"
"724","Albert","Howard","ah@example.com","Imperial College of Science"

Output 2:

"PersonId","FirstName","LastName","Em","FavoriteFood"
"983mv","Shirley","Temple","st@example.com","Lollipops"
"k28fo","Donald","Duck","dd@example.com","Pancakes"
"8xi","Albert","Howard","ahotherem@example.com","Potatoes"

---

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

Python for Salesforce Administrators - A "Combining Multiple Tables" (VLOOKUP) Example with the "Simple Salesforce" plugin

Today I used Python to facilitate a quick UPDATE of 60 Contact records in Salesforce.

In Salesforce, there were 60-ish Contact records where I had instructed an end user to populate a field called "RegSys_External_Id_B__c" with the "ID" that they could see while logged into their registration system.

However, what I really wanted to do was make sure that "RegSys_External_Id_A__c" was populated with the "real external ID" (that my end users couldn't see, but that held everything together in the back-end of their registration system) of each person they had hand-matched.

I asked them to simply fill it in with dummy values, "XXXX01," "XXXX02," "XXXX03," etc. so that I could easily find the records they had already "hand-checked" later.

I then used Python as follows:

First, I used Python's "Simple Salesforce" plugin to log into our org and download the 60 Contact records into Python's "Pandas" plugin. I saved that data into a variable "cstofixdf" (as in "Contacts To Fix DataFrame"). Note that the data that comes back from Simple Salesforce has to have "['records']" appended to it to become something that "Pandas" can read and turn into a "DataFrame" (2-dimensional table). Also, I drop the "attributes" column that comes back with "Simple Salesforce" data because I think it's ugly and takes up unnecessary space on my screen when I preview what my "DataFrame" looks like. Anyway, I also printed out all the "RegSys_External_Id_B__c" values as a comma-separated, quote-surrounded list onto my screen so that I could easily copy them to my clipboard.

import pandas
pandas.set_option('expand_frame_repr', False)

from simple_salesforce import Salesforce
sf = Salesforce(username='myemail@example.com', password='mypassword', security_token='mysalesforcesecuritytoken')

cs = sf.query("SELECT Id, RegSys_External_Id_A__c, RegSys_External_Id_B__c FROM Contact WHERE RegSys_External_Id_B__c <> NULL AND RegSys_External_Id_A__c LIKE 'X%'")['records']
cstofixdf = pandas.DataFrame(cs)
cstofixdf.drop('attributes', axis='columns', inplace=True)

print(list(cstofixdf['Flatbridge_Student_ID__c']))

My output looked like this -- only with more like 60 items in the list instead of 4:

['8294', '29842', '8482', '2081']

Once I had copied the list between the square-brackets onto my clipboard, I pasted it into the tool by which I query the back-end of my end user's course registration system. It uses normal "SQL," so my code looked like this:

SELECT IdA, IdB
FROM ExternalIdMappingTable
WHERE IdB IN ('8294', '29842', '8482', '2081')

I exported the 60 rows of output from THAT database onto my hard drive at "c:\examples\otherdb.csv"

I then ran the following code -- note that I've "commented out" the "print(list(...))" line of my code with a "#" since I no longer need to print that information to my screen.

import pandas
pandas.set_option('expand_frame_repr', False)

from simple_salesforce import Salesforce
sf = Salesforce(username='myemail@example.com', password='mypassword', security_token='mysalesforcesecuritytoken')

cs = sf.query("SELECT Id, RegSys_External_Id_A__c, RegSys_External_Id_B__c FROM Contact WHERE RegSys_External_Id_B__c <> NULL AND RegSys_External_Id_A__c LIKE 'X%'")['records']
cstofixdf = pandas.DataFrame(cs)
cstofixdf.drop('attributes', axis='columns', inplace=True)

#print(list(cstofixdf['Flatbridge_Student_ID__c']))

regdf = pandas.read_csv('c:\\examples\\otherdb.csv', dtype=object)

mergedf = cstofixdf.merge(regdf, how='inner', left_on='RegSys_External_Id_B__c', right_on='IdB')
mergedf.drop(['IdB', 'RegSys_External_Id_A__c', 'RegSys_External_Id_B__c'], axis='columns', inplace=True)
mergedf.rename(columns={'IdA':'RegSys_External_Id_A__c'}, inplace=True)

mergedf.to_csv('c:\\examples\\uploadme.csv', index=0, quoting=1)

The last 5 lines of code are the new part.

First, I import "otherdb.csv"

Next, I "inner-merge" (like a VLOOKUP, with "inner" specifying that the "ID B" must show up in both datasets) the two "DataFrames" -- the one I downloaded from Salesforce and the one I just loaded in from CSV, and save the output into a new "DataFrame" called "mergedf."

Not shown here, I did a bit of quality-checking after I wrote the line of code that did the merge, before getting rid of "excess" columns. For example, I did:

print(mergedf)

and

print(len(cstofixdf))

and

print(len(regdf))

and

print(len(mergedf))

I double-checked that all the data-sets were the same length (there shouldn't have been any duplicates or missing values), and I hand-skimmed the results of "mergedf" to make sure I hadn't written the ".merge()" code wrong.

After that, I get rid of a few columns I won't want in my output CSV file: the old "A" & "B" values from Salesforce (I just needed the Salesforce ID), as well as the "B" value from the SQL download.

Then I renamed the SQL-downloaded "IdA" column to "RegSys_External_Id_A__c" to be explicit that this was the Salesforce Contact-table field I intended to update this data into.

Finally, I dumped the "mergedf" "DataFrame" to CSV on my hard drive.

From there, I was just a Data Load away from having proper data in Salesforce!

---

P.S. You may note that some of my code reads like this:

mergedf.drop(['IdB', 'RegSys_External_Id_A__c', 'RegSys_External_Id_B__c'], axis='columns', inplace=True)

Whereas older examples in my blog read like this:

mergedf = mergedf.drop(['IdB', 'RegSys_External_Id_A__c', 'RegSys_External_Id_B__c'], axis=1)

They do the same thing.

Many Pandas functions produce an altered copy of your "DataFrame" or "Series," rather than altering the actual data you have stored into a given "variable." Therefore, if you actually want to store the "altered" version into that "variable name," you have to explicitly do so. This is actually pretty common in programming -- you'll see things like "x = x + 1" all the time.

The "inplace=True" option on certain Pandas operations is a shorthand that keeps you from having to type all that. Sometimes, it gets buggy on me, and I didn't want your first lessons to fail, so I wrote my introductory examples the "long way."

You may also notice a difference between "axis='columns'" and "axis=1". They do the same thing. "1" is faster to type than "'columns'", but obviously "'columns'" is easier to read when you come back and need to figure out what you did later. Your choice!

---

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

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.

Re-Parented Child Objects Don't Fire Triggers In Parent Record Merges

Today I learned, via the Salesforce forums:

• In Salesforce, if you merge 2 "Contact" records, and if you have a bunch of "child"-table records pointing to the "about-to-be-deleted" Contact record as a foreign key, Salesforce will automatically change the foreign key cross-reference in the "child" records to the ID of the "surviving" Contact record, and it will update those child records' "last modified" timestamp, BUT it will not allow any "UPDATE" triggers from those child tables to fire.
• The only thing you can latch onto to detect that a "Contact merge" has just happened is the "AFTER DELETE" trigger-context against the "Contact" table (you can detect a "deleted but merged" Contact in that context from an ordinary "deleted" Contact because it has a "MasterRecordId" value).
  You can't "detect" that a "child" record has just been "re-parented" in the context of a "merge."
• Annoyingly, once you're that far into the merge ("after contact delete" trigger context), you can no longer tell which "child" records were pointing to the old "parent" because that's long since been "fixed" by Salesforce.
  All you can see is which "child" records are now cross-referencing the surviving "Contact" – which includes all "child" records that were already pointing to the surviving "Contact" in the first place.
• Finally, if you use that knowledge to kick off DML against surviving Contacts' "child" records, you must be careful, because "after Contact delete" is still in the context of the initial Contact merge, and you have to avoid loops (Salesforce will yell at you if you don't).
  If your DML against the "child" records kicks off triggers that result in more DML against one of the very Contacts involved in the merge, you need to make sure you kick off the DML against the "child" records in a "@future" context to force a brand new "execution context" (roughly like a transaction, but a little different in some ways – see Dan Appleman's Advanced Apex book).

Loving my "Just Push Play" Run-Specified-Tests Script

Power tools are fun.

THIS PYTHON SCRIPT HAS CHANGED MY LIFE.

Whipped it out twice this week trying to get code written under tight deadlines.

Having a nearly-instant, low-clicking-around answer to, "Would it probably work in production?" so I can easily watch how every single change to just 1 line of code behaves, before doing a full-on deploy of code that "seems to work" with a proper deployment tool and "Run All Tests," has been AMAZING.

Dreamforce 2017 Lessons Learned & Takeaways

Lucky me, I got to attend Dreamforce again. Here's a summary.

Misc

1. "Machine learning" tools are getting point-and-click enough that feeding hundreds of pieces of historical student data (e.g. "days before schoolyear applied," "GPA," "time between inquiry & admission," "time of year applied," etc.) into them and letting a computer look for trends (e.g. "likely to submit a confirming deposit?") isn't all that out of reach. Drag-and-drop tools are coming into prominence that make dimensionality reduction & linear regression problems like this intuitive & easy to play with.
    
2. There's something called "macros" I should look into more. At a glance, not sure it's anything I can't do a lot faster w/ DemandTools, but worth a glance.
    

Org configuration change management

1. One of the concepts behind "DX scratch orgs'" mere 7-day lifespan is to encourage you to put the result of all your coding time & effort into a source code version control system.
    
2. Two guiding philosophies behind development with "DX" are that: 1) you can make all the changes you like by hand in them -- just be sure to use the DX command-line tool to pull those changes down to your computer so you can promptly get them into your version control system, and 2) you should never again be making any changes to configuration in production / normal-sandbox orgs by hand (only via deploying from a copy of whatever you did in a "DX scratch org" into one of those orgs via something like the DX command-line tool).
    
3. On a related note, I still haven't heard a really admin-geared lecture about DX and "please don't mess around with things by hand in production/staging sandboxes, and please don't deploy anything via change set."
    
4. Gearset looks like an absolute miracle for shops where those kinds of "no change sets, please" disciplined version-control practices aren't yet in place (e.g. shops transitioning from Salesforce as an "interesting side project" with a no-code solo admin). It's an enterprise-class-(seeming?) tool that simply lets you log into Salesforce and a cloud Git repository and say, "Hey, synchronize my entire org's metadata once a day -- kthxbye." It also seems to be able to facilitate rollbacks. In a small shop where communication lines are open enough that once you know what changed since things were working, sending out a few emails to figure out who changed it and if they could kindly fix it is easy, this "daily snapshot" seems well worth $3600/year. Even after you have "version control" in place -- when it's this easy, what's the harm in a "side repository" of "CYA snapshots?" (Such Git-stored snapshots could also, potentially, be mined by local scripts that transform them into things like SQL commands for dropping & rebuilding tables & SOQL queries for a local daily cache of Salesforce data.) And that's without all the other things Gearset does. (Please stay just $3600/year, Gearset!) Getting a tour from a staffer makes their product look even cooler than their summary on their web site -- apparently they can give you one over the web. *drools* I want.
    

Lightning Experience

1. Although there's a lot that's still missing from "Lightning Experience" (e.g. "bucketing" & "formulas" in native reporting tools), by the Winter '19 edition, there might be enough LE-only functionality in Salesforce's native reporting tools to justify a switch--for example, something called "joined reports," field-to-field filtering, subfoldering, easier permissions management, and bookmarking of reports. (So far, we haven't found it worthwhile.) I'm hoping to get a lot of data integration projects done & stable over the next few months so I have time to play around with Lightning Experience in a sandbox.
    
2. Apparently, having a really nice "Lightning Experience" home page for a user doesn't automatically translate into a really nice mobile version of the same page -- that has to be set up separately.
    
3. There's a thing called the "Lightning Data Service" that might make Lightning Components require almost as little code as Visualforce for simple "put this SOQL query's results on a page" use cases, unlike last year when I took a hands-on training. It also looks like it has something to do w/ getting Lightning Components on the same page to auto-update when data from the database that one of them just edited changes.
    

SOQL query performance & data models

1. OpportunityContactRole isn't anywhere close to becoming a first-class object (a table against which you can write triggers). *sigh*
    
2. The "affiliations" package might be replaceable with the "multiple accounts for contacts" feature & a few triggers similar to ones from the "affiliations" package. That said, the "affiliations" package doesn't really seem to be hurting anything. But Pardot and other vendors are working to get on board with the native version of the feature, so it's worth keeping an eye on.
    
3. Having a single "Generic Account" record for people whose account you don't know the value of slows down most SOQL queries. It improves performance to give everyone their own accounts named after them, as in the HEDA model.
    
4. Salesforce objects/tables aren't really tables. They're sets of tables (e.g. indexes are a table, fields are a table, etc.). That's why Salesforce doesn't call them "tables" and impacts SOQL query performance. We don't seem to have hit issues yet, but there's also something Salesforce can do with their back-end data model and materialized views for storing your org's data called skinny tables if you ask -- it can speed up SOQL queries.
    
5. Reminder: "=" is fast in a query that can leverage a highly selective index, "!=" is not -- "!=" always requires a table scan. Query optimization 101, but so easy to overlook when you get busy and don't write queries daily where you have to worry about it.
    
6. There's a technology called "big objects," paired with "asynchronous SOQL," coming along, but it doesn't yet look useful for us. You've got to be really sure about data not changing, because right now, you can't even drop such a table once it's created.
    

Other programming-related notes

1. Stay on top of the "metadata in Apex" project, but overall, it's on purpose that you can't change your schema & change your data via the same programming language / execution environment, so don't get too excited.
    
2. Hopefully coming within a year: the ability to easily make Apex respect CRUD permissions & field-level security permissions (when you don't want your triggers to "play God").
    
3. If you're a web developer, you can write your Visualforce/Apex to include a Visualforce Boolean variable as a toggle that switches whether Visualforce pages rely on Salesforce-hosted "static resources" (e.g. CSS, JavaScript) or "localhost"-hosted ones. This can make testing a lot easier & less fragile & faster than actually trying to update the contents of Salesforce-hosted "static resources" when you're not yet even sure if they're correct. Talk to Jon Schleicher for more info about how.
    
4. Via the reporting API (warning: the JSON to parse "is a doozy"), you can wrap Reports from Salesforce's native reporting tool in Lightning Components and therefore make them easy to embed in other pages. (Natively, only dashboards can be drag-and-dropped into Lightning Components.)
    
5. Google Docs are a form of "cloud-hosted info storage with an API" -- which means that you can include info from them in custom-programmed user interface elements like Lightning Components. Possibly overlooked form of integration of results from a cheap-and-easy aggregate-reporting tool (spreadsheets) with business users' main daily work environment (Salesforce).
    
6. Personal goal: blast through data integration projects so I can free up time to work on Trailhead modules and learn to be better able to leverage a lot of the technologies I learned about "tips & tricks" for. There's a lot out there that I'd like to do, but at 8 hours apiece, one really has to work to free up the time. But they're so well-written.

Recursion-Reduction Tips For Apex Trigger Code

I wanted to share with you some lessons I learned while working on a trigger.

---

Because a trigger against a "just-saved" record can kick off another "save" operation on that record (typically the point of a trigger), the very same trigger can get re-fired during what's known as the same "execution context."

In Apex trigger programming, it's considered best-practice to make sure that any subsequent re-firings of the same trigger against the same record don't waste valuable CPU cycles when this happens (because Salesforce limits them within an "execution context").

Therefore, when writing a trigger that "does ____ for a just-saved record," it's important to make sure that, at some point, the trigger saves the ID of that record into a "I already did _____ on all of these records" flag that's viewable across all of these recursions (usually a class-level "Static"-flagged "Set<Id>"-typed variable in the trigger handler).

And, of course, you need to program your trigger to pay attention to its own flags and avoid running expensive "consider doing ____" code against records already in that "already-did-____" set of IDs.

---

The interesting question is: When do you set the "I already did _____" flag?
 

• In certain cases, one can trust that all field-values contributing to a trigger's yes/no decision of "should I do _____ to this just-saved record?" will be set the moment that the record is first saved.
   
  In those cases, the most efficient place in your trigger code to set the "I already did _____ on this record" flag is "as soon as the trigger has seen the record for the first time, no matter whether it ends up qualifying for 'doing ____' or not."
   
  That's how I usually write my triggers if I can, since it's the most efficient way to write the trigger.
   
   
• However, in certain cases (often discovered when people test your newly-written trigger and tell you that it fails under normal usage circumstances through 3rd-party record-editing environments like external portals), the values contributing to the answer to "should I do ____ to this record?" change so that the answer goes from "no" to "yes" in the middle of the "execution context."
   
  For example, other "triggers" or equivalent pieces of code do some post-processing to the just-saved record, and it's only after those pieces of code re-save the record that the answer flips to "yes."
   
  In those cases, the most efficient place in your trigger code to set the "I already did ______" on this record" flag is "as soon as the trigger has determined that it needs to do ______ to the record."
   
  This, unfortunately, will make the trigger run "Should I do _____?" checks all the way through the execution context for records that remain "no" throughout. That's why it's less efficient.
   
  But sometimes, it's simply necessary in cases where the answer can flip from "no" to "yes" mid-execution-context.
   
   

---

If one is comfortable authoring / editing the triggers/processes/workflows that are responsible for such impactful mid-execution-context value-changes, sometimes it's possible to refactor them so that they're simply part of the same "trigger handler" code as the one you're in the middle of writing.
You could precisely control the order of code execution "after a record is saved" and author these actions in a way that ensure there will never be any "mid-execution-context surprise value-changes."
That might let you use the more efficient recursion-reduction pattern instead.

Sometimes, though, there's nothing you can do but choose the 2nd option.

Python for Salesforce Developers: "Just Push Play" Run-Specified-Tests Code Deployment

Warning: the attached Python script makes you put a password straight into the code and helps you make an end-run around all sorts of change-management best practices.

Basically, if you're not a little horrified to see this script publicly shared, you probably don't understand what it's capable of enough to be using it -- so please dont!

That said, for circumstances in which you were going to ignore a lot of change-management issues anyway, or just want to do a "check only" deploy of code to a Salesforce org, etc., this code basically lets you type a handful of classes / pages / etc. into a Python script, type in your username & password, say which tests you want to run and how you want to deploy it, and see the results really quickly.

The idea is to be almost as handy, to a developer, for small changes, as right-clicking on code files in Eclipse and "deploying" from there -- the problem with Eclipse being that it doesn't have a "Run Specified Tests" option.

At some point I might create something similar to this that lets you log into two Salesforce orgs, download a smattering of code from one to your local hard drive (instead of already having to have downloaded it with Eclipse), and proceed with the deploy from there. That really feels like playing with laziness/sloppiness fire, though (somehow seems to take out the "Would I really have deployed this just with Eclipse, anyway?" factor).

This code is probably best just for "checkOnly=TRUE" deploys. For real deploys, it's probably still best to Run All Tests, and using a "Change Set" is a lot better for anyone else who might have to stumble into your org a few weeks later and see what's been happening as far as code deploys. (And that's just a bare minimum of version control for simply-maintained orgs.)

A few notes on the code:

• Change "#'''" to "'''" around blocks of code to quickly toggle them off (no point, for example, re-logging in if your Python IDE already has your session ID in memory, and you don't want to fire up a new "deploy" just to run the "check deployment status" code again).
• "thingsToAdd" is where most of the "what you need to type" exists.
• You'll also need to set "username" & "password" values (including your security token appended to your password) in "toOrgLoginEnvelope" -- I recommend remembering to change it back by saving this script to your hard drive (if you do save it) with a filename that includes something like "INCLUDES PASSWORD" so, as you exit the IDE, you remember to change things back.
• "inputstr" will need the actual path to where you've used Eclipse to download the files you have "ready to deploy" to on your hard drive.
• "outpbase" should be somewhere easy to find and delete later, like a sub-folder on your desktop.
• "deployEnvelope" will need "checkOnly" set inside the XML itself, "testLevel" set just above it, and a (brackets-and-comma-delimited) list of tests to run towards the end of the "runtests" parameter inside the parentheses that follow the XML (if n/a, use "[]").

import os
import shutil
import base64
from xml.etree import ElementTree
import xml.dom.minidom
import requests
import re   

def getUniqueElementValueFromXmlString(xmlString, elementName):
    #Extracts an element value from an XML string.
    #For example, invoking getUniqueElementValueFromXmlString('<?xml version="1.0" encoding="UTF-8"?><foo>bar</foo>', 'foo') should return the value 'bar'.
    elementsByName = xml.dom.minidom.parseString(xmlString).getElementsByTagName(elementName)
    elementValue = None
    if len(elementsByName) > 0: elementValue = elementsByName[0].toxml().replace('<' + elementName + '>', '').replace('</' + elementName + '>', '')
    return elementValue

metadataAPIVer = '40.0'

#'''
toOrgLoginEnvelope = """<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"><soapenv:Body><login xmlns="urn:partner.soap.sforce.com"><username>{username}</username><password>{password}</password></login></soapenv:Body></soapenv:Envelope>""".format(
username='username', password='password')
tor = requests.post('https://login.salesforce.com/services/Soap/u/'+metadataAPIVer, toOrgLoginEnvelope, headers={'content-type':'text/xml','charset':'UTF-8','SOAPAction':'login'})
tosessid = getUniqueElementValueFromXmlString(tor.content, 'sessionId')
tohost = getUniqueElementValueFromXmlString(tor.content, 'serverUrl').replace('http://', '').replace('https://', '').split('/')[0].replace('-api', '')
toorgid = re.sub(r'^.*/([a-zA-Z0-9]{15})$', r'\1', getUniqueElementValueFromXmlString(tor.content, 'serverUrl'))
toapiver = re.sub(r'^.*/([0-9.]+)/[a-zA-Z0-9]{15}$', r'\1', getUniqueElementValueFromXmlString(tor.content, 'serverUrl'))
#'''

# EXAMPLE CODE:  thingsToAdd = {'classes':[''],'pages':['']}
thingsToAdd = {'classes': {'singCaps':'ApexClass','ext':'cls','toUpl':['OpportunityETLHandler','OpportunityETLTest']}, 'pages': {'singCaps':'ApexPage','ext':'page','toUpl':['OpportunityETLPage']}}
inputstr = 'C:\\EXAMPLEFOLDER\\EclipseWorkspace\\My Sandbox\\src\\'
outpbase = 'C:\\EXAMPLETEMPFOLDER\\temppkgtouploadfromeclipse\\'
outpfiles = outpbase + '\\filesbeforezip\\'
outpzip = outpbase + 'uploadme'

#'''
# BEGIN:  Code to create package
if not os.path.exists(outpbase): os.makedirs(outpbase)
if not os.path.exists(outpfiles): os.makedirs(outpfiles)
pkgroot = ElementTree.Element('Package', attrib={'xmlns':'http://soap.sforce.com/2006/04/metadata'})
for folder in thingsToAdd.keys():
    innerDict = thingsToAdd[folder]
    typesElem = ElementTree.Element('types')
    if not os.path.exists(outpfiles+folder+'\\'): os.makedirs(outpfiles+folder+'\\')
    for item in innerDict['toUpl']:
        membersElem = ElementTree.Element('members')
        membersElem.text = item
        typesElem.append(membersElem)
        shutil.copy(inputstr+folder+'\\'+item+'.'+innerDict['ext'], outpfiles+folder+'\\'+item+'.'+innerDict['ext'])
        shutil.copy(inputstr+folder+'\\'+item+'.'+innerDict['ext']+'-meta.xml', outpfiles+folder+'\\'+item+'.'+innerDict['ext']+'-meta.xml')
    namesElem = ElementTree.Element('name')
    namesElem.text = innerDict['singCaps']
    typesElem.append(namesElem)
    pkgroot.append(typesElem)
verElem = ElementTree.Element('version')
verElem.text = metadataAPIVer
pkgroot.append(verElem)
with open(outpfiles+'package.xml', 'w', newline='') as fw:
    dom_string = xml.dom.minidom.parseString(ElementTree.tostring(pkgroot)).toprettyxml(encoding='UTF-8', indent='    ')
    dom_string = '\n'.join([s for s in dom_string.decode('UTF-8').splitlines() if s.strip()]) + '\n'
    fw.write(dom_string)
# END:  Code to create package
#'''

#'''
# BEGIN:  Code to create ZIP
shutil.make_archive(base_name=outpzip, format='zip', root_dir=outpfiles, base_dir='./')
zipString = None
with open(outpzip+'.zip', 'rb') as f: zipString = base64.b64encode(f.read()).decode('UTF-8')
# END:  Code to create ZIP
#'''

# TO DO:  Figure out how to run several tests.  Maybe it's just a comma-separation?  I think it it's that in the point-and-click Change Set UI.

#'''
# BEGIN:  Code to deploy ZIP
testLevel = 'RunSpecifiedTests' # Most common values will be 'RunSpecifiedTests' or 'RunLocalTests'
deployEnvelope = """<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:met="http://soap.sforce.com/2006/04/metadata">
      <soapenv:Header>
         <met:SessionHeader>
            <met:sessionId>{sessionid}</met:sessionId>
         </met:SessionHeader>
      </soapenv:Header>
      <soapenv:Body>
         <met:deploy>
             <met:zipFile>{zipfile}</met:zipFile>
             <met:deployOptions>
                 <met:checkOnly>true</met:checkOnly>
                 <met:rollbackOnError>true</met:rollbackOnError>
                 {runtests}
                 <met:singlePackage>true</met:singlePackage>
                 <met:testLevel>{testlev}</met:testLevel>
             </met:deployOptions>
         </met:deploy>
      </soapenv:Body>
      </soapenv:Envelope>""".format(sessionid=tosessid, zipfile=zipString, testlev=testLevel, runtests=''.join(['<met:runTests>'+x+'</met:runTests>' for x in ['OpportunityETLTest1','OpportunityETLTest2']]) if testLevel=='RunSpecifiedTests' else '')
deploytor = requests.post('https://'+tohost+'/services/Soap/m/'+toapiver+'/'+toorgid, deployEnvelope, headers={'content-type': 'text/xml', 'charset': 'UTF-8', 'SOAPAction': 'deploy'})
# END:  Code to deploy ZIP
#'''

#'''
# BEGIN:  Code to check deploy
checkDeployStatusEnvelope = """<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:met="http://soap.sforce.com/2006/04/metadata">
      <soapenv:Header>
         <met:SessionHeader>
            <met:sessionId>{sessionid}</met:sessionId>
         </met:SessionHeader>
      </soapenv:Header>
      <soapenv:Body>
         <met:checkDeployStatus>
             <met:asyncProcessId>{process_id}</met:asyncProcessId>
             <met:includeDetails>true</met:includeDetails>
         </met:checkDeployStatus>
      </soapenv:Body>
      </soapenv:Envelope>""".format(sessionid=tosessid, process_id=getUniqueElementValueFromXmlString(deploytor.content, 'id'))

checkdeploytor = requests.post('https://'+tohost+'/services/Soap/m/'+toapiver+'/'+toorgid, checkDeployStatusEnvelope, headers={'content-type': 'text/xml', 'charset': 'UTF-8', 'SOAPAction': 'checkDeployStatus'})

#print(checkdeploytor.content)
#print()
print('Id: ', getUniqueElementValueFromXmlString(checkdeploytor.content, 'id'))
print('Done: ', getUniqueElementValueFromXmlString(checkdeploytor.content, 'done'))
print('Success: ', getUniqueElementValueFromXmlString(checkdeploytor.content, 'success'))
print('Status: ', getUniqueElementValueFromXmlString(checkdeploytor.content, 'status'))
print('Problem: ', getUniqueElementValueFromXmlString(checkdeploytor.content, 'problem'))
print('NumberComponentsTotal: ', getUniqueElementValueFromXmlString(checkdeploytor.content, 'numberComponentsTotal'))
print('RunTestResult: ', getUniqueElementValueFromXmlString(checkdeploytor.content, 'runTestResult'))
print('NumberTestsTotal: ', getUniqueElementValueFromXmlString(checkdeploytor.content, 'numberTestsTotal'))
#print(getUniqueElementValueFromXmlString(checkdeploytor.content, 'details'))
# END:  Code to check deploy
#'''

DemandTools MassImpact equivalent of UPDATE...SET...WHERE SQL

The CRMFusion company makes powerful Salesforce-record-editing software called DemandTools.

Below are some screenshots of setting up its "MassImpact" (single-table-editing) module to do a job equivalent to running an "UPDATE...SET...WHERE" DML SQL statement against a traditional database, for data cleansing within a specific single table.

For example, to turn all values of table Contact, field Home_Phone__c that are filled with nothing but 10 digits in a row, no punctuation, into a properly formatted US phone number, you might traditionally use the following Oracle-database-friendly DML SQL:

UPDATE Contact
SET Home_Phone__c = REGEXP_REPLACE(Home_Phone__c,'^(\d{3})(\d{3})(\d{4})$','(\1) \2-\3')
WHERE REGEXP_LIKE(Home_Phone__c,'^(\d{3})(\d{3})(\d{4})$')

In DemandTools, you would set up a MassImpact "scenario" as follows:

• Step 1: Tell DemandTools that you want to operate on the "Contact" object/table, and say which fields you want to be able to see the values of while you screen go/no-go on potential updates in the 3rd step.
  
  
  
   
• Step 2: Tell DemandTools that you want to potentially-update all records where "Home_Phone__c" isn't null (unfortunately, you can't use a regular expression in the "WHERE" with DemandTools – but step 3 has some dummy-proofing to get around too wide a selection),
  and say that you want to propose a parentheses-and-dashes-formatted replacement value for any of the returned values that consist of nothing but a string of 10 bare digits in Home_Phone__c.
  
  
  
   
• Step 3: Ensure that you aren't pushing "UPDATE" calls for any records where there is no change to the value of Home_Phone__c, or where the new value of Home_Phone__c would be blank,
  and skim the records to make sure your logic is doing what you thought it would,
  and click "Update Records."
  
  
  
  

---

P.S. Just for geekiness, and to compare ease of use, here's some "Execute Anonymous" Salesforce Apex code along the same idea.
(Note: not tested at scale. Depending on your trigger/workflow/process builder load, might not actually work since it probably all runs in 1 "execution context" of post-DML CPU usage "governor limits," whereas DemandTools will run in truly separate "execution contexts" per 200 records to UPDATE.)

Map<Integer, List<Contact>> csToUpdate = new Map<Integer, List<Contact>>();
Integer csToUpdateCount = 0;
Integer currentBatch = 0;
List<Contact> cs = [SELECT Phone FROM Contact WHERE Phone <> null];
Pattern p = Pattern.compile('^(\\d{3})(\\d{3})(\\d{4})$');
for (Contact c : cs) {
 Matcher m = p.matcher(c.Phone);
 if(m.matches() == true) {
        csToUpdateCount++;
        currentBatch = (csToUpdateCount/200)+1;
        if (!csToUpdate.containsKey(currentBatch)) { csToUpdate.put(currentBatch, new List<Contact>()); }
        c.Phone = m.replaceFirst('($1) $2-$3');
        (csToUpdate.get(currentBatch)).add(c);
 }
}
if (csToUpdate.size() > 0) {
    for (List<Contact> csToU : csToUpdate.values()) {
        UPDATE csToU;
    }
}

Python To Facilitate Adding A New Field To Multiple Custom Report Types (involves XML)

Quick code dump: if you download all your "report types" with Eclipse, this can help you put together "fresh" copies of them for use with the Workbench Deploy web site (just zip them up). Very handy if you just created a custom field on an object that's involved in dozens of report types.

This is an example of why it's good to be able to work with XML, too, in Python!

Hopefully I can clean up (or delete & replace) this post at a later date.

import os
from xml.etree import ElementTree
import xml.dom.minidom

def stripNSFromET(etRoot):
    etRoot.tag = etRoot.tag.split('}', 1)[1] # strip all namespaces level 1
    for el in etRoot:
        if '}' in el.tag:
            el.tag = el.tag.split('}', 1)[1] # strip all namespaces level 2
            for el3 in el:
                if '}' in el3.tag:
                    el3.tag = el3.tag.split('}', 1)[1] # strip all namespaces level 3
                    for el4 in el3:
                        if '}' in el4.tag:
                            el4.tag = el4.tag.split('}', 1)[1] # strip all namespaces level 4
                            for el5 in el4:
                                if '}' in el5.tag:
                                    el5.tag = el5.tag.split('}', 1)[1] # strip all namespaces level 5
    return etRoot

def isTableOfInterest(tableTagText, fieldsDatabaseTableAPIName):
    if fieldsDatabaseTableAPIName == 'Contact':
        return tableTagText == 'Contact' or tableTagText.endswith('.Contacts') or tableTagText.endswith('OpportunityContactRoles')
    else:
        return tableTagText == fieldsDatabaseTableAPIName or tableTagText.endswith('.'+fieldsDatabaseTableAPIName)

outpstr = 'C:\\example\\temppackagereporttypes\\'
pstr = 'C:\\examplepath\\EclipseWorkspace\\orgfolder\\src\\reportTypes\\'
fstr = 'C:\\examplepath\\EclipseWorkspace\\orgfolder\\src\\reportTypes\\Contact_With_Campaign_Members.reportType'

metadataAPIVer = '40.0'
fieldsDBTableAPIName = 'Contact'
fieldAPINames = ['New_Field_1__c','New_Field_2__c']
fieldAPINames.sort()

# SOME NOTES OF INTEREST:
# Contact is not only referred to in the normal way, but also often as OpportunityContactRole coming off an "Opportunity."  Not sure if ALL such OCRs are actually able to take Contact fields.
# This could make it a bit difficult to decide into what "Section," exactly, to add a new database field when adding it to every ReportType:
    # A given "Section" tag within a ReportType's XML file is NOT limited to containing "Columns" elements with the same database "Table" value.  For example, Graduate_Admissions.reportType, with a base object of Contact, has a section named "Recruit data" with fields from both the "Contact.Program_Interests__r" and "Contact" tables in it.
    # A given database "Table" value can appear in multiple "Section" tags within a ReportType's XML file (e.g. "Contact" fields could be spread across multiple sections).
    # CampaignMember often ends up repeated -- once as a top-level, once below itself -- in a ReportType where it is included (with fields duplicated and everything).  Good thing I'm not yet adding any CampaignMember fields to reports.

# SOME CODE TO REMEMBER:
# The number of tables mentioned in a ReportType file:  len({e.text for e in root.findall('./sections/columns/table')})
# The actual tables mentioned in a ReportType file:  {e.text for e in root.findall('./sections/columns/table')}
# The number of "sections" in a ReportType file:  len(root.findall('./sections'))
# The labels of the actual "sections" in a ReportType file:  {e.find('masterLabel').text for e in root.findall('./sections')}
# Iterating over a dict:  for k, v in d.items():


"""
Algorithm per ReportType file, parsed into an XML-ElementTree node called "root":
- Figure out how many 1st-level nodes called "sections" there are.
- For each 1st-level "section" node, if it's "of interest," set it aside as a key to a dict called "d",
    and a list of all its relevant child 2nd-level "columns" nodes as the values.
- Loop through "d" and set aside any keys that have the most list-items as a value, of all keys in "d";
    set aside a list of any such keys as "topSections"
- If there was just 1 such "top section" key, set that 1st-level "section" node aside as the variable "se"
    (This is, visually to the end user, where we'll be adding the new field.)
- If there was more than 1, arbitrarily pick 1 and set that 1st-level "section" node aside as the variable "se"
- Once we've picked a 1st-level "section" node as "se," cache a dict "uniqueTableNames" 
    of the distinct words appearing among its grandchild (3rd-level) "table" tag values as "key"
    and the count-of-table-tag-per-distinct-word as "value."
- Presuming there were any (I guess this serves as a sort of dummy-check for the section),
    instantiate a new ElementTree "columns" node called "newColumn,"
    append "newColumn" to "se" (make it 2nd-level)
    and flesh out "newColumn" with details of the field we want to add.
    (When fleshing it out, we arbitrarily pick a value for the 3rd-level "table" tag if "uniqueTableNames" had several keys.)

"""

changedThese = []
pkgroot = None
for i in os.listdir(pstr):
    root = None
    if not i.startswith('wrt_'):
        with open(pstr+i, 'r', encoding='utf-8') as f: # Open a ReportType metadata XML file
            root = stripNSFromET(ElementTree.parse(f).getroot()) # Store a reference to the root of the XML file currently open in a variable
            d = {}
            allFieldsForTableOfInterestColsInAllSectionsOfInterest = []
            for sec in root.findall('./sections'):
                columnsOfInterest = [e for e in sec.findall('columns/table/..') if isTableOfInterest(e.find('table').text, fieldsDBTableAPIName)]
                if len(columnsOfInterest) > 0:
                    d[sec] = columnsOfInterest # Add to "d" any "section" and a list of applicable "columns" inside it
            if len(d) < 1:
                continue # This file is not of interest if nothing got added to "d" (if it's not a ReportType that includes any fields of the object of interest) -- move on to next file
            else:
                print('\r\n' + i + ', baseObject:  ' + root.find('./baseObject').text) # Display which file we are working with and what its "Base Object" is
                se = None
                if len(d) > 0: # Why did I have this at ">1" when I found it 6 months later?  Should it be >0?
                    allFieldsForTableOfInterestColsInAllSectionsOfInterest = [item.find('field').text for sublist in d.values() for item in sublist]
                    if all(fldNm in allFieldsForTableOfInterestColsInAllSectionsOfInterest for fldNm in fieldAPINames):
                        continue # This file is not of interest if all fields are already in it
                    topSections = {k for k, v in d.items() if len(v) == max([len(arr) for arr in d.values()])}
                    if len(topSections) >= 1:
                        if len(topSections) == 1:
                            se = (next(iter(topSections))) # There was only 1 top-ranked section -- pick it
                        else:
                            topSecsWithLabelLikeTableNewFieldIsFrom = [s for s in topSections if s.find('masterLabel').text in [fieldsDBTableAPIName, fieldsDBTableAPIName+'s']]
                            if len(topSecsWithLabelLikeTableNewFieldIsFrom) > 0:
                                se = (next(iter(topSecsWithLabelLikeTableNewFieldIsFrom))) # I don't really care which it is, honestly
                            else:
                                se = (next(iter(topSections))) # I tried my best -- moving on.  Just picking a section.
                    else:
                        se = (next(iter(d.keys()))) # It was a 1-section file -- just pick the one section
                if se:
                    uniqueTableNames = {tbstr : [e.text for e in se.findall('columns/table')].count(tbstr) for tbstr in [e.text for e in se.findall('columns/table')]}
                    if len(uniqueTableNames) >= 1: # We can just tack our new column onto the only section that already has other Contact values
                        changedAnything = False
                        for fieldAPIName in fieldAPINames:
                            if fieldAPIName not in allFieldsForTableOfInterestColsInAllSectionsOfInterest:
                                changedAnything = True
                                newColumn = ElementTree.Element('columns')
                                se.append(newColumn)
                                newColumn.append(ElementTree.Element('checkedByDefault'))
                                newColumn.find('checkedByDefault').text = 'false'
                                newColumn.append(ElementTree.Element('field'))
                                newColumn.find('field').text = fieldAPIName
                                newColumn.append(ElementTree.Element('table'))
                                if len(uniqueTableNames) == 1:
                                    newColumn.find('table').text = next(iter(uniqueTableNames))
                                elif len(uniqueTableNames) > 1:
                                    newColumn.find('table').text = max(uniqueTableNames, key=uniqueTableNames.get)
                        if changedAnything:
                            se[:] = sorted(se, key=lambda x: x.tag) # Put masterLabel tag back at the end of the section.  We don't need to re-sort the fields because we might screw up where people were expecting to see them, if they weren't yet in alphabetical order.
                            root.set('xmlns','http://soap.sforce.com/2006/04/metadata')
                            if not os.path.exists(outpstr): os.makedirs(outpstr)
                            if not os.path.exists(outpstr+'reportTypes'): os.makedirs(outpstr+'reportTypes')
                            with open(outpstr+'reportTypes\\'+i, 'w', newline='') as fw:
                                dom_string = xml.dom.minidom.parseString(ElementTree.tostring(root)).toprettyxml(encoding='UTF-8', indent='    ')
                                dom_string = '\n'.join([s for s in dom_string.decode('UTF-8').splitlines() if s.strip()]) + '\n'
                                fw.write(dom_string)
                            changedThese.append(i[:-11])
if len(changedThese) > 0:
    pkgroot = ElementTree.Element('Package', attrib={'xmlns':'http://soap.sforce.com/2006/04/metadata'})
    typesElem = ElementTree.Element('types')
    for x in changedThese:
        membersElem = ElementTree.Element('members')
        membersElem.text = x
        typesElem.append(membersElem)
    namesElem = ElementTree.Element('name')
    namesElem.text = 'ReportType'
    typesElem.append(namesElem)
    pkgroot.append(typesElem)
    verElem = ElementTree.Element('version')
    verElem.text = metadataAPIVer
    pkgroot.append(verElem)
    with open(outpstr+'package.xml', 'w', newline='') as fw:
        dom_string = xml.dom.minidom.parseString(ElementTree.tostring(pkgroot)).toprettyxml(encoding='UTF-8', indent='    ')
        dom_string = '\n'.join([s for s in dom_string.decode('UTF-8').splitlines() if s.strip()]) + '\n'
        fw.write(dom_string)

print('all done')

Vendor-Provided Unit Test Bug "Fix" -- Just Add "SeeAllData=true"!

Wow, does one of our managed-package vendors have me riled up.

I detected a bug in their code that means an "@isTest"-flagged unit test can't INSERT a new record from various tables (including "User" records of a certain type of "Profile").

In other words, I can't write unit tests that have anything to do with several of our core tables.

I reported the bug and pointed out that:

• such INSERT statements work fine against "the real database," executed in the "Execute Anonymous Window" of the Developer Console, and that
• they work fine with the "Run Test" button in "@isTest(SeeAllData=True)"-flagged unit tests (a big no-no), but that
• as soon as you wrap such an INSERT statement in a test method/class annotated simply with "@isTest," it fails against various closed-source-code triggers in their managed package.

Clearly, their triggers have some weird bugs that they never detected because they "cheated" when writing all their unit tests by adding "(SeeAllData=True)" to "@isTest" (that's the case in all the open-source work they custom-developed for us).
Their codebase doesn't seem to be able to hold up to an empty database the way Salesforce code is supposed to, so it seems they simply make sure they're never showing it off to the customer on an empty database and have all unit tests look at "actual data" in the database.
Lovely.

So what'd they do in response to my problem?

Added "(SeeAllData=True)" to my proof-of-bug code and told me everything was all better.

*glares*

No.

It. most. definitely. is. not.

---

P.S. In the 29 new lines of code you "added" to my proof-of-bug unit test as well, vendor, you exposed a 2nd broken closed-source trigger when I turned off your "(SeeAllData=True)." So thanks for that, I guess -- saved me opening a 2nd ticket? *cough* #NotThatYouShouldHaveFoundItYourselfOrAnything

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