function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
BaguiarBaguiar 

bind expression type on SOQL

Hi,

I'm trying to build a query that will overcome the issue the TASK object and use it in Semi-joint queries. What I'm trying to do it to create a list and then, create another list on the same controler, where the records are NOT in the first list. here is the code:

 

private List<task> tskNO;
  public List<task> gettskNO()

    {
     candDate1=candDate-Integer.valueOf(rangedate);
    TAsk[] tskNO = [Select WhoID FROM task WHERE OwnerID = :UserInfo.getUserId() and createddate >= :candDate1];
    return tskNO;
    }

  private List<Contact> ContactNO;
  public List<Contact> getContactNO()
   {
Contact[] ContactNO = [Select c.id, c.AccountId from Contact c WHERE c.ID NOT IN :tskNO];
   return ContactNO;
   }

 

But I'm getting a "invalid binding" error on the "  :tskNO "  .

 

Is there a way to bind the criteria to another List on the controller?

 

Thanks!

Best Answer chosen by Admin (Salesforce Developers) 
saraha@groupesci.comsaraha@groupesci.com

I mean why aren;t those lines in getContactNO?

All Answers

saraha@groupesci.comsaraha@groupesci.com

tskNo needs to be a list or set of Ids (not Tasks).  You will need to loop through your list of tasks and populate the Id list, like this:

 

 

Set<Id> tskNo = new Set<Id>();
for (Task t: [SELECT WhoId FROM Task...])
{
  tskNo.add(t.WhoId);
}

List<Contact> ContactNo = [SELECT Id,AccountId FROM Contact WHERE Id NOT IN: tskNo];

  --Sarah

 

Ritesh AswaneyRitesh Aswaney

tskNO is a lisk of tasks, wheres what you want to use is a List of Ids for the NOT IN clause.

 

so iterate through the tskNO collection and return a list of Ids, rather than a list of Tasks.

 

List<Id> tskNo = new List<Id>{};

 

 

for (Task tsk : [Select WhoID FROM task WHERE OwnerID = :UserInfo.getUserId() and createddate >= :candDate1])
    tskNO.add(tsk.WhoId);
    return tskNO;
}

 

BaguiarBaguiar

Thanks Sarah. (and also Ritesh for the post right after.)

 

 

That solved the issue I had, although I ended up with another issue, due to the fact that my " candDate1=System.today()-Integer.valueOf(rangedate);  "  was being refreshed everytime I called it from the VF page button "Refreshtsk()". Now "candDate1" is not being recalculated based on the "rangedate" value from the VF page. Initially, it defaults to '30' and as the refresh button "refreshtsk()" is hit, it should capture the value from the "rangedate" SelectList and requery the db based on the new value from "rangedate". Right now it loads the VF page as a null value for the rangedate...

 

public class TaskleaderNO
{ 
    public PageReference Refreshtsk() {
        return page.task_leadershipNO;
    }
    
     public PageReference toexcel() {
        return page.Task_LeadershipXLSNO;
    }
     
  public String positions {get; set;}
  public String rangedate {get; set;}

  Date candDate;
  Date candDate1;

  public TaskLeaderNO()
  {
     rangedate='30';
     candDate=System.today();    
  }

Set<Id> tskNo = new Set<Id>();
{
   candDate1=System.today()-Integer.valueOf(rangedate);
   for (Task t: [Select WhoID FROM task WHERE OwnerID = :UserInfo.getUserId() and activitydate >= :candDate1])
   tskNo.add(t.WhoID);
}
  
  private List<Contact> ContactNO;
  public List<Contact> getContactNO()
   {
   
Contact[] ContactNO = [Select id, AccountId, Account.name, Email, Name, from Contact WHERE ID IN ( SELECT Contact__c FROM Leadership_position_code__c WHERE Position__c = :positions and  (Thru_Date__c = null OR Thru_Date__c > :system.today()) and (Start_Date__C <= :system.today()) ) and ID NOT IN :tskNO];
   return ContactNO;
   }

}

 

VF Page:

<apex:page controller="TaskleaderNO" showHeader="false" >
<apex:form >
<apex:pageBlock id="pageBlock" Title="NO Activities with Key Leadership">
<apex:pageMessages ></apex:pageMessages>
<b>Select Position:   </b>
<apex:selectList id="Positions" value="{!positions}" size="1">
<apex:selectoption itemLabel="President" itemValue="President"></apex:selectoption>
<apex:selectoption itemLabel="Co-President" itemValue="Co-President"></apex:selectoption>
</apex:selectList>
<b> How many days back? </b>
<apex:selectList id="rangedate" value="{!rangedate}" size="1" >
<apex:selectoption itemLabel="30" itemValue="30"></apex:selectoption>
<apex:selectoption itemLabel="60" itemValue="60"></apex:selectoption>
</apex:selectList>
<b>   </b>
<apex:commandButton action="{!Refreshtsk}" value="Run Report" />
<b>   </b>
<apex:commandButton action="{!toexcel}" value="Export to Excel" />
<apex:pageBlockTable value="{!ContactNO}" var="cont" rendered="{!NOT(ISNULL(ContactNO))}">
<apex:column headerValue="Name" ><apex:outputLink value="/{!cont.id}" target="_parent">{!cont.Name}
</apex:outputLink></apex:column>
<apex:column value="{!cont.Email}"></apex:column>
<apex:column headerValue="Congregation" value="{!cont.account.name}"></apex:column>
</apex:pageBlockTable>
</apex:pageBlock>
</apex:form>

</apex:page>

 

Thanks for the help!

 

 

saraha@groupesci.comsaraha@groupesci.com

What is happening is that the entire page is reloading after running Refreshtsk and the controller's contstructor is setting rangedate back to 30 and candDate1 is not set so it shows null. You need to do a partial page refresh by setting the rerender tag on your command button to the id(s) of the objects that you want to refresh, in your case probably your pageBlockTable (which will need an id tag). So if you give your pageBlockTable the id "tblContactNo" then your commandButton tag should look like this:

<apex:commandButton action="{!Refreshtsk}" value="Run Report" rerender="tblContactNo" />

 

 --Sarah

BaguiarBaguiar

Did that but still having the error on the when trying to load it. It won open and gives the error

Argument 1 cannot be null

 

Class.TaskleaderNO: line 25, column 45 External entry point  "

 

That is the "  candDate1=System.today()-Integer.valueOf(rangedate);  "  line. "Rangedate" can;t be null. I don't get it as I ahve set is a "30"  on the public Taskleader() { rangedate = '30'; }

 

public class TaskleaderNO
{ 
    public PageReference Refreshtsk() {
        return page.task_leadershipNO;
    }
    
     public PageReference toexcel() {
        return page.Task_LeadershipXLSNO;
    }
     
  public String positions {get; set;}
  public String rangedate {get; set;}

  Date candDate;
  Date candDate1;

  public TaskLeaderNO()
  {
     rangedate='30';
     candDate=System.today();    
  }

Set<Id> tskNo = new Set<Id>();
{
   candDate1=System.today()-Integer.valueOf(rangedate);
   for (Task t: [Select WhoID FROM task WHERE OwnerID = :UserInfo.getUserId() and activitydate >= :candDate1])
   tskNo.add(t.WhoID);
}
  
  private List<Contact> ContactNO;
  public List<Contact> getContactNO()
   {
   
Contact[] ContactNO = [Select id, AccountId, Account.name, Email, Name, account.billingcity, account.billingstate from Contact WHERE ID IN ( SELECT Contact__c FROM Leadership_position_code__c WHERE Position__c = :positions and  (Thru_Date__c = null OR Thru_Date__c > :system.today()) and (Start_Date__C <= :system.today()) ) and ID NOT IN :tskNO];
   return ContactNO;
   }

}

 

 

saraha@groupesci.comsaraha@groupesci.com

Is this your entire file or did you remove some parts? The reason I ask is that there is no function called Refreshtsk() in the code you posted, and this chunk shouldn't even compile:

 

 

Set<Id> tskNo = new Set<Id>();
{
   candDate1=System.today()-Integer.valueOf(rangedate);
   for (Task t: [Select WhoID FROM task WHERE OwnerID = :UserInfo.getUserId() and activitydate >= :candDate1])
   tskNo.add(t.WhoID);
}

Have you tried putting a system.debug before line 25 to see what value is actually in rangedate?

 

--Sarah

BaguiarBaguiar

Sarah, that is the entire file for the controller. I'll post the VF page again. The Refreshtsk() is on the very top and it is a pagereference . A button on the VF page calls it {!Refreshtsk} . Like you said, the whole page gets reloaded.

 

this is the entire VF page;
 

<apex:page controller="TaskleaderNO" showHeader="false" >
<apex:form >
<apex:pageBlock id="pageBlock" Title="NO Activities with Key Leadership">
<apex:pageMessages ></apex:pageMessages>
<b>Select Position:   </b>
<apex:selectList id="Positions" value="{!positions}" size="1">
<apex:selectoption itemLabel="President" itemValue="President"></apex:selectoption>
<apex:selectoption itemLabel="Co-President" itemValue="Co-President"></apex:selectoption>
</apex:selectList>
<b> How many days back? </b>
<apex:selectList id="rangedate" value="{!rangedate}" size="1" >
<apex:selectoption itemLabel="30" itemValue="30"></apex:selectoption>
<apex:selectoption itemLabel="60" itemValue="60"></apex:selectoption>
</apex:selectList>
<b>   </b>
<apex:commandButton action="{!Refreshtsk}" value="Run Report" rerender="tblContactNo" />
<b>   </b>
<apex:commandButton action="{!toexcel}" value="Export to Excel" />
<apex:pageBlockTable value="{!ContactNO}" var="cont" rendered="{!NOT(ISNULL(ContactNO))}" id="tblContactNo">
<apex:column headerValue="Name" ><apex:outputLink value="/{!cont.id}" target="_parent">{!cont.Name}
</apex:outputLink></apex:column>
<apex:column value="{!cont.Email}"></apex:column>
<apex:column headerValue="Congregation" value="{!cont.account.name}"></apex:column>
</apex:pageBlockTable>
</apex:pageBlock>
</apex:form>

</apex:page>

 

Thanks a lot for the help!

saraha@groupesci.comsaraha@groupesci.com

Ok I see it, sorry. I still don't understand how the following code fits in, it's not in any function:

 

 

{
   candDate1=System.today()-Integer.valueOf(rangedate);
   for (Task t: [Select WhoID FROM task WHERE OwnerID = :UserInfo.getUserId() and activitydate >= :candDate1])
   tskNo.add(t.WhoID);
}

 

I think that may be the problem, that code is just running at the wrong time. When do you want it to run?

BaguiarBaguiar

That can run only when the user hits the <apex:commandButton action="{!Refreshtsk}"...

When the VF page is initally loaded, it is not necessary for the <apex:pageBlockTable value="{!ContactNO}"... to load. But when the user makes the selection on the "Positions" and on the "rangedate" SelectLists and hits the button

<apex:commandButton action="{!Refreshtsk}" value="Run Report" rerender="tblContactNo" />

 Then, the pageblocktable needs to load. 

 

 

saraha@groupesci.comsaraha@groupesci.com

But why aren't these lines in getTskNo?

 

 

   candDate1=System.today()-Integer.valueOf(rangedate);
   for (Task t: [Select WhoID FROM task WHERE OwnerID = :UserInfo.getUserId() and activitydate >= :candDate1])
   tskNo.add(t.WhoID);

 

Also, you should try returning null from RefreshTsk instead of that page reference.

saraha@groupesci.comsaraha@groupesci.com

I mean why aren;t those lines in getContactNO?

This was selected as the best answer
BaguiarBaguiar

Your question is my answer! :) That is definately using the rangedate. But one weird (but maybe simple) thing is happening.

 

When I load the page, select the values on the lists and hit the "Run report" button, it brings me the correct results, lets say If I pick "30" for the rangedate. When I change to '60' and click on the button , it changes the results according to the selcted value '60'. For some reason, if i change back to '30', and hit the button, it doesn't bring me the same results as i ahd when I innitially loaded the page and hit the button for the first time...

 

It is like ai have to load the page again, select 30 on the Select list and hit the button to get the correct results on the page...

 

BTW, you are by far the quickest help I've ever had!

 

Thanks a million!

saraha@groupesci.comsaraha@groupesci.com

You probably need to clear blah each time, try this (wherever you ended up putting these lines), line added in red:

 

 

candDate1=System.today()-Integer.valueOf(rangedate);
tskNo = new List<Task>();
for (Task t: [Select WhoID FROM task WHERE OwnerID = :UserInfo.getUserId() and activitydate >= :candDate1])
    tskNo.add(t.WhoID);

 

 

It works from 30 to 60 because you are just adding more records. When you go back to 30, you still have the 60 records in there.

 

 --Sarah

 

 

BaguiarBaguiar

Sarah!

That was it, but I had to modify it to " tskNO = new Set<ID>();  ".

 

Really... Thanks a Million for your help!