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 flows 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 fourteen, she has specialized in CRM, email marketing and fundraising platforms. Jessie co-led the Seattle Salesforce Non-Profit User Group in 2015-2016. She wrote a sh*tty first draft of a novel and hopes to do something with it some day.

8 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?

      1. Hi Jessie

        As everybody else I got cut up on others tasks and coming back to this because is the End-of-Year :).
        It will be great if this can be set up for both scenarios: one email a t a time and a list of donors at once.

        The use case will be for the development officer to be able to respond quickly to a donor request but also be able to send a list email to all current recurring donors at the beginning of the year.

  2. This is amazing and exactly what we needed. I have set it up in our org and it works perfectly – apart from one issue that I hope you can help me figure out: I thought the final assignment (Close Table Markup) would ensure that only 1 email will be sent per contact. However, no matter what I try, the recipient receives as many duplicate emails as there are payments. The table in those emails is complete and includes all payments. I won’t be able to use this process if the flow sends out 20 duplicate emails to a given contact instead of just 1. Any thoughts? I would very much appreciate your help.

    1. Hi Alice! Did you install following the instructions or did you install the package? Close Table Markup is just ending the table. It doesn’t have anything to do with sending the email. It sounds like your email is happening inside the loop rather than at the end of the loop. I don’t think I was clear that in Step 9 you draw a line back to the box that started the Loop. That completes the loop. Then draw a line from the Loop to the Assignment in Step 10 and that line should say “After Last Item.” Here’s the package so you can install it and see what it should look like. https://test.salesforce.com/packaging/installPackage.apexp?p0=04t5Y000001AN1g

      1. Many thanks for your reply, I really appreciate it! I have a slightly different use case for this setup – with a schedule-triggered flow and a simpler initial setup (without steps 1-5), so I didn’t install the package, but I did close the Loop as explained in Step 9 (I have the connection from the Loop element to the Assignment and then back from the Assignment to the Loop element. I also added the “After Last Item” connection from the Loop to the Markup Assignment and from there to the Email Action). Happy to share a screenshot if that would be helpful for other users as well.

Leave a Reply to Alice Cancel reply