Difference between revisions of "HowTo:Compute a Date Value in a View or Report"
From AgileApps Support Wiki
imported>Aeric (Created page with "{{subst:HowTo Guide}}") |
imported>Aeric |
||
Line 1: | Line 1: | ||
<noinclude> | |||
{{Orientation | | {{Orientation | Designers| Intermediate | 10}} | ||
</noinclude>This guide achieves two goals: | |||
: | :* Show how to compute a value for a view or report, with creating a data field. | ||
:* | :* Show how to convert a date to text form. | ||
:* | |||
===Heading=== | ===Heading=== | ||
CONCAT( | |||
IF( MONTH(DATECREATED)=01, 'Jan, ', | |||
IF (MONTH(DATECREATED)=02, 'Feb, ', | |||
IF (MONTH(DATECREATED)=03, 'Mar, ', | |||
IF (MONTH(DATECREATED)=03, 'Apr, ', | |||
IF (MONTH(DATECREATED)=03, 'May, ', | |||
IF (MONTH(DATECREATED)=03, 'Jun, ', | |||
IF (MONTH(DATECREATED)=03, 'Jul, ', | |||
IF (MONTH(DATECREATED)=03, 'Aug, ', | |||
IF (MONTH(DATECREATED)=03, 'Sep, ', | |||
IF (MONTH(DATECREATED)=03, 'Oct, ', | |||
IF (MONTH(DATECREATED)=11, 'Nov, ', 'Dec, ') ) ) ) ) ) ) ) ) ) ) , | |||
TEXT(YEAR(DATECREATED) ) ) | |||
===Other Options==== | |||
Here are some other ways to achieve the same goal: | |||
# Create a Formula Field in the record that tests the month number to store the appropriate string for the month (IF(MONTH(DATECREATED)=01, 'January ', etc.). Then add a computed field to the View that tacks on TEXT(YEAR(DATECREATED)) to that value. | |||
#: | |||
-- | # Create a text field in the record for the month, then create a Data Policy that runs on add and update. Execute Java code in the data policy, and put the month names in a HashMap or Array. Then tack on the year value, as above. | ||
#: | |||
#: Create a Utility class. In that class, create a function that takes a date argument and returns a string in the desired format. Use an add/update data policy to do the conversion. In the data policy, access the function to store the month/day form in the record. (Advantage: The function can be used for any date. Disadvantage: Extra storage space for the same value, in two formats.) | |||
#: | |||
# Use a JSP page and a SQL query to create the desired view, and then add Javascript functions to extract the part of the date you want to display. (Advantage: No extra space required. Disadvantage: More hacking required--unless you enjoy hacking...) | |||
#: ''Learn more:'' [[HowTo:Use_a_SQL_Query_to_List_Records_in_a_Custom_Page]] |
Revision as of 23:46, 31 January 2012
For: Designers See more: |
This guide achieves two goals:
- Show how to compute a value for a view or report, with creating a data field.
- Show how to convert a date to text form.
Heading
CONCAT( IF( MONTH(DATECREATED)=01, 'Jan, ', IF (MONTH(DATECREATED)=02, 'Feb, ', IF (MONTH(DATECREATED)=03, 'Mar, ', IF (MONTH(DATECREATED)=03, 'Apr, ', IF (MONTH(DATECREATED)=03, 'May, ', IF (MONTH(DATECREATED)=03, 'Jun, ', IF (MONTH(DATECREATED)=03, 'Jul, ', IF (MONTH(DATECREATED)=03, 'Aug, ', IF (MONTH(DATECREATED)=03, 'Sep, ', IF (MONTH(DATECREATED)=03, 'Oct, ', IF (MONTH(DATECREATED)=11, 'Nov, ', 'Dec, ') ) ) ) ) ) ) ) ) ) ) ,
TEXT(YEAR(DATECREATED) ) )
Other Options=
Here are some other ways to achieve the same goal:
- Create a Formula Field in the record that tests the month number to store the appropriate string for the month (IF(MONTH(DATECREATED)=01, 'January ', etc.). Then add a computed field to the View that tacks on TEXT(YEAR(DATECREATED)) to that value.
- Create a text field in the record for the month, then create a Data Policy that runs on add and update. Execute Java code in the data policy, and put the month names in a HashMap or Array. Then tack on the year value, as above.
- Create a Utility class. In that class, create a function that takes a date argument and returns a string in the desired format. Use an add/update data policy to do the conversion. In the data policy, access the function to store the month/day form in the record. (Advantage: The function can be used for any date. Disadvantage: Extra storage space for the same value, in two formats.)
- Use a JSP page and a SQL query to create the desired view, and then add Javascript functions to extract the part of the date you want to display. (Advantage: No extra space required. Disadvantage: More hacking required--unless you enjoy hacking...)