0

My organization uses the Salesforce for Wealth Management App, which uses an object called 'Relationship Groups.' Relationship Groups are connected to Accounts (Person Accounts, in this case) via a junction object called 'Relationship Group Member'.

My users regularly log calls/emails/meetings to Accounts, and I would like to see how many times each Relationship Group (aka. group of related Accounts) has been touched in the last 12 months. Can this be done?

I can't even figure out how to get the # of Activities in the past 12 months for a single Account.

And if anyone's gotten this far, I would like to have the activities meet certain criteria (eg. Subject Does Not Contain 'Mail Merge').

Thanks!

2
  • Just to clarify: you have a many-to-many relation betwenn person account and relationship group. And you want to log activities on person accounts grouped be the relationship group!?
    – Saariko
    Nov 7, 2012 at 19:22
  • @Saariko looks like possibility of grand total of emails counted from "groups" being bigger that total of emails counted from "accounts" is the least of problems ;)
    – eyescream
    Nov 7, 2012 at 22:18

1 Answer 1

1

Geez, looks like this Wealth Management app has too normalized database design. This question is not too different from Help with a report and/or Data Loader Export/Query :)

I don't think it can be done with a normal query/report (joined report might work).

I'd like you to consider some other options before you start going too far into the rabbit hole and put rollups everywhere (here rollup can't be used anyway)... for long term scalable solution you'll have to aggregate data before building useful, fast reports. Might not even require coding - check out Analytic Snapshots?


I can't even figure out how to get the # of Activities in the past 12 months for a single Account.

This one is doable.

It's in ActivityHistory table but not accessible with direct queries. This is bad because you can forget about GROUP BY etc. You can however include it in subqueries (that's why I've said it's practically same question as the previous one, there you had subquery of "financial accounts").

SELECT Id, Name, 
    (SELECT Id, Subject FROM OpenActivities WHERE (NOT Subject LIKE '%MailMerge%') AND (CreatedDate >= LAST_N_DAYS:365)),
    (SELECT Id, Subject FROM ActivityHistories WHERE (NOT Subject LIKE '%MailMerge%') AND (CreatedDate >= LAST_N_DAYS:365))
FROM Account
LIMIT 5
3
  • Thanks! Unfortunately, we're so far down the rabbit hole with this app and the person accounts, the only way out would be to restructure the whole org from scratch (which we are in the process of trying to get management to approve). Thanks again! Nov 9, 2012 at 14:51
  • Good luck, you'll need it :) But you do realize that once you went "Person Accounts" there's no turning back, right?
    – eyescream
    Nov 9, 2012 at 15:37
  • Yes, but unfortunately those were both decisions that were implemented long before I started working here. The 'no turning back' is a big part of why we are looking to start a completely new org from scratch and migrate over our existing data. Nov 9, 2012 at 19:20

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .