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:

1. Go to Workspace > Reports > {report} or Workspace > {object}.
   A tab opens with the view or report you care about.
2. Click the link: New Computed Field
   A formula field opens:
3. Provide a label for the column heading. For example: Created When
4. Enter the type of value the formula will return. In this case: text.
5. Use the formula builder to create the formula want, as illustrated in the next section.

Learn more: Computed Fields

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:

<syntaxhighlight lang="java" enclose="div">

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

</syntaxhighlight> The formula uses the CONCAT

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