Creating virtual fields using computed columns in the Openbravo ERP Platform
Virtual Columns are those columns that do not have a column defined in the database, but can be shown in the Application as an additional field. In our earlier blogs, we saw how to create virtual fields using Client Class (here) and using Property Fields (here). In this blog, we will see one more variety that can be used to create Virtual Fields: computed columns.
Note: The examples provided in our blogs are provided in this example module.
As the name suggests, Computed Columns are basically SQL queries that can be added to a particular field which will be evaluated based on the existing columns in the field. This will be more useful in cases where you want to bring in some logic that has to be calculated from the existing fields. Let us try to understand this by using a simple use case.
When you are receiving a Goods Receipt more often than not you would want to tally the total number of items that has been delivered. This is how the current Goods Receipt window looks.
Now we can see the lines that have been ordered and sum up the quantity that has been delivered. But it is a bit of a tedious task when there are more than a hundred products that have been delivered. So now let’s add a computed column to provide the total number of products in a particular Goods Receipt.
We have to first define the column and the computational logic behind it in the ‘Tables and Columns’ window. The SQL Logic is the place where we have to write the code for the Computed Column.
Next we have to add this column to the ‘Windows and Tabs’ window to make it visible to the user.
The query that is provided is as follows,
Select sum(kui.movementqty) from m_inoutline kui where kui.m_inout_id=m_inout_id
Notice that here the final comparison is done with m_inout_id. This is the field that is present in Goods Receipt Header. When using fields that are present in the record, there is no need to provide aliases.
Now compile the application and restart tomcat. Here’s the output.
Notice that there is a new field called Total Items Present which provides the total number of items delivered in the Goods Receipt
Key points using Computed Columns:
- Computed Columns are also filterable and sortable.
- Computed Columns can also be used in DAL queries.
- Before the release 3.0 MP27, it can be used as follows, from m_inout m where m. kUITotalItems > 100
- After the 3.0 MP 27 release it can be used as follows, from m_inout m where m.computedColumns. kUITotalItems > 100
- Computed Columns cannot be used in OBCriteria and can only be used with OBQuery
Let us know through comments any points that we may have missed or any issues that you are facing when creating computing columns. You can also let us know what technical topic you would like to cover in the blogs so that we can try to provide more information on that.