0

I have a multiselect picklist called status with 3 values and 5 amount fields..The problem that i am facing is that whatever value i select from status,My code is grouping by for all 3 values.

For e.g Assume that the values in status are A,B and C.

If i select A- All A,B and C values are getting aggregated.I want to group by only for the Status values that i select.

Below is the code that i have written

 if(isMezzRecordType == true)
{

     String groupDealStatus = 'SELECT Status__c,SUM(Equity_amount__c) equitySum,SUM(Equity_Payment_Amount__c) equityPayment,SUM(Debt_Payment_Amount__c) debtAmount,SUM(Debt_Amount__c) Debt,SUM(Japan_Debt_Amount__c) JapanDebtAmount From Log__c ';
     groupDealStatus+= whereClause;
     groupDealStatus+= 'GROUP BY Status__c ';

     AggregateResult[] totalDealResults= Database.query(groupDealStatus);

     for (AggregateResult tempTotal : totalDealResults) 
    {
    totalEquityAmt =calculateAmountString(Double.valueOF(tempTotal.get('equitySum')));

    equityPaymentAmt = calculateAmountString(Double.valueOF(tempTotal.get('equityPayment')));

    debtPaymentAmount = calculateAmountString(Double.valueOF(tempTotal.get('debtAmount')));

    debtAmount=calculateAmountString(Double.valueOF(tempTotal.get('Debt')));
   japanDebtMetAmount=calculateAmountString(Double.valueOF(tempTotal.get('JapanDebtAmount')));

   }  
}

In where clause status field has condition like below

         if(status!= null)
        {
            System.debug('status check*****'+status);
            temps = status.split(',',-1); 
            //whereClause+= ' '+'and'+'('+'Status__c in:temps';
             whereClause+= ' and (Status__c in:temps';

            if(isMezzRecordType && status!='Closed')
            {
              System.debug('*****inside status********');
              whereClause+=' '+'OR (Status__c=:statusCriteria AND Funding_Date__c=LAST_WEEK) OR (Debt_Payment_Date__c=LAST_WEEK) OR (Equity_Payment_Date__c=LAST_WEEK))';
         //   whereClause+=' '+'OR (Status__c=:statusCriteria OR Funding_Date__c=LAST_WEEK OR Debt_Payment_Date__c=LAST_WEEK))';

            }
            else
            {
              whereClause+=')';

            }
        }

Please help

Thanks.

  • Where are you populating that variable "whereClause"? It should contain something like " where Status__c in ('A') " built from the choices that you have made about which Status values to include. – Doug B May 5 '16 at 11:11
  • @Doug i have updated the wherclause condition in my question..please check it – Learner_sfdc May 5 '16 at 11:19
  • 1
    You could try doing system.debug on the whereClause once you have populated it. I see that you have an OR which means that you could be coming out with " where Status__c in ('A') or (Status__c=:statusCriteria AND Funding_Date__c=LAST_WEEK) " this OR part means that you could be retrieving rows which don't match the first part, i.e. have Status not A. These will then be aggregated by your group by. – Doug B May 5 '16 at 11:26
  • @Doug i have to give this criteria as per the requirement....because whatever i select in the multiselect.... its mandatory to get the status__c=:statusCriteria,here statusCriteria have one specific value ''Closed" from the multi select picklist. – Learner_sfdc May 5 '16 at 11:37
  • @Doug You are right if i remove that condition its aggregating whatever i select,but i want this condition too....is there any other way so that the requirements dont get clashed...please guide me... – Learner_sfdc May 5 '16 at 11:44
0

Just a mistake in query.Just added brackets and it worked

whereClause+= ' and (Status__c in:temps';

if(isMezzRecordType && status!='Closed')
{
          System.debug('*****inside status********');
whereClause+=' '+'OR (Status__c=:statusCriteria AND (Funding_Date__c=LAST_WEEK OR Debt_Payment_Date__c=LAST_WEEK OR Equity_Payment_Date__c=LAST_WEEK)));

}

Thanks for the help @Doug :)

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.