1 of 1

Data grid

Introduction

This widget lets you display data from a list (database list / transient list) on to the user interaction screen, in a datagrid (that is, a table). The Datagrid displays a table where each row corresponds to a record in the specific list and each column corresponds to a field in the specific list.

Properties

Data source - This shows the list corresponding to the Datagrid that you want to display.

Filter - By default, the Datagrid shows all the records in the selected list. You could however show a filtered list, by selecting a pre-configured filter.

Illustration for Filters

For example, let's try to build a search filter that would allow to enter a text and then show a filtered list that contains the text that was entered.

Create a session variable of type 'text', that can be used as a search field.
Create a filter under the database list 'Clients' that filters projects with name containing the value in the above session variable.

Drag the text session variable and add a form at the top of the table, and the change the label as desired. Don't forget to set the Behavior: On change property of the form to Refresh screen.

Select the datagrid and then in the properties panel, click on the icon next to the field under 'Filter' and select the datalist filter from the overlay that is shown. Click 'Ok'.
'Save' the interaction template editor and preview.

Filter expression - This is no different from the Filter property, except that you do not select a database filter like you did above, but write the filter expression in the expression editor directly. This could be useful when you are using a database filter using the above Filter property, where the specific filter is used in multiple other places, but you would like to add additional filter criteria that is needed only for the specific Datagrid. Filter expression can also be used without using the Filter property. If both of the properties are used, the expression in the Filter will be AND'ed with the Filter expression.

Enable paging - This is a toggle, which when enabled, will show x rows in one page, where x is the value configured in the Paging properties: Page size property; When disabled, all the rows in the list will be shown in one single page.

Exercise caution in toggling this property off. When the database size is huge, each time the page is refreshed, all the rows will be fetched in one go, which might not be desirable performance-wise. It is always recommended to use paging for user interaction screens.

If you are using a datagrid in a Template Editor that is intended to serve as the body of an email or a document, never enable paging. Unlike a user interaction screen, you cannot interact with a Data grid to page forward/back in a table in an email body or a document.

Paging properties: Page style - Paging properties are applicable only when Enable paging property is On. Page style property determines how the page scroller appears at the bottom of the datagrid. There are three options possible - With lables (default option), Compact, Hidden.

Illustration for Paging properties: Page style

Page style = With labels (default option)
Page style = Compact
Page style = Hidden

Paging properties: Page size - Paging properties are applicable only when Enable paging property is On. Page size property determines how many rows should be shown per page in the Datagrid. The default value is 20 rows.

Paging properties: Page field - Paging properties are applicable only when Enable paging property is On. Page field can be used to set the current page of the Datagrid from a numeric field in the data model.

Illustration of Paging properties: Page field

Consider the search filter that we had added in the Filter property in the Illustration. Searching on 'o' showed a filtered list of all the rows that contain the character 'o' in the client name. In our example, the result set of the search filter was less than one page in length. So, see what happens when you use the search filter from page 2.

Search filter executed from Page 1.
Result set is less than 1 page long. So, all results are shown in the current page, which is page 1.
Search filter executed from Page 2.
Control is still on Page 2, but result set is in Page 1.

The behavior can be improved for this scenario by always showing Page 1 when the search filter is applied.

In the Template Fragment for 'Clients', change the behavior of the Form field so that it invokes a flowchart on change. The flowchart should initialise the a numeric session variable page number to 1.
Then assign the numeric session variable to the Page number property of the Datagrid.
Now, even when the search is performed from Page 2, the search results are always shown Page 1 first.

Behavior: Default sort column - This property allows you to indicate which column should be sorted in the Datagrid, by default.

Note that you can only indicate which column you would like to be used to sort the Datagrid, and not how you want the column to be sorted - ascending or descending. You can indicate that within each column.

Illustration of Behavior: Default sort column

In the Datagrid properties panel, select 'Column 1' as the Default sort column in order to sort the Datagrid based on the client name.
Then, select the first column in the Datagrid. The selected column is shown in amber outline and there is now a properties panel specific to this column. 'Sort by' can be used to configure one or more criteria based on which this column can be sorted. By default, each column is sorted in the ascending order of the colum. This can of course be changed, and more criteria can be added as desired.

Not all datatypes work well with sorting which can be resolved
by using ToString() in the sorting-field expression. 
Select the field and add the ToString() in the expression editor. 

Keep this in mind when you want to sort by rich text field or sort by concept.

Behavior: On row click - This property can be used to indicate what should happen when you click on a row in the Datagrid. When a row is clicked, the specific record is selected from the list. With this property, you can customise what you would like to do with the selected record. There are five options possible:

Refresh screen - A possible application of this could be when the Datagrid shows only a limited set of columns from the list, but clicking a row should show more fields for the selected row, in the right side of the table.
Execute flowchart - A possible application of this could be to when the Datagrid shows only a limited set of columns from the list, but clicking a row should show more fields for the selected row, in an overlay.
Navigate to - can be used to navigate to a new page from the navigation menu.
Open hyperlink - can be used to configure one of the hyperlinks (from the Hyperlinks folder in the Project resources menu in the modeler) to be opened on clicking a row.
Download file
Do nothing (default)

Appearance: Highlight row when - This property can be used to highlight a row based on specific conditions that you can add in an expression.

Appearance: Alternating rows - This property can be used to change the table style to enable/disable rows shown in stripes.

Illustration for Appearance: Alternating rows

Alternating rows - disabled
Alternating rows - enabled

Appearance: Show header - This property can be used to show/hide column headers.

Appearance: Bordered - This property can be used to show/hide borders.

Appearance: Condensed - This property can be used to add/not add padding between rows.

Illustration for Appearance: Condensed

Condensed - Disabled
Condended - Enabled

Appearance: Responsiveness - You can choose from one of the three options on how responsice the datagrid should be.

Stretch - This is the default responsiveness setting. This will try to fill the available space optimally, without showing scrollbars within the Datagrid. This is suitable when there are not too many columns in the Datagrid.
Scrollbar - This option inserts a horizontal scrollbar at the bottom of the table, if columns and content need more space than is available.
Column Break - This option should be used carefully. It tries to break rows and put columns on a next line if space is limited, which could yields unexpected and sub-optimal results.

Appearance: Grid width - This property can be used to set the width of the Datagrid, which is default 100% (using all available space of the parent container in which the Datagrid is placed).

Modifying Datagrids

After adding a Datagrid, you can still do many changes to it like inserting a column, deleting a column, or rearranging column positions.

To insert a column, drag the field from the data model into the datagrid at the desired position. You will see a green vertical line as shown below. You can let go now. The column will be inserted at that position.
To insert a column not from the data model, hover over the top right corder of a column border. The column borders will now turn blue, and you will see a '+' symbol. Clicking on the '+' will insert a blank column after this column. If you want to insert a blank column as the very first column, hover over the top left corder of the column border of the first column and then click on the '+'.
To delete a column, hover over some place close to the column border. The column borders will now turn blue. Click and then the column is selected. The selected column now is shown with amber borders. Press 'Delete' or 'Backspace' key on your keyboard to delete the column. You might have to press the keys twice to completely remove the column from the Datagrid.
To change the position of a column, hover over the column border and click, which turns the column border amber. You will also see three dots at the top centre of the column border. Hovering over that will show a right and a left arrow. Click on the appropriate arrow to move the column to the right or to the left.

Illustration

We had already used Datagrids for showing the list of clients, list of projects and list of employees in many of the earlier pages in our EMS use case. Let us revist the 'Clients' Template Fragement that we had built in the earleir page for 'Nested template'.

This is what we had. Let's delete the Datagrid and add it again, going through the steps more in detail this time.

Let's assume you have a blank canvas (Template Editor).
In the Template Editor, click on the 'Datagrid' icon and drag it to the canvas. This would prompt you to select a list.
For our example, select the 'Clients' list from the tree and click on 'Ok.
Instead of steps 2 and 3, there is another way of adding a Datagrid to the Template Editor. Drag the list from the data model tree into the Template Editor. This will bring up an overlay from which you can choose to create a Data grid.
After steps 2 & 3 or step 4 (depending on how you inserted the Datagrid), you will be prompted to select the columns from the fields in the selected list. It is important to note that the columns appear in the order that you check them. But of course, the columns can be rearranged afterwards. Furthermore, it is always possible to add more columns to the Datagrid, or remove existing columns from the Datagrid, once you have inserted the Datagrid.
For our example, let us have the client name first and then the client id. So, check the fields in that order - client name, followed by client id. Then press 'Ok'.
You now have the Datagrid inserted in the Template Editor. Press 'Save' and see what happens at run-time, while previewing the project.