You need to sign in to do that
Don't have an account?
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
Your code looks fine to me
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.
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.
Please share your VFP also would like to see how are you invoking your methods.
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.
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.