It is a tool used to list and report data or data from tables in the form.
| Appearance in Toolbox | Appearance in FreeForm | Appearance in Responsive |
Compatibility
| Free Form | Responsive Form | Mobile |
|
Features of the list tool
| ||
|
Type: Shows the field type of the added form tool. The value here cannot be changed.
Name:The name to be given to the list is written.
Text Size: The text size of the data to be displayed in the list is determined in this field.
Help Text: On the Client screen, when the mouse hovers over the form tool, a description is displayed is the entered text.
Number of Locked Columns: It is possible to lock (lock) the columns in the list to the left. (Later, during use, the option to show the column list in the list becomes active and the desired columns can be locked or unlocked.) (Columns at the beginning of the list are counted as locked columns even if they are hidden in the list.)
Height/Width:Adjust the height and width of the list widget. (The width field has been removed from Responsive Forms)
From Left / Top:Adjust the distance from left and top of the list widget. (This field has been removed from Responsive Forms)
Image Size: Adjust the height and width of the images to be displayed in the list.
Row Size: The height of the rows is adjusted.
List View on Mobile: It is determined how the list will be displayed on mobile devices. (List or Card can be selected.)
List Header Width on Mobile: The width of the list columns on mobile devices adjustment is made.
SQL Query: The SQL query prepared for the data to be listed is written in this field. You want to hide To specify a field in an SQL query, you need to add a | (vertical dash) sign before the field name. For example, for the “No” field to be hidden, the SQL query should be written as [|No]. In addition, when it is necessary to create a modifiable field in the list, the SQL query should be prefixed with The $ sign must be added. For example, to create the field “No” as a modifiable field, the SQL query. It should be written as [$No].
Xpoda Addon: In the Xpoda Platform, it may be desired to run specially developed codes in desired places. .NET Class Libraries developed for this purpose can be used. After selecting this option, it is necessary to select Library - Class - Method respectively.
Formatting Field: List rows and columns are dynamically formatted. The requested parameters must be sent in the correct order. Note: If there is a space in the field name in the SQL query of the list, when writing this field in the Field section, it must be written with spaces (the field name remains the same). Example: SELECT 20 AS [Unite Price] - It should be written as Unite Price in the Field section. Unite Price|==|0|0|Red
To apply the date format, a date value containing time (clock) information is required. To apply the percent digit format, decimal data is required. Date format examples;
Sub Total: It allows us to show subtotal information according to the desired column under the list. The requested parameters must be sent in the correct order. (Field|operation (count, sum, min, average) I Text) Note: If a space is written in the Operation section, the list will not work. Therefore, adding Operation part should be written contiguously. Budget |sum| Total Budget :
Note: If there is a space in the field name within the SQL query, this field must be written with a space (field name unchanged) when entered in the Sub Total (Field) section. (E.g., SELECT 20 AS [Unit Price] - Must be written as Unit Price in the Field section). Unit Price|sum| Total Unit Price :
Hide Search: Hides the search field of the list. Hide column heading: Hides the column headings in the list. Fit Horizontal: Fits the form tool horizontally to the full screen on the user's screen. It shortens and lengthens to the right as the screen shrinks and grows. Pin Right: Pins the form tool to the right. Display : Makes the form tool not visible on the Client screen. Detail Button: Adds a detail button to the rows in the list. Column Width Auto: When this feature is enabled, column widths are automatically sized according to the text they contain. Auto Extention: Allows the list to expand downwards as data arrives. Paging: Allows paging within the list. This ensures fast loading of multi-line data. Page Size: This field controls how many rows of data will be brought on a page in the list. If there is a value of 0 in the field, it brings 25 rows of data by default. This field should not be left empty. This value can be changed again from the Client screen. For this feature to work, the Paging option must be set to “Yes”. File Menu: It allows downloading and viewing the files in the list. In order to list the data in the XPODA_FILES table, FileID and RecordGuid fields must be present in the query. When connecting to the XPODA_FILES table, you should connect by matching the ProjectID, FormTypeID and TableID fields with the ProjectID, FormTypeID and UserTableID fields in your table. | ||
| If you want to show the path of the pictures saved in the picture field in the list tool with a URL, this can be done by writing $IMG at the beginning of the field name. |
Actions that can be used inside the list widget
There are different actions for each added form element on the form page. To view the actions defined for the list widget, first select the list widget. Then the actions menu on the right panel is opened.
The actions that can be used in the list widget and their details are as follows.
When the row is selected : When the row is selected, an action is added for the operations to be performed.
When double clicked on row: When the row is double clicked on, an action is added for the operations to be performed.
Detail is Opened: Action is added for actions to be taken when the detail is opened.
Add button to row: This is the action for adding a button to the row.
When cell value changes: An action is added for actions to be performed when the value of a cell in the selected row in the list is changed. This action is used to get information about the changes in the cell value. Within the scope of the action, the UserTableID value of the relevant row, the name of the column where the change was made and the new value of the cell can be displayed.
Example 1:
|
List Tool Value Update Process For the list widget to function correctly, an update value must be added. This can be done in the following cases: In order for the values in the list to be displayed as transactions, the update value operation must be selected. In this context, the list must be specified as a value field for the update operation. After the properties of the operation are correctly defined and saved, the relevant list will be displayed when the form is opened. |
- Let's do this by adding a button so that the list is updated when the button is clicked:
Note: You can also write the list query in the sql query field inside the list or add it to the sql query field inside the action that updates the list.
First, let's create a form with free form property. To create a free form, select the Free Form option in the window that opens by clicking on the red framed area in the screenshot below.
When Free Form is selected, a new form screen named Form_1 will be created.
Select the form named Form_1, then in the properties section of the form on the right side of the screen, name the form by typing ListUpdate in the Name field (No:1). Since we will not save to the database, let's select the type of the form as Report (No:3). Since it is a report, we can leave the TableName field blank (No:2). This section creates a table name for the Form type to the database.
Let's drag 1 Button and 1 List tool from the form tools at the bottom left and add them to our form.
Select the button and name the Text field in the properties section as shown in the screenshot below. After completing the naming, press Enter key. If you want to change the list name, you can click on the list and make changes in the Name field in the properties section.
In the SQL Query field from the properties of the list, let's write the query of the data to be reported in the list. In the example below, the query is written in the SQL Query section because it is desired to extract data from the table named XP_PROJECT_TRACKING.
In order to see the data of the list on the client screen, let's trigger the list as follows.
Select the button (1). Click on the Actions section (2).
Click on Add New Action field (3).
Select When clicked from Type of action (4).
Select Update Value in the Operation field (5).
In the Description field let's write briefly what is done in this action (6).
Select the name of the list in the Value area field (7). Save the action (8).
The saved action will look like the screenshot below. If you want to delete the action, click on the trash can symbol on the right side. If you want to make changes in the action, click on the pencil symbol in the middle and if you want to stop the action, click on the pause symbol on the left side.
After completing the necessary adjustments, save the project and then click the Run button to switch to the client screen. If the client screen is open, the process is continued by refreshing the client screen after the registration process is completed on the Studio screen.
| On the client screen, the last saved version of the project is displayed. When you switch to the client screen without saving the project, the last changes cannot be displayed on the screen. |
When the client screen is opened, it will look like below.
When the Update List button is clicked on the client screen, the data in the query will be added to the list.
Example 2:
Select free form as in the previous example. After editing the name of the form and selecting the type as report, drag the list from the form tools at the bottom left to the design screen. Click on the list and add the query to the SQL Query field from the properties. The table name added in the query is a previously created form in XPODA.
In the SQL Query field, click on the red field in the screenshot below. The query creation screen will open as a separate window named Query Result.
In the window that opens, if the footers on the left side are not visible, click on the part that says Xpoda (1).
Click on the arrow icon in the Tables field opened at the bottom and open it.
Then type the table name in the search field at the top of Xpoda. We can find our table without typing anything in the search field. Let's see the fields in the table by clicking on the small arrow icon on the left side of the table.
Drag the UserTableID field to the Query Builder field with columns at the top right (4).
Drag UserTableID, ProjectNo, ProjectName and Budget fields from the left side to the Query Builder area. The query will automatically appear in the SQlQuery 1 field. When the save icon in the upper right corner is clicked, the query will appear in the SQL Query field.
After completing the necessary adjustments, save the project and then click the Run button to switch to the client screen. If the client screen is open, the process is continued by refreshing the client screen after the registration process is completed on the Studio screen.
The image on the client screen will look like below. If the query is added in this way, the UserTableID field will not appear on the screen.
| Let's give an alias to the UserTableID field to show the “UserTableID” field in the list. |
Example 3:
To color the list, let's continue with the query given in the second example. Let's use the formatting property to assign a color to a field according to certain criteria. Here the coloring information is as follows;
Formatting (Field | Operator | Value | Type | Colors | Cell(0-1) | Colored Area(Optional for Cell value=1))
Let's go through the list defined in the previous example and add the necessary coloring rules to the Formatting section under the SQL Query field.
Field values to be used in the Formatting field should be determined according to the column names in the query defined in the SQL Query field or the alias values assigned to these columns, if any. For example, as shown in the screenshot below, if the Budget field is greater than 100000, a coloring rule is defined so that the text color of the ProjectName field is red.
Budget|>|100000|0|RED|1|ProjectName
In the property typed here, the information after each dash (|) represents the different parameters of the corresponding formatting rule respectively;
Budget 🡪 Field in query
> 🡪 Magnitude sign (Comparison sign)
100000 🡪 Value to Compare
0 🡪 0 for text text and 1 for background color
RED 🡪 Color information can also be written as HEX, rgb, rgba codes for example; #ff0000, rgb(255, 0, 0),
rgba(255, 0, 0, 0,1)
1 🡪 Enter 0 for the current column and 1 for other columns (This field is optional. If no value is entered, coloring is applied on the whole row by default).
ProjectName 🡪 Name of the column to which coloring will be applied (This field is optional. If no value is entered, coloring is applied on the whole row by default.
The Client screen looks as follows;
|
Column names with spaces in their list headers must be written with spaces (the field name remains the same) when performing the colorization process. Example: Select [New Color 1] => New Color 1|==|1|1|green |
Example 4:
Let's make an example to get the information of the selected data and column in the list.
Let's proceed from the previous example form.
Let's add 1 Text Box element on the list.
Let's change the name of the text box element to SelectedColumnHeader.
On the Client screen, when the user clicks on any field on the list, let's use the actions of the list to print the name of the clicked column in the ‘Selected Column Header’ field newly added to the form.
Click on the list (1). Select the actions section of the list (2). Click on the Add New Action field (3).
When the row is selected action in the Type of action section,
Update Value from Operation,
Select the SelectColumnHeader field we added in the Value Area section.
In the Description field, type SelectColumnHeader for the name of the action that will appear after it is saved.
To update the value of the field we just added, type SELECT ‘$PXpodaGridActiveColName$’ in the SQL Query field. (4)
Save the action (5).
Translated with DeepL.com (free version)
After completing the necessary adjustments, save the project and then switch to the client screen by clicking the Run button. If the client screen is open, the process is continued by refreshing the client screen after the registration process is completed on the Studio screen.
On the client screen, when any cell in a row on the list is clicked, the column name of the clicked cell is transferred to the Selected Column Header field. In the example below, when 'Xpoda February Project data is clicked, the name of the column to which this cell belongs, ProjectName, is displayed in the Selected Column Header field.
When a cell with a value of 1 is clicked on the list, ID is written in the Selected Column Header field, since the name of the related column is ID.
- As a different use, instead of getting the column header of the selected cell, the value information in the cell can also be retrieved.
For this example, let's change the example we just did a little bit.
Let's remove the quotes and the $ sign at the end of the ‘$PXpodaGridActiveColName$’ value we just queried with SELECT and print the value in the rows and columns we selected in a new Textbox tool.
To do this, let's first add a new Text Box element above the list.
Let's change the name of the Text box element to SelectedValue.
Click on the Label field and change the Text to Selected Value.
Click on the list and go to the actions section (1). Click on Add New Action (2).
When the row is selected action in the Type of action section,
Update Value from Operation,
Select the SelectedValue field we added in the Value Area section.
In the Description section, let's write SelectedValue for the name that will appear after the action is saved.
SQL query field to update the value of the field we just added
SELECT $PXpodaGridActiveColName
FROM XP_PROJECT_TRACKING WITH(NOLOCK) WHERE UserTableID='$PUserTableID$'
(3)
Save the action (4).
The $PUserTableID$ statement in the SQL Query field in the action corresponds to the UserTableID field defined in the SQL query of the list. Therefore, the SQL query for the list must include the UserTableID field.
After completing the necessary adjustments, save the project and then click the Run button to switch to the client screen. If the client screen is open, the process is continued by refreshing the client screen after the registration process is completed on the Studio screen.
On the Client screen, when a cell on the list is clicked, the data in the selected cell is transferred to the Selected Value field. For example, when the user clicks on the Xpoda January Project data, this value is reflected in the Selected Value field.
| NOTE: In this example, the names of the fields in your list query are not given ALIAS, field names must be used exactly as they are. |
Example 5:
| There are three different methods to add a button to a list. One of these methods is to add a button using template. The example use of the button added with template is given below under Example Use 8. In this section, the other two methods are explained. |
- If we want a button used on the form to appear in the list or if there is a button that we want to be included directly in the list, we must first add the button to the form. Then, we should go to the ‘Properties’ tab of the button and in the Linked List field, we should select on which list the button will be displayed. As a result of this process, the button will be displayed on each line in the selected list.
- To add a button to the rows in our list, let's add the Add button to row action from the actions section of the list. In the Operation section we can select the operation we want to perform. With this action, a button is added to each row in the list. When the button is clicked, the action specified in the Operation field is performed.
| When using this action, the Description field is used to determine how the button will appear on the Client screen. The text you want to appear on the button should be written in this field. When this field is left empty, there is no healthy appearance on the client screen. |
- An example demonstration where we add Delete Action
| Sample video on the subject : Using Button in List | |
Example 6:
If we want to display a column in the list in a specific width, ColumnName as ‘ColumnName[W#=200#]’ format should be used. With this expression, the column will be expanded by the specified pixel value (200px in the example). An example use of this structure is shared below.
First, let's add two list form widgets to our form and write the following queries for each list.
On the client screen, to make the list query run automatically when the form is opened, first select the relevant form on the left side. Then, go to the actions section on the right and click on Add New Action.
When the form is opened > Update Value > GridList_1 action and save it.
Then click on Add New Action again.
When the form is opened > Update Value > GridList_2 action and save it.
Finally, when we save the form and click the Run button, the lists will look like below on the client screen.
No edit has been added in the ProjectName column Column scaling format has been added in ProjectName column.
Example 7 :
On the client screen, the editable (edit) property of the selected cell in the list.
In our example, let's drag and drop 1 Text Box and 2 Figures Box form tools to our form. Let's name these tools accordingly: Text Box field as ColumnHeader, first Figures Box field as ID and the other one as ChangeValue.
Next, select our list tool and type the following query in the SQL Query field. After writing the query, the screenshot of our list will be as follows.
SELECT
XP_PROJECT_TRACKING.UserTableID,
XP_PROJECT_TRACKING.ProjectNo,
XP_PROJECT_TRACKING.ProjectName,
XP_PROJECT_TRACKING.Budget AS [$Budget],
XP_PROJECT_TRACKING.ProgressPercentage AS [$ProgressPercentege]
FROM dbo.XP_PROJECT_TRACKING WITH (NOLOCK)
When the Client screen opens, a $ sign must be added to the beginning of the alias used to select the cell to modify the data in the list.
In the query we used in our example, this rule was applied to the fields Budget AS [$Budget] and ProgressPercentage AS [$ProgressPercentage].
Before moving to the client screen, let's go to form actions and define an action to load the data given to our list when the form is opened.
For this, select our form, switch to the action tab and click on the Add New Action field.
Let's use When the form is opened / Update Value options.
Then select our list in the Value Area field and save our action.
The screenshot of our action will be as follows.
Finally, when we save the form and click the Run button, the list will appear on the client screen as follows.
When the cells of the fields defined with $ sign in the SQL query of the list are clicked, these cells become active and editable.
Navigation between cells can be performed either by clicking the desired cell with the mouse or by using the right, left, up, and down arrow keys on the keyboard.
When you are in a cell on a lower row and move to the next row using the arrow keys, the list scroll bar will automatically move downward.
In this example, let's use the When cell value changes action from the list actions.
With this action, let's display the UserTableID, Column Name and Cell value of the row selected in the list in the fields we have added to the form.
The UserTableID of the selected cell in the list will be transferred to the ID field in our form, the column name of the selected cell will be transferred to the Column Header field, and the information written in the selected cell will be transferred to the ChangeValue field.
Select the List tool and add an action.
Let's use When cell value changes / Create New Data Object options for our action.
In the Object Name field, name the object to be created. In this example, let's write the information of the object action as CellInformation in the Object Name field.
Let's write the following SQL query in the SQL Query field. (In query writing, you can type the fields in quotation marks manually, or you can select from the list using the : sign. Then the use of alias can also be added optionally).
SELECT
'$PUserTableID$' AS ID,
'$PXpodaGridChangeValue$' AS ChangeValue,
'$PXpodaGridChangeColName$'AS ChangeColumnName The screenshot of our action will look like below. Then, let's save our action by clicking the Save button just below the action.
Let's select ID, ColumnHeader and ChangeValue fields respectively.
Then from the field marked in red in the image above; In the Linked Object field drop-down box, select the object named CellInformation in the Object Name field in the Create New Data Object action.
In the Linked Object Field field drop-down box, let's match by selecting from the field names given in the SQL query of the action.
The mappings in our example are as follows:
Field Name => Object action
ID => ID
ColumnHeader => ChangeColumnName
ChangeValue => ChangeValue
Finally, when we save the form and click the Run button, the list will be displayed on the client screen as follows.
Select the cell that can be modified (the one with the $ sign added to its alias in the list query).
When the cell is modified and the enter key is pressed from the keyboard or the mouse is clicked on an empty place on the screen, the UserTableID (in the ID field), Column Name (in the ColumnHeader field) and Cell Values (in the ChangeValue field) of the modified cell are displayed in the relevant fields.
Example 8 :
List Template Feature
Open a new form with Free Form property. Select the type property of the form as report.
Drag the List tool to the form.
Clicking Add Customization in the top right corner of the List element will open the template canvas. After opening the template area, the template elements will appear in the bottom left corner. (Elements in the screenshot below)
|
|
For each template element in the list, Add Customization operation should be performed separately and a new template should be added each time and the relevant customization should be applied. |
Label Template Element;
Let's write a SQL query to get the data to be displayed in the list in the SQL Query field.
In our example, the query shared below was used.
The template will be applied in the Project Information field in the SQL query.
SELECT
XP_PROJECT_TRACKING.UserTableID,
XP_PROJECT_TRACKING.ProjectNo,
XP_PROJECT_TRACKING.ProjectName,
XP_PROJECT_TRACKING.Budget,
'' AS [Project Information]
FROM dbo.XP_PROJECT_TRACKING WITH (NOLOCK)Let's write a SQL query to get the data to be displayed in the list in the SQL Query field.
In our example, the query shared below was used.
The template will be applied in the Project Information field in the SQL query.
In the list used as an example, the Project Information field in the SQL query is selected for the template application.
In the Template field in the properties section of the Label tool, the field name in the SQL query that you want to display on the label should be written in $Pxxxx$ format.
(If there are spaces in the column name, these spaces must be written in the same way in the field name.)
For example; $PProjectNo$ in Lbl_4, $PProjectName$ in Lbl_5 and $PBudget$ in Lbl_6 will display the related data.
If an alias is used in the SQL query, the alias name should be written in the $Pxxxx$ structure.
(If there are spaces in the column name, these spaces must be written in the same way in the field name.)
Then, select the Template that has been added for Label. (This is done from the area marked in red on the left side of the screenshot above).
In the Column Name field in the Template properties, let's specify the column name that the added template will be applied on the list, based on the SQL query in the list.
If alias is used in the query, the alias name should be written in this field.
(If there are spaces in the column name, these spaces must be written in the same way in the field name.)
In our example, let's write ProjectInformation in the Column Name field highlighted in red on the right side of the screenshot.
(If there are spaces in the column name, these spaces must be written in the same way in the field name.)
When the Client screen is opened, it should be remembered to add an action to automatically trigger the list and retrieve the data saved in the list. This action can be added to the form itself or to a button on the form.
To add an action to the form; select the form and select the Action tab from the properties field on the right. After clicking on the Add New Action field, let's make edits as below and save the action.
Type of action; When the form is opened
Action; Update Value
Value Area; GridListe_X (List to be updated)
To add an action to the button; Select the button and select the Action tab from the properties area on the right. After clicking on the Add New Action field, let's make edits as below and save the action.
Type of action; When clicked
Action; Update Value
Value Area; GridListe_X (List to be updated)
Finally, let's save the form and switch to the client screen by clicking the Run button.
Client ekranında Project Information alanı aşağıdaki ekran görüntüsündeki gibi görünecektir.
Picture Template Element;
Let's write a SQL query to get the data to be displayed in the list in the SQL Query field.
In our example, the query shared below is used.
The template will be applied in the ProjectImage field in the SQL query.
By using UNION ALL in the SQL query, the data added later is displayed as a moving image (GIF).
SELECT
XP_PROJECT_TRACKING.ProjectNo,
XP_PROJECT_TRACKING.ProjectName,
ProjectImage AS ProjectImage
FROM dbo.XP_PROJECT_TRACKING WITH (NOLOCK)
UNION ALL
SELECT
'XP004' AS ProjectNo,
'Xpoda April Project' AS ProjectName,
'https://xxx.xxxxxxx.com/animation/2023/05/16/19/39/19-39-22-287_512.gif' AS ProjectImageClick Add Customization in the top right corner of the List element.
Select the Picture element and drag and drop it to the template canvas.
In the list used as an example, the ProjectImage field in the SQL query is selected for the template application.
In the Template field in the properties section of the Picture tool, the field name in the SQL query that you want to display on the picture should be written in $Pxxxx$ format.
(If there are spaces in the column name, these spaces must be written in the same way in the field name.)
In our example, $PProjectImage$ is written in the template field.
If an alias is used in the SQL query, the alias name must be written in the $Pxxxx$ structure.
(If there are spaces in the column name, these spaces must be written in the same way in the field name.)
Select the added Template for Picture. (This is done from the field marked in red on the left side of the screenshot above).
In the Column Name field in the Template properties, let's specify the column name to which the added template will be applied on the list, based on the SQL query in the list.
In our example, this field is ProjectImage. (This is done from the field marked in red on the right side of the screenshot above).
If alias is used in the query, the alias name should be written in this field.
(If there are spaces in the column name, these spaces must be written in the same way in the field name.)
To add an action to the form; select the form and select the Action tab from the properties area on the right. After clicking on the Add New Action field, let's make edits as below and save the action.
Type of action; When the form is opened
Action; Update Value
Value Area; GridListe_X (List to be updated)
Finally, let's save the form and switch to the client screen by clicking the Run button.
On the client screen, the ProjectImage field will look like the screenshot below.
Since the 3rd row data does not have ProjectImage information, it is displayed as below by default.
In the 4th row, we can see that the data is an animated image on the client screen.
Progress Bar Template Element;
Let's write a SQL query to get the data to be displayed in the list in the SQL Query field.
In our example, the query shared below is used.
In the ProgressPercentage field in the SQL query, template will be applied.
SELECT
XP_PROJECT_TRACKING.UserTableID,
XP_PROJECT_TRACKING.ProjectNo,
XP_PROJECT_TRACKING.ProjectName,
XP_PROJECT_TRACKING.Budget,
XP_PROJECT_TRACKING.ProgressPercentage
FROM dbo.XP_PROJECT_TRACKING WITH (NOLOCK)Open the template editing area by clicking Add Customization in the top right corner of the List tool.
Then drag and drop the Progress Bar tool onto the template canvas.
In the list used as an example, the ProgressPercentage field in the SQL query is selected for the template application.
In the Template field in the properties section of the Progress Bar tool, the field name in the SQL query that you want to display on the Progress Bar should be written in $Pxxxx format.
(If there are spaces in the column name, these spaces must be written in the same way in the field name.)
In our example, $PProgressPercentage is written in the template field.
If an alias is used in the SQL query, the alias name should be written in the $Pxxxx structure.
(If there are spaces in the column name, these spaces must be written in the same way in the field name.)
Select the added Template for Progress Bar. (This is done from the field marked in red on the left side of the screenshot above).
In the Column Name field in the Template properties, let's specify and write the column name to which the added template will be applied on the list based on the SQL query in the list.
In our example, ProgressPercentage is written in this field. (This is done from the field marked in red on the right side of the screenshot above).
If alias is used in the query, the alias name should be written in this field.
(If there are spaces in the column name, these spaces must be written in the same way in the field name.)
To add an action to the form; select the form and select the Action tab from the properties field on the right. After clicking on the Add New Action field, let's make edits as below and save the action.
Type of action; When the form is opened
Action; Update Value
Value Area; GridListe_X (List to be updated)
Finally, let's save the form and switch to the client screen by clicking the Run button.
Client ekranında ProgressPercentage alanı aşağıdaki ekran görüntüsündeki gibi görünecektir.
Circular Progress Bar Template element;
Let's write a SQL query to get the data to be displayed in the list in the SQL Query field.
In our example, the query shared below is used.
The template will be applied in the Percentage field in the SQL query.
SELECT
XP_PROJECT_TRACKING.UserTableID,
XP_PROJECT_TRACKING.ProjectNo,
XP_PROJECT_TRACKING.ProjectName,
XP_PROJECT_TRACKING.Budget,
XP_PROJECT_TRACKING.ProgressPercentage AS Percentage
FROM dbo.XP_PROJECT_TRACKING WITH (NOLOCK) Open the template editing area by clicking on the Add Customization field in the upper right corner of the List element.
Select the Circular Progress Bar element and drag and drop it to the template canvas.
In the list used as an example, the Percentage field in the SQL query is selected for the template application.
In the Template field in the properties section of the Circular Progress Bar tool, the field name in the SQL query that you want to display on the Circular Progress Bar should be written in $Pxxxx format.
(If there are spaces in the column name, these spaces must be written in the same way in the field name.)
In our example, $PPercentage is written in the template field.
If alias is used in the SQL query, the alias name should be written in the $Pxxxx structure.
(If there are spaces in the column name, these spaces must be written in the same way in the field name.)
Select the added Template for Circular Progress Bar. (This is done from the area marked in red on the left side of the screenshot above).
In the Column Name field in the Template properties, let's specify the column name to which the added template will be applied on the list, based on the SQL query in the list.
In our example, Percentage is written in this field. (This is done from the field marked in red on the right side of the screenshot above).
If alias is used in the query, alias name should be written in this field.
(If there are spaces in the column name, these spaces must be written in the same way in the field name.)
To add an action to the form; select the form and select the Action tab from the properties area on the right. After clicking on the Add New Action field, let's make edits as below and save the action.
Type of action; When the form is opened
Action; Update Value
Value Area; GridListe_X (List to be updated)
Finally, let's save the form and switch to the client screen by clicking the Run button.
On the Client screen, the Percentage field will look like in the screenshot below.
Rating Template element;
Let's write a SQL query to get the data to be displayed in the list in the SQL Query field.
In our example, the query shared below was used.
The template will be applied in the RatingScore field in the SQL query.
SELECT
XP_PROJECT_TRACKING.UserTableID,
XP_PROJECT_TRACKING.ProjectNo,
XP_PROJECT_TRACKING.ProjectName,
XP_PROJECT_TRACKING.Budget,
XP_PROJECT_TRACKING.RatingScore
FROM dbo.XP_PROJECT_TRACKING WITH (NOLOCK)Open the template editing area by clicking on the Add Customization field in the upper right corner of the List element.
Select the Rating element and drag and drop it to the template canvas.
In the list used as an example, the RatingScore field in the SQL query is selected for the template application.
In the Template field in the properties section of the Rating tool, the field name in the SQL query that you want to display on the Rating should be written in $Pxxxx format.
(If there are spaces in the column name, these spaces must be written in the same way in the field name.)
In our example, $PRatingScore is written in the template field.
If an alias is used in the SQL query, the alias name should be written in the $Pxxxx structure.
(If there are spaces in the column name, these spaces must be written in the same way in the field name.)
Select the Template added for Rating. (This is done from the field marked in red on the left side of the screenshot above).
In the Column Name field in the Template properties, let's specify the column name that the added template will be applied on the list, based on the SQL query in the list.
In our example, RatingScore is written in this field. (This is done from the field marked in red on the right side of the screenshot above).
If alias is used in the query, alias name should be written in this field.
(If there are spaces in the column name, these spaces must be written in the same way in the field name.)
To add an action to the form; select the form and select the Action tab from the properties area on the right. After clicking on the Add New Action field, let's make edits as below and save the action.
Type of action; When the form is opened
Action; Update Value
Value Area; GridListe_X (List to be updated)
Finally, let's save the form and switch to the client screen by clicking the Run button.
On the client screen, the RatingScore field will look like in the screenshot below.
Creating a Template with JavaScript Code;
You can also create a template in the list using JavaScript code. In this example, with the JavaScript code written in the Quality Score field, if the given value is greater than QualityScore, it is requested to display the result ‘Good’ and if it is less than QualityScore, it is requested to display the result ‘Bad’ in the list.
Let's write a SQL query to get the data to be displayed in the list in the SQL Query field.
In our example, the query shared below was used.
The template will be applied in the QualityScore field in the SQL query.
SELECT
XP_PROJECT_TRACKING.UserTableID,
XP_PROJECT_TRACKING.ProjectNo,
XP_PROJECT_TRACKING.ProjectName,
XP_PROJECT_TRACKING.Budget,
XP_PROJECT_TRACKING.RatingScore AS QualityScore
FROM dbo.XP_PROJECT_TRACKING WITH (NOLOCK)Open the template editing area by clicking on the Add Customization field in the upper right corner of the List element.
The template canvas is left blank. (This is the area marked in red on the left side of the screenshot above. It is scaled in the example shared above).
Let's select the added Template for JavaScript.
In the Column Name field in the Template properties, let's specify the column name that the added template will be applied on the list, based on the SQL query in the list.
In our example, QualityScore is written in this field. (This is done from the field marked in red on the right side of the screenshot above).
If alias is used in the query, the alias name should be written in this field.
(If there are spaces in the column name, these spaces must be written in the same way in the field name.)
Let's write the following Javascript code in the template field:
#if(QualityScore <2 )
{#
<span style=" padding: 10px 20px; display: inline-block; font-size: 14px; background: rgb(255 0 0 / 77%); color: rgb(255 255 255); border-radius: 20px;"> Bad </span>
#}else{#
<span style=" padding: 10px 20px; display: inline-block; font-size: 14px; background: rgb(55 116 0 / 77%); color: rgb(255 255 255); border-radius: 20px;"> Good </span>
# }#
If the Quality Score value is below 2, it will be shown as ‘Bad’ and if it is above 2, it will be shown as ‘Good’ in the Quality Score column.
To add an action to the form; select the form and select the Action tab from the properties area on the right. After clicking on the Add New Action field, let's make edits as below and save the action.
Type of action; When the form is opened
Action; Update Value
Value Area; GridListe_X (List to be updated)
Finally, let's save the form and switch to the client screen by clicking the Run button.
On the Client screen, the QualityScore field will look like in the screenshot below.
Button Template Element;
Let's write a SQL query to get the data to be displayed in the list in the SQL Query field.
In our example, the query shared below is used.
The template will be applied in the Information field in the SQL query.
SELECT
XP_PROJECT_TRACKING.UserTableID,
XP_PROJECT_TRACKING.ProjectNo,
XP_PROJECT_TRACKING.ProjectName,
XP_PROJECT_TRACKING.Budget,
'' AS [Information]
FROM dbo.XP_PROJECT_TRACKING WITH (NOLOCK)
Open the template editing area by clicking on the Add Customization field in the upper right corner of the List element.
Select the button element and drag and drop it to the template canvas.
In the list used as an example, the Information field in the SQL query is selected for the template application.
In the Template field in the properties section of the Button tool, the field name in the SQL query that you want to display on the Button should be written in $Pxxxx format.
(If there are spaces in the column name, these spaces must be written in the same way in the field name.)
In our example, $PInformation is written in the template field.
If an alias is used in the SQL query, the alias name must be written in the $Pxxxx structure.
(If there are spaces in the column name, these spaces must be written in the same way in the field name.)
Message is written in the Text field in the properties section of the button tool.
The desired action can be added to the button.
Select the Template added for the button. (This is done from the field marked in red on the left side of the screenshot above).
In the Column Name field in the Template properties, let's determine and write the column name to which the added template will be applied on the list, based on the SQL query in the list.
In our example, Information is written in this field. (This is done from the field marked in red on the right side of the screenshot above).
If alias is used in the query, the alias name should be written in this field.
(If there are spaces in the column name, these spaces must be written in the same way in the field name.)
To add an action to the form; select the form and select the Action tab from the properties field on the right. After clicking on the Add New Action field, let's make edits as below and save the action.
Type of action; When the form is opened
Action; Update Value
Value Area; GridListe_X (List to be updated)
Finally, let's save the form and switch to the client screen by clicking the Run button.
Client ekranında Information alanı aşağıdaki ekran görüntüsündeki gibi görünecektir.
Example 9 :
Fill the entire form with data from a list
In a form whose Type property is selected as Form, there are fields shown in the picture below. The data entered on this screen is saved to the XP_PROJECT_TRACKING table when the registration process is done. In order to display the saved data, a list tool has been added to the form. When a record is selected from the list, let's transfer the data of the selected record to the relevant fields of the form.
In the list query, the unique field named UserTableID in the table must be present.
SELECT
XP_PROJECT_TRACKING.UserTableID,
XP_PROJECT_TRACKING.ProjectNo,
XP_PROJECT_TRACKING.ProjectName,
XP_PROJECT_TRACKING.Budget,
XP_PROJECT_TRACKING.ProgressPercentage
FROM dbo.XP_PROJECT_TRACKING WITH (NOLOCK)Select the list tool.
Then click on the Add New Action field in the actions section.
In the Type of action field, select When the row is selected - In the Operation field, select Update Value - In the Value Area field, select All Forms.
In the SQL Query field, let's write the appropriate query to retrieve the data of the table. In the Where condition, let's write a condition according to the UserTableID value in the list. Save the action by clicking the save icon at the bottom right corner of the action.
To add an action to the form; select the form and select the Action tab from the properties area on the right. After clicking on the Add New Action field, let's make edits as below and save the action.
Type of action; When the form is opened
Action; Update Value
Value Area; GridListe_X (List to be updated)
Finally, let's save the form and switch to the client screen by clicking the Run button.
On the client side, a view like the one below will appear.
When we click on row 2 from the list, we can see that all the data is transferred to the relevant fields of the form.
Example 10:
Single and Multiple Selection from List
When a row is selected from the list, let's first add two text box elements to the form to transfer the data from that row to the relevant field in the form. Let's do the naming of the text box tools as in the image shared below.
If you want to make multiple selections from the list, the information in which column will be transferred to the form when the selection is made should be determined and the alias of that column should be defined as UserTableID in the query.
If the UserTableID column of the relevant table has already been added to the query, there is no need to write alias in this field again.
List query;
SELECT
XP_PROJECT_TRACKING.ProjectNo AS UserTableID,
XP_PROJECT_TRACKING.ProjectName AS Name,
XP_PROJECT_TRACKING.Budget,
XP_PROJECT_TRACKING.ProgressPercentage
FROM dbo.XP_PROJECT_TRACKING WITH (NOLOCK)Select the list tool.
Then click on the Add New Action field in the actions section.
In the Type of action field, When the row is selected - Operation field, Update Value - Value Area field, select the options with the name MultipleSelection from the text box tools we have added.
In the SQL Query field, let's write the query that is the combination of the list name and the UserTableID column as follows. UserTableID has a capital s (S) at the end.
In the query written in the action, ‘$P’ must be added as follows.
Let's save the action by clicking the save icon in the lower right corner of the action.
SELECT '$PGridListe_2_UserTableIDS'
GridListe_2 is the name of the list we used in the example we made.
To find out the name of the list used, after selecting the list tool, look at the Name field in the special section and write the list name that appears in the Name field to the query.
If you want to make a single line selection from the list, when the selection is made, the information in which column will be transferred to the form should be determined and ‘$P’ should be added to the name of that column in the action. If alias is used, ‘$P’ should be added to the beginning of the alias.
(If there are spaces in the column name, these spaces must be written in the same way in the field name.)
Select the list tool.
Then click on the Add New Action field in the actions section.
In the Type of action field, When the row is selected - Operation field, Update Value - Value Area field, select the options named SingleSelection from the text box tools we have added.
In the SQL Query field, let's write the following query to transfer the Name field in the list query to the form.
Let's save the action by clicking on the save icon in the bottom right corner of the action.
SELECT '$PName$'
To add an action to the form; select the form and select the Action tab from the properties area on the right. After clicking on the Add New Action field, let's make edits as below and save the action.
Type of action; When the form is opened
Action; Update Value
Value Area; GridListe_X (List to be updated)
Finally, let's save the form and switch to the client screen by clicking the Run button.
To make multiple selections in the list on the Client screen, you can hold down the CTRL key and click on the rows you want to select with the mouse.
Another multiple selection method is to select a row from the list and then select a lower row in the list by holding down the SHIFT key. This will make all the rows in between selected.
The data in the row selected from the list will be transferred to the text box tools added to the form as shown in the picture. When multiple selections are made, the data of the fields will be sorted side by side separated by commas.
Example 11:
Add a drop-down box to a list column
To add a pop-up box to a list column in XPODA, first of all, in the list query, in addition to the name of
the column to which the drop-down box will be made, it is necessary to return the same data with an
alias with _Text extension; e.g. Detail AS [Detail_Text] for the Detail column. Then a Template should
be created in the list, located on the right side of the Template screen;
1. The Data Edit Type field should be selected as "Combo Box",
2. In the Column Name field, the name of the column to be made a drop-down box in the list
should be written,
3. In the SQL Query section, the query that returns the data to be listed in the drop-down box
must be entered (this query must return at least one value –ID– and a text –Text– column),
4. The editable property must be set to "Yes", otherwise the drop-down box will be inactive.
When these steps are completed, when the project is run, the corresponding column appears
as a combobox, when the user makes a selection, the value field (Detail) in the list row is
updated with the ID in the combobox and shown via the displayed text (Detail_Text); Thus,
the drop-down box function on the list will work without any problems. The following is an
example of using a drop-down box in the list.
Example:
A form has been created in which project information is entered. To add a drop-down box to the List
column, let's first write our List query.
List query:
SELECT
Projects.UserTableID,
Projects.ProjectName,
Projects.StartDate,
Projects.EndDate,
Projects.CompletionRate,
Projects.ParentID,
Projects.Detail,
Projects.Detail AS [Detail_Text]
FROM
dbo.Projects WITH (NOLOCK)After writing our list query, let's add a Template to the list.
Let's fill in the fields that need to be filled in the template.
Let's save the project and control it from the Client screen.
Example 12:
Retrieving Column Name and Data Information from the List
This example explains how to configure the necessary form elements and actions to allow users to display the column name and data information for the row they select from a list on the screen.
Let's add form elements to our form screen that will allow us to display some information in separate fields in addition to the list. These will be configured as follows: a textbox (Column Name) to display the column name, and two figure boxes (List Row and List Quantity) to display the row number and stock quantity.
When the user selects a row from the list, let's define actions in the list that will automatically populate these fields with the column name, row number, and relevant data information for the selected cell.
Let's configure the actions as follows:
1. Use the SELECT ‘$PXpodaGridActiveColName$’ expression to get the column name. This allows us to retrieve the column name of the clicked cell and write it to the Column Name field.
2. Use the SELECT ‘$PRow Number$’ statement to get the row number. This allows us to transfer the row number of the selected row to the List Row field.
3. Use the SELECT ‘$PTotal Stock Quantity$’ statement to get the stock quantity. This allows us to print the stock information for the relevant row to the List Quantity field.
When the client screen is launched, for example, when the user clicks on the Organic Bananas row:
• Let's enable the automatic printing of the following information:
• “Product Name” in the Column Name field,
• “1” in the List Row field,
• “120” in the List Quantity field.
This structure allows the user to clearly see which cell they clicked on and the information associated with that cell.
Note: Field names retrieved from queries using the $P expression in the List tool were written consecutively prior to version 4.1.3. However, starting with version 4.1.3, they must be written exactly as defined in the SQL query alias.
For example:
Before 4.1.3 : $PProjectName
After 4.1.3 : $PProject Name