Organise Data to Tree Structure

This document try to describe based on available wiki pages, and issues how Tree working in iDempiere including alternative parent/child data structure without tree.

1. Tree - "standard iDempiere way"

https://wiki.idempiere.org/en/Tree_(Window_ID-163)

Tree can be maintained in window/tab by drag and drop or in special form Tree Maintenance

NF2.1 Tree Driven by Search Key example 01 parent child (01A, 01B) -> Tree's can be organised automagically into tree - when admin mark it as Driven by Search Key and run verify tree button - tree engine find parent node based on search key and write into tree database note: the tree structure is updated on each change after user change search key in window as well.

Purpose> idempiere zk components allow display data in tree and user can modify tree structure.

A. Predefined Trees (table/area)

these trees are predefined in iDempiere, ready to use

• Menu - Primary Menu - AD_Menu (isSummary)
  • usage 1. https://wiki.idempiere.org/en/Reference
  • usage 2. https://wiki.idempiere.org/en/Reference-System
• Element Value - (Primary Account Element Value) - C_Element (has AD_Tree) +C_ElementValue (isSummary)
  • this can be imported.
  • note: C_Element has AD_Tree which allow select tree filter (this looks me same as Parent_Column_ID for custom table)
• Bpartner - (Primary Business Partner) - IsSummary, BPartner_Parent_ID
• Organization - (Primary Organization) - IsSummary
• Product - (Primary Product) - IsSummary
• Project - (Primary Project) - IsSummary
• Sales Region - (Primary Sales Region) - IsSummary

B. Tree for custom table

Allow define tree for any window/tab per tenant has field isSummary=Y

more: https://wiki.idempiere.org/en/NF3.0_Tree_On_Any_Table link

custom tables displayed by dynamic validation:

• ad_menu
• ad_org
• ad_pinstance
• ad_tree_favorite
• c_activity
• c_bpartner
• c_campaign
• c_elemenvalue
• c_project
• s_salesregion
• c_tax
• i_elementvalue - exclude ?
• i_reportline - exclude ?
• m_product_category
• m_product
• pa_goal

2. Hierarchy without tree definition

iDempiere has defined on some tables %parent% and isSummary columns. This allow define hierarchical structure without iDempiere standard tree component. The next elements are defines on a table as Parent column:

• A_Parent_Asset_ID,
• PA_GoalParent_ID
• ParentValue_Parent Key - used for import element value
• Parent_Org_ID
• Parent_Tax_ID
• BPartner_Parent_ID
• SalesRep_BP_BPartner_Parent_ID
• M_Product_Category_Parent_ID

SQL snippets

1--- get trees
2
3SELECT * FROM ad_tree WHERE TreeType = 'TL'

1-- find tree for a specific tree
2SELECT t.ad_tree_id, treetype, node_id, parent_id, seqno
3FROM ad_tree t
4LEFt JOIN ad_treenode tn ON  tn.ad_tree_id=t.ad_tree_id
5WHERE TreeType = 'TL' and t.ad_tree_id=?
6order by t.ad_tree_id, node_id, parent_id, seqno

Specific Use case

We would like show to customer M_Product_Category as tree (ZK) and same time allow get data from REST API to build from M_Product_Category+M_Product_Category_Parent_ID+isSummary+SeqNo tree - flat (TREE API :) )

xref

https://www.chuckboecking.com/adempiere-idempiere-tree-management/

https://docs.mckayerp.com/introduction/system-administration/general-rules/system-settings#trees-and-tree-maintenance