Tree Structure for Custom Windows in Openbravo

Generating Tree Structure is a great innovative feature that is present in OpenbravoERP. If you are wondering what it looks like, refer to Organization window in the Client Mode or the Menu Window in System Admin mode. There will be an icon called Tree in the Toolbar. Click on that and you will see something similar to the screen shot below.For more information, refer here.

This functionality will find its use in most places where there is a hierarchy to be shown and segregations are to be visualized. One more appreciable feature is that you can drag and drop the items and rearrange it. Doesn’t it sound really cool to you? I was really excited to test this out for the custom windows and with the help of Wiki, Forge and of course my colleague, I was able to create a tree similar to the one shown above for a custom window called ‘Department’. I have attached a screen shot of my tree below.

This feature is really helpful in lot of cases. I have shared the sequence of steps that I followed that enabled me to create such a structure. We have categorized the steps that we performed into 3 sections:

1. Database changes

2. Changes to be done in the Application

3. Changes to be done in code

1. Database Changes

  • Create a new table for the department. Eg: XYZ_department.

The following are the key fields in the table.

        "value" character varying(40) NOT NULL,
        "name" character varying(60) NOT NULL,
        description character varying(255),
        issummary character(1) NOT NULL DEFAULT 'N'::bpchar,
  •  Create a new table for storing the tree nodes. Eg: XYZ_treenodedp (Structure similar to ad_treenode table)
  • Add logic in the Department Table Trigger to insert values in XYZ_treenodedp and the corresponding delete logic also. Refer Organisation table trigger for more information.
  • Add a new column in ad_clientinfo. Eg: em_XYZ_tree_department

2. Changes to be done in the Application

  • Create Window for the Department. Set the field ‘Tree Included’ to Yes in Fields Tab. Eg: In Department window
  • Import the  corresponding column created in ad_clientinfo table into the Application.
  • Add a new entry in the list reference ad_treetype. Eg: Search key: XYZ_DP Name: Department
  • Create a new entry in Tree and Node Image window both in System administrator and desired client admin. Eg. Name: Primary Department Tree type: Department in System Administrator and Name: F&B International Admin Department Tree Type: Department in F&B International Admin.

  • Update and set the value for the em_XYZ_tree_department field as the client’s tree entry. Eg: In F&B International Admin, Set the value as ‘F&B International Admin Department.


3. Changes to be done in code

  • In, add the following lines in checkSpecificChanges() Method.
else if (TreeType.equals("XYZ_DP")) { //Department
 result = "";

  • In, add the following lines in getTreeType() Method.

else if (keyColumnName.equals("XYZ_Department_ID"))
 TreeType = "XYZ_DP";

  •  In, add the following lines in getTree() Method.

else if (TreeType.equals("XYZ_DP"))
 data = WindowTreeData.selectDepartment(conn, vars.getUser(), strEditable, strParentID,  strNodeId, TreeID);

else if (TreeType.equals("XYZ_DP"))
 WindowTreeData.updateDP(conn, vars.getUser(), strParentID, strSeqNo, TreeID, strLink);

  •  In WindowTree_data.xsql, add two new sql methods to select and update the tree nodes.

<SqlMethod name="updateDP" type="preparedStatement" return="rowCount">
 <Parameter name="updatedby"/>
 <Parameter name="parentId"/>
 <Parameter name="seqno"/>
 <Parameter name="adTreeId"/>
 <Parameter name="nodeId"/>

<SqlMethod name="selectDepartment" type="preparedStatement" return="multiple">
 SELECT tn.Node_ID,tn.Parent_ID,tn.SeqNo,                             m.XYZ_Department_ID AS ID, m.Name,m.Description,m.IsSummary
 FROM XYZ_TreeNodedp tn,
 XYZ_Department m
 WHERE tn.Node_ID = m.XYZ_Department_ID
 AND tn.AD_Tree_ID = ?
 ORDER BY COALESCE(tn.Parent_ID, '-1'), tn.SeqNo
 <Parameter name="editable" optional="true" type="none" after="WHERE "                 text="tn.IsActive='Y' AND m.isActive='Y' AND "/>
 <Parameter name="parentId" optional="true" after="WHERE " text="tn.Parent_ID = ?             AND "/>
 <Parameter name="nodeId" optional="true" after="WHERE " text="tn.Node_ID = ?             AND "/>
 <Parameter name="adTreeId"/>


About Pandeeswari Ramakrishnan
Applications Engineer, Openbravo

17 Responses to Tree Structure for Custom Windows in Openbravo

  1. Julissa says:


    Smart approach!!.
    I found your post @ google.

    I have a question, maybe you can help me.
    What if I want 2 modify or create a new “menu” type_tree
    I would like that each user only sees the nodes that make reference 2 the windows that he has the permission to use.
    The normal menu type_tree show all nodes and if an user click on one node that reference any windows to which he has no permission to see then comes the ” insufficient roles message”

    I was trying to apply your approach but I’m not such an expert..

    Thanks in advance


    • Hi Julissa,
      A menu will be visible only if the role has access to that particular window, process, etc. If we didn’t give access to that window or process, we will not get that menu. The case is little different when the manual field in the General Setup -> Security -> Role is set to ‘N’. When this is ‘N’, that particular client will have access to all windows by default. In that case, it will get access to all windows that has client access(Data access level in Tables and Columns). So if we try to remove that, we need to remove all the access related to that window. If we do not remove all the dependencies completely, it will still show the menu and we will get the error when trying to access it. You can have look here.

      Beyond this, if you want to make any modifications in application menu structure, then you take a look at the Java script in ob-application-menu.js located at modules -> org.openbravo.client.application -> web -> org.openbravo.client.application -> js(if you are using 3.0RC versions).

      Hope it helps. If you have any queries, do get back.

  2. Cesar Castillo says:

    Can anybody send me this tutorial better explained?
    I need many details about this tutorial. for example when I create the table XYZ_treenodedp I don’t know what tiggers I need.
    my email is

    • Hi Cesar,
      You need to create a trigger for the table for which you need the tree. In my case, its XYZ_department. This trigger must insert all the values that are inserted into the XYZ_department table into XYZ_treenodedp along with its node. For Instance, the insert logic must be something similar to this.

      -- Fetch the primary node for that client
      SELECT c.em_xyz_Tree_department_ID, n.Node_ID   
      INTO v_xTree_ID, v_xParent_ID
      FROM AD_ClientInfo c, AD_TreeNode n
      WHERE c.em_xyz_Tree_department_ID = n.AD_Tree_ID
      AND n.Parent_ID IS NULL
      AND c.AD_Client_ID = new.AD_Client_ID;
      --  Insert into TreeNode    
      INSERT INTO XYZ_TreeNodedp 
      ( XYZ_treeNodedp_Id, AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy, AD_Tree_ID, Node_ID, Parent_ID, SeqNo) 
      (get_uuid(), new.AD_Client_ID, new.AD_Org_ID, new.IsActive, new.Created, new.CreatedBy, new.Updated, new.UpdatedBy, v_xTree_ID, new.XYZ_Department_ID, v_xParent_ID,( CASE new.IsSummary WHEN 'Y' THEN 100 ELSE 999 END ));

      Similarly you should handle other action in the trigger. You can also refer the trigger ad_org_trg().

  3. Cesar Castillo says:

    Hi: Pandeeswari Ramakrishnan
    what about xyz_treenode?
    I had to copy all structure of ad_trenode. I refer to triggers, columns and indexes. I decided to compile when I finished “Changes to be done in the Application” I had the following errors:

    Table: Node not found in runtime model, is it maybe inactive?
    Reference column for Node_ID not found in runtime model
    Parent not found in runtime model, is it maybe inactive?
    Reference column for Parent_ID not found in runtime model
    Node_ID is mapped incorrectly, there is no referenced column for it, removing from the mapping
    Parent_ID is mapped incorrectly, there is no referenced column for it, removing from the mapping

    • Hi Cesar,
      The purpose of xyz_treenodedp is to store the nodes along with their parent child relationship. When you click on the tree icon in the tool bar after creating this, you will see the tree structure which is actually retrieved from the xyz_treenodedp table. Thats the reason why we are inserting the values into this table from our xyz_department_trg.

      Regarding the error you are getting, the reference for the fields node_id and parent_id will be set as “TableDir” in the columns tab of tables and columns window. By default, Openbravo will take all the columns that ends with “id” in their name as TableDir (refer here). But that should not be the case. So you have to change this to ID reference for both these fields(not always but for this case). Also uncheck the “link to parent column” option for these columns if its checked. You can refer these columns in ad_treenode table. Once you change this and compile, it should build successfully.

      • Cesar Castillo says:

        Thank you.
        I have finished successfully.
        When I started in ” Changes to be done in code”, I asked for myself that if I could export all module “xyz” without which I need to write the code. Can I made it ?.

      • Hi Cesar,
        Glad to hear that it worked..;) As of now, the tree cannot be exported to our module. But Openbravo is working on a modular tree and I will update information about it soon.

  4. Cesar Castillo says:

    Sorry I had another error could you explain me what is.

    With your current role and settings, you cannot view this information

    • Julissa says:

      Cesar, you have to explicit give access to your tree to all roles you want to otherwise they won’t be able to use it. (Just like the error message implies 😉 )
      So go to general setup >security>role. Select the rol you want to have access to your custom tree and double click to see its description. Click on the grant access. Button and a pop up window will appear. If your tree is correctly installed must be listed either as an individual module or as a part of other module, after u select ur tree and grant access to it the user wich is assigned to that rol will be able to see your tree.
      Hope that this helps

    • Hi Cesar,
      In addition to the points which Julissa mentioned, check whether you have updated and set the value for the em_XYZ_tree_department field as the client’s tree entry. Eg: In F&B International Admin, Set the value as ‘F&B International Admin Department. If you have done that already, check openbravo.log file for errors. If you have missed something, the error message in openbravo.log can help. If in case, you don have any errors in the log, then build the application with compile.complete.deploy, deploy the application and then check it again.

  5. Cesar Castillo says:

    Hi: Pandeeswari
    I am trying to develop a query which sorts the tree for example .
    -parent 1
    -leaf 1.1
    -leaf 1.2
    -parent 1.3
    -leaf 1.3.1
    -lead 1.3.2
    -parent 2
    -leaf 2.1
    -parent 2.2

    I want a query which sorts them the following way.
    Can I find a query like this?.

  6. Cesar Castillo says:

    I have another question.
    supposing that I have two rows at the windows and I want that each row has its own tree. Can I do this?.
    for example I have two rows “project 1” and “project 2”. when I select “project 1” and click on the tree, I see all tasks which has on the project 1 but I select project 2 I will see differents tasks to project 1. the task is a second level or a tab.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: