Multi-picklist as Lookup Field on Lookup Object

[mhogg-srg]

I'd like to use the type of multi-picklist for the Lookup Field on Lookup Object, with CONCAT_DISTINCT as the aggregate function. My thinking is that this will be more intuitive for users when they are filtering views and reports.

First, am I overthinking this?

Second, I already tried this out, and it partially works! I figured it would, because Apex treats multi-picklist values as semicolon delimited text. Unfortunately, the values don't come out quite right. Every value comes out with the first multi-picklist choice along with the correct values.

I tried modifying the rollup definition, leaving all the options exactly the same, but making Lookup Field on Lookup Object a text field instead. That worked perfectly.

Is there something else I can adjust to make my concept work, or should this be an enhancement request?

[jamessimone]

@mhogg-srg lets treat this as a feature request; I’ll need to update the way concat operations work to fix the issue you were seeing (where the first value gets appended).

[jamessimone]

@mhogg-srg I can include this in the upcoming version, which I expect to be finished with by EOW. please also see my comments on #282 for more!

[mhogg-srg]

That is great, thank you!

[jamessimone]

@mhogg-srg I think I need a bit more clarification from you on what you're looking for, now that I've had time to review this. You can already select a MSP field as the Lookup Field On Lookup Object, so I'm not sure why you would have needed to change the type of the underlying Rollup CMDT to accommodate that part. Secondly - if there are multiple entries for a "lookup field" that's MSP separated by semi-colons ... are you expecting a rollup operation for each parent in that "list"?

Perhaps bulleting out what the child -> parent object structure is with some "example records" (IE - the op field on the calc item, the lookup field on the calc item, as well as the op field on the lookup item and the lookup field on the lookup item) and the expected result would help to clear this up; alternatively, you can definitely enable Is Rollup Logging Enabled on the Org Default Rollup Control record and send over an Apex log generated by Apex Rollup from the operation that you're describing so that I can see what's going on under the hood.

Thank you! Depending on the complexity of what you're looking for here, this may occur next release (probably next release) as opposed to this upcoming one to fix #287

[mhogg-srg]

@jamessimone Setting up a rollup to the MSP isn't the issue. The problem is that I'm getting incorrect values. The first picklist value option always gets included in the result. Let's suppose that I have value options are A, B, C, and D, and the correct value for 4 records would be

B
C
B;D
A

The values that I actually get are:

A;B
A;C
A;B;D
A

To (hopefully) make myself more clear. If I changed the order of the value options to D, A, B, and C, the values I'd end up with would be:

D;B
D;C
D;B
D;A

[jamessimone]

@mhogg-srg my suspicion is that the "first" value you're describing was already one of the values on the parent-level field, but I am making a change in v1.4.13 (to be released today) that should alleviate the issues you're describing here.