0

I have a requirement where the user should be able to include the records where the due date is not populated when i uncheck the checkbox field in the vf page.In my case that date field is named as Due_Date__c.

I have written the follwing dynamic soql code.But it is not fetching the records where the due date is not populated.

public class PrivateEquityReport
{
   ApexPages.StandardSetController regionStandardSetController{get;set;}
   private Id privateRecordTypeId = Fund_Activity__c.SObjectType.getDescribe().getRecordTypeInfosByName().get(' Amendment').getRecordTypeId();
   public AI_PrivateEquityWeeklyTrackingReport()
   String baseQuery = 'SELECT Id,Due_Date__c,Fund__r.Name,Company__r.Name,Subject__c,Comments__c,Date_Received__c,Business_Review__c,Attorney_Review__c FROM Fund__c WHERE Team__c=\'Equity\' AND RecordTypeId =:privateRecordTypeId';

   public PrivateEquityReport()
  {
    regionStandardSetController=new ApexPages.StandardSetController(new  list<Fund__c>());
    String dueDateCheck=System.currentPageReference().getParameters().get('DueDateCheck');
    if(dueDateCheck=='true')
      isDueDate=true; 
   else
      isDueDate=false;
  }

public String  getReportText()
{
    String checkQuery = baseQuery;

     String reportFromDateVar = System.currentPageReference().getParameters().get('dFromDate');
     Date fromDateVar = null;
    if(reportFromDateVar != null)
    fromDateVar = Date.valueOf(reportFromDateVar);
    System.debug('*****From Date***'+fromDateVar );

     String reportToDateVar = System.currentPageReference().getParameters().get('dToDate');
     Date toDateVar = null;
    if(reportToDateVar != null)
    toDateVar = Date.valueOf(reportToDateVar );
     System.debug('*****To Date***'+toDateVar );

     if((fromDateVar!= null)&&(toDateVar!=null)&&(!isDueDate))
    {
      checkQuery =checkQuery +' '+'and (Due_Date__c>=:fromDateVar AND Due_Date__c<= :toDateVar)';
      checkQuery =checkQuery +' '+'and Due_Date__c=NULL';
      system.debug('************3'+checkQuery);
    }

    regionStandardSetController=new ApexPages.StandardSetController(Database.getQueryLocator(checkQuery));
    activityRecords= regionStandardSetController.getRecords();
     displayPrivateEquity ='<table><tr><td>&nbsp;</td></tr><tr><td>&nbsp;</td></tr></table>' + displayPrivateEquity ;                
     displayPrivateEquity = prepareReportSinglePagePrivateEquity(activityRecords,displayPrivateEquity );
     return  displayPrivateEquity; 
}

Please help me with this.

Thanks.

5
  • can you tell me what 'system.debug('************3'+checkQuery);' line prints in log? Mar 2, 2016 at 7:04
  • @Saumya Ranjan Satapathy I have updated the code please check with it....i need a records within the range of from and to date that i am providing in the vf page along with the records where due_date which does not have value in that.... Mar 2, 2016 at 12:49
  • That system debug will give this 18:14:37:069 USER_DEBUG [124]|DEBUG|************3SELECT Id,Due_Date__c,Fund__r.Name,Company__r.Name,Subject__c,Comments__c,Date_Received__c,Business_Review__c,Attorney_Review__c FROM Fund__c WHERE Team__c='Equity' AND RecordTypeId =:privateRecordTypeId and (Due_Date__c>=:fromDateVar AND Due_Date__c<= :toDateVar) and Due_Date__c=NULL and (Date_Received__c>=:receivefromDateVar AND Date_Received__c<= :receivetoDateVar ) AND Executed__c=:executedCheck Mar 2, 2016 at 12:49
  • how your query string is not ending with 'and Due_Date__c=NULL'?? or are you using ************3 in other places? @Learner_sfdc Mar 2, 2016 at 13:15
  • from your query string it looks like an invalid condition, (Due_Date__c>=:fromDateVar AND Due_Date__c<= :toDateVar) and Due_Date__c=NULL'. You will not get value that is less than null AND greater than null AND equal to null. This criteria will never satishfy. Please revisit your query. Mar 2, 2016 at 13:35

2 Answers 2

1

From your query:


(Due_Date__c>=:fromDateVar AND Due_Date__c <= :toDateVar) and Due_Date__c=NULL

This criteria will never satisfy.

For Example, if fromDateVar==null and toDateVar==null, then the query is expecting a Due_Date__c that is less than null AND greater than null AND equal to null. Please revisit your query.

2
  • Ranjan To get both the criteria to work.... what query i need to use.please help Mar 3, 2016 at 1:14
  • 1
    May b like ( (Due_Date__c>=:fromDateVar AND Due_Date__c <= :toDateVar) OR Due_Date__c=NULL ) Mar 3, 2016 at 2:20
1

Can you try changing the checkQuery variable as below:

checkQuery =checkQuery +' '+'and Due_Date__c = NULL';

Note:

  • You don't need exact type variable for NULL checks, NULL is a NULL irrespective of whatever data type your want to check
11
  • Hi karthik,Thanks for the reply...No it is also not working.... The records are there with due_date__c with no data in that...Those are not fetching....it is showing blank list... Mar 2, 2016 at 6:37
  • Then I think before the AND you have the baseQuery with some other criteria which is filtering it Mar 2, 2016 at 6:41
  • No i havent given other criteria...i have commented all those...still it is not fetching any records Mar 2, 2016 at 6:58
  • Can you paste the final SOQL query ? I will try debugging it from my end Mar 2, 2016 at 6:59
  • 12:36:24:019 USER_DEBUG [119]|DEBUG|************3SELECT Id,Due_Date__c,Fund__r.Name,Company__r.Name,Subject__c,Comments__c,Date_Received__c,Business_Review__c,Attorney_Review__c FROM Fund__c WHERE Team__c='Equity' AND RecordTypeId =:privateRecordTypeId and Due_Date__c=NULL Mar 2, 2016 at 7:08

You must log in to answer this question.

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