Filter By Junction Object?

[danmalone89]

Hi, this is my first time using this package, and I have a scenario I'm not exactly sure how to tackle (or if it's even possible to tackle) using apex-rollups. While I've got apex-rollups installed and working in at least a couple scenarios, I'm struggling with one that involves filtering a rollup by a junction object.

Background: We're implementing the new standard data model for Financial Services Cloud. This means we do not have the managed package version of FSC, and so we do not have the out-of-the-box rollups that come with it.

The Use Case

We need to sum the value of accounts belonging to a person, and then also roll those financial account values up to their household. Specifically:

• A FinancialAccount has a Master-Detail relationship to FinancialAccountParty (detail).
• The FinancialAccountParty object has a picklist field called Role.
• We only want to sum the values of Primary Owner (not roles like Beneficiary).

The Core Questions

1. Is there a configuration in apex-rollups that can avoid double counting when multiple FinancialAccountParty records point to the same FinancialAccount?
2. Any guidance or best practices for summing values only when the Role is Primary Owner?
3. Would it be best to roll straight up to the household account level instead?
4. I've tried to use the WHERE clause on the child object query, but this doesn't work: Id IN ( SELECT FinancialAccountId FROM FinancialAccountParty WHERE Role = 'Primary Owner' )

Data Model Overview

flowchart TD
    classDef rollup stroke:#0a0,stroke-width:2px,stroke-dasharray: 5 5
    
    PersonAccount["<b>PersonAccount</b>
        RecordType
        <b><font color='#0a0'>Firm_AUM__c</font></b>"] --> ACR
    
    Household["<b>Household</b>
        RecordType
        <b><font color='#0a0'>Firm_AUM__c</font></b>"] --> ACR

    ACR["<b>AccountContactRelation</b>
        AccountID
        ContactID"]
    
    PersonAccount --> FAParty
    
    FAParty["<b>FinancialAccountParty</b>
        FinancialAccountID
        AccountID
        <b><font color='#ff0'>Role</font></b>"] --> FinancialAccount

    FinancialAccount["<b>FinancialAccount</b>
        <b><font color='#0a0'>Firm_Total_Value__c</font></b>"]
        
    FinancialAccount -.->|"Rollup (<b>FinancialAccountParty</b>.<b><font color='#ff0'>Role</font></b> = 'Primary Owner')"| PersonAccount
    PersonAccount -.->|Rollup| Household:::rollup

Loading

Current Attempt

I have a rollup configuration that mostly works, but it double counts financial account values when there is more than one FinancialAccountParty record related to the same FinancialAccount. Here’s a snapshot:

Example Outcome of the CMDT above

Person/Entity	Account Type	Role	Amount	Individual AUM
John Doe	Individual IRA	Primary Owner	$100,000	✅
John Doe	Joint Account	Primary Owner	$100,000	✅
Jane Doe	Individual IRA	Primary Owner	$100,000	✅
Jane Doe	Joint Account	Joint Owner	$100,000	❌
Household	Total True AUM	-	-	✅ $300,000
Household	Sum of Individual AUMs	-	-	❌ $400,000

As you can see, the total AUM is overstated because the rollup double-counts the joint account for each “Primary Owner” or “Joint Owner” record.

I’d love some help or confirmation on whether this scenario can be solved solely via CMDT configuration, or if it requires a custom approach. Thanks so much for your assistance!

[danmalone89]

@jamessimone Any chance you would chime in on this? This rollup is a punch list item for an org we're launching next month. I've tried this in the where clause without success. Any ideas? Basically, I just need to filter the rollup based on a field of a junction object. Thanks so much! Not sure if this is an issue, a feature request, or I'm just missing something obvious.

Id IN (
    SELECT FinancialAccountId 
    FROM FinancialAccountParty 
    WHERE Role = 'Primary Owner'
)

[jamessimone]

@danmalone89 apologies for the delay in response, I had drafted a response but apparently I never sent it. At the moment, de-duplicating via something like a sub-select isn't supported, but I think if you made a formula field on your child object referring to the Role field on your junction object, you could do the filtering like that. Let me know if that will work for you. Really appreciate you putting this all together, and hopefully that works!

[danmalone89]

@jamessimone Without setting that value on the child object through a flow or similar mechanism, I’m not sure this would work. Since the child object (FinancialAccount) serves as the master in a Master-Detail relationship with the junction object (FinancialAccountParty), doesn’t this inherently limit that approach?

[jamessimone]

Ah, I see. Perhaps I'll introduce a separate where clause field strictly for the one-to-many rollups, as it'll be otherwise difficult to configure. How does that sound?

[danmalone89]

@jamessimone - I deleted my previous comment. I've thought about this a few different ways. It'd be ideal to be able to filter at each junction relationship object in the 'rollup chain'. I think if we can filter at least one, we should be able your solution work. Thank you.