Dear Community

Since there are way too few options to do literally anything with Account Team Members, we want to use the following approach:

• 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 the AccountTeamHistory__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.

I had the following in place (incomplete):

global class AccountTeamMemberTracking implements Schedulable
{
	@TestVisible private map<Id, AccountTeamMember> accountTeamMembers;
	@TestVisible private list<AccountTeamMemberTracking__c> trackingRecords;
	@TestVisible private list<AccountTeamHistory__c> accountTeamHistoryRecords;
	
	global void execute(system.SchedulableContext sc) {
		accountTeamMembers = new map<Id, AccountTeamMember>(queryChanges());
		trackingRecords = queryTrackings(accountTeamMembers.keySet());
		accountTeamHistoryRecords = new list<AccountTeamHistory__c>();
		handleChanges();
		
		trackingRecords = queryDeletions();
	}
	
	@TestVisible private list<AccountTeamMember> queryChanges() {
		return [SELECT	Id,
						UserId,
						TeamMemberRole,
						AccountId,
						LastModifiedById,
						LastModifiedDate
				FROM AccountTeamMember
				WHERE Id IN (SELECT AccountTeamMemberId__c FROM AccountTeamMemberTracking__c) AND LastModifiedDate = :system.today()];
	}
	
	@TestVisible private list<AccountTeamMemberTracking__c> queryTrackings(set<Id> accountTeamMemberIds) {
		return [SELECT	Id,
						TeamRole__c,
						TeamMemberId__c,
						AccountTeamMemberId__c,
						AccountId__c
				FROM AccountTeamMemberTracking__c WHERE AccountTeamMemberId__c IN :accountTeamMemberIds];
	}
	
	@TestVisible private list<AccountTeamMemberTracking__c> queryDeletions() {
		return [SELECT	Id,
						TeamRole__c,
						TeamMemberId__c,
						AccountTeamMemberId__c,
						AccountId__c
				FROM AccountTeamMemberTracking__c WHERE AccountTeamMemberId__c NOT IN (SELECT Id FROM accountTeamMember)];
	}
	
	@TestVisible private void handleChanges() {
		for ( AccountTeamMemberTracking__c atmt : trackingRecords ) {
			// add the history records
		}
	}
}

I receive errors on line 17 and 37:


So what I was thinking now is that I could use a combination of UserId and AccountId to find matching records for a user can only appear once on an Account as a Team Member.  The problem is, I don't know how to do that. I would try to go for following, but I don't know whether they get the result I need:

@TestVisible private list<AccountTeamMember> queryChanges() {
		return [SELECT	Id,
						UserId,
						TeamMemberRole,
						AccountId,
						LastModifiedById,
						LastModifiedDate
				FROM AccountTeamMember
				WHERE UserId IN (SELECT TeamMemberId__c FROM AccountTeamMemberTracking__c) AND AccountId IN (SELECT AccountId__c FROM AccountTeamMemberTracking__c) AND LastModifiedDate = :system.today()];
	}

	@TestVisible private list<AccountTeamMemberTracking__c> queryDeletions() {
		return [SELECT	Id,
						TeamRole__c,
						TeamMemberId__c,
						AccountTeamMemberId__c,
						AccountId__c
				FROM AccountTeamMemberTracking__c WHERE TeamMemberId__c NOT IN (SELECT UserId FROM accountTeamMember) AND AccountId__c NOT IN (SELECT AccountId FROM accountTeamMember)];
	}

Does the AND here work as I need? I need to know the Account Team Members that have their UserId and AccountId matching on the same record of an AccountTeamMemberTracking__c. What I fear my code does is to check whether the UserId exists in the AccountTeamMemberTracking__c and to check whether the AccountId exists there, but not necessarily on the same record, which would be not what I want.


I hope I wrote that understandable. If you have any questions, please don't hesitate to ask.