I’ve already written about DLRS, or Dolores, one of my favorite free tools that gives you rollup summary superpowers. Today I want to tell you about a cool trick you can do with DLRS that kinda’ isn’t a “rollup” at all.
Most of us, when we think of a “rollup summary field,” start thinking in terms of math operations like average, or count, or min, or max. Makes sense. If you think about rolling up information about a group of related records (also called “child” records) onto one that they’re all related to (the “parent”), those are the main uses. I could count the number of support cases that go with an account. Or I could count the number of sessions a student was late for, then count the number of total sessions, then divide the former by the latter to get an attendance percentage. Or maybe I want to sort through all the memberships a family has purchased and roll up the date of the most recent one to know when their membership is going to expire. Or you could count the number of memberships that family has purchased over the years…
But here’s some outside-the-box thinking: You can make a rollup summary field that itself creates a special relationship to just one of the child records. (I know it’s generally frowned upon to single out one of your children for special treatment, but…)
Perhaps your database tracks a price each year:
Campbell Scholars Program wants to be able to give solid college choice advice to their students. So they want to know the tuition cost of any college or university their students are applying to each year.
PA Virtual Charter School is paid by school districts for each student enrolled. But districts have different rates from each other and those rates (of course) also change each year.
In each case we have a custom object,Tuition Cost, that holds child records to Account (representing the college or the school district). For each account for each year there is a tuition cost record. (We also know that there must be just one for each year, so we take other steps—that I won’t outline here—to make sure there is only a single record per account per year.) We have a “year” field on that record that is either itself a date field or it can be translated into a date by a formula field. It’s now very easy to make a DLRS rollup that finds the last tuition record ordered by the date field.
That’s not where the magic comes in. The magic is what we’re doing with what we’ve found. We're not looking at a number field (like the tuition cost) and doing math on it. Nothing of the sort, in fact. We take the Id of that record and we put it into a target field that is, itself, a lookup relationship field.
In the Salesforce user experience when you see a lookup field it shows as a link to the related record. But under the hood that field actually holds the unique Id of the related record, which is 15 characters of text.
So by using DLRS to put an Id in that field, we have actually created a relationship–a link to the “most recent child tuition rate.” Once you have a relationship field like that, you can make formulas that span that relationship.
That means that if a Campbell scholar is interested in a particular school, as soon as we create a college application record (to track that interest) we can show the school’s current cost just by putting a formula field on College Application.
When PAVCS wants to generate an invoice to a school district, the invoice record (child to school district) already knows what the current tuition cost is for that district.
I think that’s very cool and there are lots of ways to use it. Can you think of nifty examples in your org?