Query Account team and concatenate results

Question

My requirement is to create a new record that has data from Opportunity, Account, Product, OLI Account Team, and Opportunity Team. I have the code written so that in a for loop I query for the OLI records and then use that to create a Revenue Pipeline record.

I'm unsure how to get all the Account team members and where. Do I add as another action in the for loop? and once I get a list of the team members how can I concatenate them into a string together, do you have to count the number of records returned and then use that to space the results out? If my query returns:

Tom Dick Harry

I can get to 'Tom, Dick, Harry' so I can populate a text field.

Here is what I have thus far:

public with sharing class RevenuePipelineHandler {
public static void createRevPipeline(Set<Id> oppyIds) {
    List<Revenue_Pipeline__c> revPipeline = new List<Revenue_Pipeline__c>();
    String revType;

    for(OpportunityLineItem oli:[SELECT Id,
                                        Start_Date__c, 
                                        End_Date__c,
                                        TotalPrice,
                                        Is_Fund__c,
                                        OpportunityId,
                                        Opportunity.AccountId,
                                        Opportunity.Account.Account_BU_2__c,
                                        Opportunity.CurrencyIsoCode,
                                        Opportunity.Practice__c,
                                        Opportunity.Cash_or_Barter__c,
                                        Opportunity.StageName,
                                        Opportunity.Sales_Order_Approved_Date__c,
                                        Product2Id,
                                        Product2.ALR_Retailer__c,
                                        Product2.Product_LOB__c,
                                        Product2.Product_Type__c,
                                        Product2.Family,
                                        Product2.Product_Group__c,
                                        Product2.Product_Offering__c
                                        FROM OpportunityLineItem WHERE OpportunityId IN :oppyIds])
    {
        if(
            oli.Opportunity.StageName == 'Prospecting/Business Issue Identified' ||
            oli.Opportunity.StageName == 'Scoping/Building Business Case' ||
            oli.Opportunity.StageName == 'Proposal Development' ||
            oli.Opportunity.StageName == 'Price & Product Negotiation' ||
            oli.Opportunity.StageName == 'Contracting'){
                revType = 'Pipeline';
                }
        if(
            oli.Opportunity.StageName == 'Renew'){
                revType = 'Renewal';
                }
        if(
            oli.Opportunity.StageName == 'Closed - Won' &&
            oli.Opportunity.Sales_Order_Approved_Date__c == null){
                revType = 'Closed - Won Not Reconciled';
                }
        if(
            oli.Opportunity.StageName == 'Closed - Won' &&
            oli.Opportunity.Sales_Order_Approved_Date__c != null){
                revType = 'Booked';
                }
        else{
            revType = 'Unknown';
        }
        List<Product_Structure__c> externalId = [SELECT Id 
                                                 FROM Product_Structure__c 
                                                 WHERE External_Id__c = :oli.Product2.Family + '/' + oli.Product2.Product_Group__c + '/' + oli.Product2.Product_Offering__c];
        String accountTeam = 
        revPipeline.add(new Revenue_Pipeline__c(
            Account__c = oli.Opportunity.AccountId,
            ALR_Retailer__c = oli.Product2.ALR_Retailer__c,
            Cash_Barter__c = oli.Opportunity.Cash_or_Barter__c,
            Cross_Practice__c = oli.Opportunity.Account.Account_BU_2__c,
            CurrencyIsoCode = oli.Opportunity.CurrencyIsoCode,
            External_Id__c = oli.Product2.Family + '/' + oli.Product2.Product_Group__c + '/' + oli.Product2.Product_Offering__c,
            Is_Fund__c = oli.Is_Fund__c,
            Name = oli.OpportunityId + '-' + oli.Id,
            Opportunity__c = oli.OpportunityId,
            Opportunity_OLI__c = oli.OpportunityId + '-' + oli.Id,
            Practice__c = oli.Opportunity.Practice__c,
            Product_AU__c = oli.Product2.Product_LOB__c,
            Product_End_Date__c = oli.End_Date__c,
            Product_Price__c = oli.TotalPrice,
            Product_Start_Date__c = oli.Start_Date__c,
            Product_Structure__c = externalId[0].Id,
            Product__c = oli.Product2Id,
            Revenue_Type__c = revType,
            Subscription_Type__c = oli.Product2.Product_Type__c));
        }
    try{
        upsert revPipeline Opportunity_OLI__c;              
    } catch (DmlException ex) {
        System.debug('The folloing Exception has occured: ' + ex.getMessage() + ' at line ' + ex.getLineNumber());
    }   
}
}

Answer 1

My recommendation here is to use declarative-lookup-rollup-summaries to create a rollup of AccountTeamMember to Account using the Aggregate Operation of Concatenate Distinct and a Field to Aggregate of User.Name or User.FirstName. You can specify a Concatenate Delimiter of ,SP(). The library uses SP() to add a space in the Concatenate Delimiter.

Now you'll have a field on Account called something like Team_Member_Names__c, which you can pull into other objects via formula (on Opportunity or even OpportunityLineItem).