From AdempiereWiki

Contents 1 What and why? 2 How? 2.1 Create the virtual Column 2.2 Add the new virtual column to the window/tab

What and why?

Virtual columns are Adempiere table columns that are not part of the corresponding database table. Their values are not stored but calculated from an SQL expression.

They can be used to show additional fields from a referenced table (e.g to show name and the search key of a product in the shipment line) or to calculate a value from other table columns (eg. Age of the datarow as Updated-Created).

How?

Create the virtual Column

1. Login as System Administrator
2. Go to Application Dictionary -> Table and Column
3. Select a table (e.g. M_InOutLine) and switch to the tab 'Column'
4. Create a new column:

• Name: Value
• DB Column Name: Value
• System Element: Value
• Length: 40
• Reference: String
• Column SQL:- (SELECT Value FROM M_Product p WHERE p.M_Product_ID=M_InOutLine.M_Product_ID). Don't forget to put the bracket sign. The select statement will be translated as

    SELECT field1, field2, (SELECT Value FROM M_Product p WHERE p.M_Product_ID=M_InOutLine.M_Product_ID) as <virtual column name>
           ,field4 ...

• Updateble: false

Note that it is important to use UPPERCASE for the SQL reserved words like SELECT, FROM, WHERE etc...

Avoid the use of aliases in the SQL statement as the statement will be imbedded in a larger SQL statement for the window tab and the aliases can get confused if they are not unique within the larger statement. If you do use aliases, ensure they are unigue from all other table and column names used in the window tab.

Add the new virtual column to the window/tab

1. Go to Application Dictionary -> Window, Tab & Field
2. Select the Shipment (Customer) window and switch to tab 'Tab'
3. Select 'Shipment Line' and switch to tab 'Field'
4. Create a new Field:

• Name: Value
• Column: Value_Search Key
• Display Length: 14
• Read Only: true