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__cthat is a sort of snapshot of all Account Team Member entries
- Have a custom object
AccountTeamHistory__cthat is supposed to store changes to Account Team Members
- Use a scheduled Apex class to daily compare the Account Team Members against the
AccountTeamMemberTracking__cto find changes, additions and deletions and add that as entries to the
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
- 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__crecords, 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...