Since there are way too few options to do literally anything with Account Team Members, we want to use the following approach to track Account Team Member history:
- Have a custom object
AccountTeamMemberTracking__c
that is a sort of snapshot of all Account Team Member entries - Have a custom object
AccountTeamHistory__c
that is supposed to store changes to Account Team Members - Use a scheduled Apex class to daily compare the Account Team Members against the
AccountTeamMemberTracking__c
to find changes, additions and deletions and add that as entries to theAccountTeamHistory__c
I was thinking:
- If I look for Account Team Member records with created date today, I will obviously find insertions of new records that I need to add to the history and to the
AccountTeamMemberTracking__c
. - If I look for Account Team Member records with change date today, I will obviously find those that changed.
To compare them to
AccountTeamMemberTracking__c
, I need a good query for this - If I look for
AccountTeamMemberTracking__c
records, for which there are no Account Team Member records, I detect a deletion of an Account Team Member. For that I need a good query.
I thought I could simply store the Account Team Member Id on the AccountTeamMemberTracking__c
object as a unique case sensitive string, but it appears that the SOQL "IN" only works on IDs for Semi-Joins.
So what I was thinking now is that I could use a combination of UserId and AccountId to find matching records because a user can only appear once on an Account as a Team Member or in other words: The combination UserId & AccountId is unique for all AccountTeamMembers. The problem is, the following query will not give me the expected result:
return [ SELECT Id,
UserId,
TeamMemberRole,
AccountId,
LastModifiedById,
LastModifiedDate
FROM AccountTeamMember
WHERE UserId IN (SELECT UserId__c FROM AccountTeamMemberTracking__c)
AND AccountId IN (SELECT AccountId__c FROM AccountTeamMemberTracking__c)
AND LastModifiedDate >= :system.today()];
The issue here is, that the AND between the UserId IN join and the AccountId behaves more like an OR. It will find me AccountTeamMemberTracking__c
records for which an AccountID and also a UserId exists on AccountTeamMember, but not necessarily on the same record. I did a simple test of that and proved my assumption. If you want, I can past that code here too.
Is there any way I can solve this? I know it is doable in SQL. A colleague of mine proved that. But SQL doesn't help me much in Salesforce. Any help is much appreciated...