View source | Discuss this page | Page history | Printable version   

ERP 2.50:Developers Guide/Examples/Search

ERP 2.50:Developers Guide




This example explains the most important elements to take into account when developing a new Search reference.

It is possible to install from Central Repository the code of this example as a module and a template (Bank Account Search and Bank Account Search Template).

The code for both is also available in the forge: here the module and here the template.

Bulbgraph.png   This document explains a manual coded selector. There is a new way to define selectors. You can check this webinar: New Selectors


Our company works with a big amount of bank accounts. As Openbravo ERP core does not have a Bank Account Search, in the Financial Management || Receivables & Payables || Transactions || Bank Statement || Header tab the Bank Account field is displayed as a combo where it is difficult to select the desired value.

To solve this problem we are going to create a new Bank Account Search that will be called from that field and will allow us to select the bank account with some filtering criteria. Note the aim of this search is to be educational more than functional, so the filtering capabilities are maintained as simple as possible.

Module definition

New components should always created within modules. In this case we will create a module for the Search, and as we want to modify core to use this new search in Financial Management || Receivables & Payables || Transactions || Bank Statement || Header we will also create a template with this core modification.


Search references are defined in Application Dictionary but their user interface is manually implemented. This means that the code that shows the UI is not automatically generated but manually.

Application Dictionary

Search definition

Using the Search

Once the search is defined in Application Dicitionary, it can be associated to the columns where it is going to be used from. If they are new columns in a module nothing special must be taken into account, but for this example we are going to change the standard behavior of a core column, in this case it is necessary to create a template module that will contain this core modification.

Change the reference in C_BankStatement.C_Bank_ID column from TableDir to Search and select the new Search in the Reference Key Search field.

Manual Implementation

As Searches are implemented manually they can virtually be done in any way. For example it would be possible to create a graphical Search for Products displaying a tree for product categories and products. Anyway the standard Searches consist in a filter section where it is defined the criteria to find data and a grid where data is displayed and can be selected from. This example implements a standard one.

Searches are designed to be invoked from a field. There are two ways of invoking a Search:


doPost method
KEY command
  if (vars.commandIn("KEY")) {
      String strKeyValue = vars.getRequestGlobalVariable("inpNameValue", "BankAccount.key");
      final String strIDValue = vars.getStringParameter("inpIDValue");
      final String strOrg = vars.getStringParameter("inpAD_Org_ID");
      if (!strIDValue.equals("")) {
        final String strNameAux = BankAccountData.existsActualValue(this, strKeyValue, strIDValue);
        if (!strNameAux.equals(""))
          strKeyValue = strNameAux;
      if (!strKeyValue.equals(""))
        vars.setSessionValue("BankAccount.key", strKeyValue + "%");
      final BankAccountData[] data = BankAccountData.selectKey(this, vars.getLanguage(), Utility
          .getContext(this, vars, "#User_Client", "BankAccount"), Utility.getSelectorOrgs(this,
          vars, strOrg), strKeyValue + "%");
      if (data != null && data.length == 1) {
        printPageKey(response, vars, data);
      } else
        printPage(response, vars, strKeyValue + "%", "", "paramKey");

This piece of code is executed when enter key is pressed in the associated field, it receives in strKeyValue the value in that field and checks whether there are records matching this value (data = BankAccountData.selectKey), in case there is only one it calls printPageKey which just selects that value in the field and closes the Search if there are more it calls printPage method which will display all these records.

Notice also strOrg variable, it receives the Organization in the current record and it is used to obtain the list of organizations that can be referred from that one by Utility.getSelectorOrgs(this, vars, strOrg) that will be used in the SQL query.

DEFAULT command
  if (vars.commandIn("DEFAULT")) {
      String strNameValue = vars.getRequestGlobalVariable("inpNameValue", "");
      final String strIDValue = vars.getStringParameter("inpIDValue");
      final String strKeyValue = vars.getGlobalVariable("inpKey", "BankAccount.key", "");
      if (!strIDValue.equals("")) {
        final String strNameAux = BankAccountData.existsActual(this, vars.getLanguage(),
            strNameValue, strIDValue);
        if (!strNameAux.equals(""))
          strNameValue = strNameAux;
      if (!strNameValue.equals(""))
        vars.setSessionValue("", strNameValue + "%");
      printPage(response, vars, strKeyValue, strNameValue.concat("%"), "paramName");

This code is executed when the button next to the field is clicked. It just checks if there are records matching the criteria (now it is checked by the column identifier) and calls in any case the printPage method which will display all the matching elements and will leave opened the Search.

Grid Ajax commands: STRUCTURE and DATA

These two commands are called using Ajax to display the grid with data. STRUCTURE command just calls printGridStructure method which prints the XML structure for the grid, whereas DATA receives all filters and calls printGridData method that queries for that info and prints all the record matching the criteria.

printPage method
 void printPage(HttpServletResponse response, VariablesSecureApp vars, String strKeyValue,
      String strNameValue, String focusedId) throws IOException, ServletException {
    final XmlDocument xmlDocument = xmlEngine.readXmlTemplate(
//... some more code...
    try {
      ComboTableData comboTableData = new ComboTableData(vars, this, "TABLEDIR", "C_Bank_ID", "",
          "C_Bank", Utility.getContext(this, vars, "#AccessibleOrgTree", "BankAccount"), Utility
              .getContext(this, vars, "#User_Client", "BankAccount"), 0);
      Utility.fillSQLParameters(this, vars, null, comboTableData, "C_Bank", "");
      xmlDocument.setData("reportBank_ID", "liststructure",;
      comboTableData = null;
    } catch (Exception ex) {
      throw new ServletException(ex);
    xmlDocument.setParameter("directory", "var baseDirectory = \"" + strReplaceWith + "/\";\n");
    xmlDocument.setParameter("language", "defaultLang=\"" + vars.getLanguage() + "\";");
//...some more code: set all parameters for filter...
    response.setContentType("text/html; charset=UTF-8");
    final PrintWriter out = response.getWriter();

This method is used to display the filter section, it just receives the selected values for the filter fields and displays them. Note that when the HTML page is printed it will call with ajax the methods to print the grid filtering with the values it has received in these fields.

In this case there is a combo for the banks to select as a filter, it is populated using a ComboTableData object, the most important think to keep in mind is the filtering for organizations. Here it is used the #AccessibleOrgTree session variable which holds the complete list for all the accessible organizations by the current role.

printPageKey method

It is called when there is a single record matching the select criteria in the KEY command. It just displays a javascript function that contains the for the selected row and closes the pop up. It uses a core common template instead of one defined within the current module: org/openbravo/erpCommon/info/SearchUniqueKeyResponse which just receives an script parameter. This parameter is generated by the generateResult method:

  String generateResult(BankAccountData[] data) throws IOException, ServletException {
    final StringBuffer html = new StringBuffer();
    html.append("\nfunction validateSelector() {\n");
    html.append("var key = \"" + data[0].cBankaccountId + "\";\n");
    html.append("var text = \"" + Replace.replace(data[0].name, "\"", "\\\"") + "\";\n");
    html.append("var parameter = new Array();\n");
    html.append("parent.opener.closeSearch(\"SAVE\", key, text, parameter);\n");
    return html.toString();

In the key variable it is stored the primary key for the selected row, whereas in the text one it is the text to be displayed in the field.

printGridStructure method

It displays the structure for the grid (which columns has, which is the key...), it also uses a common template: org/openbravo/erpCommon/utility/DataGridStructure, where the most important parameter is structure1 which receives a SQLReturnObject[] object.And the second parameter backendPageSize is to display number of records to display in a single row.Define the number of rows in TableSQLData.maxRowsPerGridPage

  final XmlDocument xmlDocument = xmlEngine.readXmlTemplate(
  final SQLReturnObject[] data = getHeaders(vars);
  xmlDocument.setData("structure1", data);  xmlDocument.setParameter("backendPageSize",String.valueOf(TableSQLData.maxRowsPerGridPage));

this object is obtained by getHeaders method which is listed bellow.

private static final String[] colNames = { "name", "value", "bank", "rowkey" };
private SQLReturnObject[] getHeaders(VariablesSecureApp vars) {
    SQLReturnObject[] data = null;
    final Vector<SQLReturnObject> vAux = new Vector<SQLReturnObject>();
    final String[] colWidths = { "400", "200", "200", "0" };
    for (int i = 0; i < colNames.length; i++) {
      final SQLReturnObject dataAux = new SQLReturnObject();
      // set all paramters for the SQLReturnObject ...
      // ...
      final String name = Utility.messageBD(this, "BNKACTS_" + colNames[i].toUpperCase(), vars
      dataAux.setData("name", (name.startsWith("BNKACTS_") ? colNames[i] : name));
      dataAux.setData("type", "string");
      dataAux.setData("width", colWidths[i]);
    data = new SQLReturnObject[vAux.size()];
    return data;

This method creates a String[] in colNames with all the columns the grid will have. Note that these names must match with the names the FieldProvider when retrieving the data. colWidths is another array with the same length that indicates the size for each of the columns defined in colNames.

Then a SQLReturnObject is created for each column, here the most interesting part is how these columns are titled, it is done with the "name" property, note that it is calculated using a message defined in database, these messages must be defined within the current module.

rowkey column will contain the ID and the text to be displayed in the field in case the row is selected.

printGridData method

This is the method that retrieves and displays all data in the grid.

  void printGridData(HttpServletResponse response, VariablesSecureApp vars, String strKey,
      String strName, String strOrg, String strBankId, String strOrderCols, String strOrderDirs, String strOffset,
      String strPageSize, String strNewFilter) throws IOException, ServletException {
    int page = 0;
    final SQLReturnObject[] headers = getHeaders(vars);
    if (headers != null) {
      try {
        // build sql orderBy clause
        String strOrderBy = SelectorUtility.buildOrderByClause(strOrderCols, strOrderDirs);
      page = TableSQLData.calcAndGetBackendPage(vars, "BankAccount.currentPage");
        if (vars.getStringParameter("movePage", "").length() > 0) {
        // on movePage action force executing countRows again
        	strNewFilter = "";
        int oldOffset = offset;
        offset = (page * TableSQLData.maxRowsPerGridPage) + offset;
        log4j.debug("relativeOffset: " + oldOffset + " absoluteOffset: " + offset);
        // New filter or first load
        if (strNewFilter.equals("1") || strNewFilter.equals("")) {
        String rownum = "0", oraLimit1 = null, oraLimit2 = null, pgLimit = null;
        if (this.myPool.getRDBMS().equalsIgnoreCase("ORACLE")) {
	        oraLimit1 = String.valueOf(offset + TableSQLData.maxRowsPerGridPage);
	        oraLimit2 = (offset + 1) + " AND " + oraLimit1;
	        rownum = "ROWNUM";
        } else {
        	pgLimit = TableSQLData.maxRowsPerGridPage + " OFFSET " + offset;
          strNumRows = BankAccountData.countRows(this,rownum, Utility.getContext(this, vars,
              "#User_Client", "BankAccount"), Utility.getSelectorOrgs(this, vars, strOrg), strKey,
              strName, strBankId,pgLimit, oraLimit1, oraLimit2);
          vars.setSessionValue("BankAccount.numrows", strNumRows);
        } else {
          strNumRows = vars.getSessionValue("BankAccount.numrows");
        // Filtering result
        if (this.myPool.getRDBMS().equalsIgnoreCase("ORACLE")) {
          final String oraLimit = (Integer.parseInt(strOffset) + 1)
              + " AND "
              + (Integer.parseInt(strOffset) + Integer.parseInt(strPageSize));
          data =, "ROWNUM", vars.getLanguage(), Utility.getContext(
              this, vars, "#User_Client", "BankAccount"), Utility.getSelectorOrgs(this, vars,
              strOrg), strKey, strName, strBankId, strOrderBy, oraLimit, "");
        } else {
          final String pgLimit = strPageSize + " OFFSET " + strOffset;
          data =, "1", vars.getLanguage(), Utility.getContext(this,
              vars, "#User_Client", "BankAccount"), Utility.getSelectorOrgs(this, vars, strOrg),
              strKey, strName, strBankId, strOrderBy, "", pgLimit);
      } catch (Exception e) {
        // ... some code here
    StringBuffer strRowsData = new StringBuffer();
    // ... populate rowsData with the xml structure
    response.setContentType("text/xml; charset=UTF-8");
    response.setHeader("Cache-Control", "no-cache");
    final PrintWriter out = response.getWriter();
    if (log4j.isDebugEnabled())

First it is calculated the total number of rows that matches the criteria and it is stored in strNumRows variable, note that it must be calculated apart from the actual data since it has an offset and a limit that can reduce the amount of records.

After it is calculated the limitation of records that will be applied, this is done throw database specific code, so it is necessary to know if current database is Oracle or PostgreSQL.

And finally data is populated with the result of the query: data =

After that a XML structure for that data is generated in rowsData and it is directly printed. The XML structure must look like:

  <rows numRows="1" backendPage="5">
      <td><![CDATA[Uncle Scroogre Bank - Uncle Scroogre Bank. 0000-0000-00-0000000000]]></td>
      <td><![CDATA[Uncle Scroogre Bank]]></td>
      <td><![CDATA[1000000#Uncle Scroogre Bank - Uncle Scroogre Bank. 0000-0000-00-0000000000]]></td>

If a selector which can be used to select multiple records at once is to be converted one additional change is needed to its java code. The multiple selectors do have one additional methods in its servlet which is called to get the list of id's inside a specific record offset-range. This record offset-range needs to be converted from client-side relative offsets to server-side absolute offsets (by simply adding backendPageSize+backendPage).

private void printGridDataSelectedRows(HttpServletResponse response, VariablesSecureApp vars,
      String strKey, String strName, String strProductCategory, String strOrg, String strOrderCols,
      String strOrderDirs) throws IOException, ServletException {
    int minOffset = Integer.parseInt(vars.getStringParameter("minOffset"));
    int maxOffset = Integer.parseInt(vars.getStringParameter("maxOffset"));
    log4j.debug("Output: print page ids, minOffset: " + minOffset + ", maxOffset: " + maxOffset);
    String type = "Hidden";
    String title = "";
    String description = "";
    FieldProvider[] data = null;
    FieldProvider[] res = null;
    try {
      // build sql orderBy clause
      String strOrderBy = SelectorUtility.buildOrderByClause(strOrderCols, strOrderDirs);
      String strPage = vars.getSessionValue("BankAccount|currentPage", "0");
      int page = Integer.parseInt(strPage);
      int oldMinOffset = minOffset;
      int oldMaxOffset = maxOffset;
      minOffset = (page * TableSQLData.maxRowsPerGridPage) + minOffset;
      maxOffset = (page * TableSQLData.maxRowsPerGridPage) + maxOffset;
      log4j.debug("relativeMinOffset: " + oldMinOffset + " absoluteMinOffset: " + minOffset);
      log4j.debug("relativeMaxOffset: " + oldMaxOffset + " absoluteMaxOffset: " + maxOffset); 
      // Filtering result
      if (this.myPool.getRDBMS().equalsIgnoreCase("ORACLE")) {
        String oraLimit1 = String.valueOf(maxOffset);
        String oraLimit2 = (minOffset + 1) + " AND " + oraLimit1;
        data =, "ROWNUM", vars.getLanguage(), Utility.getContext(
              this, vars, "#User_Client", "BankAccount"), Utility.getSelectorOrgs(this, vars,
              strOrg), strKey, strName, strBankId, strOrderBy, oraLimit, "");
      } else {
        // minOffset and maxOffset are zero based so pageSize is difference +1
        int pageSize = maxOffset - minOffset + 1;
        String pgLimit = pageSize + " OFFSET " + minOffset;
        data =, "ROWNUM", vars.getLanguage(), Utility.getContext(
              this, vars, "#User_Client", "BankAccount"), Utility.getSelectorOrgs(this, vars,
              strOrg), strKey, strName, strBankId, strOrderBy, oraLimit, "");
      // result field has to be named id -> rename by copy the list
      res = new FieldProvider[data.length];
      for (int i = 0; i < data.length; i++) {
        SQLReturnObject sqlReturnObject = new SQLReturnObject();
        sqlReturnObject.setData("id", data[i].getField("rowkey"));
        res[i] = sqlReturnObject;
    } catch (Exception e) {
      log4j.error("Error obtaining id-list for getIdsInRange", e);
      type = "Error";
      title = "Error";
      if (!e.getMessage().startsWith("<![CDATA["))
        description = "<![CDATA[" + e.getMessage() + "]]>";
    XmlDocument xmlDocument = xmlEngine.readXmlTemplate(
    xmlDocument.setParameter("type", type);
    xmlDocument.setParameter("title", title);
    xmlDocument.setParameter("description", description);
    xmlDocument.setData("structure1", res);
    response.setContentType("text/xml; charset=UTF-8");
    response.setHeader("Cache-Control", "no-cache");
    PrintWriter out = response.getWriter();


XSQL file does not deserve any special consideration, it is just needed to implement all the queries used in the previous sections taking always into account to filter properly by client and organization. Sample xsql for countRows method is given below.

<SqlMethod name="countRows" type="preparedStatement" return="String">
      <Sql> <![CDATA[
        SELECT 1
        FROM C_BankAccount B
        WHERE B.AD_CLIENT_ID IN ('1') 
        AND B.AD_ORG_ID IN ('1') 
        AND B.ISACTIVE = 'Y' 
        AND 1=1
		) B
		) A 
        <Field name="position" value="count"/>
	 <Parameter name="rownum" type="replace" optional="true" after="FROM ( SELECT " text="'0'" />
        <Parameter name="language"/>
        <Parameter name="adUserClient" type="replace" optional="true" after="P.AD_CLIENT_ID IN (" text="'1'"/>
        <Parameter name="adUserOrg" type="replace" optional="true" after="P.AD_ORG_ID IN (" text="'1'"/>
        <Parameter name="key" ignoreValue="%" optional="true" after="AND P.ISACTIVE = 'Y'"><![CDATA[ AND UPPER(B.Value) LIKE UPPER(?) ]]></Parameter>
        <Parameter name="name" ignoreValue="%" optional="true" after="AND P.ISACTIVE = 'Y'"><![CDATA[ AND UPPER(B.Name) LIKE UPPER(?) ]]></Parameter>
        <Parameter name="pgLimit" type="argument" optional="true" after="AND 1=1"><![CDATA[LIMIT ]]></Parameter>
        <Parameter name="oraLimit1" type="argument" optional="true" after=") B"><![CDATA[ WHERE ROWNUM <= ]]></Parameter>
        <Parameter name="oraLimit2" type="argument" optional="true" after=") A "><![CDATA[WHERE RN1 BETWEEN ]]></Parameter>

HTML Template

Let's briefly discuss the most relevant sections in the HTML template.

  function validateSelector(action) {
    var pos; var keys;  var key; var text; var parameter;
    if(action == "SAVE") {
        pos = getSelectedPos();
        keys = getSelectedValues().split("#");
        key = keys[0];
        text = keys[1];
        parameter = new Array();
    parent.opener.closeSearch(action, key, text, parameter);

This function is used when a row is selected, it takes the selected row's rowkey field and splits it to obtain the ID for the record and its text that will be displayed in the field that invoked the Search, then it closes the pop-up.

function gridMovePage(direction) {
	return true;
 function updateHeader(liveGrid, offset) {
	var backendPageSize = liveGrid.getBackendPageSize();
	var currPageStart = (liveGrid.metaData.getBackendPage()*backendPageSize);
	var pageFull = (liveGrid.metaData.getTotalRows() >= backendPageSize);
	var firstPage = (liveGrid.metaData.getBackendPage() == 0);
	var res = "<nobr class='DataGrid_Popup_text_bookmark'>";
	if (!firstPage) {
	res = res + "<a href='#' onclick='gridMovePage(\"PREVIOUSPAGE\"); setWindowElementFocus(\"grid_table_dummy_input\",\"id\");' class='DataGrid_Popup_text_pagerange' id='prevPage_link'>" + getMessage("GridPreviousPage") + " " + backendPageSize +"</a>&nbsp;|&nbsp;";
	res = res + ((liveGrid.visibleRows>0)?(currPageStart+offset+1):0) + " - "+ (currPageStart+offset+liveGrid.visibleRows) + " / " + (currPageStart+liveGrid.metaData.getTotalRows());
	if (pageFull) {
	res = res + "&nbsp;|&nbsp;<a href='#' onclick='gridMovePage(\"NEXTPAGE\"); setWindowElementFocus(\"grid_table_dummy_input\",\"id\");' class='DataGrid_Popup_text_pagerange' id='nextPage_link'>" + getMessage("GridNextPage") + " " + backendPageSize +"</a>";
	res = res + "</nobr>";
	dojo.byId('bookmark').innerHTML = res;

These function are used for pagination in Grid Struture.When the Next button is clicked it will call the function gridMovePage and the rows are calculated according to the backendPageSize.

Filter fields

The filter section is composed by regular fields that will conform the filter.

Search button
<td class="Button_LeftAlign_ContentCell">
    <button type="button" 
      onclick="setFilters();return false;" 
      onfocus="buttonEvent('onfocus', this); window.status='Search'; return true;" 
      onblur="buttonEvent('onblur', this);" 
      onkeyup="buttonEvent('onkeyup', this);" 
      onkeydown="buttonEvent('onkeydown', this);" 
      onkeypress="buttonEvent('onkeypress', this);" 
      onmouseup="buttonEvent('onmouseup', this);" 
      onmousedown="buttonEvent('onmousedown', this);" 
      onmouseover="buttonEvent('onmouseover', this); window.status='Search'; return true;" 
      onmouseout="buttonEvent('onmouseout', this);">
      <table class="Button">
  	<td class="Button_left"><img class="Button_Icon Button_Icon_search" alt="Search" title="Search" src="../../../../../web/images/blank.gif" border="0" /></td>
  	<td class="Button_text Button_width">Search</td>
  	<td class="Button_right"></td>

It is a regular button that calls the setFilters() method in searchs.js.

Data grid
    <div class="Popup_ContentPane_Client" style="overflow: hidden; height: 0px;" id="client_middle">
      <table summary="" id="sectionDetail">
            <table cellspacing="0" cellpadding="0" width="10px" class="DataGrid_Header_Table DataGrid_Body_Table" style="table-layout: auto;" id="grid_sample">
              <tr class="DataGrid_Body_Row" id="grid_sample_header">
                <th width="10px" class="DataGrid_Header_Cell">DataGrid</th>
              <tr class="DataGrid_Body_Row DataGrid_Body_Row_1" onclick="" id="grid_sample_row">
                <td class="DataGrid_Body_Cell">Loading...</td>
            <table cellspacing="0" cellpadding="0" width="0px" height="0px" cellspacing="0" cellpadding="0">
                <td><input type="text" class="DataGrid_Table_Dummy_Input" id="grid_table_dummy_input"></input></td>
             <table width="100%" cellspacing="0" cellpadding="0" border="0" id="grid_bookmark">
	           <td class="DataGrid_Popup_text_container">
	              <div id="bookmark">
	                <nobr class='DataGrid_Popup_text_bookmark'>&nbsp;</nobr>
            <table width="100%" cellspacing="0" cellpadding="0" border="0" class="Main_Client_TableRelation" id="grid_table">
                  <div id="grid" dojotype="openbravo.widget.DataGrid"
                    calculatenumrows="true" editable="false" sortable="true"
                    deleteable="true" oninvalidvalue="alert" onscroll="updateHeader"
                    ongridload="onGridLoadDo" buffersize="3.0" showlinenumbers="true"
                    offset="0" sortcols="1" sortdirs="ASC" defaultrow="0"
                    maxwidth="99%" percentagewidthrelativetoid="client_middle"
                    preventcache="true" usecache="true" cachecontent="false">

This piece of code shows the data grid calling the ajax methods to populate it.

OK button

Bellow the grid a OK button is displayed, it is a standard button which calls the validateSelector('SAVE') method.

Final result

The final result for this search looks like follows.


ERP 2.50:Developers Guide/Examples | ERP 2.50:Developers Guide/Examples/Report 

Retrieved from ""

This page has been accessed 17,548 times. This page was last modified on 14 June 2011, at 11:04. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.