HowTo:Compute a Date Value in a View or Report

For:   Designers Level: Intermediate Time:  10 minutes See more:     ◾ HowTo Guides

This guide shows how to compute a value for a view or report, without creating a data field in the record, and how to use some of the many available functions to convert a date into a text format.

Compute a Field for a View or Report

Not all fields need to take up storage space. When the data you care about is a variation of data that is already contained in the record, you can add a Computed Field to a View or Report to construct that variation.

Start by opening the view or report you care about:

1. Go to the Reports tab
2. Click the Edit link for the Report you want to modify
   

Then create a computed field in that view or report:

1. Click the link: New Computed Field
   A formula field opens.
2. Provide a label for the column heading. For example: Created When
3. Enter the type of value the formula will return. In this case: text.
4. Use the formula builder to create the formula you want, using combinations of the many possible functions. (The next section illustrates a few of them.)

Learn more:

• Computed Fields
• Formula Functions

Convert a Date to Text

In this case, the goal is convert a date (like DATECREATED into a month day like "Feb, 2012". The following formula does that:

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) ) 
)

The formula uses the CONCAT function to combine the month-expression with the year-expression. The year-expression is simple: TEXT(YEAR(DATECREATED). The month-expression uses a sequence of nested IF statements.

The syntax of the IF statement is IF (test, value-if-true, value-if-false). So the first test is MONTH(DATECREATED)=01 and the value returned if that expression is true is the string, 'Jan, '.

The tricky bit is the value-if-false part of the expression. There, a whole new copy of the expression needs to be embedded, to test for the month = 02. If it is, then the value of that expression is 'Feb'. But if the month isn't 02, yet another copy is needed to test for 03... and so on down to 11. (If that fails, the answer is 'Dec'). In outline, that part of the formula looks like this:

IF( month=01, 'Jan', IF (month=02, 'Feb', ... IF (month=11, 'Nov, 'Dec'') ) ) ) ) ) ) ) ) ) )

(Eleven closing parentheses are needed at the end of the expression--one for each IF test.)

Other Options

Here are some other ways to achieve the same goal:

1. 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.

2. 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.

3. 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.)

4. 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