# 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. {% hint style="info" %} The main reason of using Power Grid instead of Data Grid is that the Power Grid can show aggregates and do pivot mode. {% endhint %} ## Get Started

Power Grid

### Creating a Power Grid

Create a Power Grid

Here are the steps for creating a Power Grid: 1. Open the [Form Editor](/reference/servoy-developer/object-editors/form-editor.md) of the form where you need to place a **Power Grid** 2. Find **Power Grid** in *Grids* section in the components' pallet 3. Drag and drop the **Power Grid** component in the desired place of the form 4. [Set the columns](#setting-the-columns) 5. Edit other [**Power Grid** properties](/reference/servoyextensions/ui-components/grids/power-grid.md#properties) and [**Power Grid column** properties](/reference/servoyextensions/ui-components/grids/power-grid.md#column) {% hint style="danger" %} **IMPORTANT!**\ In order to fill data to the table, [renderData](/reference/servoyextensions/ui-components/grids/power-grid.md#renderdata-dataset-pks) 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. ```javascript function onShow(firstShow, event) { var ds = databaseManager.convertToDataSet(foundset, ['orderid', 'shipcountry', 'orderdate']); elements.powergrid_orders.renderData(ds); } ``` {% endhint %} ### Setting the Data Source By default, when creating a Power Grid, its foundset is considered to be the form's datasource. ### Setting the columns

Adding and editing tab properties

After dragging the component on the form, find the **Power 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`](/reference/servoyextensions/ui-components/grids/power-grid.md#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) 2. Expand the [`columns`](/reference/servoyextensions/ui-components/grids/power-grid.md#columns) property to see the list of columns. They are also shown in the **Power Grid** component (in the form editor) 3. 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](/reference/servoyextensions/ui-components/grids/power-grid.md#column) {% hint style="info" %} **Power Grid column**\ It is necessary to set an unique `id` in the Power Grid **column** properties.\ Example: `orderid` {% endhint %} #### Setting the column data provider A column's data provider is set in the [dataprovider](/reference/servoyextensions/ui-components/grids/power-grid.md#column) property of the Power Grid Column. A column's dataprovider can be one of the foundset's column from the dataset used in [renderData](/reference/servoyextensions/ui-components/grids/power-grid.md#renderdata-dataset-pks) API method of the component. #### Setting Column Header Column Header is set in the [headerTitle](/reference/servoyextensions/ui-components/grids/power-grid.md#column) property of the Column. It can be edited by entering a value in the property field or by entering the [Text Property Editor](/reference/servoy-developer/object-editors/text-property-editor.md). 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](/reference/servoy-developer/solution-explorer/resources/i18n.md#i18n) is also supported. Examples:

Column headerTitle - plain text

Column headerTitle - i18n

## Grid Height in Responsive Form It can be used both in responsive forms and absolute forms. If used in responsive mode, its [responsiveHeight](/reference/servoyextensions/ui-components/grids/power-grid.md#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](/reference/servoyextensions/ui-components/grids/power-grid.md#responsiveheight) property to `0`, let the table grow up to 100% height of parent element (see more on flex-layout [here](/guides/develop/application-design/forms/layout-management/home/flexbox.md#flexbox-layout)). 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](/reference/servoyextensions/ui-components/grids/power-grid.md#enablesorting) property of the Power Grid must be set to `true`, as well as [enableSort](/reference/servoyextensions/ui-components/grids/power-grid.md#column) property of each desired column. {% hint style="warning" %} If [enableSorting](/reference/servoyextensions/ui-components/grids/power-grid.md#enablesorting) property of the Power Grid is set to `false`, then none of the columns will be sortable, even if their [enableSort](/reference/servoyextensions/ui-components/grids/power-grid.md#column) property is set to `true`. {% endhint %} ### Column width and Resizing #### width Column's width is set in the [width](/reference/servoyextensions/ui-components/grids/power-grid.md#column) property of each column. This property is applied when [columnsAutoSizing](/reference/servoyextensions/ui-components/grids/power-grid.md#columnsautosizing) property of the Power Grid must be set to `NONE`. #### enableColumnResize

Enable Columns resizing

In order to allow the user to resize columns, the [enableColumnResize](/reference/servoyextensions/ui-components/grids/power-grid.md#enablecolumnresize) property of the Power Grid must be set to `true`, as well as [enableResize](/reference/servoyextensions/ui-components/grids/power-grid.md#column) property of each desired column. {% hint style="warning" %} If [enableColumnResize](/reference/servoyextensions/ui-components/grids/power-grid.md#enablecolumnresize) property of the Power Grid is set to `false`, then none of the columns will be sortable, even if their [enableResize](/reference/servoyextensions/ui-components/grids/power-grid.md#column) property is set to `true`. {% endhint %} #### 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. {% hint style="warning" %} The `minWidth` property of a column will be taken into consideration if [enableColumnResize](/reference/servoyextensions/ui-components/grids/power-grid.md#enablecolumnresize) property of the Power Grid is set to `true` and the column's [enableResize](/reference/servoyextensions/ui-components/grids/power-grid.md#column) property is set to `true`, as well. {% endhint %} #### maxWidth In order to set the minimum width that a column can have, the value must be specified in [maxWidth](/reference/servoyextensions/ui-components/grids/power-grid.md#column) property of each desired column. {% hint style="warning" %} The [maxWidth](/reference/servoyextensions/ui-components/grids/power-grid.md#column) property of a column will be taken into consideration if [enableColumnResize](/reference/servoyextensions/ui-components/grids/power-grid.md#enablecolumnresize) property of the Power Grid is set to `true` and the column's [enableResize](/reference/servoyextensions/ui-components/grids/power-grid.md#column) property is set to `true`, as well. {% endhint %} #### columnsAutoSizing Auto sizing for columns can be set in [columnsAutoSizing](/reference/servoyextensions/ui-components/grids/power-grid.md#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](/reference/servoyextensions/ui-components/grids/power-grid.md#continuouscolumnsautosizing) property of the Power Grid set to `true`. #### Setting column Format Columns' Format can be set in [Format](/reference/servoyextensions/ui-components/grids/power-grid.md#column) property of each column.\ The [formatType](/reference/servoyextensions/ui-components/grids/power-grid.md#column) column property must be set before defining the column Format.\ Depending on the option selected for [formatType](/reference/servoyextensions/ui-components/grids/power-grid.md#column), the column Format will be set via the format editors for [DATETIME](/reference/servoy-developer/object-editors/date-format-editor.md), [TEXT](/reference/servoy-developer/object-editors/text-format-editor.md), [NUMBER](/reference/servoy-developer/object-editors/number-format-editor.md). ## Editing in the Power Grid

Column `editType`

Editing in the Power Grid is enabled by selecting an option in the [editType](/reference/servoyextensions/ui-components/grids/power-grid.md#column) 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 {% hint style="warning" %} The [readOnly](/reference/servoyextensions/ui-components/grids/power-grid.md#readonly) property of the Power Grid must be set to `false` in order to have editable columns / cells. {% endhint %} ### Advanced #### Custom editors It is possible to use a form as a custom editor, by setting the [editType](#editing-in-the-power-grid) 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](/reference/servoyextensions/ui-components/grids/power-grid.md#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](/reference/servoyextensions/ui-components/grids/power-grid.md#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](/reference/servoyextensions/ui-components/grids/power-grid.md#stopcellediting) API should be used. ## Filtering Data

Column `filterType`

Filtering Columns in the Power Grid is enabled by selecting an option in the [filterType](/reference/servoyextensions/ui-components/grids/power-grid.md#column) 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](/reference/servoyextensions/ui-components/grids/power-grid.md#column) 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](/reference/servoyextensions/ui-components/grids/power-grid.md#column) 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](/reference/servoyextensions/ui-components/grids/power-grid.md#column) 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](/reference/servoyextensions/ui-components/grids/power-grid.md#column) column property set to `true` * [rowGroupIndex](/reference/servoyextensions/ui-components/grids/power-grid.md#column) column property set to `0` * `column B` column: * [enableRowGroup](/reference/servoyextensions/ui-components/grids/power-grid.md#column) column property set to `true` * [rowGroupIndex](/reference/servoyextensions/ui-components/grids/power-grid.md#column) 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:

Power Grid - column setting for pivot mode

1. [enablePivot](/reference/servoyextensions/ui-components/grids/power-grid.md#column) column property must be set to `true` 2. set the aggregation in [aggFunc](/reference/servoyextensions/ui-components/grids/power-grid.md#column) 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 3. the Power Grid must have at least one other column to be grouped by; in order to do that, set [enableRowGroup](/reference/servoyextensions/ui-components/grids/power-grid.md#column) column property to `true` and set that column's [rowGroupIndex](/reference/servoyextensions/ui-components/grids/power-grid.md#column) property to `0`. You can find more information about **pivoting** [here](https://www.ag-grid.com/javascript-data-grid/pivoting/). 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.

Power Grid pivot example

## Scripting a Power Grid ### Main events You can find a list of Power Grid **events** [here](/reference/servoyextensions/ui-components/grids/power-grid.md#events).\ You can find a list of Power Grid **API methods** [here](/reference/servoyextensions/ui-components/grids/power-grid.md#api). #### onCellClick This event is called when the mouse is clicked on a row/cell. Here is an example of how to use the [onCellClick](/reference/servoyextensions/ui-components/grids/power-grid.md#oncellclick) event of Power Grid in the [Scripting Editor](/reference/servoy-developer/object-editors/scripting-editor.md):\ 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. ```javascript /** * @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](/reference/servoyextensions/ui-components/grids/power-grid.md#oncellrightclick) event of Power Grid in the [Scripting Editor](/reference/servoy-developer/object-editors/scripting-editor.md):\ 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. ```javascript /** * @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](/reference/servoy-developer/object-editors/scripting-editor.md) of the main form, using the the [newColumn](/reference/servoyextensions/ui-components/grids/power-grid.md#newcolumn) API: ```javascript /** * @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](/reference/servoy-developer/object-editors/scripting-editor.md) of the main form, using the the [deleteColumn](/reference/servoyextensions/ui-components/grids/power-grid.md#deletecolumn) API: ```javascript /** * @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'](/reference/servoyextensions/ui-components/grids/power-grid.md#rowstyleclassfunc), [groupRowRendererFunc](/reference/servoyextensions/ui-components/grids/power-grid.md#grouprowrendererfunc) table properties and the column's properties [cellRendererFunc](/reference/servoyextensions/ui-components/grids/power-grid.md#column), [cellStyleClassFunc](/reference/servoyextensions/ui-components/grids/power-grid.md#column).\ 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](/reference/servoyextensions/ui-components/grids/power-grid.md#column); a different styleClass is returned depending on the columnData. ```javascript (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"; }) ``` --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://docs.servoy.com/guides/develop/application-design/ui-components/grids/power-grid.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.