Rollup through junction object

[mhogg-srg]

I have a situation where I'm using a junction object to create a many-to-many relationship between two objects. Let's call the main objects A and B and the junction J. I'd like to take a values from A and roll them into B. Is it possible to do this without first creating formula fields on J that output values directly from A?

[jamessimone]

@mhogg-srg converted this to a discussion, though I may re-create the issue should the need arise. Let's talk about the situation here, and what you're looking to do.

Let's say you have 5 records for Object A, with 5 junction records in J. They're all linked to one record in Object B. That's the situation we're referring to, yes? If I'm understanding you correctly, you'd like to be able to roll up the values from A -> J -> B. That should be possible using a grandparent rollup, though I have not tested this with a many-to-many junction previously. Have you checked out the wiki article for this?

Again - it's possible there are some edge-cases not considered in the existing grandparent rollup framework, and I'm happy to get an issue going for those after chatting with you here, but let's first try out using the grandparent rollups. If it doesn't work, it would be great to enable Is Rollup Logging Enabled on your Rollup Control record and get the Apex Debug Logs generated from attempting this attached here.

Looking forward to your response!

[mhogg-srg]

I'm thinking that probably my original idea of putting formula fields on J is the simplest and most pragmatic way forward. However, this conversation may be fruitful.

The obstacle I'm running into with your proposed solution is Lookup Field on Calc Item. Since J is a child of A, there's no lookup field on A to select.

[jamessimone]

Ah of course. Ok, let me think about this — you may be right that short-term the formula fields are the way to go, but I’ll definitely be able to support your original use case in the near future

[jamessimone]

@mhogg-srg I have a prototype version of this working locally. I am going to keep the Lookup Field On Calc Item a required field, since it will be for the vast majority of rollup use-cases; for this specific case, you can use any field there since it won't really be looked at. Here's my proposal:

• fill out the Grandparent Relationship Field Path
• also fill out a new field, One To Many Grandparent Fields

As an example, I've created a test that tests going from Account -> Contacts -> Individual (totally contrived example):

• my grandparent field path is: Contact.Individual.Name
• my one to many grandparent field (which can be a comma-separated list if you need to hop through multiple junction objects) contains the object name and the field API name: Contact.AccountId

How does that kind of mapping sound to you?

[mhogg-srg]

That solution makes a lot of sense. The one thing I find a bit unintuitive is using Contact.Individual.Name rather than Contacts.Individual.Name (notice the s), which is the actual name of the relationship going from Account -> Contact. Using Contact.AccountId in the One to Many Grandparent Fields does make sense to me since it's naming the object itself.

[jamessimone]

It is unintuitive, I agree - I can update that to use the relationship name. It's a bit more code, but it will be more intuitive as to how the grandparent field path should be filled out. Thanks so much for the feedback! I will create an issue to track this and tag you in it, along with #283.

[mhogg-srg]

@jamessimone I can't figure out what I'm doing wrong. When I run Recalculate Rollup with this, the job completes successfully, but none of the values are updated. Field_Leadership__c is the junction object with two parent-detail relationships: Leader__c to Contact, and Field_Sector__c to Team__c. Any recommendations?

[jamessimone]

@mhogg-srg I don't think you're doing anything wrong - I need to check on the Recalculate Rollup app as there seems to be something unexpected happening there. Give me a few hours to address this!

[jamessimone]

@mhogg-srg just to confirm - in your grandparent relationship field path, is Field_Sector__r a relationship field on your Field_Leadership__c junction object?

[mhogg-srg]

That's correct.

…

On Mon, Apr 18, 2022, 2:04 PM James Simone ***@***.***> wrote: @mhogg-srg <https://github.com/mhogg-srg> just to confirm - in your grandparent relationship field path, is Field_Sector__r a relationship field on your Field_Leadership__c junction object? — Reply to this email directly, view it on GitHub <#282 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/APGWZ2RO4I7B55ATNO7G7LDVFWPY7ANCNFSM5S455URA> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[jamessimone]

@mhogg-srg I'm generating a beta package with some updates, with the intent to release the new version tomorrow. If you're doing any of this testing in a sandbox, you should be able to access the updated Readme with the beta package link in it within the next few minutes. Note that the above link will cease to function once I've merged the changes to Apex Rollup's main branch (which, again, I'm planning to do as part of getting this release out the door tomorrow).

If you are able to test before I come online again, great; otherwise, I'll wait a few hours tomorrow prior to releasing.

[mhogg-srg]

@jamessimone It worked!

[jamessimone]

@mhogg-srg great! I'm going to go ahead and cut a release shortly.