VSys One: Volunteer Management Software

Previous Topic

Next Topic

Book Contents

Book Index

Calculated Field Operations

In the following example, a date field has two operations performed on it:

  1. The date is replaced by the quarter of that date, e.g. "January 14, 2008" becomes "Q1".
  2. Text is added to the beginning of the field, e.g. "Q1" becoming "Quarter: Q1".
    Calculated report field window

Any number of operations can be added here, and they're performed sequentially - one after the other - so the second operation gets the result of the first to work on.
Calculated fields operation window showing adding the letter Q to the beginiing on the field

Operation

Action/Example

Add text to the end, Add text to the beginning

Puts your Text to add value at the beginning or end of the current value.

Convert to upper-case

"Smith, Dale" becomes "SMITH, DALE"

Convert to lower-case

"Smith, Dale" becomes "smith, dale"

Convert to mixed-case

"SMITH" becomes "Smith"

Convert HTML to RTF

Converts HTML-formatted code to RTF (RTF text can be used with special fields in Report Builder).

Convert HTML to text

Strips all HTML formatting and code from a text or memo field.

Add years to a date

For all "Add XXX to a date", if the date is blank, no action is taken.

Add months to a date, Add weeks to a date, Add days to a date

Adds the value you select to the existing date field. Negative values are valid here. A date field which is blank will not be changed.

Day of week

e.g. "1"

Day of week name

e.g. "Monday"

Day of week name (short)

e.g. "Mon"

Week of year, Month of year, Year portion of date, Fiscal month of year, Fiscal year of date

 

Quarter of year

"January 14, 2008" becomes "Q1"

Fiscal quarter of year

For a fiscal year starting in April, "January 14, 2008" becomes "Q4"

Add/subtract number, Multiply, Divide

 

Round a number

14.5678, 2 becomes 14.57
143.56, -1 becomes 140.

Truncate a number

14.5678, 2 becomes 14.56
143.56, -1 becomes 140.

Choose text based on the field being TRUE or FALSE

All three of these take a wide view of "True" and "False": "1", "T", and "Y" are TRUE, "0", "N" and "F" are FALSE.

Choose a number based on the field being TRUE or FALSE, Choose a date based on the field being TRUE or FALSE

 

Date portion of a date/time value

"12/17/2011 11:47 AM" becomes "12/17/2011"

Time portion of a date/time value

"12/17/2011 11:47 AM" becomes "11:47 AM"

Convert to mixed case

"JOHN DOE" becomes "John Doe"

First day of month

"03/07/2011" becomes "03/01/2011"

First day of year

"03/07/2011" becomes "01/01/2011"

Month of year with name

"03/07/2011" becomes "03 - March"

Fiscal month of year with name

"03/07/2011" becomes "06 - March" when the fiscal year starts on October 1.

Year portion of date

"03/07/2011" becomes "2011"

See Also

Calculated Fields