+ Start a Discussion
aKallNVaKallNV 

First time with Dynamic SOQL...can't find variable.

The code below is a VF controller extension of a custom object.

 

I am trying to create a dynamic SOQL query to allow the user to filter a list.

 

There is a field on the User object called Unit__c. There is a Unit__c field on the custom object, as well. Both fields contain the same values.

 

So, what I want is for the user to see a list of the custom objects that belong to his/her unit when they first open the page and have the ability to filter the list by other Unit values that are present. I am having trouble just geting this first part to work. I have created a variable called unitFilter which I populate with a method called setUpUnitFilter which is called in the Contructor. I have debugged the method to verify that is working and does find a value in the Unit__c field, but for some reason when I try to use that value in the method that makes the SOQL query which is called getStrategyQuery() it comes up null everytime...I have tried to populate the variable a few different ways but it always comes up null. I don' think this actually has do with Dynamic SOQL, and more to do with the fact that I am an admin that doesn't fully understand  variable instantiation and what not. I have underlined the relevant bits of code and tried add some comments next to them.

 

Thanks for any help!

 

public with sharing class narrativeWizard_Controller {
        
        public String accountID;
        public String swpnAccount { get; set; }
        public SchoolWorkPlanLog__c getLog { get; set; }
        public List<UniversalJoin__c> juncObs { get; set; }
        public List<cAIS> theWrappers { get; set; } 
        public Map<ID,List<cAIS>> theWrapperMap { get; set; }
        public List<DeployedStrategy__c> theDSs { get; set; }
        public List<cGS> theGoalWrappers { get; set; }
        public Set<String> unitOptions = new Set<String>();
        public String selectedUnit { get; set; }
        public Set<String> princyOptions = new Set<String>();
        public String selectedPrincy { get; set; }
        public Set<String> catyOptions = new Set<String>();
        public String selectedCaty { get; set; }
        public List<cCONS> theConWrappers { get; set; }
        public List<cCASES> theCaseWrappers { get; set; }
        public Boolean firstTime { get; set; }
        public String unitFilter { get; set; } //here is the filter variable
        
        //sets up class extension of SchoolWorkPlanLog__c
        private final SchoolWorkPlanLog__c swpn;
                
        //CONSTRUCTOR
        public narrativeWizard_Controller(ApexPages.StandardController swpnController) {
                this.accountID = ApexPages.currentPage().getParameters().get('aID');
                this.swpn = (SchoolWorkPlanLog__c)swpnController.getRecord();
                this.setUpAccount();  
                this.makesStrategyWrappers();
                this.makesGoalWrappers();
                this.makesContactWrappers();
                this.makesCaseWrappers(); 
                this.getLog= new SchoolWorkPlanLog__c();
                this.getUnits(); 
                this.firstTime = true;
        	    this.setUpUnitFilter(); //here is where call the method to create the filter
        }
        
        public void setUpAccount() {
                Account acct = [select ID, Name from Account where ID = :accountID Limit 1];
                this.swpnAccount = acct.Name;
        }
        
        public void setUpUnitFilter() { //here is the method that is called by constructor. verified it finds a Unit
        	ID UP = Userinfo.getUserId();
        	User userX = [select ID, Name, Department__c, Unit__c from User where ID =:UP];        	
        	this.unitFilter = userX.Unit__c;
        }
        
        private String getStrategyQuery() {
        	
        	String losFilters = '';
        	
        	if(null!=this.selectedUnit) {
        		losFilters += ' AND Unit__c =:selectedUnit';
        	}else if (null != this.unitFilter) {
        		losFilters += ' AND Unit__c =:unitFilter';//here is where try to use the value created constructor that comes up null.
        	}else {
        		losFilters += '';
        	}
        	
        	return 'select ID, Name, Status__c, Strategy__r.Name,Strategy__r.Category__r.Name, Strategy__r.Category__r.Principle__r.Name, EndDate__c, Unit__c, StartDate__c, (select ID, Name, Name__c, Status__c, DeployedStrategy__r.Strategy__r.Name, EndDate__c, LatestRating__c from DeployedActionItem__r Order By ItemNumber__c ASC) from DeployedStrategy__c where Account__c = :accountID And Status__c = \'Active\''+losFilters;
        }
        
        //attempt at Dynamic Query
        public void makesStrategyWrappers() {
        	//variables    
            theDSs = new List<DeployedStrategy__c>();
            List<cAIS> cAIS1 = new List<cAIS>();   
            theWrappers = new List<cAIS>();
            theWrapperMap = new Map<Id, List<cAIS>>();
            
            String queryString = this.getStrategyQuery();
            theDSs = Database.query(queryString);
            
            for(DeployedStrategy__c ds : theDSs) {
            	
            	unitOptions.add(ds.Unit__c);
                princyOptions.add(ds.Strategy__r.Category__r.Principle__r.Name);
                catyOptions.add(ds.Strategy__r.Category__r.Name);
                
                for(DeployedActionItems__c actI : ds.DeployedActionItem__r) {
                	cAIS1 = theWrapperMap.get(ds.Id);
                    
                    if(null == cAIS1) {       
	                    cAIS1 = new List<cAIS>();
	                    theWrappers.add(new cAIS(actI)); 
	                    theWrapperMap.put(ds.Id, cAIS1);                              
                    }
                    cAIS1.add(new cAIS(actI));
                }
            }.....lots more code below this but probably not relevant

 

 

 

Shashikant SharmaShashikant Sharma

Your code looks fine to me 

private String getStrategyQuery() {
        	
        	String losFilters = '';
        	system.debug('In this.unitFilter : ' + this.unitFilter);
        	if(null!=this.selectedUnit) {
        		losFilters += ' AND Unit__c =:selectedUnit';
                        
        	}else if (null != this.unitFilter) {
        		losFilters += ' AND Unit__c =:unitFilter';//here is where try to use the value created constructor that comes up null.
        	}else {
        		losFilters += '';
        	}
        	
        	return 'select ID, Name, Status__c, Strategy__r.Name,Strategy__r.Category__r.Name, Strategy__r.Category__r.Principle__r.Name, EndDate__c, Unit__c, StartDate__c, (select ID, Name, Name__c, Status__c, DeployedStrategy__r.Strategy__r.Name, EndDate__c, LatestRating__c from DeployedActionItem__r Order By ItemNumber__c ASC) from DeployedStrategy__c where Account__c = :accountID And Status__c = \'Active\''+losFilters;
        }

 

I know you said you have verified the data, I have added a debug statement , please one more time verify field value is not null i added this in your code, I added this above the condition so that it can be determing whether value is null or dynamic query is not able to read it.

 

system.debug('In this.unitFilter : ' + this.unitFilter);

 

Please let me know what is the outcome, will be helpful to solve the issue.

 

aKallNVaKallNV

Your suggestion returns null.  However when I run the same debug in the seUpUnitFilter() method it returns the expected value.  So, I'm confused about why the unitFilter variable in the getStrategyQuery() method is returning null...it seems to me it should have the value being returned in the setUpUnitFilter() method.

 

 

Shashikant SharmaShashikant Sharma

Please share your VFP also would like to see how are you invoking your methods. 

aKallNVaKallNV

here is the vfp:

 

The page block section that the code in question is controlling has the following title:

title="Select the Work Plans, Goals, Cases and Contacts that this Entry pertains to."

 

In that section you will find the filters that I want to use. there are 3: Unit, Principle and Category...just trying to get Unit working first. And beneath those filters the pageblock tables that will present lists that I want to be filttered to the User's unit when they open page and then use the Unit filter to look at other units in the ORG. This is a Tab panel tha will have unique filters on each tab. So we're just talking about the first tab, at the moment.

 

 

<apex:page sidebar="false" showHeader="true" standardController="SchoolWorkPlanLog__c" extensions="narrativeWizard_Controller" id="tt1">
    <apex:form >
      <apex:pageBlock mode="edit" id="pb1">  
      <apex:pageBlockButtons >
         <apex:commandButton action="{!processSelected}" value="Save"/>
      </apex:pageBlockButtons>  
      <apex:pageBlockSection title="{!swpnAccount}" columns="1">
              <!--<apex:inputField value="{!getLog.Subject__c}"/> Decided we didn't need a Subject but leaving in place in case decision is overruled.-->
              <apex:inputField value="{!getLog.InteractionDate__c}"/>
              <apex:inputField value="{!getLog.PreEngagementPlan__c}"/>
              <apex:pageBlockSection collapsible="true" columns="1" title="Narrative">
              <apex:inputField value="{!getLog.HoursSpent__c}"/>
              <apex:inputField value="{!getLog.Narrative__c}"/>
              </apex:pageBlockSection>
      </apex:pageBlockSection>
      <apex:pageBlockSection columns="1" collapsible="true" title="Next Steps">
          <b><apex:outputLabel value="Use the following fields to define your next step. Upon saving this record a new Log will be created with the Date and Subject you specify here."/></b>
          <apex:inputField value="{!getLog.NextStepDate__c}"/>
          <apex:inputField value="{!getLog.NextStepPlan__c}"/>
      </apex:pageBlockSection>
      <apex:pageBlockSection columns="1" id="pbs1" title="Off-Plan?">
          <apex:outputPanel >
              <b><apex:outputLabel value="Check the box to indicate that none of the options below are appropriate:"/></b>
              <apex:actionRegion >
                  <apex:inputField value="{!getLog.OffPlan__c}">
                      <apex:actionSupport event="onclick" reRender="table1, catField, pbt1"/>
                  </apex:inputField>
              </apex:actionRegion>
          </apex:outputPanel>
      </apex:pageBlockSection>
      <apex:outputPanel id="catField">
      <apex:pageBlockSection rendered="{!showAIandGoal = false}" columns="1">
          <b><apex:outputText value="Find a category that best classifies this narrative:"/></b>
          <apex:inputField value="{!getLog.Category__c}"/>
      </apex:pageBlockSection>    
      </apex:outputPanel>
      </apex:pageBlock>          
      <apex:pageBlock id="pb2" >
      <apex:outputPanel id="table1">
      <apex:pageBlockSection title="Select the Work Plans, Goals, Cases and Contacts that this Entry pertains to." collapsible="false" rendered="{!showAIandGoal}" columns="1">
          <apex:tabPanel switchType="client" selectedTab="tab1" rendered="{!showAIandGoal}">
              <apex:tab label="Work Plans" id="tab1">
                  <!--<apex:outputPanel id="pbt1" rendered="{!showAIandGoal}">-->
              <apex:pageBlockSection collapsible="false" rendered="{!showAIandGoal}" columns="3">
              <apex:pageBlockSectionItem >
               <apex:outputLabel value="Filter by Unit"/>
               <apex:selectList value="{!selectedUnit}" size="1">
                  <apex:selectOptions value="{!Units}"/>
                  <!--<apex:actionSupport event="onchange" reRender="table1, pbt1"/>-->
               </apex:selectList>
              </apex:pageBlockSectionItem>          
              <apex:pageBlockSectionItem >
              <apex:outputLabel value="Filter by Principle"/>
               <apex:selectList value="{!selectedPrincy}" size="1">
                  <apex:selectOptions value="{!Princys}"/>
                  <!--<apex:actionSupport event="onchange" reRender="pbt1"/>-->
               </apex:selectList>
              </apex:pageBlockSectionItem>
              <apex:pageBlockSectionItem >
              <apex:outputLabel value="Filter by Category"/>
               <apex:selectList value="{!selectedCaty}" size="1">
                  <apex:selectOptions value="{!Catys}"/>
                  <!--<apex:actionSupport event="onchange" reRender="pbt1"/>-->
               </apex:selectList>
              </apex:pageBlockSectionItem>
              </apex:pageBlockSection>
                  <apex:pageBlockSection collapsible="false" columns="1">
                      <apex:pageBlockSectionItem >
                          <apex:pageBlockTable value="{!theDSs}" var="d"  >
                              <apex:column headerValue="Strategies">
                                  <b>{!d.Strategy__r.Name }</b>
                              </apex:column>
                              <apex:column headerValue="Unit">
                                  <b>{!d.Unit__c}</b>
                              </apex:column>
                              <apex:column headerValue="Start Date" width="125">
                                  <b><apex:outputText value="{0,date,MM-dd-yyyy}">
                                      <apex:param value="{!d.StartDate__c}"/>
                                  </apex:outputText></b>                  
                              </apex:column>
                              <apex:column breakBefore="true" colspan="3" >
                                  <apex:dataTable value="{!theWrapperMap[d.id]}" var="a" width="100%" border="5px">
                                      <apex:column >
                                          <apex:inputCheckbox value="{!a.selected}"/>
                                      </apex:column>
                                      <apex:column headerValue="Action Items">
                                          {!a.wAI.Name__c}
                                      </apex:column>
                                      <apex:column headerValue="Status">
                                          {!a.wAI.Status__c}
                                      </apex:column>
                                      <apex:column headerValue="Current Rating" width="110">
                                          {!a.wAI.LatestRating__c}
                                      </apex:column>
                                      <apex:column headerValue="New Rating">
                                          <apex:selectList size="1" value="{!a.selectedRating}">
                                              <apex:selectOptions value="{!a.ratings}"/>
                                          </apex:selectList>
                                      </apex:column>
                                  </apex:dataTable>
                              </apex:column> 
                          </apex:pageBlockTable>
                      </apex:pageBlockSectionItem>
                  </apex:pageBlockSection>
                      <!--</apex:outputPanel>-->
                  </apex:tab>
                  <apex:tab label="Goals" id="tab2">
                      <apex:pageBlockSection columns="1">
                      <apex:pageBlockSectionItem >
                      <apex:pageBlockTable value="{!theGoalWrappers}" var="d">
                          <apex:column >
                              <apex:inputCheckbox value="{!d.selected}"/>
                          </apex:column>              
                          <apex:column headerValue="Goals">
                             {!d.wG.Name }
                          </apex:column>
                          <apex:column headerValue="Pillars">
                              {!d.wG.Pillar__c}
                          </apex:column>
                          <apex:column headerValue="Unit">
                              {!d.wG.Unit__c}
                          </apex:column>
                          <apex:column headerValue="Start Date" width="125">
                              <apex:outputText value="{0,date,MM-dd-yyyy}">
                                  <apex:param value="{!d.wG.StartDate__c}"/>
                              </apex:outputText>                  
                          </apex:column>
                      </apex:pageBlockTable>
                      </apex:pageBlockSectionItem>
                   </apex:pageBlockSection>
               </apex:tab>
              <apex:tab label="Cases" id="tab3">
                  <apex:pageBlockSection columns="1">
                      <apex:pageBlockSectionItem >
                      <apex:pageBlockTable value="{!theCaseWrappers}" var="case">
                          <apex:column >
                              <apex:inputCheckbox value="{!case.selected}"/>
                          </apex:column>              
                          <apex:column headerValue="Cases">
                             {!case.wCase.CaseNumber }
                          </apex:column>
                          <apex:column headerValue="Title">
                              {!case.wCase.Subject}
                          </apex:column>
                      </apex:pageBlockTable>
                      </apex:pageBlockSectionItem>
                   </apex:pageBlockSection>
              </apex:tab>
              <apex:tab label="Contacts" id="tab4">
                  <apex:pageBlockSection columns="1">
                      <apex:pageBlockSectionItem >
                      <apex:pageBlockTable value="{!theConWrappers}" var="con">
                          <apex:column >
                              <apex:inputCheckbox value="{!con.selected}"/>
                          </apex:column>              
                          <apex:column headerValue="Contacts">
                             {!con.wCon.Name }
                          </apex:column>
                          <apex:column headerValue="Title">
                              {!con.wCon.Title}
                          </apex:column>
                      </apex:pageBlockTable>
                      </apex:pageBlockSectionItem>
                   </apex:pageBlockSection>
              </apex:tab>
          </apex:tabPanel>
          </apex:pageBlockSection> 
      <!--</apex:outputPanel>-->
      </apex:outputPanel>
     
      </apex:pageBlock>
    </apex:form>   
</apex:page>

 

aKallNVaKallNV

I figured it out. Revised code is posted below.

 

I had to call the setUpUnitFilter() method query from the getStrategyQuery() method. so I had to change the setUpUnitFilter() to return a String rather than void.

 

 

public with sharing class narrativeWizard_Controller {
        
        public String accountID;
        public String swpnAccount { get; set; }
        public SchoolWorkPlanLog__c getLog { get; set; }
        public List<UniversalJoin__c> juncObs { get; set; }
        public List<cAIS> theWrappers { get; set; } 
        public Map<ID,List<cAIS>> theWrapperMap { get; set; }
        public List<DeployedStrategy__c> theDSs { get; set; }
        public List<cGS> theGoalWrappers { get; set; }
        public Set<String> unitOptions = new Set<String>();
        public String selectedUnit { get; set; }
        public Set<String> princyOptions = new Set<String>();
        public String selectedPrincy { get; set; }
        public Set<String> catyOptions = new Set<String>();
        public String selectedCaty { get; set; }
        public List<cCONS> theConWrappers { get; set; }
        public List<cCASES> theCaseWrappers { get; set; }
        public Boolean firstTime { get; set; }
        public String unitFilter { get; set; } 
        
        //sets up class extension of SchoolWorkPlanLog__c
        private final SchoolWorkPlanLog__c swpn;
                
        //CONSTRUCTOR
        public narrativeWizard_Controller(ApexPages.StandardController swpnController) {
                this.accountID = ApexPages.currentPage().getParameters().get('aID');
                this.swpn = (SchoolWorkPlanLog__c)swpnController.getRecord();
                this.setUpAccount();  
                this.makesStrategyWrappers();
                this.makesGoalWrappers();
                this.makesContactWrappers();
                this.makesCaseWrappers(); 
                this.getLog= new SchoolWorkPlanLog__c();
                this.getUnits(); 
                this.firstTime = true;
        	    this.setUpUnitFilter();
        }
        
        public void setUpAccount() {
                Account acct = [select ID, Name from Account where ID = :accountID Limit 1];
                this.swpnAccount = acct.Name;
        }
        
        public String setUpUnitFilter() {
        	ID UP = Userinfo.getUserId();
        	User userX = [select ID, Name, Department__c, Unit__c from User where ID =:UP];        	
        	
        	return userX.Unit__c;
        }
        
        private String getStrategyQuery() {
        	unitFilter = setUpUnitFilter();
        	String losFilters = '';
        	if(null!=this.selectedUnit) {
        		losFilters += ' AND Unit__c =:selectedUnit';
        	}else if (null != unitFilter) {
        		losFilters += ' AND Unit__c =:unitFilter';
        	}else {
        		losFilters += '';
        	}
        	
        	return 'select ID, Name, Status__c, Strategy__r.Name,Strategy__r.Category__r.Name, Strategy__r.Category__r.Principle__r.Name, EndDate__c, Unit__c, StartDate__c, (select ID, Name, Name__c, Status__c, DeployedStrategy__r.Strategy__r.Name, EndDate__c, LatestRating__c from DeployedActionItem__r Order By ItemNumber__c ASC) from DeployedStrategy__c where Account__c = :accountID And Status__c = \'Active\''+losFilters;
        }
        
        //attempt at Dynamic Query
        public void makesStrategyWrappers() {
        	//variables    
            theDSs = new List<DeployedStrategy__c>();
            List<cAIS> cAIS1 = new List<cAIS>();   
            theWrappers = new List<cAIS>();
            theWrapperMap = new Map<Id, List<cAIS>>();
            
            String queryString = this.getStrategyQuery();
            theDSs = Database.query(queryString);
            
            for(DeployedStrategy__c ds : theDSs) {
            	
            	unitOptions.add(ds.Unit__c);
                princyOptions.add(ds.Strategy__r.Category__r.Principle__r.Name);
                catyOptions.add(ds.Strategy__r.Category__r.Name);
                
                for(DeployedActionItems__c actI : ds.DeployedActionItem__r) {
                	cAIS1 = theWrapperMap.get(ds.Id);
                    
                    if(null == cAIS1) {       
	                    cAIS1 = new List<cAIS>();
	                    theWrappers.add(new cAIS(actI)); 
	                    theWrapperMap.put(ds.Id, cAIS1);                              
                    }
                    cAIS1.add(new cAIS(actI));
                }
            }
            
        }