I’m not sure what you mean here  Shaik.  Do you want a list of dates to appear in the drop down,  and then have the selected date filter the table of records?  Or do you want a diffferent sort of control used to set the created date?  (Which is probably not possible) 

Hi Rob,

Thanks for your replay,

Our scenario:a list of dates to appear in the drop down, and then have the selected date filter the table of records.

Thanks,
Shaik.

That is an interesting scenario. It will take some customization to get done, but here is how:

1. Set up a list of dates.

I assume you will want the drop down box to show only the dates on which records were actually created. To do this we need to build an aggregate model on the same object as our table. (I’ll use accounts for convenience). This model needs to be grouped by created date. Unforunately there is no date function that returns the full mm/dd/yyyy format for a date time field - so we have to group records three times. To do this you need to go to the XML.

So… on your aggregate model, group createdDate by Calendar year, save your page, click the “View/Edit XML” link at the bottom of the page, find the grouping section of your aggregate model and copy the Calendar Year grouping twice. Then adjust the name and function as shown below.

You probably also want to sort this model so it returns the most recent dates and only returns a limited number of dates. (Be careful of an incredibly LONG drop down box). That is done on the advanced tab of the model.

**2. Create a field that can be used to filter records. **

You cannot just build a condition on created date, because it is a date/time field, and you would never get a match if you tried to compare a date from your list to a date time field. I created a formula field that pulled out the date elements of the created date field. I also wanted to make sure that no leading zeros were in that. Here is the formula I used:

TEXT(YEAR( DATEVALUE (CreatedDate)))& "-" & TEXT(MONTH( DATEVALUE (CreatedDate)))& "-" & TEXT (DAY( DATEVALUE (CreatedDate)))

Make a condition on that formula field is filterable-default off.

**3. Create a filter that ties the two together. **

Now to pull things together. Create a filter on your table of type “Select Option” It should manually choose conditions and options. Add a Filter Option Source of type “Rows in a Model” and link it to the model you created in step 1. Use our merge syntax to pull together the three created date grouping fields from that list. I adjusted the Lable template slightly so that it looked better as a date (MM/DD/YYYY) while the value to pass into the condition matched the output of my formula field from step 2 (YYYY-MM-DD).

That should do it.

A final tip.

You could also add a second condition directly on the real CreatedDate field, and some manual sources to your filter so that in addition to specific dates you could filter on “Last Month”, “Last Quarter”, “Last Year” Etc. These manual sources would need to do two things. They would need to deactivate the condition you used in step 3 above, and they would need to activate and send value to the second condition on the real CreatedDate field. You would also need to deactivate the second condition as part of the option source you set up in step 3 above.

All should be good. Have a happy friday.