2

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!

  • Realised that 2 level nested sub queries are not permitted. – techtrekker Oct 23 '12 at 21:11
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.