0

I'm attempting to create a report that maps activity on an account, by signalling whether certain 'capabilities' (services sold) stored as picklist values are selected on all related opportunity records.

To give a 'simple-terms' example, I have a picklist field with the options 'red', 'green' and 'blue' on my opportunity page. If I have 2 opportunities for my account "customer 1", where on Opportunity A I have selected 'red', and opportunity B I have selected 'red' and 'green', I would map that my activity with customer 1 is 2 red, 1 green and 0 blue.

I've tried to explain my (noob) logic below:

public List<Double> getWhichCapabilities(){
        List<SelectOption> Myoptions = new List<SelectOption>();
        List<Double> CapabilityMap = new List<Double>();

        // I want to keep my Options to compare against dynamic, as I may add new 'capabilities' in future. I therefore count the picklist values available to set a limit for my loop.
        Schema.DescribeFieldResult fieldResult = Opportunity.Capability__c.getDescribe();

        List<Schema.PicklistEntry> ple = fieldResult.getPicklistValues();

        for( Schema.PicklistEntry f : ple){
            Myoptions.add(new SelectOption(f.getLabel(), f.getValue()));
        }           

        //I should loop through the possible picklist values, each time checking to see if they have been selected.
        for(Integer a = 0; a < Myoptions.size(); a++){

        //I create a list of all opportunity records related to the account. I filter by each picklist value (currently active in the loop).    
            List<account> OppsWithCapabilities = [
            SELECT id, name, (
                SELECT id, capability__c from Opportunities
                )
            FROM account 
            WHERE ( account.id = :Current_Acc_Id ) AND ( account.opportunity.capability__c includes (:Myoptions.get(a)) )
            ];

            //I then add an item to my final list ('Capability Map') which is the size of the OppsWithCapabilities (basically a count of the records that match the filter)
            CapabilityMap.add(OppsWithCapabilities.count);

            //finally, clear the list ready for the next iteration
            OppsWithCapabilities.clear();
        }
        //output a list of integers
        return CapabilityMap;

    }

Phew!

So I get thrown the error (in the title) which signals to me that my sub-query on the opportunity records for my picklist field is wrong. For the life of me I can't work out why.

I expect to be returned a list of Integers which I can then manipulate on my vf page. I'm confident with my VF code that I can do this, so that isn't an issue.

For now, if you could assist with my compile error, that would be great. Also, for education purposes, if you know a more efficient way of performing this, I would appreciate the heads up (I'm learning as I go).

Thanks for your help and patience with my long post.

2
  • You can't go parent to child in dot notation like Account.Opportunity. Have a look at Relationship Queries also read and understand how to write soql query with filters – highfive Jun 27 '16 at 4:20
  • You are correct - I had in vain tried the dot notation and forgot to remove it before posting. I have now solved the issue and have posted the finished code below for reference – LFitzer Jun 27 '16 at 13:07
2

This code is not valid:

AND ( account.opportunity.capability__c includes

The subquery returns a List of Opportunities related to the account. You will have to Iterate over the list of opportunities for each account to see if they match your criteria

for(Account a : OppsWithCapabilities){
   for(Opportunity o : a.Opportunities){
      //Check values here and do what you need to
   }
}
1
  • Thanks, you were completely right. Hving read up on Soql and subqueries, I found my answer. – LFitzer Jun 27 '16 at 13:07
0

Solved.

Working code with VF below for anyone who might find it useful. This is run on a VF page that uses the standard account controller. The output is a table that shows me which services I perform per account. I use an apex:repeat to build the table, and where the count is >0 I use a class to turn the cell green.

VF:

<table class="table3">

  <th class="rotate" style="width:112px;"><div style="transform:translate(45px, 82px) rotate(270deg)"><span>Services</span></div></th>

  <apex:repeat value="{!ListOfCapabilityValues}" var="headers">
    <th class="rotate"><div><span>{!headers}</span></div></th>
  </apex:repeat>


  <tr>
    <td>Performed?</td>

    <apex:repeat value="{!WhichCapabilities}" var="CapabilityCount">

        <apex:repeat value="{!CapabilityCount}" var="countTrue" rendered="{!CapabilityCount>0}">
            <td class="green">{!CapabilityCount}</td>
        </apex:repeat>

        <apex:repeat value="{!CapabilityCount}" var="countFalse" rendered="{!CapabilityCount=0}">
            <td class="capabilitycount">{!CapabilityCount}</td>
        </apex:repeat>

    </apex:repeat>

</tr>
</table>

Extension:

    public List<string> getListOfCapabilityValues(){
    List<SelectOption> Myoptions = new List<SelectOption>();
    List<string> CapabilityValues = new List<string>();

    // I want to keep my Options to compare against dynamic, as I may add new 'capabilities' in future. I therefore count the picklist values available to set a limit for my loop.
    Schema.DescribeFieldResult fieldResult = Opportunity.Capability__c.getDescribe();

    List<Schema.PicklistEntry> ple = fieldResult.getPicklistValues();

    for( Schema.PicklistEntry f : ple){
        Myoptions.add(new SelectOption(f.getLabel(), f.getValue()));
    }
    for(Integer i = 0; i < Myoptions.size(); i++){
        CapabilityValues.add(Myoptions[i].getLabel());
    }
    return CapabilityValues;  
 }  


 public List<integer> getWhichCapabilities(){
    List<SelectOption> Myoptions = new List<SelectOption>();
    List<integer> CapabilityMap = new List<integer>();

    // I want to keep my Options to compare against dynamic, as I may add new 'capabilities' in future. I therefore count the picklist values available to set a limit for my loop.
    Schema.DescribeFieldResult fieldResult = Opportunity.Capability__c.getDescribe();

    List<Schema.PicklistEntry> ple = fieldResult.getPicklistValues();

    for( Schema.PicklistEntry f : ple){
        Myoptions.add(new SelectOption(f.getLabel(), f.getValue()));
    }           

    //I create a list of all opportunity records related to the account. 
        List<account> OppsWithCapabilities = [
        SELECT id, name, (
            SELECT id, capability__c from Opportunities WHERE capability__c != Null AND StageName='Closed Won'
            )
        FROM account
        ];

    //define a variable 'j' for counting
    integer j=0;

    //I should loop through the possible picklist values, each time checking to see if they have been selected on each opportunity.
    for(Integer i = 0; i < Myoptions.size(); i++){               

        //for the accounts identified... 
        for(Account a : OppsWithCapabilities){            

                //scrutinise the opportunities that have been found
                for(Opportunity o : a.Opportunities){
                    String str = o.capability__c;


                //if the current picklist value is selected, increment the counter 'j'+1
                if (str.contains( Myoptions[i].getLabel() ) ) { j++;}

            }

        }
        //I then add an item to my final list ('Capability Map') which is the value of my counting variable, 'j' (basically a count of the records that match the filter)
        CapabilityMap.add(j);

        //finally, clear the count variable ready for the next loop
        j=0;

    }
    //output a list of integers
    return CapabilityMap;
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.