User Interface

Creating a Window using HQL Query in Openbravo

Flexibility and extensibility are two of the key factors which companies consider while deciding on a ERP platform. Openbravo as an ERP and as an application platform makes it a lot easier to create user interface components like Window, fields, buttons without any coding involved. Let’s look at one more way of using this flexibility. In Openbravo we can create a window even without any database table just by using an HQL query. Seems cool right. Let’s see with a small example how this can be achieved.

Let’s assume a client wants to know a rough list of the Sales Orders done by their clients and the number of items ordered in each order. If they want extensive analytics of their sales process, they can use the Openbravo Analytics module, but for the sake of simplicity let’s say the consultant writes a simple HQL query and provides the result. Now the client wants to access this information frequently so we decide to create a read-only window for the same.

Table Definition:
In Tables and Columns, there is a field called Data Origin. It takes three possible values: Table, HQL Query or Datasource. Table is the normal database table, HQL Query is the table definition created from HQL Query and Datasource is the option where a table can be created from non-database resources like spreadsheets etc which we will review in another blog. The Table can be defined as per the screenshot provided below

Table-Def
The HQL query that we have used is,
select e.businessPartner.name, e.orderDate, e.documentNo, e.orderLineList.size from Order e WHERE @additional_filters@ AND @insertion_point_0@

The @additional_filters@ is used to set runtime filtering from the tab definition. That string will be replaced with the mandatory client and organization filters, and with the filters generated from the criteria included in the datasource request.

The @insertion_point_0@ allows to extend the query or modify the query at runtime by writing a new java class extending the base class HQLInserter.

Column Definition:
The columns for the HQL Tables have to be defined manually. The screenshot provided below shows the columns that have been added from the base HQL query. In the DB column name, the query column has to be provided and in the Name column any alias name used in the query can be used.

Col-Def
Window Definition
Create a window and tab similar to normal table related tab definition and we added the columns manually to avoid any confusion. This can also be done using the Create Fields button.
Field-Def

Define a menu for the window and run ant smartbuild and restart tomcat and then you should have an output similar to the below screenshot.

Output

Output
Voila! A new window has been created using an HQL query without any coding effort and probably in 5 minutes. Please follow the Openbravo wiki link  for more in-depth information on this topic.

 

Also please tell us any doubts or issues that you are facing when trying to create the same.

Note: The examples provided in our blogs are provided in this example module.

Previous post

Creating virtual fields using computed columns in the Openbravo ERP Platform

Next post

Customizing the User Experience Using Preferences

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>