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.

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.