Tree List

It is used to report data in a hierarchical way. The requested information is displayed as a tree list.

Appearance in Toolbox	Appearance in Free Form	Appearance in Responsive Form

Compatibility

Free Form	Responsive Form	Mobile

Properties

• Name: The name of the widget will be written here.
• Text Size: Sets the text size inside the widget.
• Height / Width: Sets the height and width of the widget (Width field has been removed from Responsive Forms).
• From Left / Top: Sets the distance of the widget from the left and top (This field has been removed from Responsive Forms).
• SQL Query: SQL queries will be written here.
• Formatting Field (Field|Operator|Value|Type|Colors|Cell(0-1)|Colored Area(Optional for Cell value=1): The specified color, text properties or date format is applied according to the specified field information.
• Hide Column Heading: Hides the column headings in the list.
• Fit Horizontal: Fits the widget to the full screen to the right on the user's screen.
• Pin Right: Pin the widget to the right.
• Display: Makes the widget not visible on the screen.
• Detail Button: Adds a detail button to the rows in the report.

Actions

• When the row is selected: This is the action for actions to be taken when the row is clicked once.

Let's make an example as When the row is selected action.

When the row is selected, let's use the Open form-List action to open the popup list after the selection.

Let's select our tree list tool and create the action that appears on the left side of the screenshot shared below.

Save our action with the save button.

 It should not be forgotten to scale the open form to be opened, scaling is done from the Form Widhth/Height fields in the action. Then save our form and go to our client screen with Run. In the client image on the right of the screenshot, the form is opened after selecting the row from the tree list tool.

• When double clicked on row: This is the action performed by double clicking on the row of the list.

Let's make an example as When double clicked on row action.

Let's select the row in the list by double clicking on a text box field added to the form and show the UserTableID value only in the text box whose property is open on the form.

Let's select our tree list tool and create our action that appears on the left side of the screenshot shared above and save our action with the save button. Then save our form and go to the client screen with the Run button. In the client image on the right of the screenshot, the UserTableID value of the bottom row is shown in the RowID added to the form.

On the Client screen, when writing a query for columns that should not be displayed in the list, the | character is added at the beginning of the ALIAS given to the column name.
For example: SELECT 1 AS [|id]

This value, which is not visible in the list but exists in the dataset, should be used as shown below within the relevant action if it needs to be transferred to a field on the form:

SELECT '$Pid$'

Form Action: It is necessary to add an event in the Tree List tool. When the form is opened, the Update Value action is created for the tree list tool. In the screenshot shared below, the details of the action created for the tree list element to work when the form is opened are shared.

Example 1:

Let's add a Tree List tool to our form and write a query like below in the SQL Query field of our Tree List tool.

SELECT 1 AS [|id], null AS [|parentId], 'FACULTY OF EDUCATION' AS 'Faculties and Departments', '39' AS Quantity  
UNION ALL  
SELECT 2, 1, 'Education Sciences', '16'  
UNION ALL  
SELECT 3, 1, 'Department of Computer and Instructional Technology Teacher Education', '2'  
UNION ALL   
SELECT 4, null, ' FACULTY OF ENGINEERING' , '21'  
UNION ALL   
SELECT 5, 4, 'Department of Computer Engineering','8'  
SELECT 6, 4, 'Department of Electrical and Electronics Engineering', '13' 

The appearance of this query on the client screen will be as follows.

In the Search field, a search is made among the values in the list.

In this query, rows with IDs 1 and 4 can be considered as parent since their parentId fields are empty. The row IDs Id 2 and Id 3 are under row 1 because the parentId field is 1. And the row with Id 5 and Id 6 will be listed below row 4, again because the parentID field is 4.

By clicking on the triangles on the left side, the values in them are also displayed.

Formatting (Field|Operator|Value|Type|Colors|Cell(0-1)|Colored Area(Optional for Cell Value=1)); This field is used if you want to color the data in the tree list. Let's give an example about coloring.

Field:  Field name to be checked for value

Operator:  Comparison criterion or change format ( for numeric/datetime fields ) 

Value: (to write value to compare) or (to write numeric/datetime format) 

Type: 
0 = To change text color 
1 = To change background color 
2 = To change text color and font style to bold 
3 = To change text color and font style to italic 
4 = To change background color and font style to bold 
5 = To change background color and font style to italic 
right =align text right 
left = align text left 
center = align text center

Colors:   Type the desired color

Cell: 
0 To apply the style to the entire row 
1 To apply the style to the selected area

Colored Area: To change the color of another area instead of the checked value area (Optional for Cell value=1

In the above coloring example, columns with values greater than 20 in the column Quantity will be colored red.

In the second row, columns with values greater than 0 in the parentId column will be colored blue and the related texts will be italicized.

Unlike the Tree-list tool, in the Tree List tool, the desired rows can be linked to the parent field with the value in the parentId field at the end of the query.

The List Tree can be thought of as a family tree. If the row (parentId) to which the List value is linked is null in the tree structure, the parent will be listed as lineage.

Example 2:

Let's enter our project and design the order creation screen by dragging the elements needed from the form elements area on the left side to the design screen and add the queries. After designing, let's save from the area marked in red in the screenshot below, press the save button, press the run button and go to the client screen.

Enter the information according to the order number on the order screen. Let's enter and add details.

In this example, 3 order numbers and details are entered.

Then let's add a second form in our project and create it as a report. Let's drag a button and a tree list element to the screen.

Let's add When clicked - Update Value - TreeList_1 action in the button to trigger the tree list element.

Click on the Tree List element. Let's add our query to the SQL Query field from the properties opened on the right side.

Let's add our query below according to our project.

SELECT 
0 AS [|DetailID],
O.OrderNo AS [|OrderNo],
O.OrderNo AS [|id],
O.Customer,
O.OrderDate,
CASE O.Status WHEN 1 THEN 'Processing' WHEN 2 THEN 'Shipped' ELSE 'Delivered' END  AS Status,
FORMAT(O.TotalAmount , 'N2', 'tr-TR') AS TotalAmount,
'' AS Product,
'' AS Quantity,
'' AS Discount,
'' AS UnitPrice,
ROUND(SUM(D.TotalPrice),2) AS TotalPrice,
NULL AS [|parentId]
FROM dbo.XPD_ORDERS O WITH (NOLOCK)
LEFT OUTER JOIN dbo.XPD_ORDERS_DETAIL D WITH (NOLOCK) ON O.OrderNo=D.OrderNo
GROUP BY O.OrderNo,O.Customer,O.OrderDate,O.Status,O.TotalAmount
UNION ALL
 SELECT 
 UserDetailTableID ,
 O.OrderNo ,
NULL AS [|id],
O.Customer,
O.OrderDate,
'' AS Status,
'' AS TotalAmount,
D.Product,
FORMAT(D.Quantity,  'N2', 'tr-TR') AS Quantity,
FORMAT(D.Discount,  'N2', 'tr-TR') AS Discount,
FORMAT(D.UnitPrice, 'N2', 'tr-TR') AS UnitPrice,
ROUND(D.TotalPrice,2) AS TotalPrice,
D.OrderNo  AS [|parentId]
FROM dbo.XPD_ORDERS O WITH (NOLOCK)
LEFT OUTER JOIN dbo.XPD_ORDERS_DETAIL D WITH (NOLOCK) ON O.OrderNo=D.OrderNo

Things to consider when writing a query;

• In order to open details in the query, alias should be given to ID fields. It should be added as “id” and “parentId”.
• “id” alias should be written in lowercase letters in the same way.
• For the “parentId” alias, the letter I should be capitalized and the others should be written in the same way.
• The number values in the “id” column must be different for each row.

(In this example, “null” value was given for the “id” column in the detail section).

• The number values in the “parentId” column must contain the number values written in “id”. This column is the value that will match. Rows with null “parentId” are listed as parent row. parentId should not be written as empty or 0, it should be written as null.

As a result of the query written above, a screenshot is given below. The detail row can be opened and closed from the arrow sign at the beginning of the main rows. When the arrow is clicked, the “parentId” of the incoming rows and the “id” of the clicked main row are the same.

Let's color the rows. For this, click on the tree list element. Let's write our condition in the Formatting Field field from its properties. In this example, let's make the color of the main rows blue. For this, we have the “id” of our main rows, if the condition is added as “id” greater than 0 in the screenshot below, the main rows will be blue.

According to the formatting field information written above, the tree list element view is shown below.

The tree list element has two actions “When the row is selected” and “When double clicked on row”.

Examples for these two actions are given below.

First, let's start with an example of the action that occurs on a single click on the row.

First, let's add a text box field to the form containing the tree list element. Let's name it as list unit price. Then click on the tree list element and select the action tab from the properties section on the right side and click on the add new action field. Select When the row is selected action.

For the When the row is selected action, select the Update Value action and select the newly added field from the Value Area section. Add $P to the SQL Query field to pull the data in the UnitPrice field in the query in the list.

After the data is filled in the tree list element on the client side, click on the first row. Since the first row has no unit price information, there will be no data in the List Unit Price field on the screen.

When the second row is clicked in the tree list element, the data will come to the List Unit Price field on the screen because the row has a unit price value.

Let's use the When double clicked on row action as the second action example. Let's open the form where we added the order in this action. Let's click on the tree list element on the Studio side and add it from the actions. Select Open Form Linked for this action. Select Module and Form names. Select the tree list name we added as a mapping list.

Let's match the column names in the tree list with the add order form by clicking the wizard in the Mapping section. Save the action by pressing the Save and Exit button.

Double click on one of the main rows in the tree list element on the Client screen. The order form window will open. The order number of the selected row will be added to the order form. The whole form will be updated accordingly.

Click on a row from the sub details of the main row in the tree list element. The order form will open in a new window. The order form screen will be filled with the order number and detail ID of this row.