Data Grid

Overview

Data Grid is a table providing advanced functionalities such as row grouping for the given foundset.

To see a live sample of the component you can go here.

This table component is designed to work with large data, potentially infinite, since data is loaded lazily into the table even when grouped; group nodes are loaded lazily with incremental scrolling and children of groups are loaded only when the nodes are expanded; also children of groups are loaded incrementally with scrolling.

IMPORTANT ! Grouping is not yet supported for foundset having multiple primary key columns. To use grouping feature of this component the foundset MUST have a single primary key.

Since all data is loaded lazily on demand, the table doesn't provide any built in aggregation functionality such as count, sum, max, min etc.

Get Started

Data Grid

Creating a Data Grid

Create a Data Grid

Here are the steps for creating a Data Grid:

1. Open the Form Editor of the form where you need to place a Data Grid

2. Find Data Grid in Grids section in the components' pallet

3. Drag and drop the Data Grid component in the desired place of the form

4. Set the columns; this can be done via wizard or in the properties panel

5. Edit other Data Grid properties and Data Grid column properties

Setting the Data Source

The Data Source of a Data Grid is set in the myFoundset property of the component.

Form Foundset

By default, when creating a Data Grid, its foundset is considered to be the form's datasource. In this case, a column's dataprovider can be chosen from the foundset table (table column, calculations, aggregates), form variables, scope variables, as well as tables related to the foundset's one.

Related or other foundset

setting Data Grid foundset

Data Grid's foundset can be changed to a Related Foundset, Separate foundset or Named foundsets. In case of a Related Foundset a column's dataprovider can be chosen from the related table (table column, calculations, aggregates), form variables, scope variables.

Setting the columns (in the properties panel)

Adding and editing tab properties

After dragging the component on the form, the Property configurator for columns wizard appears. Close the editor without setting anything here, find the Data Grid in the form editor, click it and proceed with the following steps:

1. Add a column. There are 2 ways of adding a column:

   1. Select the columns property and click the + button in order to add a column. Next columns can be added the same way or by clicking the + button (insert a new array item below) of another column. You can change the columns' order by dragging them into the desired placed inside the Data Grid (in the form editor).
   2. Drag and drop column component (of an Data Grid in Grids section in the components' pallet) into the Data Grid component (in the form editor)

2. Expand the columns property to see the list of columns. They are also shown in the Data Grid component (in the form editor)
3. In order to edit each column, expand it or click the column name in the Data Grid component (in the form editor) and set its properties

Data Grid column It is necessary to set an unique id in the Data Grid column properties. Example: orderid.

Setting the column data provider

A column's data provider is set in the dataprovider property of the Data Grid Column.

Column dataprovider with Form foundset, Separate foundset or Named foundsets

In case the Data Grid foundset is set as Form foundset, Separate foundset or Named foundsets, a column's dataprovider can be selected from the foundset table (table column, calculations, aggregates), form variables, scope variables, as well as tables related to the foundset's one.

Column dataprovider with Related Foundset

In case of a Related Foundset, a column's dataprovider can be selected only from the related table (table column, calculations, aggregates), form variables, scope variables.

Setting Column Header

Column Header is set in the headerTitle property of the Column. It can be edited by entering a value in the property field or by entering the Text Property Editor. Usually this will be plain text or it can contain data from table columns, aggregations, calculations, relations or from and scopes variables, all of them can be combined, as well. i18n is also supported.

Examples:

Column headerTitle - plain text

Column headerTitle - i18n

Setting the columns via the wizard

Property configurator for columns wizard

After dragging the component on the form, the Property configurator for columns wizard appears. In order to set the columns using the wizard, you need to do the following steps:

1. Find in the left side of the wizard the column you need: it can be part of a related or unrelated datasource, calculations, form and scope variables, aggregates

2. Click on the column name

3. The selected column will appear on the right side of the wizard, showing some of the column properties:

   1. COLUMNS : the column's dataprovider or styleClassDataprovider

   2. DATAPROVIDER : selected if the column id value represents the column's dataprovider

   3. STYLECLASSDATAPROVIDER: selected if the column id value represents the column's styleClass dataprovider

   4. STYLECLASS: the name of the style class that should be applied to this component.

   5. HEADERTITLE: the column's title text (i18n supported)

   6. delete icon : you can remove a tab by clicking the icon

4. Click OK button after all columns have been added

When using the wizard, column id property is automatically set:

• the same as dataprovider - in case the COLUMN was set as DATAPROVIDER at creating stage; example: orderid.

• idX, where X starts from 0 and increments according to the number of columns that are set as STYLECLASSDATAPROVIDER at creating stage; example: id0, id1, id2, etc. Column ids can be manually changed by the user in the column properties panel.

Grid Height in Responsive Form

It can be used both in responsive forms and absolute forms. If used in responsive mode, its responsiveHeight property must be set in Properties View (which is the fixed height it will occupy).

Adding a table in a flex-content layout and setting the table responsiveHeight property to 0, let the table grow up to 100% height of parent element (see more on flex-layout here). Used with other containers than flex-content layout in order to grow the table to 100% height, the parent element must have a known height.

Working with Grid Columns

Click-Sorting by Column

Enable Columns sorting

In order to have sortable columns by clicking on the column's header, the enableSorting property of the Data Grid must be set to true, as well as enableSort property of each desired column.

If enableSorting property of the Data Grid is set to false, then none of the columns will be sortable, even if their enableSort property is set to true.

Reordering Columns

In order to enable moving of columns, the enableColumnMove property of the Data Grid must be set to true.

Column width and Resizing

width

Column's width is set in pixels in the width property of each column. This property is applied when columnsAutoSizing property of the Data Grid must be set to NONE.

enableColumnResize

Enable Columns resizing

In order to allow the user to resize columns, the enableColumnResize property of the Data Grid must be set to true, as well as enableResize property of each desired column.

If enableColumnResize property of the Data Grid is set to false, then none of the columns will be sortable, even if their enableResize property is set to true.

min-width

In order to set the minimum width that a column can have, the value must be specified in min-width property of each desired column.

The min-width property of a column will be taken into consideration if enableColumnResize property of the Data Grid is set to true and the column's enableResize property is set to true, as well.

max-width

In order to set the minimum width that a column can have, the value must be specified in max-width property of each desired column.

The max-width property of a column will be taken into consideration if enableColumnResize property of the Data Grid is set to true and the column's enableResize property is set to true, as well.

columnsAutoSizing

Auto sizing for columns can be set in columnsAutoSizing property of the Data Grid and has the following options:

• SIZE_COLUMNS_TO_FIT: makes the currently visible columns fit the screen

• AUTO_SIZE: the grid will work out the best width to fit the contents of the 'visible' cells in the column

• NONE:` no auto sizing action performed

continuousColumnsAutoSizing

Apply 'columnsAutoSizing' whenever columns width are changed, continuousColumnsAutoSizing property of the Data Grid set to true.

Setting column Format

Columns' Format can be set in Format property of each column. Depending on the type of each dataprovider, this will be done via the format editors for date, text, integer/number.

Example:

Focusing on the country column of the following Data Grid, without having any Format:

`country` column with no Format

If the countries' names need to be all upper case, then we apply a Format to the country column of the Data Grid:

set `country` column Format

Here is how it looks after the Format has been set:

`country` column with applied Format

Editing in the Data Grid

Column `editType`

Editing in the Data Grid is enabled by selecting an option in the editType property of the desired column:

• NONE: disables column editing

• TEXTFIELD: shows a text field where the user can edit the value of the column by typing

• DATEPICKER: allows users to enter a date either through text input, or by choosing a date from the calendar

• COMBOBOX: shows a popup that enables users to choose a value for the input from a collection

• TYPEAHEAD: shows a text field that offers values from a provided value list, filtering the list as the user starts typing

• FORM: shows a form

• CHECKBOX: shows a checkbox which the user can select / unselect

The readOnly property of the Data Grid must be set to false in order to have editable columns / cells.

Example:

• COMBOBOX Select the COMBOBOX option in the editType property of the desired column and set the valuelist property:

`COMBOBOX` Column `editType` and Column `valuelist`

Here is the result:

Editable `Discount` column using `COMBOBOX` option

Advanced

isEditableDataProvider

Use a Servoy calculation as isEditableDataprovider property of the desired column to set edit state conditionally to the table cell.

Example: Let's consider the case when Discount column need to be editable only for values smaller than 0.20. In order to get that, isEditableDataprovider property of Discount column has to be set with the calculation name (increase_discount):

/**
 * @properties={type:-4,typeid:36,uuid:"37BEFEFE-F7BC-4998-8ABE-326AE5391BBF"}
 */
function increase_discount()
{
	if (discount.toFixed(2) < 0.20)
    {return true;}
	else {return false;}
}

If the calculation returns true/false, then its Returned Type needs to be set as MEDIA. If the calculation returns 1/0, then its Returned Type needs to be set as INTEGER.

Custom editors

It is possible to use a form as a custom editor, by setting the editType of a column to FORM and editForm to the desired form. When a cell will enter edit mode, the form will be shown in a popup and the onColumnFormEditStarted handler will be called; this is where you can setup the values displayed in the form. To update the edited cell value with the result of the form editor, the setFormEditorValue api function should be used (ex. if you have a save/ok button in the editor, this is the function that you need to call to set the result of the editing). In order to stop the editing and close the form editor popup, the stopCellEditing api should be used.

Filtering Data

Column `filterType`

Filtering Columns in the Data Grid is enabled by selecting an option in the filterType property of the desired column:

• NONE: disables column filter

• TEXT: displays text related filter options

• NUMBER: displays number related filter options

• DATE: displays date related filter options

• VALUELIST: displays a text field filter option, with the specified valuelist items as suggestions

• RADIO: displays filter options with the specified valuelist items as a single choice

Example:

• TEXT Select the TEXT option in the filterType property of the desired column. Here is the result:

Filter `Ship country` column using `TEXT` option

• RADIO Select the RADIO option in the filterType property of the desired column and set the valuelist property. Here is the result:

Filter `Discount` column using `RADIO` option

Grouping Data

In order to enable grouping data, the enableRowGroup property of the desired column must be set to true.

IMPORTANT ! Grouping is not yet supported for foundset having multiple primary key columns. To use grouping feature of this component the foundset MUST have a single primary key.

IMPORTANT ! Calculations, Aggregations or form variables SHOULD not be used in group mode and enableRowGroup MUST be set to false.

Grouping at runtime by the user

The user can change grouping criteria at runtime for the columns that have enabled grouping data.

Example: Having enabled grouping data in Servoy developer for Ship country, Ship city, Customer ID and Order date columns, here is the behavior at runtime:

Grouping columns at runtime

Advanced: Grouping at design time

The table can be grouped on any foundset's dataprovider or related dataprovider by setting the rowGroupIndex in the column's properties. The grouped columns can be configured at design time and the user can change grouping criteria at runtime if enableRowGroup column property is true.

Example: Let's consider the case when columns need to be grouped first by Ship country and then by Ship city. In order to get that, the following settings need to be done:

• Ship country column:

  • enableRowGroup column property set to true
  • rowGroupIndex column property set to 0
• Ship city column:

  • enableRowGroup column property set to true
  • rowGroupIndex column property set to 1

Here is the result at runtime:

Grouping columns at design time

Scripting a Data Grid

Main events

You can find a list of Data Grid events here. You can find a list of Data Grid API methods here.

Record selection

When the table is showing plain rows without grouped columns it highlights the selected record in foundset; if the selected record in foundset is changed serverside the table will change the selection to the new record however it won't scroll to the selected record but will remain to it's position.

When the table is grouping one ore more columns instead the record selection is disabled. If you would like to use change the selected record in foundset you can use the onCellClickEvent to change record selection on the foundset. Please note that the foundsetIndex param of the onCellClickEvent will always be -1 when there are grouped columns; use the pks of the record object to select the clicked record.

onCellClick

This event is called when the mouse is clicked on a row/cell (foundset and column indexes are given). The foundsetindex is always -1 when there are grouped rows. Here is an example of how to use the onCellClick event of Data Grid in the Scripting Editor: Let's consider a Data Grid showing employees table columns. When clicking on a table cell, the application will show a form containing details of that specific employee record.

/**
 * @param {Number} foundsetindex
 * @param {Number} [columnindex]
 * @param {JSRecord} [record]
 * @param {JSEvent} [event]
 *
 * @private
 *
 * @properties={typeid:24,uuid:"9520356E-2893-41D8-97EA-3EC1BD96635B"}
 */
function onCellClick(foundsetindex, columnindex, record, event) {
    forms.employee_details.controller.loadRecords(record.foundset);
    application.showForm('employee_details');
}

onCellRightClick

This event is called when the right mouse button is clicked on a row/cell (foundset and column indexes are given). The foundsetindex is always -1 when there are grouped rows. Here is an example of how to use the onCellRightClick event of Data Grid in the Scripting Editor: Let's consider a Data Grid showing employees table columns. When right clicking on a table cell, the application will show a pop up form containing an employees card menu of that specific employee record.

/**
 * @param {Number} foundsetindex
 * @param {Number} [columnindex]
 * @param {JSRecord} [record]
 * @param {JSEvent} [event]
 *
 * @private
 *
 * @properties={typeid:24,uuid:"620BB9D4-7585-4B4D-A5E5-4E84BE4FC7E6"}
 */
function onCellRightClick(foundsetindex, columnindex, record, event) {
	var elementX = event.getX() - 210;
	var elementY = event.getY() + 20;
	plugins.window.showFormPopup(null, forms.employeeCardMenu, foundset.getSelectedRecord(), null, 222, 184, elementX, elementY);
}

Add a column

Here is an example of how to programmatically add a column in the Scripting Editor of the main form, using the the newColumn API:

/**
 * @param {Boolean} firstShow form is shown first time after load
 * @param {JSEvent} event the event that triggered the action
 *
 * @properties={typeid:24,uuid:"B6E9D07A-8C72-452A-B282-F394DD6B3D5F"}
 */
function onShow(firstShow, event) {
	var column = elements.datagrid_orders.newColumn('shipaddress');
	column.headerTitle = "Ship address";
}

Remove a column

Here is an example of how to programmatically remove a column in the Scripting Editor of the main form, using the the removeColumn API:

/**
 * @param {Boolean} firstShow form is shown first time after load
 * @param {JSEvent} event the event that triggered the action
 *
 * @properties={typeid:24,uuid:"B6E9D07A-8C72-452A-B282-F394DD6B3D5F"}
 */
function onShow(firstShow, event) {
	var index = elements.datagrid_orders.getColumnIndex("requireddate");
	elements.datagrid_orders.removeColumn(index);
}

Advanced

Data changes and Data broadcast

When the table is showing plain rows without grouped columns it updates spontaneously its rows at any data change which affect the rows visible in the table viewport; as common tables in Servoy the table component receive foundset updates which may be originated from a foundset update within the client itself or from a data broadcast update; it always presents the latest data to the user when the columns are not grouped.

This is not the case when the table is grouping one ore more columns; a data update may affect how the groups are displayed; a new group node or sub-node may be created or even removed after a data update. Adding and removing groups on the fly will result on a bad user experience therefore the table component doesn’t react proactively to data updates.

The table allows the user to ask for latest data with a “Refresh Data” action; the refresh button will show up to the top-right corner of the table when there are grouped columns.

When the table receives any data change (either a record insert, a record delete or a record update for any of the columns in table) which affect the records in it’s viewport, the table will notify the user highlighting the “Refresh Data” action; by default the refresh button is colored as orange to notify the user of a pending data change. You can style in CSS the refresh button when highlighted with the .ag-table-info-notify style Class.

Notice that, for obvious perfomance reasons, the table components won’t listen for data changes on the whole foundset’s table (and related foundset table) but only for the data in table's viewport; the implication of this is that if a table record, which is not in viewport, is being updated (for instance the client receives a data broadcast from another client) the table component won’t be notified even if the update may result in a new group node within the table viewport.

If you would like to notify the table component of ANY data change, you can use the notifyDataChange api of the component. The notifyDataChange api is best use in conjunction with the onDataBroadcast event of Servoy to notify the table of any data change from other clients and with the onAfterRecordInsert, onAfterRecordUpdate, onAfterRecordDelete events to notify the table of any data changes within the client; the client doesn’t receive a data broadcast notification for it’s own changes, therefore if the foundset’s record (or the related foundset’s record if related dataproviders are used in columns), which are not in the viewport, are changed in background you can rely on the onAfterRecordInsert/Update/Delete, or proactively notify the table component in your business logic. Please note that it’s not necessary to notify the table component is the component is not visible; the component will always present the latest data when rendered again.