+ Start a Discussion
Lloyd SilverLloyd Silver 

Using a more "normal" date entry field for date range on filter

This is a force.com page that has two date fields used to define a date range to lookup records to display. I want the user to be able to enter a simple date in a format such as MM/DD/YY instead of how it is currently. I've got no clue how to fix this if you could help. Thanks.


public with sharing class EstimateCommList {
    public list<Data_Estimated_Comm> LDEC {get;set;}
    public User user;
    public String curruserid;
    public String CId;
    public String sort_by {get;set;}
    public Date from_date {get;set;}
    public Date to_date {get;set;}
    public EstimateCommList() {
        curruserid = UserInfo.getUserId();
        //get user record with contact__c
        user = [Select u.Name, u.Id, u.ContactId From User u where u.Id =:curruserid];
        CId = user.ContactId;
        from_date = System.today();
        to_date = System.today().addMonths(6);
        from_date =  System.now().addHours(0);
        String SStartDT = from_date.formatGMT('yyyy-MM-dd') + 'T' + StartDT.formatGMT('HH:mm:ss.SSS') + 'Z';
        to_date =  System.now().addHours(4);
        String SEndDT = to_date.formatGMT('yyyy-MM-dd') + 'T' + EndDT.formatGMT('HH:mm:ss.SSS') + 'Z';
        system.debug('from_date: '+from_date+ 'to_date: '+to_date);
        list<Estimated_Commissions__c> LEC = [Select e.Valid_From__c, e.SystemModstamp, e.Name, e.LastModifiedDate, 
                    e.LastModifiedById, e.LastActivityDate, e.IsDeleted, e.Insurance_Case__c, 
                    e.Id, e.FYC_Rate__c, e.FYC_Amount__c, e.Excess_Commission_amount__c, 
                    e.Excess_Commission_Rate__c, e.CreatedDate, e.CreatedById, e.Case_Split__c, 
                    e.Agent__c, e.Agency__c , e.Agent__r.Name , e.Agency__r.Name,
                    e.Insurance_Case__r.Target_Premium__c, e.Insurance_Case__r.Product__r.Name, 
                    e.Insurance_Case__r.Primary_Insured_or_Annuitant__r.Name, e.Insurance_Case__r.Premium_Mode__c, 
                    e.Insurance_Case__r.Line_of_Business__c, e.Insurance_Case__r.Carrier__r.Name
                    From Estimated_Commissions__c e
                    where e.Agent__c  =:CId 
                    order by CreatedDate desc limit 10 ];   
        if(LDEC == null) LDEC = new list<Data_Estimated_Comm>{};
        for ( Estimated_Commissions__c EC : LEC){
            // Create a Data record and add it to the list
            Data_Estimated_Comm DEC = new Data_Estimated_Comm();
            DEC.Agency = EC.Agency__r.Name;   
            DEC.Agent = EC.Agent__r.Name;
            DEC.Casesplit = EC.Case_Split__c;
            DEC.ExcessCommAmt = EC.Excess_Commission_amount__c;
            DEC.ExcessCommRate = EC.Excess_Commission_Rate__c;
            DEC.FYCAmt = EC.FYC_Amount__c;
            DEC.FYCRate = EC.FYC_Rate__c;
            DEC.Insurance_Commission_Name = EC.Name;
            DEC.PolicyNumber = EC.Insurance_Case__r.Policy_Number__c;
            DEC.LineOfBusiness = EC.Insurance_Case__r.Line_of_Business__c;
            DEC.Carrier = EC.Insurance_Case__r.Carrier__r.Name;
            DEC.Product = EC.Insurance_Case__r.Product__r.Name;
            DEC.Client = EC.Insurance_Case__r.Primary_Insured_or_Annuitant__r.Name;
            DEC.PremiumMode = EC.Insurance_Case__r.Premium_Mode__c;
            DEC.Premium = EC.Insurance_Case__r.Target_Premium__c;
            DEC.Valid_From = EC.Valid_From__c;
    public List<SelectOption> getSort() {
            List<SelectOption> options1 = new List<SelectOption>();
            options1.add(new SelectOption('Valid_From__c','Date'));
            options1.add(new SelectOption('Agent__r.Name','Agent Name'));
            options1.add(new SelectOption('Insurance_Case__r.Carrier__r.Name','Carrier'));
            options1.add(new SelectOption('Insurance_Case__r.Line_of_Business__c','Line Of Business'));
            options1.add(new SelectOption('Insurance_Case__r.Product__r.Name','Product'));
            options1.add(new SelectOption('Insurance_Case__r.Policy_Number__c','Policy Number'));
            options1.add(new SelectOption('Insurance_Case__r.Primary_Insured_or_Annuitant__r.Name','Client'));
            options1.add(new SelectOption('Insurance_Case__r.Premium_Mode__c','Premium Mode'));
            options1.add(new SelectOption('Insurance_Case__r.Target_Premium__c','Premium'));
            options1.add(new SelectOption('FYC_Rate__c','FYC Rate'));
            options1.add(new SelectOption('FYC_Amount__c','FYC Amount'));
            options1.add(new SelectOption('Excess_Commission_Rate__c','Excess Rate'));
            options1.add(new SelectOption('Excess_Commission_amount__c','Excess Amount'));
            return options1;
    public void searchEsti() {
        List<Estimated_Commissions__c> LEC = new  List<Estimated_Commissions__c>{};
        String DB1 = 'Select e.Valid_From__c, e.SystemModstamp, e.Name, e.LastModifiedDate,'; 
               DB1 += 'e.LastModifiedById, e.LastActivityDate, e.IsDeleted, e.Insurance_Case__c,'; 
                    DB1 += 'e.Id, e.FYC_Rate__c, e.FYC_Amount__c, e.Excess_Commission_amount__c, '; 
                    DB1 += 'e.Excess_Commission_Rate__c, e.CreatedDate, e.CreatedById, e.Case_Split__c,'; 
                    DB1 += 'e.Agent__c, e.Agency__c , e.Agent__r.Name , e.Agency__r.Name,';
                    DB1 += 'e.Insurance_Case__r.Target_Premium__c, e.Insurance_Case__r.Product__r.Name,'; 
                    DB1 += 'e.Insurance_Case__r.Primary_Insured_or_Annuitant__r.Name, e.Insurance_Case__r.Premium_Mode__c,'; 
                    DB1 += 'e.Insurance_Case__r.Policy_Number__c, ';
                    DB1 += 'e.Insurance_Case__r.Line_of_Business__c, e.Insurance_Case__r.Carrier__r.Name';
                    DB1 += 'From Estimated_Commissions__c e ';
                    DB1 += ' where e.Agent__c  =:CId ';
                    DB1 += ' and e.Valid_From__c >= '+from_date;
                    DB1 += ' and e.Valid_From__c <= '+to_date;
                    DB1 += ' order by '+ sort_by + ' asc ' ;
                    DB1 += ' limit 10';
                    LEC = DataBase.query(DB1);  
        LDEC = new list<Data_Estimated_Comm>{};
        for ( Estimated_Commissions__c est : LEC){
            // Create a Data record and add it to the list
            Data_Estimated_Comm DEC = new Data_Estimated_Comm();
            DEC.Agency = est.Agency__r.Name;   
            DEC.Agent = est.Agent__r.Name;
            DEC.Casesplit = est.Case_Split__c;
            DEC.ExcessCommAmt = est.Excess_Commission_amount__c;
            DEC.ExcessCommRate = est.Excess_Commission_Rate__c;
            DEC.FYCAmt = est.FYC_Amount__c;
            DEC.FYCRate = est.FYC_Rate__c;
            DEC.Insurance_Commission_Name = est.Name;
            DEC.PolicyNumber = est.Insurance_Case__r.Policy_Number__c;
            DEC.LineOfBusiness = est.Insurance_Case__r.Line_of_Business__c;
            DEC.Carrier = est.Insurance_Case__r.Carrier__r.Name;
            DEC.Product = est.Insurance_Case__r.Product__r.Name;
            DEC.Client = est.Insurance_Case__r.Primary_Insured_or_Annuitant__r.Name;
            DEC.PremiumMode = est.Insurance_Case__r.Premium_Mode__c;
            DEC.Premium = est.Insurance_Case__r.Target_Premium__c;
            DEC.Valid_From = est.Valid_From__c;



<apex:page controller="EstimateCommList" showHeader="false" standardStylesheets="true">
<!-- The site template provides layout & style for the site -->
    <apex:composition template="{!$Site.Template}">

    <apex:define name="body">
    <apex:form style="position:relative;top:50px;margin-left:25px;" >   
        <div style="width:1200px;margin-left:20px;">
        <apex:pageBlock title="Estimated Commission List">          
            <apex:pageblockSection title="Search Section" columns="2" >
                    <apex:outputText value="Date Range " style="margin-left:100px;"></apex:outputText>
                    <apex:inputText value="{!from_date}" style="margin-left:20px; width:155px;"></apex:inputText> 
                    <apex:outputText value="To" style="margin-left:20px; display: block; text-align: right;"></apex:outputText>
                    <apex:inputText value="{!to_date}" style="margin-left:20px; width:155px;"></apex:inputText> 
                    <apex:outputText value="Sort by" style="margin-left:100px;"></apex:outputText>
                    <apex:selectList value="{!sort_by}" id="sort1" multiselect="false" size="1" style="padding-left: 30px;">
                            <apex:selectOptions value="{!Sort}" /> 
                    <apex:commandButton style="width:75px;margin-left:250px;" action="{!searchEsti}" value="Search"/>
            <apex:pageBlockTable value="{!LDEC}" var="e"   id="instancesTable">
                <apex:column >   
                    <apex:facet name="header"> Date</apex:facet>
                    <apex:outputText value="{0,date,MM'/'dd'/'yyyy }">
                                   <apex:param value="{!e.Valid_From}" /> 
                <apex:column >   
                    <apex:facet name="header"> Agent Name</apex:facet>
                    <apex:outputText value="{!e.Agent}" ></apex:outputText>          
                <apex:column >   
                    <apex:facet name="header"> Carrier</apex:facet>
                    <apex:outputText value="{!e.Carrier}" ></apex:outputText>           
                <apex:column >   
                    <apex:facet name="header"> Line Of Business</apex:facet>
                    <apex:outputText value="{!e.LineOfBusiness}" ></apex:outputText>           
                <apex:column >   
                    <apex:facet name="header"> Product</apex:facet>
                    <apex:outputText value="{!e.Product}" ></apex:outputText>           
                <apex:column >
                    <apex:facet name="header"> Policy Number</apex:facet> 
                    <apex:outputText value="{!e.PolicyNumber}"></apex:outputText>          
                <apex:column >   
                    <apex:facet name="header"> Client</apex:facet>
                    <apex:outputText value="{!e.Client}" ></apex:outputText>          
                <apex:column >   
                    <apex:facet name="header"> Premium Mode</apex:facet>
                     <apex:outputText value="{!e.PremiumMode}" ></apex:outputText>          
                <apex:column >   
                    <apex:facet name="header"> Premium</apex:facet>
                <apex:outputText value="{!e.Premium}" ></apex:outputText>          
                <apex:column >
                    <apex:facet name="header"> FYC Rate</apex:facet>
                    <apex:outputText value="{!e.FYCRate}"></apex:outputText> 
                <apex:column >
                    <apex:facet name="header"> FYC Amount</apex:facet> 
                    <apex:outputText value="{!e.FYCAmt}"></apex:outputText>          
                <apex:column >
                    <apex:facet name="header"> Excess Rate </apex:facet> 
                    <apex:outputText value="{!e.ExcessCommRate}"></apex:outputText>          
                <apex:column >
                    <apex:facet name="header"> Excess Amount </apex:facet> 
                    <apex:outputText value="{!e.ExcessCommAmt}"></apex:outputText>           
                <apex:column >
                    <apex:facet name="header"> Insurance Commission Name</apex:facet> 
                    <apex:outputText value="{!e.Insurance_Commission_Name}"></apex:outputText>          
                <apex:column >
                    <apex:facet name="header"> Agency</apex:facet>
                    <apex:outputText value="{!e.Agency}" ></apex:outputText> 
                 <apex:column >
                    <apex:facet name="header"> Case split</apex:facet>
                    <apex:outputText value="{!e.Casesplit}"></apex:outputText> 
                <apex:column >
                    <apex:facet name="header"> Excess Commission amount</apex:facet>
                    <apex:outputText value="{!e.ExcessCommAmt}"></apex:outputText>           
                <apex:column >
                    <apex:facet name="header"> Excess Commission Rate</apex:facet>
                    <apex:outputText value="{!e.ExcessCommRate}"></apex:outputText> 

kevin lamkevin lam
Do you mean you want the users to be able to enter from_date and to_date as other date fields with a datepicker?
Lloyd SilverLloyd Silver
Right now the default text is in a format similar to Thu May 22 00:00:00 GMT 2014. I would prefer if the user could just enter 05/22/14 (as an example). And actually, it looks like the search isn't working correctly anyway. So I could use help not only making the date format what I'm looking for, but also getting it to work at all.

In the connector it's lines 22 and 23, and 114 and 115.

kevin lamkevin lam
An option is to make use of two date fields of a dummy object, e.g. StartDate and EndDate of Contract, and change <apex:inputText> to <apex:inputField> in your visualforce page for those two date fields.
I'm still struggling to figure this out if there's any additional help or feedback.

To summarize the issue, I have a VF page where a user can input a start date and end date and then an object's records are searched and returned if a date field in those records is within the start date and end date range. I'd like the user to be able to input a start date and end date in a format such as MM/DD/YY.

kevin lamkevin lam
With my previous suggestion, users will be able to enter the dates in their locale format, then in your SOQL string, you just need to convert it into the standard yyyy-MM-dd like this:

DB1 += ' and e.Valid_From__c >= ' + String.valueOf(from_date);
So I understand changing <apex:inputText> to <apex:inputField>. But I"m confused about a couple of other things.

You say to make use of two date fields of a dummy object, e.g. StartDate and EndDate. Do you mean that I should create two custom fields on an object like the Estimated_Commissions__c object? I tried doing that, and using the name of the field (Estimated_Commissions__c.StartDate__c) as the value in <apex:inputField"> but that didn't work.

Then I tried just chaning <apex:inputText> to <apex:inputValue> and that didn't work either.

So I'm definitely doing something wrong.

Thanks for the help.
kevin lamkevin lam
Assuming that you only need those two date fields for capturing the inputs from the user and subsequently use them in the SOQL query, then you don't need to create two custom fields for them, what you need are:

A new variable for a dummy Contract record:
Contract dummyContract {get; set;}

Initialise it in your constructor:
dummyContract = new Contract();

Update the input components of from and to dates:
<apex:inputField value="{!dummyContract.StartDate}/>
<apex:inputField value="{!dummyContract.EndDate}/>

Update your SOQL query:
DB1 += ' and e.Valid_From__c >= ' + String.valueOf(dummyContract.StartDate);
DB1 += ' and e.Valid_From__c <= ' + String.valueOf(dummyContract.EndDate);
Lloyd SilverLloyd Silver
Okay I think I'm close.

I updated the class to include Contract dummyContract {get; set;}

I updated the SOQL query in the class.

I changed the inputText to input Field.

Not sure what to do with dummyContract = new Contract(); though if you could provide direction with that. 

kevin lamkevin lam
Just add that to the beginnng of your constructor like this (added new code to initialise those two date field):

public EstimateCommList() {
        dummyContract = new Contract(StartDate = System.today(), EndDate = System.today().addMonths(6));
        curruserid = UserInfo.getUserId();