4.4.4 Examples of Virtual Field Applications
The 4.4.2 Distinction between real and virtual fields section described a very simple example of three "virtual" fields that were directly derived from a "real" field by simple mapping.
These examples demonstrate some of the capabilities of the virtual field facility and common examples of its usage. Hopefully it can also be seen that the capabilities are only really limited by the imagination of the user:
Substring and concatenation
The example in the preceding section that involved breaking a field down into three virtual fields is an example of substring. Virtual fields can be used in all forms of substring. This includes substringing one real field into multiple virtual fields and substringing multiple real fields into one virtual field (also called concatenation).
Access to files with no external description
Many older S/38 applications, and S/36 migrated IBM i applications, use files that have no "external" field descriptions. These are also referred to as "internally described" files. These files appear as if they only contain one very large field which is in fact the entire file record, rather than a series of fields.
In such cases, all the fields in the file can be defined as virtual fields and then mapped from the record. This is in fact another example of substringing one real field into multiple virtual fields.
This case would also involve the mapping of virtual fields to the real field before output to the file. For more details, refer to examples 1 and 2 in Examples of Virtual Fields & Derivation Code in the .
Simplification and standardization of common data manipulations
In some applications the manipulation of data from a file in a particular way is very common and is repeated many times in differing applications.
For instance, consider an inventory file. The rule to determine whether a product requires re-ordering may be something like ("quantity on hand" + "quantity awaiting return" - "quantity on order") < "re-order level". This rule may be used in many different applications, particularly in the inquiry and reporting areas.
The logic in this rule can be simplified and standardized by using a virtual field. If a virtual field called REORDER was set up in the file definition, then derivation code could be written to evaluate the rule and set REORDER to "YES" or "NO".
This approach has several advantages:
- Simplification. To users of the file there appears to be a field called REORDER which can be very simply tested as "YES" or "NO".
- Standardization. The rule to determine the re-order flag is standardized. There is no chance of slightly different rules being used in different applications.
- Centralization. The rule is held in one place only, not repeated in many different applications. If the rule changes it need only be changed in one place.
Type and length conversions
When the type (i.e. alpha, packed or signed) or length of a field is inappropriate or inconvenient for common usage requirements a virtual field can be established.
For instance a 15 digit numeric field that never contains more than 999 can annoy users in reporting environments because it wastes 12 spaces on the report. A virtual field containing only 3 digits can be set up for use on reports.
Likewise an alphanumeric date may annoy users because it cannot be printed with an edit code/word. A numeric virtual field could be set up to solve this problem.
Aggregation and accumulation
Many database files contain "arrays". This most commonly takes the form of a series of fields like SAL01, SAL02, SAL03 ...... SAL12 representing company sales for each of the months of the year.
Virtual fields can be used to aid users when working with these type of structures, particularly in the reporting and inquiry environments.
For instance virtual field SALYR could be defined to contain the total of fields SAL01 -> SAL12.
Similarly virtual fields SALQ1 (containing total of SAL01 -> SAL03), SALQ2 (containing total of SAL04 -> SAL06), etc, could be defined to contain quarterly sales totals.
Date conversion
When a date is held in a real field in format YYMMDD it can be easily mapped into a virtual field in format DDMMYY. The real field is the most commonly used format for ordering the file, but the virtual field format is the most commonly used for printing.
Date to age conversion
A date (possibly of date of birth) field in a file can be converted into two virtual fields containing "age in years" and "age in months". Note that derivation logic here would actually involve the date field in the file and the current date (which is why the "age" cannot be stored in the database file - it will be wrong tomorrow).
Note that the logic involved here is more complex. In fact many sites would have a subroutine to do the job. Since derivation logic is specified to LANSA as RPG code it is easy to call existing subroutines.
Dynamic data conversion
Just about any form of dynamic data conversion can be achieved by using virtual fields.
To take an example to the extreme, imagine that a new company requirement for printing customer names is that:
- They are always in uppercase
- All quotes, commas, full stops and hyphens are removed
- The name must be reversed (i.e. printed backwards)
If the customer name field was called CUSNAM then it would be a simple task to define a virtual field in the file called PRTNAM that matched these requirements.
Also See