We have a custom object(Request__c) with lookup to PersonAccount(AssigneeAccount__c) and Account(BusinessAccount__c) similar to AccountContactRelation(ACR) standard object. There is a requirement where, on After Update of Request__c record, I need to query and check if there is any ACR record having same PersonAccount and Account lookup as that of Request__c. If there is any matching existing ACR found, I need to update that ACR with a custom field RoleName__c from Request__c object. If there is no matching ACR found, then I need to insert ACR with the PersonAccount and Account lookup as that of Request__c.
I tried it but as the 2 objects do not have direct parent-child relationship I am ending up in querying in for loop which may hit governor limits. Is there any possible way to avoid this please. The following is the code:
trigger RequestTrigger on Request__c (after update)
{
if(Trigger.isAfter && Trigger.isUpdate)
{
List<AccountContactRelation> acrListToInsert = new List<AccountContactRelation>();
List<AccountContactRelation> acrListToUpdate = new List<AccountContactRelation>();
List<Request__c> requests = [Select Id, BusinessAccount__c , Status__c, RoleName__c, AssigneeAccount__c,AssigneeAccount__r.PersonContactId from Request__c Where AssigneeAccount__c!=null AND BusinessAccount__c!=null AND Status__c='Active' AND Id IN : Trigger.new];
for(Request__c request : requests)
{
List<AccountContactRelation> accountContactRelations = [ SELECT Id, AccountId, ContactId, Roles
FROM AccountContactRelation WHERE
Contact.Id =:request.AssigneeAccount__r.PersonContactId
AND AccountId = :request.BusinessAccount__c ];
if (accountContactRelations.isEmpty())
{
//Create new ACR
AccountContactRelation acr = new AccountContactRelation();
acr.AccountId = request.BusinessAccount__c;
acr.ContactId = request.AssigneeAccount__r.PersonContactId;
acr.Roles = request.RoleName__c;
acr.IsActive = true;
acr.StartDate = Date.today();
acrListToInsert.add(acr);
}
else
{
//Update ACR
for( AccountContactRelation acrRecord : accountContactRelations )
{
String existingRoles = acrRecord.Roles;
List<String> existingRolesList = existingRoles.split(';');
existingRolesList.add(request.RoleName__c);
String newRoles = String.join(existingRolesList, ';');
acrRecord.Roles = newRoles;
acrListToUpdate.add(acrRecord);
}
}
}
if(acrListToInsert.size()>0)
{
Database.insert(acrListToInsert);
}
if(acrListToUpdate.size()>0)
{
Database.update(acrListToUpdate);
}
}
}