3

I have two different lists. A list of opportunities and a list of sales (Umsatz__c), the accountid appears in both lists. How can I merge these lists into one finallist at account level? This list should

Opp List

AccName ExpectedRevenue Amount
A1 5000 10000
B2 500 250
A7 10 10

Sales List

AccName BWS
A1 500
B5 -100
H9 25

Final List

AccName ExpectedRevenue Amount Sales
A1 5000 10000 500
B2 500 250 250
B5 00 00 -100
A7 10 10 0
H9 0 0 25

CLASS

public class VTPv1 {       
    Public List<AggregateResult> OppGroup {get; set;}
    Public List<AggregateResult> SalesGroup {get; set;}
    
    public VTPv1() {          
        OppGroup    = [SELECT Account.Name AccName, Sum(ExpectedRevenue) Rev, Sum(Amount) Fin FROM Opportunity WHERE OwnerId = '005b0000001UAm4AAG' AND CloseDate = THIS_YEAR AND (IsClosed = false OR IsWon = true) GROUP BY Account.Name];
        SalesGroup  = [SELECT Einzelrisiko__r.Gruppenvertrag__r.Unternehmen__r.Name AccName, Sum(BWS_Brutto__c) BWS FROM Umsatz__c WHERE Berater__r.Id = '005b0000001UAm4AAG' AND Bewertungsmonat__c = THIS_YEAR GROUP BY Einzelrisiko__r.Gruppenvertrag__r.Unternehmen__r.Name];          
    }
}

VISUAL PAGE

<apex:page controller="VTPv1">        
    <apex:pageBlock title="Opportunities Group">
        <apex:pageBlockTable value="{!OppGroup}" var="oppg">  
            <apex:column value="{!oppg['AccName']}">
                <apex:facet name="header">Account Name</apex:facet>
            </apex:column>
            <apex:column value="{!oppg['Rev']}">
                <apex:facet name="header">Expected Revenue</apex:facet>
            </apex:column>
            <apex:column value="{!oppg['Fin']}">
                <apex:facet name="header">Amount</apex:facet>
            </apex:column>             
        </apex:pageBlockTable> 
    </apex:pageBlock>           
    <br/>           
    <apex:pageBlock title="Sales Group">
        <apex:pageBlockTable value="{!SalesGroup}" var="salesg">  
            <apex:column value="{!salesg['AccName']}">
                <apex:facet name="header">Account Name</apex:facet>          
            </apex:column>
            <apex:column value="{!salesg['BWS']}">
                <apex:facet name="header">Sales</apex:facet>          
            </apex:column>
        </apex:pageBlockTable> 
    </apex:pageBlock>                
</apex:page>

Update 22.10.21 I tried to create a new class with two maps, but I don't know how can I display the results at the visualforce page.

public class VTPv2 {
    public String CombinedSummary { get; set; }
    
    public VTPv2() {      
    }
    
    Public class CombinedSummary {
        public String AccName {get; set;}
        public Decimal expectedRevenue {get; set;}
        public Decimal amount {get; set;}
        public Decimal BWS {get; set;}  
        
        Public CombinedSummary (AggregateResult OppAr, AggregateResult SalAr) {
            AccName = (String) OppAr.get('AccName');
            expectedRevenue = (Decimal) OppAr.get('expectedRevenue');
            amount = (Decimal) SalAr.get('amount');
            BWS = (Decimal) SalAr.get('BWS');
        }
    }
    
    Public List<CombinedSummary> generateWrappers() {
        
        List<CombinedSummary> combinedSummaries = new List<CombinedSummary>();
        
        Map<Id, AggregateResult> OppGroup = new Map<Id, AggregateResult>();
        for(AggregateResult OppAr :[SELECT Account.Name AccName, Sum(ExpectedRevenue) Rev, Sum(Amount) Fin FROM Opportunity WHERE OwnerId = '005b0000001UAm4AAG' AND CloseDate = THIS_YEAR AND (IsClosed = false OR IsWon = true) GROUP BY Account.Id, Account.Name]) {
            OppGroup.put((Id)OppAr.get('Id'), OppAr);
        }
        
        Map<Id, AggregateResult> SalGroup = new Map <Id, AggregateResult>();
        for(AggregateResult SalAr :[SELECT Einzelrisiko__r.Gruppenvertrag__r.Unternehmen__r.Name AccName, Sum(BWS_Brutto__c) BWS FROM Umsatz__c WHERE Berater__r.Id = '005b0000001UAm4AAG' AND Bewertungsmonat__c = THIS_YEAR GROUP BY Einzelrisiko__r.Gruppenvertrag__r.Unternehmen__r.Id, Einzelrisiko__r.Gruppenvertrag__r.Unternehmen__r.Name]) {            
            SalGroup.put((Id)SalAr.get('Einzelrisiko__r.Gruppenvertrag__r.Unternehmen__r.Id'), SalAr);
        }
        
        Set<Id> AccSet = new Set<Id>();
        AccSet = SalGroup.keySet();
        AccSet.addAll(SalGroup.keySet());
        
        FOR (Id Id : AccSet) {
            combinedSummaries.add(new CombinedSummary(OppGroup.get(Id), SalGroup.get(Id)));
        }        
        Return combinedSummaries;        
    }  
}

enter image description here

6
  • 1
    Why does H9 make it to the final table, but B5 does not? Because the value is negative? Or some other reason?
    – sfdcfox
    Commented Oct 13, 2021 at 13:06
  • I think you will need a wrapper class to represent your merged rows. You should also be able to use a Map<String, AggregateResult> to tie the two queries together based on Account Name. Example: salesforce.stackexchange.com/questions/126017/…
    – nbrown
    Commented Oct 13, 2021 at 13:25
  • Any reason why this can't be a lwc? This would be pretty easy to do in javascript. Commented Oct 13, 2021 at 23:54
  • @sfdcfox Thanks, you are right, I have forgotten to add B5 to final list.
    – Rag21
    Commented Oct 14, 2021 at 6:41
  • @CasparHarmer No reason, I now only this way. You can suggest your idea.
    – Rag21
    Commented Oct 14, 2021 at 6:43

1 Answer 1

4

At the very least, you would want to store the results of one of your queries in a map.

Given a public Map<String, Object> myMap {get; set;} in a controller or controller extension, you can access the value of a map in Visualforce using [].

<apex:repeat var="key" value="{!mapKeysList}">
    <apex:outputText value="{!myMap[key]}" />
</apex:repeat>

For readability though, I would suggest making a wrapper class and keep the logic in your controller. It can be a simple inner class that only contains a few variables. Your constructor would be responsible for populating a List of these wrapper class instances.

public class VTPv1 {
    public class ResultWrapper{
        // You may be able to store AggregateResult records, but it's simpler
        //   to access plain variables in Visualforce (and not much more typing)
        String name;
        Decimal expectedRevenue;
        Decimal amount;
        Decimal sales;
    }

    public List<ResultWrapper> resultList {get; set;}
    
    public VTPv1() {
        resultList = new List<ResultWrapper>();

        // We're still going to need to store one of these queries in a Map
        //   so that we can match records from the other query.
        // Shouldn't matter which one you store in a map.
        Map<String, AggregateResult> accNameToOppResult = new Map<String, AggregateResult>();
        for(AggregateResult ar :[SELECT Account.Name AccName, Sum(ExpectedRevenue) Rev, Sum(Amount) Fin FROM Opportunity WHERE OwnerId = '005b0000001UAm4AAG' AND CloseDate = THIS_YEAR AND (IsClosed = false OR IsWon = true) GROUP BY Account.Name]){
            accNameToOppResult.put((String)ar.get('AccName'), ar);
        }

        for(AggregateResult ar :[SELECT Einzelrisiko__r.Gruppenvertrag__r.Unternehmen__r.Name AccName, Sum(BWS_Brutto__c) BWS FROM Umsatz__c WHERE Berater__r.Id = '005b0000001UAm4AAG' AND Bewertungsmonat__c = THIS_YEAR GROUP BY Einzelrisiko__r.Gruppenvertrag__r.Unternehmen__r.Name]){
            // Here's where the bulk of the logic is
            // Find the matching record in the map, and then add a wrapper
            //   class instance to your list
            AggregateResult opportunityAR = accNameToOppResult.get((String)ar.get('AccName'));

            ResultWrapper combinedResult = new ResultWrapper();
            combinedResult.name = (String)ar.get('AccName');
            combinedResult.expectedRevenue = (Decimal)opportunityAR.get('rev');
            // ...and so on

            resultList.add(combinedResult);
        }
    }
}

You would then be able to iterate through that list in Visualforce using one of the normal methods (pageblocktable, datatable, repeat) and access individual fields using dot notation as per usual.

+edit:

Clarifying the following line
AggregateResult opportunityAR = accNameToOppResult.get((String)ar.get('AccName'));

It's not creating a new AggregateResult, it's retrieving an existing result from the first query.

The idea here is that to combine the results of your two queries, there needs to be a common piece of information shared between them. In my example, I used the AccName alias, though using an Id would likely be better.

The loop over the first query populates the map.

When you loop over the second query, since AccName is the shared piece of information, you can use that to get the corresponding value that was stored in the map from the first loop.

(String)ar.get('AccName') fetches the account name
accNameToOppResult.get(<actual account name here>) then fetches the corresponding Opportunity's AggregateResult.

You could break this into two lines as well, which might help clear things up.

String actualAccountName = (String)ar.get('AccName');
AggregateResult opportunityAR = accNameToOppResult.get(actualAccountName);

After that line of code executes, you're still in the loop for the second query.

ar is the result of the query on Umsatz__c, and opportunityAR is the matching result from the query on Opportunity (if there is any matching result).

After creating wrapper class instances (and adding them to a property, resultList in my example), displaying the result on a Visualforce page is then functionally the same as displaying a list of any other object.

<!-- pageblockTable or dataTable would also work -->
<apex:repeat var="wrapper" value="{!resultList}">
    <apex:outputText value="{!wrapper.name}" />
    <apex:outputText value="{!wrapper.expectedRevenue}" />
    <!-- and so on... -->
</apex:repeat>
5
  • that looks good, but how can I display the sales at the visualforce page? Unfortunately not all opportunities have sales, so the code runs into a an error "Attempt to de-reference a null object". And I think it would be better to use the AccId instead of the Accname for the mapping or is my understanding wrong? Contain the final lists also the Accounts which have sales but no opportunities?
    – Rag21
    Commented Oct 14, 2021 at 12:26
  • @Rag21 I was under the impression that you only wanted to display the combined result. The code I provided was meant to be an example, and I skipped putting in safety checks (e.g. checking that opportunityAR is not null before trying to use it). I didn't use Account Id because you weren't using Account Id. Using the Account Name would be fine as long as you don't have any duplicate names (but using the Account Id would be the generally preferred approach).
    – Derek F
    Commented Oct 14, 2021 at 12:41
  • could you help me a bit more. Please help me to understand the syntax. The map ist clear for me, but I don't understand this --> AggregateResult opportunityAR = accNameToOppResult.get((String)ar.get('AccName')); This is the creation of a new aggregateresult but what is happen after the equal? Is that the mapping from the first and the second aggregate? Could we create to maps and merge these? This could be more clear for me?
    – Rag21
    Commented Oct 19, 2021 at 7:25
  • I added a new code snippet from me where I tried to get the data with two maps. Could you take a look and give me some tipps? I don't know how can I display the results at my visualforce page.
    – Rag21
    Commented Oct 22, 2021 at 13:20
  • Thanks for your clarification, at the moment I try to understand the code step by step. If I try to display the result at the visualforce with your code {!wrapper.expectedRevenue} I get an error in the console of Unknown Property 'VTPv2.ResultWrapper.expectedRevenue'. If I try to chance to {!wrapper['rev']} I get no console error but the visualforce page display the error Unknown Property 'VTPv2.ResultWrapper.rev'. But the Debug shows me that the values are stored in the list (see screen above).
    – Rag21
    Commented Oct 25, 2021 at 9:47

You must log in to answer this question.

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