“Ya know,” my partner begins. “There seems to be a disproportionate ratio between number of showers I take and the number of times I clean the hair from the drain filter.”
“Is that so?” I reply. “Sounds like you need to take more showers, then!”
There are several important morals to take away from this anecdote.
- My partner should shower more often (the hygienist)
- My partner has bad luck with the shower drain (the statistician)
- Our shower drain is poor quality (the structural, root cause analyst)
- The clogged drain bothers him more than it bothers me (the therapist)
- I should clean out the drain more often (the prosecutor)
- Statistics can be very misleading (the responsible journalist)
- My partner is very generous about cleaning out the drain (the optimist)
- You should think twice before moving in with me (the pragmatist)
Suffice it to say, these are ALL valid conclusions.
As you will learn below, I took a shower tonight. The drain seemed fine to me.
This post isn’t really about the shower. It’s all about interpretation and making meaning, and the shower is one of many places where that can happen. Tonight, for example, as I was rinsing the conditioner out of my (already long, now during social distancing very very long) hair, I was struck by inspiration to solve a problem that had been vexing me for weeks! The specific problem was all about determining eligibility for taking a survey, and how to make spreadsheets interpret eligibility criteria in the quickest, easiest way.
Let’s start with the most manually intensive way. It would look something like this: I present you with a list of people, and you tell me which people should receive the survey invitation. Sure, it’s subject to a margin of error (you might not know all of the people, you might be wrong sometimes, you might procrastinate… who ME? never!!!) but it gets the job done and sometimes that’s all that matters!
In the scenario that I’ve been turning over in my mind, the naked eye can’t tell! We need to look at a list of volunteers, and only send the survey to volunteers who volunteered this semester as well as in previous semesters. How many times can I say “volunteer” in a sentence?! I figured out a way to do this with spreadsheets, VLOOKUPs, pivot tables, and a complicated data upload process. It worked, but I made a big mistake the first time, and ended up with more eligible people than I should have. Oopsie daisy! I already cleaned up the mistake and apologized, but it still left me with an uneasy feeling. There must be a better way!
There are essentially three different data questions that we need to answer in order to determine eligibility, at least for this project.
- Did the person volunteer this semester? (yes or no)
- Did the person volunteer in any previous semesters? (yes or no)
- Is the answer to both of the above questions, yes?
- Is the answer to the above question yes?
- Ok I’m going to stop here or else this will recur for ever ;P
To answer (1), I used a Salesforce report to pull all of the current volunteers, filtered by everyone who has a successful volunteer session in their history. Easy, breezy, beautiful!
Better solution: Use a roll-up field on the Volunteer Term record to determine # of successful Volunteer Sessions. Anyone with a number greater than zero meets Criteria 1.
To answer (2), I exported all Volunteer Terms, used a pivot table to group by person (technically, by email), and then determined how many volunteers had more than one term in their history. Then, I took that table (now grouped by person instead of volunteer term), and copied the # Of Volunteer Terms back to the list of Current Volunteers (using a VLOOKUP function), and then I filtered out the people who only had 1 volunteer term, because that means they are not a returning volunteer. Blegh, that’s a lot of steps, and there’s no simple way to automate it. If you didn’t follow all of those steps, don’t worry!
Better solution: Use a roll-up field on the Contact to determine # of volunteer terms (where each volunteer term had at least one successful volunteer session). Then, copy the roll-up field from the Contact to each of the Contact’s volunteer terms. Anyone with a number greater than 1 meets criteria 2.
To answer (3), I used a combination of filters in an Excel table to find volunteers for whom 1 and 2 were both true. Then, I copied the filtered data into a new tab so that I could upload back into Salesforce.
Better solution: Use a formula field on the Volunteer Term to compare these values to the criteria.
I just ran a report to compare the “automated” version to the “manual” version (that I did in Excel and then uploaded) and I got roughly the same answer. The difference can be attributed to new volunteers who meet the criteria NOW who did not meet the criteria 2 weeks ago when I pulled the data. SUCCESS!!
So, let’s review the pro’s and con’s of these methods!
Manual (Excel formula)
- Pro: Doing this one step at a time let’s me eye-ball the list as I go
- Pro: Captures the eligible volunteers at a moment in time (does not change as conditions change in real life!)
- Con: Extra work
- Con: Complicated formulas
- Con: Prone to error
Auto (Salesforce rollup method)
- Pro: Fast, reliable, anyone can pull a report any time
- Pro: Updates in real time
- Con: Cannot run “as of” reports (for example, eligible volunteers as of May 1, unless you did Reporting Snapshots yadda yadda)
- Con: Requires roll-up fields, which are precious (!)
- Con: Requires building multiple custom fields, which you might not want to do for a one-time exercise
“I FIGURED IT OUT!” I proclaimed from the shower, suds and all.
“Is it about spreadsheets?” replied my partner, followed by a sarcastic text to my family’s thread. His guess was right.
Reportedly, Archimedes “coined” (har har) the term “Eureka!” when he ran through Syracuse positively butt-naked after figuring out that the king’s crown was not, indeed, made of gold. So it seems only fitting to exclaim a Eureka of my very own, naked and all. Now I can finally put this problem (and my self) to bed!