Send Emails with Tables in Flow – NPSP Example with Payments

This post is building on the work of the super-smarty Narender Singh aka ForcePanda aka @Nads_P07. With Spring ’21 rich text emails, you can now send tables with lists of child objects.

I followed his tutorial and built two Flow’s specifically using Nonprofit Success Pack (NPSP) objects. The first one is a list of payments and the second is a list of completed volunteer activities. I’ll blog about the second one later. Also on the to-do list is to make one that lists all the gifts received in memory or in tribute to someone else.

List of Payments

  • If you like, install the Flow directly into your sandbox from this unmanaged package. The package currently doesn’t work in a trial org of NPSP.
  • This trigger flow will fire when a payment is marked paid.
  • A decision looks to see if this is the only payment on the opportunity. If so, an email will be sent to the primary contact on the opportunity with a list of all payments.

How to Build It

Step 1. Start a new record triggered Flow that fires when a record is updated and after the record is saved.

Step 2. Choose the Payment object (only in Nonprofit Success Pack) and set your conditions to Paid = true.

Step 3. Use Get Records to get the opportunity associated with the triggering payment. To do this, under Value scroll down to Global Variables and click on $Record. Search for the opportunity field. Automatically store all fields.

Step 3a. Add a Decision Element. Let’s see if the opportunity record’s primary contact even has an email! No point in doing any of this if we can’t email the person. As your resource select the Opportunity from getOppRecord, then find Primary Contact, then find Email.

Step 4. Add a Decision Element. From the opportunity record that you just got, select the field Number of Payments. Set that to be greater than 1. We don’t want to fire this email when there is only one payment.

Step 5. Get all other payment records associated with this opportunity using a get records element. Sort them by Scheduled Date or Payment date or whatever feels appropriate.

Step 6. Create a table variable. This variable contains the code needed to start the table. I’m changing what Narender did in this step. I’ve consolidated his steps 3,4,5 into one.

Set Default Value to equal:

<table style="width:100%; border: 1px solid black;">
<tr>  
<th style="border: 1px solid black;">Amount</th>  
<th style="border: 1px solid black;">Scheduled Date</th>  
<th style="border: 1px solid black;">Payment Date</th>  
<th style="border: 1px solid black;">Status</th> 
</tr>

Each of the words or phrases here “Amount,” “Scheduled Date,” etc will be column headers. To add in additional columns, copy and paste one line of <th…. to /th> and add in the name of that column!

Step 7. Start your loop of the payment records we got in Step 5.

Step 8. Create a Text Template called tableRowTextTemplate. This is an improvement on Narender’s process again. Instead of using a formula, we use a Text Template. That way the date and currency formats come out just beautifully. I love Text Templates!

Toggle to “View as Plain Text” and paste in this code:

<tr><td style="border: 1px solid black;">${!loopOverPayments.npe01__Payment_Amount__c}  
</th><td style="border: 1px solid black;">{!loopOverPayments.npe01__Scheduled_Date__c} 
</th><td style="border: 1px solid black;">{!loopOverPayments.npe01__Payment_Date__c}  
</th><td style="border: 1px solid black;">{!loopOverPayments.Payment_Status__c}  
</th></tr>

Each row actually represents one column. So the first column will have the payment amount, the second column will be scheduled date, etc. This should match up with our column headers in step 6.

Step 9. Inside your loop add an assignment! For each payment record in the loop, you’ll add one row to your table variable based on the Text Template. It will keep growing and growing for each payment.

Step 10. After your loop, you need to finalize the table so add another assignment. You add one tiny piece of code </table> to the table variable to tell it the table is done.

Step 11. Create a formula AmountPaid, for the total of all their payments. I tried just using the roll-up field from the Opportunity, but it is calculated after the Flow. I use a datatype of currency, and I am taking the roll-up field and adding the amount of the current payment that triggered the flow. An alternative way to do this would be to add the payment amount in the loop to a currency variable and total it up as it goes through the loop with an assignment.

{!getOppRecord.npe01__Payments_Made__c}+{!$Record.npe01__Payment_Amount__c}

Step 12. Create a formula RemainingBalance. Again, I can’t just use the Rollup field for Amount Outstanding because that is calculated after the loop. (Thanks order of operations!)

({!getOppRecord.npe01__Amount_Outstanding__c}-{!$Record.npe01__Payment_Amount__c})

Step 13. Create the email body with another delightful text template. Let’s call it emailBody.

Dear {!getOppRecord.npsp__Primary_Contact__r.FirstName},

Thank you for your recent payment of ${!$Record.npe01__Payment_Amount__c} on {!$Record.npe01__Payment_Date__c}.

{!table}

Amount Paid: {!AmountPaid}

Amount Outstanding: {!RemainingBalance}

Thank you for your support.

Step 14. LAST STEP! WE MADE IT! Add SendEmail Action. In Body, select emailBody. Under email addresses, you go getOppRecord —> Primary Contact –> Email. Toggle Include to on! Here’s the biggest gotcha of the whole thing. Under “Rich-Text Formatted Body” you have to select the Global Constant True and toggle to “Include” to be on. If you want to send the email from a specific email address that isn’t the your user, read this blog post.

Thanks, Narender, for tagging me in your tweet about this. It’s been really fun building this! I hope to share the two other NPSP example soon: Volunteer hours as requested by Russ Feldman on Twitter and memorial gifts requested in this Hub post.

Published by

JessieRymph

Jessie is Success Content Specialist at Salesforce.org. All opinions expressed on this blog are her own or those of the contributors. For twelve years, she has specialized in CRM, email marketing and fundraising platforms. Jessie co-led the Seattle Salesforce Non-Profit User Group in 2015-2016. She's working on writing her first novel.

3 thoughts on “Send Emails with Tables in Flow – NPSP Example with Payments”

  1. Jessie this is amazing. Thanks for sharing. Which modifications to this flow need to be made to send a similar email to recurring donors – summary of all recurring gifts received on the previous FY (Jan-Dec Fiscal Year). Ideally the flow will need to be triggered by a button or a screen

    1. Yes!! This is on my to-do list. Thank you for the nudge. You want it to be one- like send one email at a time or to get all of your donors at once?

Leave a Reply