Virtual Columns
⇐ Table of Contents{{#if: Application Dictionary| | Application Dictionary }}{{#if: Table and Column| | Table and Column }}{{#if: | | [[{{{4}}}]] }}{{#if: | | [[{{{5}}}]] }} | Virtual Columns{{#if: ??| | ?? }} ⇒
Contents
Introduction
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. Show the age of the data in the row as Updated-Created).
How to Create a Virtual Column
To follow these steps, you should understand the SQL query language and be familiar with the table structure within ADempiere. Before you begin, you will need to have an SQL query required to generate the data in the field you want to display. This query should return a single value or perform a simple calculation.
In the example below, we will add the M_Product."value" (or product search key) to the M_InOutLine table and display it in the {{#if: Shipment (Customer)|Shipment (Customer)|Shipment(Customer) }} Window.
Add a Virtual Column to the Source Table
- Login as System Administrator
- In the Menu Tree navigate to Application Dictionary{{#if: | » }}{{#if: | » }}{{#if: | » }}{{#if: | » }} and open the {{#if: Table and Column|Table and Column|TableandColumn }} Window
- Search for and select the table M_InOutLine and then switch to the {{#if: |{{{3}}}|Column }} Tab
- Create a new column with the following values:
- Name: Value
- DB Column Name: Value
- System Element: Value
- Length: 40
- Reference: String
- Updateble: false
- Column SQL: (SELECT Value FROM M_Product p WHERE p.M_Product_ID=M_InOutLine.M_Product_ID)
- Save your work.
The Column SQL text will be included in the table SQL query as
- SELECT field1, field2, <Column SQL> as <virtual column name>, field4... WHERE ...[Client/Org tests] ... [Private Access tests] ...
The Column SQL statement does not have to be a full SQL Select statement with a WHERE clause. If you are simply performing a calculation, only the variables and operation need be included. For example, suppose we add the Column T_Integer to the XX_Material table where the field is simply the sum of two other fields, ColorNr and MATNR. The Column SQL is simply (COLORNR + MATNR). In the final SQL statement used to select records from the table, it will look like this:
SELECT IsActive, XX_Material_ID, MatNr, ColorNr, Name , (COLORNR + MATNR) AS T_Integer , AD_Client_ID, AD_Org_ID, Created, CreatedBy, Updated, UpdatedBy FROM XX_Material WHERE XX_Material.AD_Client_ID IN (0,1000000) AND XX_Material.AD_Org_ID IN(0,1000000) AND XX_Material.XX_Material_ID NOT IN ( SELECT Record_ID FROM AD_Private_Access WHERE AD_Table_ID = 1000007 AND AD_User_ID <> 100 AND IsActive = 'Y' )
In this case, the virtual column (COLORNR + MATNR) is just included in the comma separated field list.
Note: Don't forget to put the bracket signs () around your SQL statement if you do anything complex. The software does not add them.
Add the New Virtual Column to the Target Window/Tab
- Go to the {{#if: Window, Tab & Field|Window, Tab & Field|WindowTabField }} Window
- Select the Shipment (Customer) record and switch to the {{#if: |{{{3}}}|Tab }} Tab
- Select Shipment Line record and switch to {{#if: |{{{3}}}|Field }} Tab
- Create a new Field with the following values.
- Name: Value
- Column: Value_Search Key
- Display Length: 14
- Read Only: true
- Save your work.
Test the Column
- Login to the Client as the appropriate user.
- Select the {{#if: Shipment (Customer)|Shipment (Customer)|Shipment(Customer) }} Window.
- Verify that the virtual column is working properly.
Troubleshooting
If you have problems with the column SQL, check the error log for issues and look at the log output in the console.