Power Grid

Overview

Power Grid is a feature-rich table component that offers advanced functionalities such as row grouping, pivoting, and dynamic row rendering. It allows users to load data from a dataset, customize the appearance of rows and cells, and use custom cell editors.

Unlike Data Grid, which load its data from foundsets, the Power Grid load its data from a dataset. The key difference is between dataset and foundset is that the former is cached in-memory and the latter is loaded dynamically from a backend datasource.

The main reason of using Power Grid instead of Data Grid is that the Power Grid can show aggregates and do pivot mode.

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

Get Started

Creating a Power Grid

Here are the steps for creating a Power Grid:

Open the Form Editor of the form where you need to place a Power Grid
Find Power Grid in Grids section in the components' pallet
Drag and drop the Power Grid component in the desired place of the form
Set the columns
Edit other Power Grid properties and Power Grid column properties

IMPORTANT! In order to fill data to the table, renderData API menthod must be called, with a dataset, that should have columns defined. The name of the columns from the dataset will be used to match the defined columns on the table, based on the column's dataprovider.

function onShow(firstShow, event) {
	var ds = databaseManager.convertToDataSet(foundset, ['orderid', 'shipcountry', 'orderdate']);
	elements.powergrid_orders.renderData(ds);
}

Setting the Data Source

By default, when creating a Power Grid, its foundset is considered to be the form's datasource.

Setting the columns

After dragging the component on the form, find the Power Grid in the form editor, click it and proceed with the following steps:

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 Power Grid (in the form editor).
2. Drag and drop column component (of an Power Grid in Grids section in the components' pallet) into the Power Grid component (in the form editor)
Expand the columns property to see the list of columns. They are also shown in the Power Grid component (in the form editor)
In order to edit each column, expand it or click the column name in the Power Grid component (in the form editor) and set its properties

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

Setting the column data provider

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

A column's dataprovider can be one of the foundset's column from the dataset used in renderData API method of the component.

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:

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

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

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

Column width and Resizing

width

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

enableColumnResize

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

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

minWidth

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

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

maxWidth

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

The maxWidth property of a column will be taken into consideration if enableColumnResize property of the Power 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 Power 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 Power Grid set to true.

Setting column Format

Columns' Format can be set in Format property of each column. The formatType column property must be set before defining the column Format. Depending on the option selected for formatType, the column Format will be set via the format editors for DATETIME, TEXT, NUMBER.

Editing in the Power Grid

Editing in the Power 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 Power Grid must be set to false in order to have editable columns / cells.

Advanced

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

Filtering Columns in the Power 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

Grouping Data

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

Grouping at runtime by the user

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

Advanced: Grouping at design time

The table can be grouped on any column 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 column A and then by column B. In order to get that, the following settings need to be done:

column A column:
- enableRowGroup column property set to true
- rowGroupIndex column property set to 0
column B column:
- enableRowGroup column property set to true
- rowGroupIndex column property set to 1

Pivoting Data

Pivoting lets you convert column values into separate columns.

Pivoting allows you to transform the values of a column into separate columns. For instance, you can pivot on "Country" column to create columns for "Ireland," "United Kingdom," "USA," and so forth.

However, pivoting only becomes meaningful when combined with aggregation. If you pivot a column, you must have at least one active aggregation (value) for the configuration to make sense. For example, when pivoting by country, you must provide a measurement, such as "gold medals per country."

Activating pivot mode is necessary for pivoting to take effect. When the grid is in pivot mode, the following occurs:

only columns with Group, Pivot, or Value activated will be included in the grid.
only aggregated rows will be displayed; the lowest level rowData will not appear.

If pivot mode is deactivated, adding or removing pivot columns will have no impact.

In order to enable pivoting data, the following steps need to be made:

enablePivot column property must be set to true
set the aggregation in aggFunc column property. Select an option from the menu:
- sum: adds up the values of a specific field in a dataset
- min: the smallest value in a dataset
- max: the largest value in a dataset
- count: counts the number of records or occurrences in a dataset
- avg: calculates the mean value of a field in a dataset
- first: the first occurrence of a value in a dataset
- last: the last occurrence of a value in a dataset
the Power Grid must have at least one other column to be grouped by; in order to do that, set enableRowGroup column property to true and set that column's rowGroupIndex property to 0.

You can find more information about pivoting here.

Example: Below is a straightforward pivot example performed on the "Sport" column, utilizing the "Gold," "Silver," and "Bronze" columns as values. The "Date" and "Year" columns, despite being defined as such, are not visible in the grid. This is because they lack any grouping, pivoting, or value association.

Scripting a Power Grid

Main events

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

onCellClick

This event is called when the mouse is clicked on a row/cell. Here is an example of how to use the onCellClick event of Power Grid in the Scripting Editor: Let's consider a Power 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 rowData
 * @param {String} [columnId]
 * @param [cellData]
 * @param {JSEvent} [event]
 *
 * @private
 *
 * @properties={typeid:24,uuid:"9520356E-2893-41D8-97EA-3EC1BD96635B"}
 */
function onCellClick(rowData, columnId, cellData, 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. Here is an example of how to use the onCellRightClick event of Power Grid in the Scripting Editor: Let's consider a Power 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 rowData
 * @param {String} [columnId]
 * @param [cellData]
 * @param {JSEvent} [event]
 *
 * @private
 *
 * @properties={typeid:24,uuid:"620BB9D4-7585-4B4D-A5E5-4E84BE4FC7E6"}
 */
function onCellRightClick(rowData, columnId, cellData, 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.powergrid_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 deleteColumn 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) {
	elements.powergrid_orders.deleteColumn('shipaddress');
}

Dynamic row rendering

Is it possible to dynamically render and style the rows depending on their content using the Power Grid *Func properties such as 'rowStyleClassFunc', groupRowRendererFunc table properties and the column's properties cellRendererFunc, cellStyleClassFunc. A common use case is to color a cell or a whole row differently depending of the row data.

The *Func in Power Grid are functions defined as String and evaluated client-side (in the Browser itself) by the Power Grid allowing to customize rows dinamically.

Example for the column's cellStyleClassFunc; a different styleClass is returned depending on the columnData.

(function cellStyleClassFuncStatusColor(rowIndex, rowData, field, columnData, event) {
   if (!columnData) {
      return "";
   }
   if (columnData) {
      switch (columnData) {
      case "New Order":
         return "label-tag text-info";
         break;
      case "Completed":
         return "label-tag text-success";
         break;
      case "Planned":
         return "label-tag text-info";
         break;
      default:
         break;
      }
   }
   return "label-tag text-info";
})

PreviousData Grid NextTable

Last updated 4 months ago