[SalesForce] Help with a report and/or Data Loader Export/Query

I am currently use the Salesforce for Wealth Management managed package (unfortunately), and I am struggling to export or report on some of the data.

The model is set up like this:

  • 'Relationship Groups' are related to the 'Relationship Group Member' junction object via master-detail with 'Relationship Group Member' is the child

  • 'Account' is related to the 'Relationship Group Member' junction object via master-detail with 'Relationship Group Member' as the child

  • 'Financial Accounts' are related to 'Accounts' via master-detail (Account is parent)

I need a report/export/query that will give me:

  • All Relationship Groups WHERE RG_Status__c == 'Active' AND Financial_Rollup_Group == 'Yes'

WITH

  • All Financial Accounts WHERE FinAcct_Status__c == 'Open' AND the Relationship Group Member whose Account is related to the Financial Account has the field 'Include in Rollup'==True

A crappy diagram might look like:

Relationship Group (Status=='Active', Financial Rollup=='Yes')

V

Relationship Group Member (IncludeInRollUp==True)

V

Account

V

Financial Account (Status=='Open')

I know this is complicated, but any help on creating this as a report or as a SOQL query that I can run from the data-loader would be much appreciated! Please let me know if you have any questions! Thanks!

EDIT: To clarify (maybe), the end-goal here is to have a report that tells you how many Financial Accounts are associated with a Relationship Group. Thanks again!

Best Answer

I think you'll have to cheat a bit on that one...

  1. Make a rollup summary field on Account that would calculate count of "Financial Accounts". Use the optional filter options (on the bottom of the field wizard page where you select COUNT/SUM/MAX etc) to filter them by Status=Active.
  2. This field on account should now become accessible in any kind of reporting that relies on "Relationship Group Member" - since it's a junction object you'll probably be even able to pull it on the related list of "rel group members" under "Relationship Groups".
    • If for some reason you can't - cheat by creating a formula field on the junction object that would point to the Account
    • or maybe just create a custom report type and "add fields via lookup". But I think it will work out of the box
  3. That's it, should be reportable/queryable now? Apply rest of the filters you need.
Related Topic