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!
Attribution to: jackerman09
Possible Suggestion/Solution #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
Attribution to: eyescream
This content is remixed from stackoverflow or stackexchange. Please visit https://salesforce.stackexchange.com/questions/4153