User Interface

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.

Use Case:

Scenario:

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.

Definition:

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.

tableDef1

tableDef2

Next we have to add this column to the ‘Windows and Tabs’ window to make it visible to the user.

Window Definition

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 the 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.

Output:

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.

Previous post

Creating Virtual Fields Using Property Fields in the Openbravo ERP Platform

Next post

Creating a Window using HQL Query in Openbravo

No Comment

Leave a reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>