Rollup and Report on Who

Here's an interesting challenge. Modern Classrooms Project, who runs a program for teachers, wanted to be able to show, on the account page, counts of various program metrics. For each of those metrics, they also wanted to make it easy for users to see which records went into those counts.

Taken on their own, either request seems pretty easy.

• If you want a count (such as enrolled participants from this school) you can make a rollup summary field, either standard or using DLRS.

• And if you want to see the data that goes into that count, you run a report.

[In theory, a related list could also fill this need. But if we're talking about having filters on the count or different counts with different filters, having filtered related lists for each scenario will get out of hand. Even for only one count, a filtered related list isn't going to be very functional and probably isn't a good use of screen real estate. And finally, assuming a lot of records meet the filter, it's never going to be as convenient to view those in a related list as it would be in a report.]

But together these two solutions don't make for a good user experience. If we show the count on the page, users know the total. But it's not that easy for them to figure out which report they would run to replicate that total. And even if they know the report's name (perhaps we put it in the help bubble), it would be several clicks for them to actually run that report.

Idea! 💡

But we can solve this with two fields:

1. A rollup summary

   and

2. a formula field.

Only the second field, the formula, is going to be on the page layout. The rollup summary field is a helper that stays in the shadows.

The Rollup Summary

Fill the data in this field however you want. If a standard rollup summary field can do for you, great! But if you need relative date filters or other special powers, fill it with DLRS or even a flow. There's no special magic here, just get the metric you need populated into this field.

While I'm at it, let me make a plea for documenting the heck out of this field! If it's a DLRS rollup, my naming convention is to start the API name with "DLRS_". And if the field won't be on page layouts, as these won't be, I also leave "DLRS_" in the field label. Note in the description, "Filled by DLRS," particularly since being in a DLRS doesn't result in an entry in Where is this used? And in this case, I would also recommend noting that it's a helper field for the formula, etc, etc.

The Formula is the Magic

Truly, the magic in my solution comes from the formula field. This is the field you're going to put on page layouts for users. It's going to look like your basic rollup summary field. It's going to show a number. But it's going to actually be a hyperlink. (So I guess it doesn't quite look like a basic number field, since it shows in blue.)

The formula for this field is:

HYPERLINK(

"/lightning/r/Report/Id_of_your_report/view" &

"?fv1=" & Id,

TEXT(API_of_your_helper_field)

)

It's outside the scope of today's blog to go deeply into how that formula is built. But a quick overview:

• It returns a hyperlink, so it will be clickable.

  • In this case it's a hyperlink to a particular report.

• The two bold blue bits are simple enough:

  • the Id of the report you are redirecting users to and

  • the rollup summary to show the number as what this field displays.

• The bold middle line is where the report is getting the Id of the record on which we're displaying this formula put into a filter that's waiting for it. (See this Help article.)

That's it!

Now you can put the fields somewhere on the page and you get a result like this:

Not just numbers, but clickable links to reports about each of those numbers!