Manage Virtual Fields

In this document the methods are described for defining virtual fields in 'Virtual Fields. Connect' (CMS012) for fields that do not exist in any BE table. Use alternative 12='Virtual fields' (Ctrl+12) in these programs to launch (CMS012):

  • 'List and Printer Programs. Configure' (CMS005)
  • 'XML Structure Section. Open Element' (CMS009)
  • 'Information Browser Category. Open' (CMS010)

Follow these steps

The naming convention for a virtual field is &XXXX.

  1. On (CMS012/B), specify the virtual field ID and use alternative 1='Create'.

  2. Specify a name for a virtual field by entering either a description or a message ID. If you specify a description, it can also be defined per language by clicking the 'Language' button. If you specify a message ID, the column heading is automatically displayed in the end user's M3 language.

  3. Define a field type. Use these alternatives:

    1 = Value calculated by a formula

    2 = Value retrieved by a function program

    3 = Value from a description field in CSYTAB

    4 = Value from a name field in CSYTAB

    5 = Value from a currency conversion

    6 = Value from a logical formula

    7 = Date conversion

    8 = Merge data

  4. Specify a 'Calc sequence'.

    The calculation sequence is used if other virtual fields are used as objects in the calculation formula. The current virtual field must have a higher calculation sequence than the virtual fields used as objects in the calculation formula. The number of decimal places is controlled either by specifying the number of decimal places (0–4) or selecting a field information code.

  5. Click 'Next' to activate the virtual field.

Value calculated by a formula

The value is calculated based on fields from the master table, related tables, or other virtual fields. Specify the calculation formula by entering a value for 'Operator' (+, -, /, *), 'Object' (field from master table, related table, or other virtual field), and 'Factor'.

Value from description or name field in CSYTAB

The value is retrieved from the 'Description' or 'Name' field in the system table CSYTAB.

  • For the key 2 field, specify the 'Read' option for the key field CTDIVI (division). This field is usually set to 'Blank'.
  • For the key 3 field, specify the 'Read' option for the key field CTSTCO (constant value). This field is usually set to 'Equal to'. 'Value' normally corresponds to the final 4 positions of the field.
  • For the key 4 field, specify the 'Read' option for the key field CTSTKY (key value). This field is usually set to 'Equal to'. Specify the field name as the object.
  • For the key 5 field, specify the 'Read' option for the key field CTLNCD (language). Normally this field is set to 'Blank'.

You can also perform a quick entry to define a virtual field for the name from the related system table (CSYTAB):

  1. Click 'Related System Table' on (CMS012/E).

  2. Specify the key value (for example MMCUCD for the currency code name based on MITMAS).

  3. Click OK.

Value from a currency conversion

The value is retrieved from a currency conversion.

Specify the read option for these fields:

  • Base value: This alternative is the value to be converted.
  • Base division: The division can either be set 'Equal to' a division object, or found through a facility or warehouse field.
  • Rate type: If 'Blank value' is selected, rate type 01 is used.
  • From currency: This alternative is the currency of the base value before the conversion.
  • To currency: This alternative is the currency of the base value after the conversion.

Value from a logical formula

Values are displayed (or not) based on a logical formula.

Specify this information:

  • Logical type: Alphanumeric or numeric.
  • First operand: Always a field from the field group.
  • Condition.
  • Second operand: Field from the field group, specific value, or leave blank.
  • Combine: Specify how to combine two or three different conditions.
  • Value if condition is true: Field from the field group, specific value, or leave blank.
  • Value if condition is false: Field from the field group, specific value, or leave blank.

Date conversion

Convert a date to a unique serial number. The serial number starts by 1 on the first day in the system calendar. By using two virtual fields for a date in a virtual field for calculation, it is possible to calculate the difference between two dates. It is also possible to decide whether the unique sequence number should include all days, only working days, or only banking days.

Specify this information:

  • Base date: 1-'Current date', 2-'Object', 3-'Specific date'
  • Output date: 1-'Date YYYYMMDD', 2-'Day number', 3-'Working day number', 4-'Bank day number'

Merge data

The value is merged from different fields into one field, for example to display an order number and an order line number in the same field like this: 3953987-0020. Up to five different sections may be merged into the new virtual field. For every section, a number of parameters are specified.

Specify the following information:

  • Base date: If data for the section is retrieved from a field or if a manually entered value is used.
  • Length: Fixed length for the section. If no length is specified, this is set dynamically based on the value retrieved.
  • Field: Field ID if data should be retrieved from a field.
  • Value field: If data is entered manually.
  • Separator: Separator to be used last in a section.

    These characters are the valid alternatives:

    Semicolon (;)

    Colon (:)

    Forward slash (/)

    Comma (,)

    Period (.)

    Dash (-)

    Underscore (_)

  • Add blank: Select this to add one blank space after the separator.
  • Decimal format: Decimal format to use for numeric values retrieved from the database. If left blank, the user's decimal format is used.
  • Trim zeros: If leading zeros should be removed. This setting is only used if the field is numeric and a fixed length has been specified.
  • Date format: Format that must be used for date fields. These formats are the valid alternatives:

    These formats are the valid alternatives:

    Blank = Date format according to user setting in 'User. Open' (MNS150)

    1 = YYMMDD (year, month, day)

    2 = DDMMYY (day, month, year)

    3 = MMDDYY (month, day, year)

    4 = YYWWD (year, week, weekday)

    5 = YYYYMMDD (year, month, day)

    6 = YYYY-MM-DD (year, month, day)

    7 = YYWW (year, week)

    8 = DDMMYYYY (day, month, year)

  • Date editing: The character used to separate different parts of a date. These characters are the valid alternatives:

    Blank = no separator is used

    / = is used as a separator

    , = is used as a separator

    . = is used as a separator

  • Zero suppression: Select if values of zero should not be displayed (zero is then displayed as a blank value).

Outcome

You may define an unlimited number of virtual fields. When created, the field group is updated with this field. The virtual fields can be selected as fields (columns) in the list.

Related topics