|
A virtual column or joined column is a column that does not actually exist in the database, but takes its value at the USoft level by copying a stored column value from a corresponding record in a parent table. The virtual column itself is defined in the child table.
Example
A TRAVELLER table stores information about travellers, identifying each traveller by a PERSON_ID number. TRAVELLER is the child table of a relationship with parent table PERSON.
On the basis of the PERSON_ID number, you can find the family name of each traveller in the PERSON parent table. You can create a FAMILY_NAME virtual column in TRAVELLER. As a result, when processing TRAVELLER records, USoft will automatically "borrow" the corresponding family name from the PERSON table. In the database, family names are not stored in the TRAVELLER table but only in the PERSON table.
Context
|
Solution
|
Batch reporting
|
For reporting and like purposes, add virtual columns by defining a logical view. In the FROM clause of the logical view, define a join with the parent table you want to "borrow" the values from. Then add the parent column to the output list, ie., the list of columns in the SELECT clause.
|
Windows Designer
|
Windows Designer has a powerful feature called Insert Joined Columns. See the Windows Designer Guide.
|
Web Designer
|
Web Designer has more flexible ways of linking up related data sources. See "Lookup objects" and "Relate objects" in the Web Designer Guide.
|
Services Framework
|
For getting virtual columns into a REST API definition, define logical views. Look at rules-based logical views if you want API users not just to view or query a virtual column, but also supply or change values for it as if manipulating the parent table where the values are ultimately stored.
|
|
The functionality of a virtual column is a based on a parent-child relationship. The virtual column itself is in the child table. The database column that it takes values from, is in the parent table. The features mentioned in "Alternative solutions" above are more powerful because they allow you to take values not just from a direct parent table, but also from a grandparent or higher-level ancestor table, or from a table that is otherwise related to the record your virtual column is in.
The relationship itself uses key columns: one or more primary key columns in the parent table and one or more foreign key columns in the child. By contrast, the virtual column, and the parent column that it takes values from, are not themselves key columns.
A virtual column must be based on the same domain as the parent column that it takes its values from.
Virtual columns are especially useful when foreign keys are not meaningful to the end user. This can happen when the relationship uses some numbering system but the virtual column supplies more meaningful parent information, such as names of persons, or names of geographic destinations of tour products in a Travel Agency.
Virtual columns cannot be accessed by means of ODBC.
|
To define a virtual column:
| 1. | In the child table, define a new column, specifying Virtual (not Database) in the Type field. |
| 2. | With this new virtual column as the current record, click the Related List button on the toolbar (or type F9), and choose Column that Gives a Value to This Joined Column. (You can also access the related window by means of the Related option on the Box menu, using either the Column box of the Tables window or the Relationships window as your starting point.) |
| The Column that Gives a Value to This Joined Column related window appears. |
| 3. | Complete the window as follows: |
•Click the Child Table lookup button to select the parent object, role and child object that is needed to 'join' the joined column with the parent column. •Click the Parent Column lookup button to select the column that will give its value to the joined column. |
See also
Tables
Columns
Relationships
|
