how to concatenate lookup fields given certain circumstances

Question

I finished up this batch but came across an unforeseen scenario and can't seem to figure out how to code in the case it occurs. Here is my current code:

global class Batch implements Database.Batchable<sObject>, Database.Stateful, Schedulable {
global String query = 'SELECT Id, Account__c, Book_of_Business__c, Status__c, Book_of_Business__r.Level__c, Book_of_Business__r.Relationship_Owner__c, Book_of_Business__r.Type__c FROM Account_BOB__c ' +
   'WHERE Status__c = Active' +
   'AND Effective_Date__c = TODAY';

public Database.QueryLocator start(Database.BatchableContext BC) {       
    return Database.getQueryLocator(query);
}

public void execute(Database.BatchableContext BC, List<Account_BOB__c> scope) { 
    Map<Id, Account> accMap = new Map<Id, Account>();
    
     for(Account_BOB__c accBOB : scope) { 
         Account acc;
         if (accMap.containsKey(accBOB.Account__c)) {
             acc = accMap.get(accBOB.Account__c);
         }
         if (accBOB.Book_of_Business__r.Level__c == 'External I') {
             acc.Annuity_External_1__c = accBOB.Book_of_Business__r.Relationship_Owner__c;
         }
         if (accBOB.Book_of_Business__r.Level__c == 'External II') {
             acc.Annuity_External_2__c = accBOB.Book_of_Business__r.Relationship_Owner__c;
         }
         if (accBOB.Book_of_Business__r.Level__c == 'Internal' && accBOB.Book_of_Business__r.Type__c == 'Level 3') {
             acc.Annuity_Internal_Rep__c = accBOB.Book_of_Business__r.Relationship_Owner__c;
         }
         if (accBOB.Book_of_Business__r.Level__c == 'Internal' && accBOB.Book_of_Business__r.Type__c == 'Level 2') {
             acc.Annuity_Internal_Rep_2__c = accBOB.Book_of_Business__r.Relationship_Owner__c;
         }
         accMap.put(acc.Id, acc);
     }    
    if(!accMap.IsEmpty())  
        Database.Update(accMap.values(),false);
}

The issue is shown in this screenshot. There are multiple different Book of Businesses with the same level. In this scenario, the corresponding field on Account should show both relationship owners, not just one. These fields are both lookup(user) data type. How should I alter my code so that if there are multiple Book of Businesses with the same Level (and Type) it takes both of the corresponding owners?

Thanks in advance!

Edit- I think the approach I will take if there are multiple options is instead of updating a lookup field, I'll update a new text field to hold the concatenated values. How should I adjust my code accordingly though?

Answer 1

Consider any of these two options:

1) A limitation of Lookup Fields is that they can only hold/accept one record Id. Therefore instead of Lookup you could use a Text field (of adequate length to hold your combined/concatenated values). Then adjust your code to write the concatenates/combined data values to the Text field when needed.

2) Alternatively you could consider keeping the Lookup field model but change how you are viewing the information; for example try using a Matrix Report, and group by datapoints that you see as redundant, as one way to tidy up the display of information.