Foundation: Transformations and Conditions

Transformations

TimeXtender allows you to transform the values within a specific field using Transformations. This can be anything from simply changing a number from positive to negative, to complex calculations using other fields in the table.

Here is the list of Transformation types available with their function:

  • Upper Converts all text values to upper-case
  • Lower Converts all text values to lower-case
  • First Returns the number of beginning characters specified by the user
  • Last Returns the number of ending characters specified by the user
  • TrimLeft Trims padded spaces from the left of the data
  • TrimRight Trims padded spaces from the right of the data
  • Trim Trims padded spaces from the left and right of the data
  • Fixed Inserts a fixed value that is specified by the user (Does not require string quotation)
  • Custom Allows for custom SQL code to be executed
  • ReverseSign Reverses the sign for numeric values
  • TimeOnly Returns only the time portion of a datetime field
  • DateOnly Returns only the date portion of a datetime field
  • Replace Replaces one set of characters with another

Conditions

Transformations can be made significantly more useful when combined with Conditions. Conditions are how you assign an "if" or "case" statement within TimeXtender. (e.g. SET Name = 'John Doe' IF Name IS NULL). Conditions can be applied to other functions in TimeXtender in addition to Transformations.

Here is the list of Condition types available:

  • No Field Selected
    • Custom Allows for Custom SQL code to set Condition
  • Field Selected
    • Non-Comparison Conditions - Do no reference or compare the field value to another value.
      • Empty Null or Blank String
      • Not Empty Not Null or Blank String
      • Is Numeric
      • Is Not Numeric
      • Is Date
      • Is Not Date
    • Comparison Conditions - Require user to input a fixed value OR reference another field in the table.
      • Equal (Does not require string quotation)
      • Not Equal
      • Greater Than
      • Greater or Equal
      • Less Than
      • Less or Equal
      • Max. Length
      • Min. Length
      • In List (Comma separated list, No Spaces)
      • Not in List
      • Like
      • Not Like

Next