Hierarchical data in OutSystems

A self-referencing entity, recursive SQL statement and a tweaked tree view

Hierarchical data is a structure of data that references itself to represent a tree. You can think of its individual elements as parents and children. A single element can have several children and itself can be the child of exactly one other element. In practice, you often find such structures for mapping organizational hierarchies, folders or taxonomies. Although OutSystems knows the language construct of structures, these can only have attributes of a primitive data type or a different structure, but not themselves. This means that hierarchical data cannot be mapped one-to-one. In this article, I describe how you can nevertheless work with hierarchical data in OutSystems, from persistence in the database to processing it in the core service and displaying it in the UI.

What you need

If you want to follow up the implementation on your own, you need:

• An OutSystems account (a free personal environment is sufficient)
• OutSystems Service Studio

I would like to illustrate the topic using a simple sample application for storing organizational units.

Core Service: Where to store hierarchical data

The easiest way to store data in OutSystems is within the database, i.e. the entities. From an architectural point of view, we therefore start again with a core service called OrgUnit_CS. For the sake of simplicity of our sample application, this encapsulates the persistence layer and the business logic layer. So we can omit a separate BL module. This also allows to keep the entities private so we can control any access to the data inside the core service.

The minimal data model shown here only consists of a single entity for the organizational unit. In addition to the primary key OrgUnitId, we also provide a Name attribute of type Text, in which we can store a user-friendly display name. The hierarchy is created by the foreign key ParentOrgUnitId, which points to the entity itself and can hold the reference to the respective superior organizational unit (parent element). For top-level units that are not subordinate to any other organizational unit, the attribute therefore remains empty (NullTextIdentifier). We also add the standard audit attributes for creation and last modification date and the respective user. For this sample, we also want to ensure the name of an organizational unit within the same direct parent is unique. We can enforce this at database level using an index. The entity now looks the following:

/**
 * @entity OrgUnit
 * Organizational unit
 * Public: No
 *
 * @attribute {Text} OrgUnitId
 * Id of the organizational unit
 * Length: 36
 *
 * @attribute {Text} Name
 * Name of the organizational unit
 * Length: 100
 *
 * @attribute {OrgUnit Identifier} [ParentOrgUnitId]
 * Parent of the organizational unit (Null = root level)
 * Delete Rule: Protect
 *
 * @attribute {Date Time} CreatedOn
 * Timestamp of creation
 *
 * @attribute {User Identifier} CreatedBy
 * User who created the record
 *
 * @attribute {Date Time} UpdatedOn
 * Timestamp of last update
 *
 * @attribute {User Identifier} UpdatedBy
 * User who last updated the record
 *
 * @index UX_Parent_Name
 * Unique: Yes
 * Attribute 1: ParentOrgUnitId
 * Attribute 2: Name
 */

Note the lengths of the text attributes here, as these will become relevant later. We use GUIDs as identifiers (OrgUnitId), hence the length 36. The length of the name attribute and the delete rule of ParentOrgUnitId have also been chosen with care, but more on this later.

Trading storage space for performance

As we can already see from the data model, the foreign key relationship to the own entity results in a referential chain that gets longer with the number of hierarchy levels. As mentioned above, this parent reference is empty for top-level entities. Both root elements as well as all direct child elements of a specific organizational unit can therefore be selected very easily by filtering on the ParentOrgUnitId attribute. However, if you want to select all elements of one hierarchy level or elements across multiple levels, you have to recursively trace the tree starting from one or more parent elements. This means you must first select all child elements of the initial element followed by their respective child elements and so on. But this process is quite IO and computing intensive. In addition, this procedure can result in an endless loop when a circular reference occurs in the chain, i.e. when an element is defined as a parent element of an own (grand)parent element. Fortunately, circular references like this hardly ever show up in practical requirements. It is therefore far better to avoid such a constellation in the first place so as not to have to deal with infinite loops later.

Usually, data is read much more often than it is written. Wouldn’t it be nice if we could create a more performant solution that allows us to avoid recursions at least when selecting data? For the reasons mentioned above, we have to accept them for validation purposes at writing time anyways. This is why we create some additional attributes in the database:

/**
 * @entity OrgUnit
 * Organizational unit
 * Public: No
 *
 * [...]
 *
 * @attribute {Integer} [HierarchyLevel]
 * Zero-based level of the organizational unit in hierarchy tree
 *
 * @attribute {Text} [HierarchyIds]
 * Concatenated OrgUnitId path of the organizational unit in hierarchy tree
 * Length: 722
 *
 * @attribute {Text} [HierarchyNames]
 * Concatenated name path of the organizational unit in hierarchy tree
 * Length: 1954
 */

So what are these attributes for and where do the weird text lengths come from? All Text fields in entities must have a defined length. As described below, this results in a technical requirement for our application. OutSystems maps the data types to database types on publishing. Up to a length of 2000 characters, Text is mapped to the NVARCHAR (SQL Server) resp. VARCHAR2 (Oracle) type, values above that to the less performant binary type NVARCHAR(MAX) resp. CLOB. Therefore we need to limit the maximum allowed depth of the hierarchy — in our case to 19 levels:

• HierarchyLevel allows selections and calculations based on the hierarchical depth of an element
• HierarchyIds contains the sequence of OrgUnitIds from the root to the respective element and thus enables queries and checks for parenthood. As we will see later, this also helps prevent circular references. The length is calculated by the depth limit:
  19 levels * Length("{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}")
• HierarchyNames contains a user-friendly representation of the organizational units name path separated by backslashes, which leads to the length: 19 levels * 100 + 18 separators * Length(" \ ")

Now we need a little more storage space in the database, but we can perform many queries with simple selections that would normally only be possible recursively. Basically, it’s a similar deal as with indexes.

Maintaining consistency

But wait, the three new attributes contain information that is redundant to the ParentOrgUnitId relationship, don’t they. In addition to increased memory requirements, this also means a risk of data inconsistency. For example, if someone changes the ParentOrgUnitId or the name of an element but does not adjust the Hierarchy… attributes, the database contains contradictory information. This gets even worse because both attributes HierarchyIds and HierarchyNames contain concatenated information originating from other records, the own parent element chain.

At this point, the chosen software architecture comes to our aid: All organizational units are stored and managed within our core service. This is why we have created the entity with Public set to No. It is therefore only available within the core service and can only be accessed using our own logic. By doing so, the logic can ensure consistent changes across all attributes and even multiple records. As a general rule, it is good practice to keep entities private or at least only expose them for read-only access.

So let’s start with the first server action OrgUnit_UpdateHierarchy of the core service. Its job is to recursively build the tree after changes to organizational units, recalculate the three Hierarchy… attributes for all elements and write them back to the database. This can be accomplished with a single advanced SQL call:

WITH HIERARCHY_CTE AS
(
    SELECT
        {OrgUnit}.[OrgUnitId],
        {OrgUnit}.[ParentOrgUnitId],
        0 AS HierarchyLevel,
        CONVERT(VARCHAR(MAX),{OrgUnit}.[Name]) AS HierarchyNames,
        '{' + CONVERT(VARCHAR(MAX),{OrgUnit}.[OrgUnitId]) + '}' AS HierarchyIds
    FROM {OrgUnit} WHERE {OrgUnit}.[ParentOrgUnitId] IS NULL
    UNION ALL
    SELECT
        self.[OrgUnitId],
        self.[ParentOrgUnitId],
        parent.[HierarchyLevel] + 1 AS HierarchyLevel,
        parent.[HierarchyNames] + ' \ ' + CONVERT(VARCHAR(MAX),self.[Name]) AS HierarchyNames,
        parent.[HierarchyIds] + '{' + CONVERT(VARCHAR(MAX),self.[OrgUnitId]) + '}' AS HierarchyIds
    FROM {OrgUnit} self
    INNER JOIN HIERARCHY_CTE parent ON parent.[OrgUnitId] = self.[ParentOrgUnitId]
)

UPDATE {OrgUnit} SET
    {OrgUnit}.[HierarchyLevel] = HIERARCHY_CTE.[HierarchyLevel],
    {OrgUnit}.[HierarchyNames] = HIERARCHY_CTE.[HierarchyNames],
    {OrgUnit}.[HierarchyIds] = HIERARCHY_CTE.[HierarchyIds]
FROM {OrgUnit} JOIN HIERARCHY_CTE ON {OrgUnit}.[OrgUnitId] = HIERARCHY_CTE.[OrgUnitId]

As you can see there is no input parameter and also no output generated. Nevertheless, OutSystems forces us to add an output structure to the SQL element, so we can just use the entity OrgUnit here. Should you not be that familiar with SQL, I would like to explain what the above script is doing:

• The script is split into two parts. The first one is a so called common table expression to recursively select the tree of organizational units as a flat list containing the OrgUnitId, ParentOrgUnitId and the three Hierarchy… columns.
• It starts with selecting only the root level elements by filtering WHERE {OrgUnit}.[ParentOrgUnitId] IS NULL, returning HierarchyLevel being zero (indicating root level) and “initializing” HierarchyNames and HierarchyIds with name and parenthesized GUID of the respective unit.
• This is then combined by using UNION ALL with the selection of direct child elements (alias self) by joining with the already selected common table expression result HIERARCHY_CTE (alias parent) on the join condition parent.[OrgUnitId] = self.[ParentOrgUnitId]. HierarchyLevel is increased by selecting parent.[HierarchyLevel] + 1 AS HierarchyLevel while HierarchyNames and HierarchyIds are extended on the right-hand side by concatenating parent.[Hierarchy...] with the corresponding string ' \ ' + self.[Name] resp. '{' + self.[OrgUnitId] + '}'.
• The second part of the script uses the first part’s result to update the records in OrgUnit with the data corresponding by OrgUnitId.

To indicate that these attributes in the entity are calculated, I recommend to extend their description in OutSystems with a hint like “(calculated in OrgUnit_UpdateHierarchy)”.

Validating incoming data

The second server action we implement is used to validate organizational units before they are saved. For receiving data to be saved, we define the structure OrgUnit_Save with attributes for the name and the ParentOrgUnitId. The easiest way to do this is to copy the entire entity and paste it under Structures. The advantage with doing so is OutSystems not only takes over the individual attributes but also their configuration such as description, data type, lengths and so forth. Afterwards, you can simply delete attributes that are not required and make any necessary adjustments.

/**
 * @structure OrgUnit_Save
 * Details of an organizational unit to be saved
 * Public: Yes
 *
 * @attribute {Text} Name
 * Name of the organizational unit
 * Length: 100
 *
 * @attribute {OrgUnit Identifier} [ParentOrgUnitId]
 * Parent of the organizational unit (Null = root level)
 */

The service action has two input parameters OrgUnitId and OrgUnitSave, validates the data and throws a ValidationException if a check fails:

• First we check if the name is not empty as it should be a mandatory information so an organizational unit always has a name to be displayed: Trim(OrgUnitSave.Name) <> ""
• In GetSiblingByName we try to select another organizational unit (OrgUnit.OrgUnitId <> OrgUnitId) that is a direct sibling in the tree (OrgUnit.ParentOrgUnitId <> OrgUnitSave.ParentOrgUnitId) and has the same name (OrgUnit.Name = OrgUnitSave.Name). Giving aggregates a Max. Records value is recommended by OutSystems AI Mentor Studio for performance reasons. Since there cannot be more than one result due to our UX_Parent_Name index, and we only need the result to check for at least one conflict, we can set it to one.
• If no name conflict is found and the organizational unit is on root level OrgUnitSave.ParentOrgUnitId = NullTextIdentifier() we can end the validation. Otherwise we also need to validate the parent relationship.
• To do so, we need to GetParentById (OrgUnit.OrgUnitId = OrgUnitSave.ParentOrgUnitId). If the parent cannot be found (GetParentById.List.Empty), the own level would be to deep (GetParentById.List.Current.OrgUnit.HierarchyLevel >= 18) or the parent would be an own child (Index(GetParentById.List.Current.OrgUnit.HierarchyIds, "{" + OrgUnitId + "}") <> -1) we throw an exception.

Here for the first time we see the advantage of the two additional attributes HierarchyLevel and HierarchyIds in the database. With their help, we no longer have to perform a recursive database query for validation, but can directly select and check the depth of the future parent element, and thus also our own depth. We can also use HierarchyIds to check whether our own id occurs in the id path of the parent element, which means it is a direct or indirect child and would create a circular reference in the tree.

Exposing a service interface

So far, all implemented elements of the core service except for the OrgUnit_Save structure were non-public, i.e. usable within the OrgUnit_CS module only. It is now time to provide an interface for the outside world. Of course we want to allow consumers of the service to perform the default CRUD (create, read, update and delete) actions on organizational units. We do so by exposing some public server actions:

OrgUnit_Create

/**
 * @serveraction OrgUnit_Create
 * Creates a new organizational unit
 *
 * @input {OrgUnit_Save} OrgUnitSave
 * Details of the organizational unit to be saved
 *
 * @output {OrgUnit Identifier} OrgUnitId
 * Id of the new organizational unit
 */

• In the introduction, the action makes use of the previously implemented non-public server action OrgUnit_Validate on the data to be saved. If this check fails, a ValidationException is thrown there, aborting processing.
• The next two steps are the basic ones to create the new record in the database and return the new identifier in output parameter OrgUnitId. By the way, this is generated directly in the CreateOrgUnit call via the expression TextToIdentifier(GenerateGuid()). GenerateGuid is added to the dependencies from System.
• Finally, the call of OrgUnit_UpdateHierarchy ensures the three hierarchy attributes for optimized queries on the tree are updated in the database.

OrgUnit_Update

/**
 * @serveraction OrgUnit_Update
 * Updates an organizational unit
 *
 * @input {OrgUnit Identifier} OrgUnitId
 * Id of the organizational unit
 *
 * @input {OrgUnit_Save} OrgUnitSave
 * Details of the organizational unit to be saved
 */

// Update attributes
GetOrgUnitForUpdate.Record.OrgUnit.Name = Trim(OrgUnitSave.Name)
GetOrgUnitForUpdate.Record.OrgUnit.ParentOrgUnitId = OrgUnitSave.ParentOrgUnitId
GetOrgUnitForUpdate.Record.OrgUnit.UpdatedOn = CurrDateTime()
GetOrgUnitForUpdate.Record.OrgUnit.UpdatedBy = GetUserId()

This is implemented the same way as OrgUnit_CreateOrg is except the middle part which of course is not inserting a new record to the database but selecting the record specified by OrgUnitId and updating the relevant attributes.

OrgUnit_Delete

/**
 * @serveraction OrgUnit_Delete
 * Updates an organizational unit
 *
 * @input {OrgUnit Identifier} OrgUnitId
 * Id of the organizational unit
 *
 * @input {Boolean} [IsRecursive]
 * True to recursively delete all children
 * Default: False
 */

This action has two different operation modes:

• If IsRecursive is not set, aggregate GetOrgUnitByHierarchyIds selects if there is at least one (Max Records set to one again) direct or indirect child of the organizational unit to be deleted by filtering OrgUnit.HierarchyIds like "%{" + OrgUnitId + "}%". If so, a ValidationException is thrown otherwise the record is deleted in the entity.
• The second operation mode allows to delete an organizational unit and all its direct and indirect children. This is performed with the following advanced SQL statement which again as result structure simply uses the OrgUnit entity: DELETE FROM {OrgUnit} WHERE {OrgUnit}.[HierarchyIds] LIKE '%[' + @OrgUnitId + '}%'

OrgUnit_Search

/**
 * @serveraction OrgUnit_Search
 * Search for organizational units
 *
 * @input {OrgUnit_SearchCriteria} [SearchCriteria]
 * Criteria to search for organizational units
 *
 * @output {OrgUnit_Result List} Results
 * List of matching organizational units
 */

The last of the four CRUD actions is to read data and uses the aggregate GetOrgUnitsBySearchCriteria to select from OrgUnit, joined two times with the System entity User (alias CreatedByUser and LastUpdatedByUser) to also get details about the users who created (OrgUnit.CreatedBy = CreatedByUser.Id) and last updated (OrgUnit.LastUpdatedBy = LastUpdatedByUser.Id) the record. The search is performed by the filtering in that aggregate:

• To search for SearchTerm in hierarchy names path: OrgUnit.HierarchyNames like "%" + SearchCriteria.SearchTerm + "%"
• To restrict the results to only direct children (ParentOrgUnitIdIsRecursive = False) or otherwise also indirect children of a single element in the tree: SearchCriteria.ParentOrgUnitId = NullTextIdentifier() or OrgUnit.ParentOrgUnitId = SearchCriteria.ParentOrgUnitId or (SearchCriteria.ParentOrgUnitIdIsRekursive and OrgUnit.HierarchyIds like "%{" + SearchCriteria.ParentOrgUnitId + "}%")
• To exclude a single element and all its direct and indirect children: SearchCriteria.ExcludeOrgUnitIdRecursive = NullTextIdentifier() or (OrgUnit.OrgUnitId <> SearchCriteria.ExcludeOrgUnitIdRecursive and not (OrgUnit.HierarchyIds like "%{" + SearchCriteria.ExcludeOrgUnitIdRecursive+ "}%"))
• To limit the result to a certain maximal depth level: SearchCriteria.MaxHierarchyLevel = -1 or OrgUnit.HierarchyLevel <= SearchCriteria.MaxHierarchyLevel

Instead of a tree structure, the result obviously is a flat list of organizational units. As described above, OutSystems does not support self-referencing structures and therefore does not allow returning a recursive tree structure. In order to nonetheless provide the tree in a form that is easy to consume, the result list in the aggregate is sorted by HierarchyNames. In this way, child elements always immediately follow their individual parent element in alphabetical order.

I’m sure you’ve noticed that SearchCriteria and Results each use their own structure. Of course, I don’t want to withhold their configuration either:

/**
 * @structure OrgUnit_SearchCriteria
 * Criteria to search for organizational units
 * Public: Yes
 *
 * @attribute {Text} [SearchTerm]
 * Search in HierarchyNames
 *
 * @attribute {OrgUnit Identifier} [ParentOrgUnitId]
 * Filter by parent unit (parent unit is only included if ParentOrgUnitIdIsRecursive = True)
 *
 * @attribute {Boolean} [ParentOrgUnitIdIsRecursive]
 * True to also return the specified parent unit (ParentOrgUnitId) and all indirect children
 *
 * @attribute {OrgUnit Identifier} [ExcludeOrgUnitIdRecursive]
 * Exclude a specific unit and it's direct and indirect children
 *
 * @attribute {Integer} [MaxHierarchyLevel]
 * Maximum returned hierarchy level (-1 for no filtering)
 * Default: -1
 */

/**
 * @structure OrgUnit_Result
 * Result of an organizational unit search
 * Public: Yes
 *
 * @attribute {OrgUnit Identifier} OrgUnitId
 * Id of the organizational unit
 *
 * @attribute {Text} Name
 * Name of the organizational unit
 * Length: 100
 *
 * @attribute {OrgUnit Identifier} [ParentOrgUnitId]
 * Parent of the organizational unit (Null = root level)
 *
 * @attribute {Integer} [HierarchyLevel]
 * Zero-based level of the organizational unit in hierarchy tree
 *
 * @attribute {Text} [HierarchyIds]
 * Concatenated OrgUnitId path of the organizational unit in hierarchy tree
 *
 * @attribute {Text} [HierarchyNames]
 * Concatenated name path of the organizational unit in hierarchy tree
 *
 * @attribute {Date Time} CreatedOn
 * Timestamp of creation
 *
 * @attribute {User Identifier} CreatedBy
 * User who created the record
 *
 * @attribute {Text} [CreatedByName]
 * Name of the user who created the record
 *
 * @attribute {Date Time} UpdatedOn
 * Timestamp of last update
 *
 * @attribute {User Identifier} UpdatedBy
 * User who last updated the record
 *
 * @attribute {Text} [UpdatedByName]
 * Name of the user who last updated the record
 */

UI: How to display hierarchical data

One of the first options that comes to mind for displaying hierarchical data in a GUI is certainly the well-known tree view. However, if we look through the default controls of the OutSystems UI, we won’t find such a component there. But don’t worry, we don’t have to code our own screen widget and struggle with the depths of React and JavaScript. Instead, I would like to present a simple solution based on the list control:

Obviously, we need the service actions of the core service, so we add them as a dependency to the UI module.

Loading the tree

For the simplicity of this demo, I decided to implement everything on one screen. This loads the list of organizational units via the fetch action GetOrgUnits. This calls the OrgUnit_Search in the core service and assigns the result to the output parameter List. As can be seen in the screenshot above, in addition to the organizational unit data, we need further information to display the tree view, e.g. whether an element is collapsed. To do this, we create a new structure in the UI module:

/**
 * @structure OrgUnit_TreeItem
 * Tree item data to display an organizational unit
 * Public: No
 *
 * @attribute {OrgUnit_Result} OrgUnit
 * Organizational unit data
 *
 * @attribute {Boolean} HasChildren
 * True to indicate the element has children
 *
 * @attribute {Boolean} IsCollapsed
 * True if the element is currently collapsed in the tree view
 *
 * @attribute {Boolean} IsVisible
 * True if the element is currently visible in the tree view
 */

To hold this extended data for the tree view on the screen, we create a new local variable TreeItems of type OrgUnit_TreeItem List. After the organizational unit data has been loaded, this variable must of course also be filled with data. To do so, we handle the On After Fetch event of GetOrgUnits:

• As GetOrgUnitsOnAfterFetch is not only called once but after each data fetch and we do not want to loose the collapse state of the tree, in the first loop we append the ids of all expanded elements to the variable ExpandedOrgUnitIds of type OrgUnit Identifier List.
• Afterwards the local screen variable TreeItems is cleared so it can easily be repopulated within the next loop.
• This next loop iterates all freshly fetched organizational units in GetOrgUnits.List and assigns its values to the temporary variable CurrentOrgUnitTreeItem, which has our target type OrgUnit_TreeItem. Also the attribute HasChildren is calculated based on the subsequent element. By default IsCollapsed is initialized with True for all elements that have children.

// CurrentOrgUnitTreeItem
CurrentOrgUnitTreeItem.OrgUnit = GetOrgUnits.List.Current
CurrentOrgUnitTreeItem.HasChildren = (
  GetOrgUnits.List.CurrentRowNumber < GetOrgUnits.List.Length - 1 and
  GetOrgUnits.List.Current.OrgUnitId = GetOrgUnits.List[GetOrgUnits.List.CurrentRowNumber + 1].ParentOrgUnitId
)
CurrentOrgUnitTreeItem.IsCollapsed = CurrentOrgUnitTreeItem.HasChildren

• Elements without children (not CurrentOrgUnitTreeItem.HasChildren) cannot be collapsed, so they can directly be appended to the TreeItems.
• For collapsible elements we check by their id if they were previously expanded (existent in ExpandedOrgUnitIds) and overwrite their IsCollapsed attribute with not AnyExpandedOrgUnitIds.Result before appending them to TreeItems.
• Finally Tree_RefreshItems is called to iterate all elements in the updated TreeItems list to also set their IsVisible attribute. This is located in a separate client action because it is reused when switching the collapsed state of an element later.

// IsVisible
TreeItems.Current.IsVisible = (
  LastCollapsedIdPath = "" or
  Substr(TreeItems.Current.OrgUnit.HierarchyIds,0,Length(LastCollapsedIdPath)) <> LastCollapsedIdPath
)

• The temporary variable LastCollapsedIdPath is set to the HierarchyIds value of the current element if it is visible and collapsed. In combination with the previous assignment, this ensures that all children of the last collapsed element (HierarchyIds starts with LastCollapsedIdPath) are invisible, while the next element, which is not a child, is visible.

Designing the screen

Now that the data is prepared for display, we can implement the UI:

• We place a Container_TreeCollapseAll in the MainContent to hold two links for collapsing and expanding all elements in the tree.
• List_Tree is a normal list control to display the tree items.
• Each item will be represented by an instance of Container_TreeItem which is styled with the CSS classes list-item vertical-align gap-s padding-s. This aligns Container_TreeItem_HierarchyLevel and Container_TreeItem_OrgUnitName to be in line.
• The magic is happening on Container_TreeItem_HierarchyLevel, styled by CSS class text-align-right in combination with its attribute style being "width:" + (TreeItems.Current.OrgUnit.HierarchyLevel * 2 + 2) + "em;". This generates the indentation by 2 em per level. The +2 at the end adds space for the icon to expand or collapse (Size = 2x font size).
• At a time, only one of both icons Icon_Plus and Icon_Minus is visible (Visible = TreeItems.Current.IsCollapsed eg. not TreeItems.Current.IsCollapsed).
• Container_TreeItem_OrgUnitName is just showing the organizational units name and linking it to a client action we will implement later.

Pretty cool, isn’t it? The only little bit missing is some display logic for expanding and collapsing — and needless to say, neither the name link will remain unexplained.

Collapsing tree items

With all the preparation we did so far, expanding and collapsing tree items is very easy. The Link_OrgUnitCollapse is only displayed for elements having children (Visible = TreeItems.Current.HasChildren). Switching the collapse state of a tree item is simply invert its IsCollapsed attribute:

TreeItems.Current.IsCollapsed =
  not TreeItems.Current.IsCollapsed and
  TreeItems.Current.HasChildren

The additional check for HasChildren is to ensure only collapsible tree items are modified, just in case the action should be called for a childless element. Again, finally we call Tree_RefreshItems so the IsVisible attribute of all TreeItems is updated accordingly.

This next client action allows to expand or collapse all elements at once (two links above the list). Therefore, Tree_OnCollapseAll receives IsCollapse as Boolean input parameter and assigns its value to all TreeItems by iterating them. Of course, the action is concluded by calling Tree_RefreshItems.

Editing tree items

As promised, I won’t withhold what happens in the small sample application when you click on an organizational unit. In this case, a pop-up dialog opens containing a text field for the name and a dropdown for selecting the parent unit.

• To display the parent elements indented by level in the dropdown as well, we can set Options Content to Custom. This allows us to place a Container in the dropdown control that now represents the individual option entries.
• Setting Style Classes to "full-width" ensures that the entry extends across the entire width. The style attribute uses "padding-left: " + GetParentOrgUnits.List.Current.HierarchyLevel + "em:" to indent again.

For sure, this selection list must also be loaded. The interesting part is to prevent circular references. Wouldn’t it be smart to offer organizational units that are eligible as parents only. Fortunately, with the attribute ExcludeOrgUnitIdRecursive in OrgUnit_SearchCriteria we have created a suitable filter to achieve this.

• GetParentOrgUnits can be fetched Only on demand because it is exclusivly used by the popup dialog and refreshed in OrgUnit_ShowPopup when needed. To determine this, we need to know which OrgUnitId has been excluded from the loaded selection list. This is what the second output parameter ExcludedOrgUnitId is for.
• OrgUnit_ShowPopup opens or closes the popup depending on the input parameter Show, whereby the optional parameter OrgUnitId specifies which organizational unit is to be opened.
• First we check if the popup is to be hidden or shown to create a new entry (not Show or OrgUnitId = NullTextIdentifier()). If so, the local variable OrgUnit_Data is cleared:

/**
 * @record OrgUnit_Data
 * Organizational unit data for the popup dialog
 * 
 * @attribute {Boolean} [HasChildren]
 * @attribute {OrgUnit Identifier} [OrgUnitId]
 * @attribute {OrgUnit_Save} [OrgUnitSave]
 */

// Clear OrgUnitData
OrgUnit_Data.OrgUnitId = NullTextIdentifier()
OrgUnit_Data.OrgUnitSave.Name = ""
OrgUnit_Data.OrgUnitSave.ParentOrgUnitId = NullTextIdentifier()
OrgUnit_Data.HasChildren = False

• Otherwise the index of OrgUnitId in TreeItems is searched and the respective data is assigned to OrgUnit_Data: OrgUnit_Data = TreeItems[IndexOfOrgUnitId.Position]. If not found an error message is displayed.
• Finally, we check whether the selection list needs to be fetched (Show and (not GetParentOrgUnits.IsDataFetched or GetParentOrgUnits.ExcludedOrgUnitId <> OrgUnitId)), trigger RefreshGetParentOrgUnits accordingly and show or hide the popup (OrgUnit_ShowPopup = Show).

Not to make the article any longer, I will abbreviate the implementation of the buttons on the popup dialog. The X to close and the Cancel button call OrgUnit_ShowPopup with the parameter Show set to False. Save and Delete buttons each have their individual client action, which should be straightforward:

You can download the complete application from the OutSystems Forge:

Hierarchical data in OutSystems (Demo)

I hope this article was inspiring and gave you some ideas on how you can implement hierarchical data in your OutSystems projects easily and without much effort. If you liked it, feel free to follow me on my social media accounts. In case you have any questions left open or have tips for me to improve, please contact me via Medium or my OutSystems profile.