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.

7
  • 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, 2016 at 11:11
  • @Doug i have updated the wherclause condition in my question..please check it May 5, 2016 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, 2016 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. May 5, 2016 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... May 5, 2016 at 11:44

1 Answer 1

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

You must log in to answer this question.

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