1

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());
    }   
}
}
3
  • Have you looked at dlrs? There's an Aggregate Operation called Concatenate Distinct.
    – Adrian Larson
    Nov 2, 2016 at 16:11
  • I have, because this custom object and the Account Team object are not related do you think the best way is dlrs on Account populated text field, and then bring that text field into my query? Nov 2, 2016 at 16:21
  • Exactly thus. Get the concatenated team member names on the Account first, then move from there.
    – Adrian Larson
    Nov 2, 2016 at 16:22

1 Answer 1

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).

3
  • Did you ever find a way to have the delimiter accept spaces? When you do , it ignores the space and just squishes them together. if you do something like ', ' then it adds the apostrophe as well.. Nov 2, 2016 at 20:19
  • No, I haven't tried to include whitespace in the delimiter. I imagine the code calls trim on it, removing outer whitespace. Hmm...
    – Adrian Larson
    Nov 2, 2016 at 20:27
  • 1
    Apparently this is already a feature, I'm just on the wrong version.. github.com/afawcett/declarative-lookup-rollup-summaries/issues/… Nov 2, 2016 at 20:34

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .