Rollup picklist or text values?

[afawcett]

Some requests via my blog for this, something to ponder...

[wes1278]

Would LOVE this as a feature. Hard to quantify the parameters that determine text based roll ups.

[cropredy]

Something as simple as equivalent to String.join(fieldname in child,delimiter) would be a great start

[afawcett]

Thanks @cropredy, just so i can understand this better, are you able to share a little bit about your typical business use cases for this?

[cropredy]

Yes Andy -- The typical use case is to provide convenience to reports, export-to-excel, and views. For example, if you have a parent - child relationship and the child has some text field: textField__c, it is very handy for reports/list views on parent to have a simple column that is the concatenation of all children textField__c delimited by commas.

Why convenient? List views can't should detail records at all and reports, all they can show detail records, will display as one line per detail record and hence parent data is duplicated per row. This makes for exports to excel confusing to non-regular SFDC users..

The summarized child field would typically be read only on a standard page layout. As an added benefit, it can save scrolling down to the bottom of a detail page to see some pertinent information

While it is true that you could have hundred/thousands of details underneath a parent and the text rollup could blow up the size of the rollup field but for most use cases I've run into, the number of summarized children is < 5. Even if the rollup field blows up in size, DLRS could simply detect and terminate by ellipsis.

[afawcett]

Awesome background thanks!

[afawcett]

Ok, i've made a start on this, sadly didn't make todays release, but hopefully next weekend!

[afawcett]

I'm thinking of adding two new fields to the rollup definition.

Concatenate Delimiter is a text field, and allows specification of the delimiter to be used
Concatenate Distinct is a checkbox, and will remove duplicates before concatenating the values together.

I have also considered the following use cases, let me know your thoughts...

Scenario 1
Field to Aggregate: Text / Picklist
Aggregate Result Field: Text / TextArea
Aggregate Operation: Sum
Concatenate Distinct: False
Concatenate Delimiter: ,
Result: Text concatenated together separated by delimiter and duplicates removed (ellipses applied in case of overflow)

Scenario 2
Field to Aggregate: Text / Picklist
Aggregate Result Field: Text / TextArea
Aggregate Operation: Sum
Concatenate Distinct: True
Concatenate Delimiter: ,
Result: Text concatenated together separated by delimiter and duplicates removed (ellipses applied in case of overflow)

Scenario 3
Field to Aggregate: Picklist / Multi Picklist
Aggregate Result Field: Multi Picklist
Aggregate Operation: Sum
Concatenate Distinct: True (implicit)
Concatenate Delimiter: N/A
Result: Mult Picklist represents summary of Picklist / Multi-Picklist items

Scenario 4
Field to Aggregate: Text / Text Area / Picklist / Multi-Picklist
Aggregate Result Field: Text / Text Area / Multi-Picklist
Aggregate Operation: Max
Concatenate Distinct: N/A
Concatenate Delimiter: N/A
Result: Takes the last record in the list (ORDER BY is by Field to Aggregate) and copies Field to Aggregate to Aggregate Result Field

Scenario 5
Field to Aggregate: Text / Text Area / Picklist / Multi-Picklist
Aggregate Result Field: Text / Text Area / Multi-Picklist
Aggregate Operation: Min
Concatenate Distinct: N/A
Concatenate Delimiter: N/A
Result: Takes the first record in the list (ORDER BY is by Field to Aggregate) and copies Field to Aggregate to Aggregate Result Field

Scenario 6
Field to Aggregate: Text / Text Area / Picklist / Multi-Picklist
Aggregate Result Field: Text / Text Area / Multi-Picklist
Aggregate Operation: Avg / Count
Concatenate Distinct: N/A
Concatenate Delimiter: N/A
Result: Error not supported

[afawcett]

Sorry Scenario 1 should have been... Result: Text concatenated together separated by delimiter and duplicates not removed (ellipses applied in case of overflow)

[JodieM]

For the concatenate delimiter, how would I enter a BR()? Business case: We have a receipt that is sent out for tickets purchased at an event. On the receipt we would like to list the attendees paid for (attendees are in a related object) . They should be in a list with each name on a separate line.

Bonus points for sorting the values before they are concatenated.

On Scenario 6 there may be a reason to count the number of items in a multi select picklist.

If you have a sum of multi select picklists in Scenario 3, why can't you have an average (scenario 6) also? Or have I misunderstood scenario 3 - are you saying if the picklist items contain values that can be converted into numbers, then sum all the numbers? Then in that case, average and count would be useful also.

[JodieM]

And I'm assuming that all these will still work with the condition field? So you can rollup all "children" into a comma separated list where the status is closed.

[wes1278]

@afawcett I think we need to be able to separate what we order by from what we want to rollup.

Use case is this:
I want to rollup some text value in some_field__c for the latest created date child record.

Something like "Latest Note" field

Make sense?

[afawcett]

@JodieM Thanks for the feedback, very useful. Yep sorting will be included, and yes, i'll figure a good way to allow for line breaks as delimiters. Regarding sum vs average, i am really using 'sum' in a text aggregate context to mean 'Concatenate'. Though your thinking that this implies some type conversation to number might suggest that going this route would confuse users. I'll have a think....

@wes1278 Yes, was wondering about that, and thanks for the reminder about your use case, now that you state it, i recall it from another idea raised.

[afawcett]

Ok decided to add new operations in the end, rather than re-interpret how the existing ones would work, which in the end felt confusing for the end user. Hopefully what i've got now is easier to understand. Note that i've added Count Distinct as part of another enhancement going into the next release.

I've taken some screenshots, how does this work for everyone?

[wes1278]

Looks good to me. Only thing I might have done differently is to make concatenate distinct another aggregate operation instead of a checkbox for consistency. But I'm good either way. Thanks so much for working on this!!! Exciting

[afawcett]

Thats a great idea, will adjust!

[JodieM]

Looks great Andrew. Thanks. I do like the the picklist showing all the
different options, it makes it much easier to understand. And entering BR()
in the delimiter field works well too. Fabulous!
On Feb 13, 2015 3:54 AM, "Andrew Fawcett" [email protected] wrote:

Thats a great idea, will adjust!

—
Reply to this email directly or view it on GitHub
#76 (comment)
.

[afawcett]

Ok coding and unit tests done, going out for a mo, will do some final smoke tests when i get back, if all still good, i'll release today!

[afawcett]

Added in version 1.18, see README for more.

[dkdolch]

I absolutely love the Concatenate function. I am able to do something that would normally require a trigger. My only issue is that I cannot get it to work with a text field larger than 255. Is this possible?

[afawcett]

Is this related to the size of your target field? There is no restriction i am aware.

[RussAtlanta]

Hi There....I am basically new to Declarative Rollups and have found it great for all my numerical rollups in Salesforce.
However, I'm trying to create a new concatenated rollup and have run into a few issues:

1. I was able to finally get a text rollup to work but needed to add a BR() delimiter. Now it will not update the parent object field to create the line breaks, or any update at all. I've removed the Run the Calculation Job ID and ran it again. No luck. Here's my rollup:
   
   Any ideas?

2. When the rollup is Active, if I try to add a new child record, it gives me an error message,
   Error: Invalid Data.
   Review all error messages below to correct your data.
   Apex trigger dlrs_Managed_Premier_SVCsTrigger caused an unexpected exception, contact your administrator: dlrs_Managed_Premier_SVCsTrigger: execution of AfterInsert caused by: System.QueryException: unexpected token: 'ORDER': Class.dlrs.LREngine.QueryExecutor.query: line 561, column 1
   

If I deactivate the rollup, the record saves fine.

This is a great product and I'm finding lots of ways to use it...now if I can only get the text rollup to work correctly, I'll be ecstatic!! Thanks for your work!!

[RussAtlanta]

I'm not sure what happened, but just for giggles, I deleted the whole rollup and recreated it. Now everything works fine....no errors, I can even save new records when active. My apologies.
However, maybe I'm missing something...I have the rollup set to RealTime but if I change a child record, the text rollup doesn't flow to the parent object unless I Run the Calculate Job again. Should the parent object automatically update when the child record is saved?

[WhatTheHelal]

Is it possible to concatenate different fields together? For example; I have Parent A and Child B. I want the most recent Child B record which has 3 text fields; I want to find the most recent child B record and combine 3 fields to display on the parent. I realize I could either do 3 different lookup summaries or I could make a formula field on the child that combines them and then have 1 lookup summary take that value and write it to the parent. Just wondering if it can be achieved directly with DLRS in 1 lookup summary.

[klbee16]

Is it possible to create a DLRS that will provide a count for the number of times a certain picklist value was selected on the child object? Here's the use case - for a foundation I'm trying to create a "voting" feature. They use Funding Request object for grant information and a Reviews object which are related via a Lookup relationship. We have a Vote picklist on Reviews, with the options Yes, No, Maybe and I want rollups on the Funding Request (parent) that count the number of Yeses, Nos and Maybes. Is that possible?

[aheber]

@klbee16 that isn't currently supported. It has come up a few times in the past but hasn't made it to the top of any feature requests.

I have explored doing it with the tool, it would take a lot of code changes but I believe it is possible. I don't have the ability to give you any clear timeline when it could happen.

[klbee16]

I was able to get this working using the COUNT function and filtering by the picklist value (so essentially counting the number of records that are returned when we filter by the picklist value). It seems to work fine except now the client wants to add another filtering condition! I'm sure it'll work out, it's just - you think you're done then you demo it...