Convert Record Data into Tables for Email Automation With ‘Generate Collection Report’

Suppose you have a set of records and you want to insert them into an email message. To do this, you can use the updated GenerateCustomReport action to generate a chunk of HTML suitable for adding to the Body of an email action that supports rich text, like Send Rich Email.

You pass in a comma-separated list of the fields you want to show in the table. Make sure to use the full api names, including ‘__c’ for custom fields.

You can also decide whether or not to show a Header row with the names of your fields.

You can style your table. The styling support is powerful but primitive. You can pass in a css Style string for each of three sections: the overall Table, the Header, and the Rows. Because style strings are a little fussy, I like to use Text Templates for them.

Here’s a sample configuration:

Here’s an example of styled output on a Flow Screen:

Keep in mind that if you want to provide the data in a form on the flow screen that can be manipulated, selected, sorted, etc, you should instead use the Datatable component. The action described here is mainly intended for when you want to output the data through email or into a Quip page.

Styling

You can add styling information directly to an input, or put it in a Text Template and pass the Text Template into the input. Here’s an example of one of the Text Templates used to generate the above table:

This is conventional css, and everything you can do in a <style> html tag is available to you. For more information on styles you can use, consult a resource like W3Schools.

Usage Note: if you use Text Templates, you MUST change the menu button from View as Rich Text to View as Plain Text before making any change to your text. If you don’t extra html tags will be inserted and your formatting won’t work properly. We’re working on a fix for this.

Using with Email

Finally, here’s the result in an email message:

In this case, I’ve simply routed the output from my action to the HTML Body input of the Send Rich Email action:

Note that you can use another Text Template to provide boilerplate text:

And the email result is:

The key concept to understand is that at the end of the day, a rich email body is just a single string full of html tags. Each instance of this action will spit out one such string, and the text templates also spit out strings. You can combine them together using formulas or another text template (see the video for an example of this) or an Assignment element.

Creating Headers and Parent/Child Reports

You can combine multiple instances of this action to create richer report structures. Here’s an example that uses an output string from an Account record and a second output string from a collection of Contact records:

To accomplish this effect, the HideHeader input is set to true on the parent Account Generate Collection Report action and it’s given a font-size style string to make it larger. On the second Generate Collection Report action that displays the child contacts, the style ‘margin-left’ is applied at the table level (not the row level) to achieve the desired indentation:

See the above video for more information.

Working with Multiple Parent/Child Combinations

You can create a large report in Flow with many Parents that have many children, but you may have to take some steps to avoid triggering the 100 SOQL Queries Limit, because you need to carry out a Contact query for each Account and this normally will all be done in a single transaction. If your parent list is around 100 records or larger, you’ll need to break up your flow from a transaction point of view. To solve this, the flow below uses the ‘new’ Commit Transaction action which forces the transaction to close on each iteration through the loop:

This allows you to create large reports:

Be aware, though, that bypassing the normal transaction unification process in this way can result in flows that take a long time to run.

Installation

This action is part of the Collections Action package and the features described here are available starting in version 1.18 of that package.

Subscribe
Notify of
guest
23 Comments
Inline Feedbacks
View all comments

[…] For full information on this action, see this post. […]

[…] Flow highlights the ability of the Flow Action Generate Collection Report to create attractive output out of lists of […]

Yanir

When I try to install the package (into sandbox) I get the following massage:
This app can’t be installed.
There are problems that prevent this package from being installed.
Mismatching VersionsThe AppExchange Application or component you have selected is not yet available on your instance of salesforce.com. Please check back in a few days to retry the installation. Press the back button of your browser now and bookmark the AppExchange Directory page so that you can find it later.

Any Idea?

Last edited 2 months ago by Alex Edelstein
Yanir

Thanks a lot ! That will be awesome! 🙂
I currently have a use case for this ready to be implemented.

Thanks again! The work on the components developed and shared in this site makes me a lot more capable as a consultant.

Bobby K

I am in the same boat as Yanir, have been anxiously awaiting to get my hands on this but still no luck as of right now installing in Spring ’20 sandbox or prod unfortunately

Yanir

Hi Alex, tried to download the package again but without success from this link: 1.18.1 Unmanaged (Improvements to Generate Collection Report (6/15/20)

Yanir

This Time I was able to download the package. I will try soon to use it and will let you know how it was (I’m excited! 🙂 ).
I was having a problem download the package at first – but I was because of a different error (something regarding another apex class I think), So I downloaded the package in advanced mode and compiled only this package apex classes and it was successfully installed. I just hope I will not have problems when I will try to deploy.

Thank You very much Alex!

Bobby K

Thanks, Alex! Works now appreciate everything!

Yanir

Hi alex, There is a small bug regarding Custom Fields on the Shown fields – I think the apex is removing the “__” before the c. you can look at the flow error I’m getting:
GENERATECOLLECTIONREPORT (APEX): Active_DE_Table
Inputs:
displayMode = table
inputCollection = {!Active_Delivery_Junctions} ([Delivery_Junction__c (a1CL0000004x4dDMAQ),Delivery_Junction__c (a1CL0000004x0fNMAQ)])
shownFields = Name,OwnerId,State__c

Error Occurred: An Apex error occurred: System.SObjectException: Invalid field Statec for Delivery_Junction__c

Yanir

It also remove the “.” with relationship fields (I don’t know if it will work regardless to this issue… but I hope it will, I have a lot of lookup fields that I Prefer to show the name and not the record ID):

GENERATECOLLECTIONREPORT (APEX): Active_DJ_Table
Inputs:
displayMode = table
inputCollection = {!Delivery_Junctions.sObjects} ([Delivery_Junction__c (a1CL0000004x4dDMAQ),Delivery_Junction__c (a1CL0000004x0fNMAQ)])
shownFields = Name,Owner.Name

Error Occurred: An Apex error occurred: System.SObjectException: Invalid field OwnerName for Delivery_Junction__c

Bobby K

Hi Alex – I was having the same problem this morning as custom fields were having underscores removed. I’m not a developer so take it for what its worth, but I tweaked the below line of code in the GenerateReportCollection class in my sandbox which seemed to correct my issue.

Thanks for all your work on this!

 List<String> shownFieldsArray = shownFields.replaceAll('[//s+]', '').split(','); //previously ^a-zA-Z0-9//,
Yanir

Thanks Bobby!
I Changed the code as you did and it Worked.
I did encounter a problem with some fields for example: Product_Component_Version__c
I think it some how related to the name of the field because I having this problem with this field also:
Product_Component_Version_State__c

Any Idea?

Yanir

Definitely Related to the field name!
I changed the field name to:
PCV_ID__c and then it worked fine.

MORGAN FOLEY

Is there any way to add cell level formatting? I’m trying to add some padding, and I can’t find anything that works. Though it might be because I’m just trying to output it on a flow screen? Seems like according to the example above, the flow screen doesn’t respect the border attributes.

MORGAN FOLEY

I actually was referring to cell padding, rather than putting padding around the entire table.

Kristina Williamson

Love the concept of this and have so many use cases. My users want to scrape related lists all the time for their management reports and documentation. Cut-n-paste isn’t friendly. Now I can make HTML output for them to take away. I have an issue with adding numbers and currencies fields as columns in my table. I am trying to table Opp Line Items (ProductCode,Description,Quantity,UnitPrice,TotalPrice) for an Opportunity set as the recordId input parameter. Runtime Error I get is this: Error Occurred: An Apex error occurred: System.TypeException: Invalid conversion from runtime type Decimal to String . Is there a trick to… Read more »

Dan Thompson

Hi,
Any way to change the way the header titles are generated? Looks like it is using the API names as entered in the ShownFields input. Could we use the name of the field or a separate input for the header columns?