MT-ing My Head

Welcome to my soapbox of Salesforce technical writing, music, and miscellaneous stuff

An Intro to Predictive Modeling for Salesforce Peeps — April 16, 2021

An Intro to Predictive Modeling for Salesforce Peeps

I wanted to write about prediction because it’s seemingly everywhere: in marketing emails, in real estate, and even in criminal justice (blah).

An understanding of prediction and modeling can be pretty beneficial in business, however. Think lead scoring for new prospective clients or donors, employee attrition, or revenue forecasting. By understanding the capabilities of prediction, it might help us predict what data to collect in order to make more informed decisions.

Salesforce has released some A.I. products over the last few years under various names: Einstein, Tableau CRM, Wave Analytics. We can also create models ourselves, using different programming languages such as R and Python, or even in Excel.

First, a quote.

“All models are wrong, but some are useful”

George E. P. Box

Human nature can be unpredictable. We are also biased and have blind spots. I recently watched the documentary Coded Bias on Netflix, which showed how bad models can affect real people. So handle prediction with care.

So how does prediction work?

A predictive model tells us, based on existing criteria, what outcome is expected. For instance, what is someone’s income based on their race, gender, and profession?

Think of this as a mathematical formula similar to the ones we learn in high school for a straight line or a parabola. The inputs are x and the outputs are y.

y=mx+b

y=x^2

y=1/x

To build a predictive model, we use mathematical methods to find a formula that provides an output that is as close as possible to the real output for the greatest number of records (e.g. people, schools, whatever).

We can use both categorical (e.g. picklists) and numeric values in prediction.

So let’s say we want to predict a donor’s 2021 donation amount. We can use a method called regression.

In order to build a model to predict someone’s next donation amount, we need a “training data set” – a report of donors who have made 2021 Donations. We choose fields we think may be relevant (called independent variables, e.g. Age, Gender, Last Donation Amount, and Average Donation Size) and a “target” dependent variable (a field called 2021 Donation Amount.)

Then, we use a tool (Excel, R, Python, or a calculator with a huge memory :P) to create the model. Our goal is to get the most accurate model, so we may remove variables that don’t add that much value.

Our final output might look like this:

ŷ (this is the predicted value of 2021 Donation Amount) = 10 (this is the y-intercept) + 1.4*Age + 1.5*GenderFemale(true=1 or false=0) + 1.02*LastDonationAmount + 0.01*AverageDonationSize

So to predict the next donation amount of a donor who is:

  • Age = 45
  • Gender = Male
  • LastDonationAmount=$200
  • AverageDonationSize=$250

We would calculate the predicted value here:

ŷ = 10 + (1.4*45) + (1.5*0)+(1.02*200)+(.01*250)

ŷ = $279.50

NOTE: This is not a real model, and I am also oversimplifying.

From this result, we can see that the predicted donation amount for 2021 is higher than the donor’s last donation amount and average donation. Perhaps, in our fictional world, donors are very generous in 2021.

Conclusion and Disclaimer

I am passionate about educating people on how different technology works and hopefully saving them some money. However, I am a student and I am writing about what I am learning in class at the current time, and I am not an expert.

If you’re interested in learning more, check out online courses on modeling with R or Python, and then apply these skills to your Salesforce data. Courses will be able to explain some of the road bumps to look out for (e.g. multicollinearity, non-normality) and go more into detail about how to figure out if your model is actually a reliable one.

The example above is what is called linear regression. Logistic regression is another type of model…a way to predict the likelihood of an event. A famous example is whether or not someone died on the Titanic, which, spoiler alert: given a number of variables, is predicted best gender (“women and children” first) and ticket level (a proxy for wealth). I don’t know which method would be quicker for me: watching the 2 1/2 hour Kate Winslet and Leonardo DiCaprio movie or trying to build a model in R, but I digress…

Happy modeling!

Thoughts on FormAssembly — February 22, 2021

Thoughts on FormAssembly

FormAssembly is a go-to tool for many Salesforce partners. It allows people to make forms, both pulling data from Salesforce and also updating Salesforce. I want to quickly review what I think is good about this tool and what can be improved upon.

The Good

  • I love how customizable it is – it’s so easy to adjust the look and feel of the form with either a theme or CSS.
  • Functionality-wise you can do more with Javascript. As compared to Formstack, the ability to use Javascript is a huge plus here. They even give users example snippets of code.
  • Their support is AWESOME – super responsive by email and chat. They have gone above and beyond for me on multiple occasions and the reps are more like partners.
  • The prefill connector pulls data from Salesforce in a seamless manner by passing variables such as record IDs into the URL. In Formstack, you have to pass every field you want into the URL; FormAssembly just queries the database and “prefills” fields instead. This also allows for scalability; you can use one form for multiple purposes by conditionally showing or hiding information.
  • The repeater option – which allows you to a) pull in data from multiple records and b) create or update multiple records is very clever. We have used this to pull info from child objects on a one-to-many relationship – for instance, dates and times for modules (child) of a virtual event (parent). Or create many records at once (for instance, for guest lists for a benefit). You can even remove the submit button and use it to display information from your database using the pre-fill connector.
  • E-checks in FormAssembly/Authorize give us more payment options and flexibility for our donors. We created a form that allows us to collect money from a pledge, passing in the donation ID. Then the form updates fields on the record when the person pays.
    • Sub-point: You can save and pass Authorize.net transaction IDs into Salesforce records!! This is something Formstack cannot do.
  • The CSV import of picklist items that populates other fields. We used this to load the entire US school database into a form and populate fields like name, district, and address.

The Meh

  • The fact that this is based in Javascript can cause problems.
    • Learning curve or requirement to hire implementation team. Javascript is not normally in the skillset of a Salesforce admin, but it is a good skill to have.
    • The user’s browser must have Javascript enabled in order for the form to work properly. FormAssembly recommends adding a snippet of code that shows up when Javascript is not enabled, but this snippet should come with the platform in my opinion. There is inherent risk here otherwise.
  • On forms where you accept payment there is no native plugin for discount codes.
    • It’s also hard to discount to $0 and hide the credit card fields – you need Javascript for this.
  • The post submission connector is clunky at times. If I’m making an update, I’d love to be able to reference an existing value in Salesforce and then decide what to do – for instance, if it’s filled out already, don’t overwrite it with the new value. Also, there are some cases when the update/insert option doesn’t work for my use case and I have to do an update/insert in multiple steps instead.
  • I wish that FormAssembly had a Google Drive integration; any time I want to save files in a Google Drive I have to use Formstack.
  • The e-signature function is very sleek but there’s not a great way to report on signatures that have been “verified” through email; you have to go into each record to do that.
  • The WordPress plugin does not allow you to add fields into the URL, so we have had to use iFrames.

What’s the coolest thing you’ve done on FormAssembly? Tweet @ me at @ melanietolomeo!

Chronicles of Setting up NPSP from Scratch: Days 1 and 2 — January 7, 2021

Chronicles of Setting up NPSP from Scratch: Days 1 and 2

Hello readers,

Happy January / “end of ze world” week!

Wanted to share what I’m working on, outside of feeling existential dread.

My nonprofit org is using Salesforce but we are not on NPSP or Lightning. It is not ideal.

We are currently looking for a partner to do a needs assessment of our org. In order to deepen my understanding of NPSP and also “sell” this project internally, I have decided to make an attempt at setting up NPSP with minimal sample data. I have never used NPSP before so my only points of reference are Trailhead, Salesforce documentation, and being part of the NPSP Videography project.

Day 1 – Setup

Yesterday (January 6, 2021), I created a dev org and installed NPSP. Taking it slooooow.

Day 2 – The Basics

Today I created some record types that weren’t available in the installation. I also created a new user with a new profile that I can use for testing.

Then I noticed that there is a whole guide on configuration steps that I can draw from. I’m curious as to why this guide includes creation of buttons instead of actions but I assume that there’s a reason for that.

Challenges So Far

Now some observations on challenges so far…

Since I know the core Salesforce product and nonprofit terminology, NPSP is quite intuitive. However, I am having trouble staying focused, especially as I try to write out everything I do.

I started to think about why this type of task – reading instructions, making configurations, and documenting – is so daunting for me. I like printouts of instructions, but that’s not eco-friendly or space-friendly. I like pen and paper, but my handwriting is so bad and the output is something I would have to type later anyway. I have two screens, but that doesn’t feel like enough.

As someone who is (self-diagnosed) neurodivergent, I need some simpler ways of working. Perhaps I can display the documentation on my iPad. Perhaps I need to learn more Mac keyboard shortcuts in order to move from one screen to another.

I think what I’m learning about reducing clicks and being able to seamlessly switch between tasks (reading, writing, configuring) is going to help me in system design in the future.

Sporadic Notes on Flow and Marketing Attribution — December 9, 2020

Sporadic Notes on Flow and Marketing Attribution

I’ve been working in Salesforce Flow…a lot. I am a novice but have really enjoyed learning about software development best practices through a point-and-click tool.

My use case has been the very simple (sarcasm) marketing problem of attribution.

We send out emails with the call to action to donate. We segment our audiences based on whether they’ve donated in the past year, previous years’ donations, etc. and send different campaigns to each segmented group. I’d like to be able to understand roughly how much money came from each campaign.

“Use campaign influence!” you say. However, it is clear as dirt to me how to set that up or what it even does, so I’m going to do this my way by creating a flow that will probably hit all the governor limits.

So, for starters/background, we use Cazoomi Syncapps to sync Mailchimp with Salesforce.

Here’s how it works:

  1. We send out an email to a group of people via Mailchimp. This segment could be created in Mailchimp or Cazoomi (which has a Salesforce > Mailchimp sync option)
  2. Cazoomi automatically creates a campaignand campaign members for each person who received the email in Salesforce.
  3. Cazoomi also syncs open and click rates into Salesforce

What Cazoomi doesn’t do is track conversions of donations. We have relied on Google Analytics to do this.

So here’s my flow building process…

  1. Describe the actions in English, as if a human was doing it.
    1. Start with a donation. Which donations are included? Just won ones coming in from online.
    2. Look to the donor name.
    3. See if the donor received any emails for this end-of-year campaign.
    4. See if those emails happened before the close date of the opportunity so that we can be somewhat confident of the attribution.
    5. Find the closest campaign member and update the donation/opportunity with that campaign.
  2. Translate the above notes to fields and limitations. For instance, for step #1, I filtered out anything that didn’t come in from online and wasn’t Closed Won.
  3. Build out the flow, keeping in mind limitations of the flow function and its quirks. Some notes I found were:
    1. LIMITATIONS:
      1. Within the GET function, I’d love to be able to query on multiple levels using dot notation. For instance, it would be great to be able to find all Campaign Members where the Parent Campaign is “End of Year Campaign”.
      2. I can’t find all of the campaign members and store them to a variable, and then find all of the campaign IDs and store them to a variable; I have to use a loop.
    2. TIPS:
      1. When I am building for one record, if you are planning to scale up the flow, a good hint is to actually create a record variable for that record. Then, when it comes time to edit the flow to perform on a group of records, attempt to delete that record variable. It will show you a list of everything you have to replace.
      2. Name the variables with what type they are (e.g. Updated_opps_collection or Initial_opp_single_var)
      3. In my flow, I had to use an integer variable to figure out the closest campaign date to the close date (without going over). If I change this flow to operate on multiple Donation records, I will need to clear this value with every iteration of the loop.
  4. Debug using the Debug option.
  5. Create a button that deploys the flow.
  6. Deploy using a change set.

This is my final solution (adapted to be a nightly scheduled flow, which I haven’t tested yet). In the automated Tips section of the flow it says that I shouldn’t be doing database queries within a loop but I’m not sure how to avoid that in this case:

I also tried to figure out how to get this attribution to happen in bulk from a campaign but kept running into SOQL query limits, so I am probably doing something wrong.

In a different post, I will explain my interpretation of each type of action in flow to try to help others decode it. Stay tuned.

The Joys of Salesforce Admin Office Hours — November 24, 2020

The Joys of Salesforce Admin Office Hours

A few weeks ago, I put Office Hours on the calendar to discuss Salesforce, how a college professor might.

The goal of this hour is to create a relaxed space to talk about tech, hear people’s system woes and use cases, and conduct some training. It’s been a super enlightening, collaborative time so far!

Thank you to my wonderful coworker Michelle Shen for creating this graphic and inspiring this blog post.

My HQ office, like many others, went remote in early 2020. We no longer have sporadic meetings and space to catch up over coffee. This allows some open time to catch up and share what’s important to us right now.

This designated time has also had the unexpected benefit of helping my teammates see what others are working on and how their roles fit in with other people’s.

Plus, I can listen to users in a more personal setting than just via email / submitting tickets and really take the time dive into their issues with them. The people who show up want to learn about the business and the inner workings of the system, and I love to help them understand!

Highly recommend other admins to implement this.

Setting Up Internal IT Support with Email-to-Case — November 23, 2020

Setting Up Internal IT Support with Email-to-Case

If I were a recipe blogger, I’d probably write about how I got into a snuggly sweater and curled up with a cup of tea and lovely set of Salesforce support docs to set up Email-to-Case this afternoon.

But I’m not that person, so I’ll just start writing.

The Business Problem

We needed a way to collect internal IT support requests. We used to have a a technology request form, but it was awkward to point someone to a form every time they had a simple question.

How We Solved It

We set up email-to-case to solve this issue. This was up and running in a few hours, which is a testament to Salesforce’s help docs and the awesome community.

In this post, I will detail some funny barriers I ran into setting up the creation of the support cases. We are currently trying to figure out how to communicate with users (e.g. case comments, carrier pigeon, etc.)

The way email-to-case works is that users send to an actual email address such as support@fakeorganization.org. You have your IT department set up forwarding so that when a person emails that address, the email is forwarded to a special Salesforce email (e.g. help@salesforce.blahblahblah.com), and that creates a case.

There are a few options that you can set up on Email-to-Case, such as default owner, what to do with files, and domains and email addresses that are allowed to create cases.

The main advantage to Email-to-Case is that it’s more similar to how someone might communicate with another person than, say, filling out a lengthy form.

The disadvantage is that we don’t capture as much data for routing purposes. For instance, moving forward, my manager and I will split up the systems we administer. When there is just an email (rather than a series of picklists), there is some manual case transferring that needs to occur.

As pre-work, I:

  • Created an organization support email address – let’s say it’s called support@fakeorganization.org. I imagine this could also work with a listserv but I didn’t do it that way.
  • Created a special record type called “Tech Request”
  • Created each user as a contact in Salesforce with their company email in the email field (because of the way Cases work, this step was necessary)

I had never set up email-to-case before. I used the Salesforce help documentation to set this up so I won’t spend too much time detailing that.

I did run into a few hiccups along the way which I would like to share:

  1. At first I was not able to verify the Salesforce email through Gmail. When you set up email forwarding through Gmail, there is an extra verification step. The system sends an email to the recipient email (so that would be help@salesforce.blahblahblah.com). Since that’s not a real email, this took some Googling. I eventually found this help article to set up Email Snapshots. I set up an email snapshot for that same help@salesforce.blahblahblah.com address and that allowed me to see my verification code.
  2. I accidentally created a recursive loop by creating a queue with the same email address as we were using for help requests (support@fakeorganization.org), enabling email on it, and auto-assigning to this queue. Don’t do this.
  3. Email signatures were looking very ugly and clunky when the cases were coming in. I fixed this by creating a process builder on Case that removes everything after the “–“:
  1. Criteria:
AND(Not(Isblank([Case].RecordTypeId)),[Case].RecordType.Name = "Tech Request",ISNEW(),CONTAINS([Case].Description ,"-- "))

2. Field Update:

LEFT([Case].Description,FIND("--",[Case].Description)-1)

This will glitch out if someone includes “– ” in the body of their ticket. I also don’t know if it works anywhere except Gmail. But good enough. #rogueadmin

What’s Next

Next, we need to figure out a process for supporting users that is intuitive but also helps us track our work. Maybe we will even implement support metrics. Maybe there is a Service Cloud Cert in my future? Okay, I shouldn’t get ahead of myself.

Mobile Giving: An Intro — November 13, 2020

Mobile Giving: An Intro

Today’s topic is mobile giving.

Mobile giving is an important tool for a nonprofit organization’s individual giving strategy. According to this article at Nonprofit Source:

  • 25% of donors complete their donations on mobile devices
  • 51% of people who visit a nonprofit’s website do so from a mobile device

etc.

Mobile giving can be separated into a few different areas, such as:

  • Text-to-Give/Donate (SMS Donations) – Texting a keyword to a phone number to give money. Both options are great for events, virtual and in-person alike.
  • Scan to Give – Scan a QR code that leads to a donation form.
  • Outbound SMS – SMS serves as a mass communication channel similar to email. It may convey information or have a call-to-action to donate. Retail stores often use this to give discounts and share sales.

Today, I am focusing on SMS Donations. In a future post, I may detail how one might set this up in Salesforce using an external (paid) tool.

Text-to-Give versus Text-to-Donate

When choosing a provider

Text-to-Give means texting a keyword to a phone number and the donation being added to your cellphone bill.

Pros:

  • Simple experience – one step and no forms.
  • Does not require Internet access, only cell service.
  • Presumably less cart abandonment since there’s just one step.

Cons:

  • Works in pre-determined small amounts, so giving capacity is limited.
  • Delay in receiving the funds.
  • Donor data can be sparse, may need to set up flows to get additional data later.
  • Can be costly – important to make sure there is ROI here.

Implementation Options:

Text-to-Donate means texting a keyword to a phone number and receiving a link to a mobile-responsive donation form via SMS. This form can be accessed via a browser.

Pros:

  • Richer donor data.
  • Can donate any amount.
  • Can get funds more quickly since they come in through a regular payment processor.

Cons:

  • Need to be connected to Wifi or use data.
  • More steps for the donor.
  • If the donor thinks that the functionality is like text-to-give, they may think that by texting, they have already made the donation. This can be remedied to some extent by writing clear copy, but there will always be people who are just not paying attention.

Options for Implementation:

  • Text-to-Give Software: Text-to-give software is available. We were impressed by Zoomgive.
    • Pro: Likely provides analytics, and attractive mobile-first donation forms. They know mobile-first best practices and are mobile-first.
    • Con: Requires integration with CRM. Also sometimes not a lot of room for form customization (e.g. adding tribute options). Finally, there are likely fees associated so the ROI needs to make this option “worth it”.
  • Salesforce SMS and Donation Form: Use an SMS provider (we had SMS Magic for other purposes) to create a keyword automation that sends donor a mobile-responsive donation form (we use Formstack right now)
    • Pro: Easy integration into Salesforce for donation logging, since you are using an existing donation form. Inexpensive.
    • Con: May need automation chops, depending on which SMS tool you use. Also can be clunkier than an out-of-box solution.
  • Another SMS tool and Donation Form – We once used Mobomix and our regular donation form. We purchased some SMS credits and a keyword for $10/month and were off to the races.
    • Pro: Inexpensive. Easy-to-implement tool. Includes short code (see caveats below).
    • Con: Sometimes you may not get the keyword you want. Also, you are sharing a short-code phone number with other companies.

A Quick Note on Short Codes

Short codes are 5-6-digit phone numbers like “55055”. They are very convenient for text-to-give and mass communication purposes, compared to long toll-free numbers like 1-888-230-1204. They are easier for the donor to type, have higher message throughput, and can be customized to a brand.

However, there are downsides to the short codes. To get a dedicated short code for your company can cost up to $12,000 per year. Many companies share short codes (for instance, a restaurant might have the same short code as a clothing store.). Also, cell phone carriers are getting stricter about short code spam and urging SMS marketers to switch to toll-free long codes.

Conclusion

Text-to-Give and Text-to-Donate both have pros and cons, and the decision of one over the other depends on your use case and budget.

Thanks for reading. If this was helpful, would love to hear from you! And feel free to send me any questions, comments, or corrections.

Additional Reading

How to Find and Fix a Pesky Automation — November 12, 2020

How to Find and Fix a Pesky Automation

The Business Problem

I inherited a mature Salesforce org. I am always finding new surprises – quirks and automations that make my job full of ~wonder~.

Today I ran into this issue with our online donations coming in through Formstack:

The value was supposed to be “Completed,” but an automation (process builder or workflow rule) was changing it back to “Required.” Quelle horreur!

Ah Mon Dieu, Quelle Horreur! GIF - HowDareYou - Discover & Share GIFs

How I Solved It

I created a validation rule that did not allow the status to be changed to “Required.” Here is the logic. The Follow_up__c field is the field that’s labeled “Tax Letter Status.”

AND(ISCHANGED(Follow_up__c),text(Follow_up__c)="Required")

I made the error message “cant change status” and activated the rule.

Then I filled out our donation form on our website.

It gave me this error:

From this I knew exactly which process is interfering.

I removed the node of the Process Builder process that was changing the value back to Required. I knew I could do this because it was redundant – “Required” is already the default value for this custom field on the opportunity.

Then I activated the new version of the process and deactivated my validation rule.

The result was successful:

Notes

  • It’s important to do brute force tasks like this in Sandbox so that they don’t impact the business. That said, I did this test in production because I don’t have a Sandbox version of our donation form. Do as I say, not as I do 🙂
  • It’s also important to test that everything is working just fine after you remove an automation completely (i.e. perform regression testing.)

Conclusion

Any day when an admin discovers something that’s old and obsolete is a good day. Onward!

Analyzing Data from a Multiselect Picklist – Two Options — November 11, 2020

Analyzing Data from a Multiselect Picklist – Two Options

Today I’m writing about how to use multiselect picklists in an effective way using Salesforce or Excel.

The Business Problem

We took a survey of constituents. It integrated into Salesforce and populated a multi-select picklist. Let’s say that picklist was called “Fruits” and showed a survey participant’s favorite fruits.

First mistake was using a multi-select picklist! 😉
Report of results

This report is…okay. We can see that apples are popular by eyeballing the report. But when we try to count how many people selected each fruit, we run into a bit of an issue.

This is the stuff of nightmares.

So we need another way.

Two Solutions

We can solve this issue ~for free~ in two ways:

  1. The first method involves creating formula fields in Salesforce.
    1. Benefit: The data will always be current and accurate.
    2. Drawback: If you have many options and many multi-select picklists, you can run out of fields pretty quickly.
    3. Best for when: You don’t have lots of options, you need to see aggregated data in realtime (in a dashboard or report)
  2. The second method involves Excel.
    1. Benefit: You don’t waste Salesforce fields.
    2. Drawback: You have to do some periodic copying and pasting if you want live data; can’t create dashboards easily, etc.
    3. Best when: there are lots of options, the survey period is over, you just need raw data and don’t need dashboards

Solution 1: Salesforce Fields

The idea is to create a field for each multiselect picklist option, per this help article.

I created a formula checkbox field because they are visually appealing and can be summed easily. The formula is as follows:

if(includes( Fruits__c ,"Oranges"),true,false)

The result is something like this:

We would need one field for each answer choice.

Solution 2: Excel

The second option would be to export the results and use formulas in Excel to populate columns for each answer choice.

First, I export my report results to Excel.

Then, I find all of the multiselect options. I go to the field in Setup and click Printable View. I copy this list using Cmd/Ctrl+C.

Then, I create a new tab in my Excel spreadsheet. I paste the list into that tab.

Next, I go back to the original tab and use the Transpose function in cell C1 to list out the options horizontally across the header row.

=TRANSPOSE(Options!A2:A5)

Then I used a FIND formula in cell C2 to figure out if cell B2 had the word “Apples” in it. This is probably a total kludge, so if there’s a more efficient way to do it please let me know:

=IFERROR(IF(FIND(C$1,$B2)>0,1,0),0)

This is saying that if it finds the word “Apples” in the B column at any position (position > 0), return 1. If not, return 0. I used IFERROR because it gave me an error when nothing was found. This is probably because I am an Excel noob. But hey, it works.

The $’s are there so that I can drag the formula into the other columns and it will still work – the correct row and column “sticks” and other data is dynamic. So here’s the final product:

Now we can do cool stuff like sum them up per column and even create data visualizations.

How bout them apples?

Conclusion

These solutions turn something virtually unusable into something a little less unusable. Enjoy.

PS: I’m pretty sure another option would be to feed this data into Tableau and create formulas that way. But I don’t have Tableau. If someone wants to donate a license to me for the sake of the blog, LMK.

Saving Money with Visualforce Templates — November 9, 2020

Saving Money with Visualforce Templates

Business Problem

My organizations has partnerships with different online education vendors such as Ukueoke, Fender Play, and MasterClass. These companies provide us with In Kind Donations of access codes. Our constituents can then request codes.

In the past, people had requested one code at a time.

However, a unique issue came up in 2019 when a vendor donated 8,000 codes, and constituents could apply for an unlimited number of codes. One person requested over 300!

We needed a way to send teachers codes in a way that would not mean sending, like, a billion emails.

My first attempt at solving this was to build a system in Flow that automatically assigns codes to Contact (more on that in at another time). Then, I created a Conga template that printed all of the Special Codes related to an application.

I needed to use Conga Conductor to send these out in mass (at one point we had about 400 to send out.) This got expensive at ~$1.00 per send.

Data schema spaghetti :-)~

How I Solved It

Using this blog post as a guide (like, amazing? THANK YOU), I went to play in my Sandbox. I created three components:

  1. An Apex Controller
  2. A Visualforce component
  3. A Visualforce email template

Da Controlla

A controller allows us to use code in email templates and Visualforce pages.

NSFW probably?
public class SpecialCodeController

## Gives the thing a name

{
    public Id teacherAppId {get;set;}

## Gives the Teacher Application record a place to live called TeacherAppId and sets permissions/says "welcome!"

    public List<Special_Codes__c> getCodeList()

## Creates a cradle for the baby codes

    {
        List<Special_Codes__c> codeList;
        codeList = [SELECT Name, Type_of_Code__c, Code_Expiration_Date__c, MasterClass_Code__c, FourChords_Code__c FROM Special_Codes__c WHERE Teacher_Application__c =: teacherAppId ORDER BY Name ASC];
        return codeList;
    }
}
##Adds the baby codes to the codeList. We know they're the right ones because of the WHERE clause which references the teacherAppId variable above! Cool!

Visualforce Component

A Visualforce Component takes the code from above and makes it usable! It’s like a widget according to this help article.

This Component is called specialCodeVis.

<apex:component controller="SpecialCodeController" access="global">
## References the controller above

<apex:attribute name="teacherAppIdVis" type="Id" description="Id of the Teacher App" assignTo="{!teacherAppId}"/>     
##Creates an attribute called teacherAppIdVis that references an ID to a Teacher Application that we will add later

<table border = "2" cellspacing = "5">
#This is a table created with in HTML. Brings me back to my Neopets days!
<tr>
<td>Name</td>
<td>Code</td>
</tr>
<apex:repeat value="{!codeList}" var="code">

##An apex:repeat iterates over a list. The variable for each Special Code in the query is "code". That's how we get this table to be dynamic.

<tr><td>{!code.Name}</td>
<td>{!code.MasterClass_Code__c}</td>
</tr>

</apex:repeat>
</table> </apex:component>                                      

Email Template

Finally, we can pipe the Visualforce Component into a Visualforce Email Template! Exciting!

    <c:specialCodeVis teacherAppIdVis="{!relatedTo.Id}" />
##OMG! This references the Visualforce Component and defines that the variable will be the {!relatedTo.Id}**

**Side note: I also learned that the “relatedTo.” tag in Visualforce email templates are a GREAT way to get merge fields many layers deep in an email template using dot notation :o) Good bonus from this project. However these templates can be pretty annoying to edit without admin privileges.

Final Product!

This is the Visualforce email template we built. The table is what we built with this code!
Thanks to our marketing team for providing 🔥 copy

Implementation Notes

  • I deployed the three items above from my Sandbox into production using a change set.
  • Make sure that the Security on the Controller is set so that anyone using the template (or is the running user on an automation that uses the template) is able to access it. After I deployed this, one of my users had a permissions issue and I had to add some profiles to the Controlla.

Conclusion

This will likely save us thousands of dollars in automations (For instance $1 per email x 5 partnerships x 500 emails per partnership = $2,500). It’s worth noting that I built the expensive solution to begin with, though. :’)