Does anyone know what soql queries you would run on Task and Event equivalent to what salesforce uses to generate the Activity History and Open Activities related lists.
The OpenActivity and ActivityHistory views get you this but they don't work for my use-case as I need to perform aggregate queries and I have a custom formula field which shows as null when queried through those views.
In general I know that it is something like the following:
contact/lead task: select id from task where whoid = :recordID history: and isclosed = true open: and isclosed = false account/opp/.../custom object event: select id from event where whatid = :recordID history: and ((isalldayevent = true and activitydate <= today) OR (isalldayevent = false and enddatetime < now)
but there is a lot of special logic. e.g.
- accounts and parent accounts will see activities related to their contacts
- I think there are some nuances around how ActivityDate is handled for recurring tasks/events as well.
- manywho (shared activities) and invitee events need some special handling as well I believe.