You need to sign in to do that
Don't have an account?
JosephJ
Cannot include date field in the query
I'm facing a seious issue here for my query :
String qStr = 'Select OwnerId,Subject,Status,Priority from Task where Subject like \'%'+searchText+'%\' OR Status like \'%'+searchText+'%\' Order By Subject,Status limit ' + QueryLimit + ' offset ' + OffsetSize;
Now the issue is , i need to filter this by one more field called "ActivityDate " which is for Task object. The document says , LIKE operator works only on Strings and hence if i'm including OR ActivityDate like \'%'+searchText+'%\' it will not work.. How can i get this ? I'm stucked here.Thanks in advance.
public class PagingTasksController1{
public List<Task> tasks;
public Integer CountTotalRecords{get;set;}
public String QueryString {get;set;}
public Integer OffsetSize = 0;
private Integer QueryLimit = 5;
public List<Task> lstTasks;
public String searchText {get;set;}
public string sortField = 'Subject'; // default sort column
private string sApplySOQL = '';
// the current sort direction. defaults to ascending
public String sortDir {
get { if (sortDir == null) { sortDir = 'asc'; } return sortDir; }
set;
}
// the current field to sort by. defaults to role name
public String getsortField() {
return sortField;
}
// the current field to sort by.
public void setsortField(string value) {
sortField = value;
}
// toggles the sorting of query from asc<-->desc
public void toggleSort() {
// simply toggle the direction
sortDir = sortDir.equals('asc') ? 'desc' : 'asc';
integer iIndex = sApplySOQL.indexOf('Order By');
if (iIndex > -1){
sApplySOQL = sApplySOQL.substringBefore('Order By');
sApplySOQL = sApplySOQL + ' Order By ' + sortField + ' ' + sortDir;
}
tasks = Database.query(sApplySOQL );
}
public PagingTasksController1 (){
//CountTotalRecords= [select count() from Task];
}
public List<Task> getTasks(){
if(tasks == null){
tasks = new List<Task>();
}
return tasks;
}
public void findTasks(){
String qStr2 = 'Select count() from Task where Subject like \'%'+searchText+'%\' OR Status like \'%'+searchText+'%\'';
CountTotalRecords = Database.countQuery(qStr2);
queryTasks();
}
public void queryTasks(){
String qStr = 'Select OwnerId,Subject,Status,Priority from Task where Subject like \'%'+searchText+'%\' OR Status like \'%'+searchText+'%\' Order By Subject,Status limit ' + QueryLimit + ' offset ' + OffsetSize;
sApplySOQL = qStr;
tasks = Database.query(sApplySOQL );
//tasks.sort();
}
public Boolean getDisablePrevious(){
if(OffsetSize>0){
return false;
}
else return true;
}
public Boolean getDisableNext() {
if (OffsetSize + QueryLimit < countTotalRecords){
return false;
}
else return true;
}
public PageReference Next() {
OffsetSize += QueryLimit;
queryTasks();
return null;
}
public PageReference Previous() {
OffsetSize -= QueryLimit;
queryTasks();
return null;
}
public PageReference save() {
update tasks;
return ApexPages.CurrentPage();
}
}
String qStr = 'Select OwnerId,Subject,Status,Priority from Task where Subject like \'%'+searchText+'%\' OR Status like \'%'+searchText+'%\' Order By Subject,Status limit ' + QueryLimit + ' offset ' + OffsetSize;
Now the issue is , i need to filter this by one more field called "ActivityDate " which is for Task object. The document says , LIKE operator works only on Strings and hence if i'm including OR ActivityDate like \'%'+searchText+'%\' it will not work.. How can i get this ? I'm stucked here.Thanks in advance.
public class PagingTasksController1{
public List<Task> tasks;
public Integer CountTotalRecords{get;set;}
public String QueryString {get;set;}
public Integer OffsetSize = 0;
private Integer QueryLimit = 5;
public List<Task> lstTasks;
public String searchText {get;set;}
public string sortField = 'Subject'; // default sort column
private string sApplySOQL = '';
// the current sort direction. defaults to ascending
public String sortDir {
get { if (sortDir == null) { sortDir = 'asc'; } return sortDir; }
set;
}
// the current field to sort by. defaults to role name
public String getsortField() {
return sortField;
}
// the current field to sort by.
public void setsortField(string value) {
sortField = value;
}
// toggles the sorting of query from asc<-->desc
public void toggleSort() {
// simply toggle the direction
sortDir = sortDir.equals('asc') ? 'desc' : 'asc';
integer iIndex = sApplySOQL.indexOf('Order By');
if (iIndex > -1){
sApplySOQL = sApplySOQL.substringBefore('Order By');
sApplySOQL = sApplySOQL + ' Order By ' + sortField + ' ' + sortDir;
}
tasks = Database.query(sApplySOQL );
}
public PagingTasksController1 (){
//CountTotalRecords= [select count() from Task];
}
public List<Task> getTasks(){
if(tasks == null){
tasks = new List<Task>();
}
return tasks;
}
public void findTasks(){
String qStr2 = 'Select count() from Task where Subject like \'%'+searchText+'%\' OR Status like \'%'+searchText+'%\'';
CountTotalRecords = Database.countQuery(qStr2);
queryTasks();
}
public void queryTasks(){
String qStr = 'Select OwnerId,Subject,Status,Priority from Task where Subject like \'%'+searchText+'%\' OR Status like \'%'+searchText+'%\' Order By Subject,Status limit ' + QueryLimit + ' offset ' + OffsetSize;
sApplySOQL = qStr;
tasks = Database.query(sApplySOQL );
//tasks.sort();
}
public Boolean getDisablePrevious(){
if(OffsetSize>0){
return false;
}
else return true;
}
public Boolean getDisableNext() {
if (OffsetSize + QueryLimit < countTotalRecords){
return false;
}
else return true;
}
public PageReference Next() {
OffsetSize += QueryLimit;
queryTasks();
return null;
}
public PageReference Previous() {
OffsetSize -= QueryLimit;
queryTasks();
return null;
}
public PageReference save() {
update tasks;
return ApexPages.CurrentPage();
}
}
I am not sure if I understand but if someone is searching for a date, I would rather add a additional search box for Date only asking the user to pick for a date. And yes you cannot do a search for date using LIKE as it doesnt make any sense.
Also when you are creating a dynamic SOQL for date please use the format YYYY/MM/DDThh:mm:ssz without any quotes.
You add an additional inputTexts for searching within date range.
1. Define fromDate & toDate Date types in your controller
2. Add two <apex:inputs for entering the dates
3. when users enters dates and clicks on search, in your queryTasks() method, add the dates to the SOQL,
Hope this helps
Hope this helps !!
<apex:form >
<apex:input label="datePicker" value="{!fromDate}" type="auto"/>
<apex:input label="datePicker" value="{!toDate}" type="auto"/>
</apex:form>
public class PagingTasksController1
{
public Date fromDate {get;set;}
public Date toDate {get;set;}
}
How do i mention in my query here,becuase it says LIKE does not support Dates
String qStr = 'Select OwnerId,Subject,Status,Priority from Task where Subject like \'%'+searchText+'%\' OR Status like \'%'+searchText+'%\' Order By Subject,Status limit ' + QueryLimit + ' offset ' + OffsetSize;
Really appreciate the help.
Thanks for the information. I feel shy posting this. I'm getting an error Error: Could not resolve field 'ActitvityDate' from <apex:outputField> value binding '{!tsk.ActitvityDate}' in page New_Test_task_Assignment in Vf page.Below is my code.
public class PagingTasksController1{
public List<Task> tasks;
public Integer CountTotalRecords{get;set;}
public String QueryString {get;set;}
public Integer OffsetSize = 0;
private Integer QueryLimit = 10;
public List<Task> lstTasks;
public String searchText {get;set;}
public Date fromDate {get;set;}
public Date toDate {get;set;}
public string sortField = 'Subject'; // default sort column
private string sApplySOQL = '';
// the current sort direction. defaults to ascending
public String sortDir {
get { if (sortDir == null) { sortDir = 'asc'; } return sortDir; }
set;
}
// the current field to sort by. defaults to role name
public String getsortField() {
return sortField;
}
// the current field to sort by.
public void setsortField(string value) {
sortField = value;
}
// toggles the sorting of query from asc<-->desc
public void toggleSort() {
// simply toggle the direction
sortDir = sortDir.equals('asc') ? 'desc' : 'asc';
integer iIndex = sApplySOQL.indexOf('Order By');
if (iIndex > -1){
sApplySOQL = sApplySOQL.substringBefore('Order By');
sApplySOQL = sApplySOQL + ' Order By ' + sortField + ' ' + sortDir + ' limit ' + QueryLimit + ' offset ' + OffsetSize;
}
tasks = Database.query(sApplySOQL );
}
public PagingTasksController1 (){
//CountTotalRecords= [select count() from Task];
}
public List<Task> getTasks(){
if(tasks == null){
tasks = new List<Task>();
}
return tasks;
}
public void findTasks(){
String qStr2 = 'Select count() from Task where Subject like \'%'+searchText+'%\' OR Status like \'%'+searchText+'%\'';
CountTotalRecords = Database.countQuery(qStr2);
queryTasks();
}
public void queryTasks(){
String qStr = 'Select OwnerId,Subject,Status,Priority,ActivityDate from Task where Subject like \'%'+searchText+'%\' OR Status like \'%'+searchText+ '%\' Order By ' + sortField;
if(fromDate != null)
qStr += ' AND ActivityDate >= ' + fromDate;
if(toDate != null)
qStr += ' AND ActivityDate <= ' + toDate;
qStr += ' Order By Subject,Status limit ' + QueryLimit + ' offset ' + OffsetSize;
System.debug('**Query Generated**');
System.debug(qStr);
sApplySOQL = qStr;
tasks = Database.query(sApplySOQL );
//tasks.sort();
}
public Boolean getDisablePrevious(){
if(OffsetSize>0){
return false;
}
else return true;
}
public Boolean getDisableNext() {
if (OffsetSize + QueryLimit < countTotalRecords){
return false;
}
else return true;
}
public PageReference Next() {
OffsetSize += QueryLimit;
queryTasks();
return null;
}
public PageReference Previous() {
OffsetSize -= QueryLimit;
queryTasks();
return null;
}
public PageReference save() {
update tasks;
return ApexPages.CurrentPage();
}
}
<apex:page controller="PagingTasksController1" docType="html-5.0">
<apex:form >
<apex:pageBlock title="Tasks" id="pgBlock">
<apex:pageBlockButtons >
<apex:commandButton action="{!save}" id="saveButton" value="Save"/>
<apex:commandButton onclick="resetInlineEdit()" id="cancelButton" value="Cancel"/>
</apex:pageBlockButtons>
<apex:inlineEditSupport showOnEdit="saveButton, cancelButton"
hideOnEdit="editButton" event="ondblclick"
changedStyleClass="myBoldClass" resetFunction="resetInlineEdit"/>
<apex:inputText id="searchBox" value="{!searchText}"/>
<apex:commandButton value="Search" reRender="pgTable,pgBlock" action="{!findTasks}"/>
<apex:pageBlockTable value="{!Tasks}" var="tsk" id="pgTable" >
<apex:input label="datePicker" value="{!fromDate}" type="auto"/>
<apex:input label="datePicker" value="{!toDate}" type="auto"/>
<apex:column >
<apex:outputLink value="{!URLFOR($Action.Task.Delete, tsk.id,['retURL'='/apex/New_Test_task_Assignment'])}" >Delete</apex:outputLink>
</apex:column>
<apex:column headerValue="Subject">
<apex:facet name="header">
<apex:commandLink value="Subject" action="{!toggleSort}" rerender="pgTable" >
<apex:param name="sortField" value="Subject" assignTo="{!sortField}"/>
<apex:outputPanel rendered="{!BEGINS(sortField,'Subject')}">
<apex:image value="{!IF(sortDir = 'asc','/img/arrowDown.gif','/img/arrowUp.gif')}"/>
</apex:outputPanel>
</apex:commandLink>
</apex:facet>
<apex:outputField value="{!tsk.Subject}"/>
</apex:column>
<apex:column headerValue="Status">
<apex:facet name="header">
<apex:commandLink value="Status" action="{!toggleSort}" rerender="pgTable" >
<apex:param name="sortField" value="Status" assignTo="{!sortField}"/>
<apex:outputPanel rendered="{!BEGINS(sortField,'Status')}">
<apex:image value="{!IF(sortDir = 'asc','/img/arrowDown.gif','/img/arrowUp.gif')}"/>
</apex:outputPanel>
</apex:commandLink>
</apex:facet>
<apex:outputField value="{!tsk.Status}"/>
</apex:column>
<apex:column headerValue="Priority">
<apex:outputField value="{!tsk.Priority}"/>
</apex:column>
<apex:column headerValue="OwnerId">
<apex:outputField value="{!tsk.OwnerId}"/>
</apex:column>
<apex:column headerValue="date">
<apex:outputField value="{!tsk.ActitvityDate}"/>
</apex:column>
</apex:pageBlockTable>
<apex:pageBlockButtons >
<apex:commandButton value="Previous" action="{!Previous}" rerender="pgTable,pgBlock"
status="status" disabled="{!DisablePrevious}" />
<apex:commandButton value="Next" action="{!Next}" reRender="pgTable,pgBlock"
status="status" disabled="{!DisableNext}" />
<apex:actionStatus id="status" startText="Please Wait..."/>
</apex:pageBlockButtons>
</apex:pageBlock>
</apex:form>
<apex:enhancedlist type="Activity" height="800" rowsPerPage="50" customizable="False"/>
</apex:page>
I feel its a dumb question, but i'm stucked . Please help
<apex:column headerValue="date">
<apex:outputField value="{!tsk.ActitvityDate}"/>
</apex:column>
unexpected token: 'Order'
Error is in expression '{!findTasks}' in component <apex:commandButton> in page new_test_task_assignment: Class.PagingTasksController1.queryTasks: line 83, column 1
Class.PagingTasksController1.findTasks: line 61, column 1
String qStr = 'Select OwnerId,Subject,Status,Priority,ActivityDate from Task where Subject like \'%'+searchText+'%\' OR Status like \'%'+searchText+ '%\' Order By ' + sortField;
you have it at the bottom, after the Wheres are built.
Thank you