Functional explanation
The Data Grid shows the rows of one table or view of an external database on a page. The database is a Database Connection (Configuration > Database Connections) of type SQL Server, MySQL, SQLite, PostgreSQL or InfluxDB 3, and the table is picked from the connection, so no SQL is written anywhere. An InfluxDB 3 grid is read only; see the section InfluxDB 3. Sorting, column filters, search and paging run on the database server, which keeps a grid on a large table responsive.
With editing switched on, operators add, change and delete rows directly in the grid or in a dialog. The insert, update and delete statements are generated from the Table, the Key Column and the columns marked Editable; every write is recorded in the Audit Trail and can require an electronic signature in compliance mode.
The Data Grid replaces the Data Table object. Existing Data Table objects are converted automatically when a project is upgraded or imported; see the section Migration from the Data Table object at the end of this article.
InfluxDB 3
A Data Grid on an InfluxDB 3 connection shows one measurement of the database. Table lists the measurements, and the time, the tags and the fields are the columns, with the type taken from InfluxDB: Date Time for the time, Number for numeric fields, Boolean for boolean fields and Text for tags and text fields. Sorting, column filters, search, paging and footer totals run on the InfluxDB server, as on the other databases.
- The grid is read only: InfluxDB 3 SQL has no INSERT, UPDATE or DELETE, so Add, Edit and Delete stay off whatever the Editing settings.
- The time column holds UTC and is shown as stored. A filter on the time column compares with the same UTC value.
- Expression columns are calculated in the browser.
- InfluxDB 3 Core limits the number of Parquet files that one query reads. On a large measurement, use a Fixed Filter on the time column to keep the grid within that limit; a query beyond it shows the message of InfluxDB.
- The Connection list shows the InfluxDB 3 connections; an InfluxDB 2.x connection serves Data Forwarders.
Runtime operations
The following operations are available to operators at runtime. Add, the Edit and Delete buttons and the inline editors only appear when the corresponding editing setting is on and the operator belongs to the Access Right group.
| Operation | Description |
|---|---|
| Sort | Click a column header to sort ascending, again for descending, a third time to remove the sort. Hold Shift to sort on more than one column; the header shows the sort position. |
| Filter | The funnel icon in a column header opens a filter that matches the column type: text (contains, starts with, equals, empty), number (comparison or a range), date time (Today, Yesterday, Last 7 days, Last 30 days or a custom range), boolean (Yes / No) and list (tick the values). Active filters are shown as chips above the grid; click a chip to remove it or Clear all. |
| Search | The search box matches the text columns marked Searchable. |
| Paging | With Paging set to Pages the footer shows the page numbers and a page size selector. With Load More a button at the bottom appends the next rows. With Off every row is loaded at once, up to the Row Limit. |
| Columns | The Columns button opens the column chooser: tick the columns to show and drag them into the order wanted. Drag the border between two headers to resize. The layout is remembered per user in the browser when Remember Layout Per User is on; Reset Columns restores the designer layout. |
| Export | Downloads the rows that match the current filters as a CSV file, up to the Row Limit. |
| Refresh | Reloads the rows. The grid also reloads every Refresh Time seconds; the reload pauses while a row is being edited or a filter is open. |
| Select | Click a row to select it. With Selection set to Multiple, tick the checkboxes or hold Ctrl while clicking. The selected values are written to the Selection Variable so other objects can follow the selection. |
| Add | Adds a row. Inline mode opens a new row at the top of the grid with the column defaults filled in; Dialog mode opens the Add dialog. |
| Edit | The pencil button turns the row into editors (Inline) or opens the Edit dialog (Dialog). Save writes the row; Cancel discards the change. When another user changed the same row in the meantime the save is refused and the grid reloads. |
| Delete | Removes the row, after a confirmation when Confirm Delete is on. |
| Buttons | Buttons defined on a Buttons column run their Actions with the values of that row. A button with a Visible When condition only appears on the rows where the condition is true. |
No rows match the filter.
Object name
Set the object name shown in the Data Grid Decorator.
| Setting | Description | Example | Units |
|---|---|---|---|
| Object Name | Name used to identify the Data Grid object. | OrdersGrid | Text |
No rows match the filter.
Data
Select where the rows come from. Pick a Connection first; the Table list is read from that database, and picking a table detects its columns and primary key.
| Setting | Description | Example | Units |
|---|---|---|---|
| Connection | Database Connection the grid reads from (Configuration > Database Connections). | ProductionSQL | Selection |
| Table | Table or view of the connection. A view is read only unless the database allows updates through it. | dbo.BatchOrders | Selection |
| Key Column | Column that identifies a row. Detected from the primary key; needed for editing and for stable paging. | OrderId | Selection |
| Fixed Filter | Conditions applied to every request, before the operator's own filters. A value may be a literal or a variable reference in braces; the value is bound as a parameter, never pasted into the statement. The variable button next to each value opens the variable list and fills in the selected variable in braces. | Line = {client.SelectedLine} | Column, operator, value |
| Default Sort | Initial sort order, one or more columns with asc or desc. | StartTime desc | Text |
| Refresh Time(s) | Seconds between reloads. 0 disables the periodic reload; use PageRefreshDataGrid in a Page Script to reload on demand. | 5 | Seconds |
| Row Limit | Upper bound on the rows the grid pages through and exports. | 2000 | Rows |
| Name | Name that Page Script functions address the grid by, and the key under which the remembered column layout is stored. It is also shown at the left of the toolbar; the Show Title switch under Features hides it there. | BatchOrders | Text |
No rows match the filter.
Columns
The Columns list defines what the grid shows. Detect From Table fills the list with every column of the table, Add Column adds one by hand, the pencil opens the Column editor and the rows can be dragged into order. An empty list shows every column of the table as detected.
The Column editor holds the settings below. Sections appear according to the Type.
| Setting | Description | Example | Units |
|---|---|---|---|
| Column | Column of the table this grid column shows. Sorting and filtering run on the server against it. | Quantity | Selection |
| Label | Header text. | Quantity | Text |
| Type | Auto (detect) takes the type from the database. Text, Number, Boolean, Date Time and List show and edit a table column. Expression shows a calculated value, Variable a live variable (shown with the Format of the variable when it has one), Image an image URL, Link a hyperlink, Buttons a set of per-row buttons. | Number | Enum |
| Width, Alignment, Priority | Column width in pixels, text alignment (Auto follows the type), and the priority that decides which columns hide first when the object is narrow (Core never hides). | 120, Right, High | px, Enum, Enum |
| Decimals, Units | Number and Expression columns: decimals shown and a unit text after the value. | 0, kg | Number, Text |
| Date Format | Date Time columns: display format with yyyy, MM, dd, HH, mm and ss tokens. | yyyy-MM-dd HH:mm | Text |
| True Label, False Label | Boolean columns: text shown next to the check mark or cross. | Released, Open | Text |
| List Source | List columns: Fixed values gives a comma separated list in List Values; Table reads the choices from another table of the same connection through Source Table, Value Column (what is stored) and Display Column (what is shown). This is how a lookup is shown without a join. | Table: Products, ProductId, Name | Enum |
| Expression | Expression columns: a calculation with {Row.Column} for values of the row and plain variable names for live values. | {Row.Quantity} * {Row.UnitPrice} | Expression |
| Evaluate | Expression columns: Auto runs the expression on the server when it uses only {Row.Column}, numbers, + - * /, ROUND, ABS, COALESCE and CONCAT, so sorting, filters and totals cover every row; otherwise it runs in the browser over the loaded rows. Server and Client force one side; Server is refused for an expression the database cannot run. The editor shows which side applies. | Auto | Enum |
| Default Value | Value of the column in a new row: a literal, a variable in braces, {Now}, {Today} or {User}. The variable button next to the field fills in the selected variable in braces. | {Now} | Text |
| Visible, Sortable, Filterable, Searchable | Behaviour switches of the column. | on | Boolean |
| Editable, Required | Editable columns are written by Add and Edit; Required refuses an empty value. The Key Column is never editable. | on | Boolean |
| Show In Footer Totals | Adds the column to the footer: sum for numbers (average, minimum and maximum on hover), count otherwise, always over every row that matches the filters. | on | Boolean |
| Translate Values | Translates the header label through the page translations. | off | Boolean |
| Cell Colors | Conditions that colour the Text, the Cell, a Badge around the value or the whole Row, with the same syntax as Color States. The last matching condition wins. | {Row.Status} == "Fault" | Condition, target, color |
| Buttons | Buttons columns: one entry per button with an icon, a label, a Visible When condition, its Actions (the standard Actions editor, with {Row.Column} in any action field replaced by the row value) and its own Access control condition and filter. Edit and Delete buttons are added automatically when Allow Update or Allow Delete is on. | Start, {Row.Status} == "Planned" | List |
No rows match the filter.
Features
Switch the operator features on or off.
| Setting | Description | Example | Units |
|---|---|---|---|
| Sorting, Column Filters, Search, Column Chooser, Export, Remember Layout Per User, Footer Totals | Enable the corresponding runtime operation. | on | Boolean |
| Show Title | Shows the Name at the left of the toolbar. Switch it off to hide the title without clearing Name, which the Page Script functions still address the grid by. | on | Boolean |
| Paging | Off, Pages or Load More. | Pages | Enum |
| Page Size | Rows per page; the operator can change it in the footer. | 25 | Rows |
| Group By | Groups the rows by a column. Each group has a collapsible header with the row count and the footer totals of that group. | Line | Selection |
| Selection | None, Single or Multiple. | Single | Enum |
| Selection Variable | Prefix of the variables that receive the selected row: one variable per column, named prefix.Column, plus prefix.Count. Client variables are the usual choice. | client.SelectedOrder | Variable |
No rows match the filter.
Editing
Editing writes to the table through the connection. The statements are generated on the server from the Table, the Key Column and the Editable columns; the built-in EnvisionSCADA databases stay read only. A write needs the primary server, the Access Right group and, in compliance mode with Electronic Signature on, a signature for the action key DataGrid.TableName.Insert, .Update or .Delete. Every write is recorded in the Audit Trail with the key value and the changed columns.
| Setting | Description | Example | Units |
|---|---|---|---|
| Allow Insert, Allow Update, Allow Delete | Enable Add, the Edit button and the Delete button. | on | Boolean |
| Edit Mode | Inline edits the row in place; Dialog opens the Add and Edit dialogs. | Inline | Enum |
| Confirm Delete | Asks for confirmation before a row is deleted. | on | Boolean |
| Electronic Signature | Asks for an electronic signature on every write when compliance mode requires one. | off | Boolean |
| Access Right | User groups allowed to edit, comma separated. Empty allows every user who can open the page. | Operators, Supervisors | Text |
No rows match the filter.
Row Actions
Row Click Actions and Double Click Actions point the Actions section of the decorator at the chosen list; the section heading follows the button, and the field next to each button summarises the actions configured for it. This is the same Actions editor a Button uses, so the same actions are available. Any action field may hold {Row.Column}, which is replaced by the value of the clicked row before the actions run, for example a Navigate To with the order number as replacer or a Set Value that writes the key to a variable.
Individual
Configure the colours of the grid. Background Color and Header Background Color accept a gradient as well as a single colour; see the Gradients section of the Color Library article.
| Setting | Description | Example | Units |
|---|---|---|---|
| Background Color | Background of the grid and of the odd rows. | #FFFFFF | Color |
| Alternate Row Color | Background of every second row. | #F7F7F7 | Color |
| Header Background Color | Background of the column headers. | #EEEEEE | Color |
| Selected Background Color | Background of a selected row. | #FFF3D1 | Color |
| Hover Color | Background of the row under the pointer. | #F1F1F1 | Color |
| Text Color, Header Text Color, Selected Text Color | Text colours of rows, headers and selected rows. | #545454 | Color |
| Gridline Color | Colour of the lines between rows and columns and of the border. | #C0C0C0 | Color |
| Icon Color | Colour of the buttons, sort marks, filter marks and the current page number. | #DAA520 | Color |
No rows match the filter.
Page Script functions
The Name setting is the name these functions take. PageGetSelectedDataGridRows(name) returns the selected rows as objects keyed by column, PageGetSelectedDataGridCells(name) returns the visible cell texts of the first selected row in column order, PageDeselectDataGrid(name) clears the selection, PageRefreshDataGrid(name) reloads the rows, PageSelectDataGridRow(name, keyValue) selects the row with that key, PageSetDataGridFilter(name, column, operator, value, value2) sets a filter (operators: contains, startswith, equals, notequals, gt, gte, lt, lte, between, in, empty, notempty), PageClearDataGridFilters(name) removes every filter and the search text, and PageExportDataGrid(name) downloads the CSV export.
Page Scripts written for the Data Table object are rewritten when the project is upgraded: PageGetSelectedDataTable becomes PageGetSelectedDataGridCells, PageDeselectDataTable becomes PageDeselectDataGrid and PageRefreshDataTable becomes PageRefreshDataGrid. The old names are no longer available, so a script typed by hand has to use the names above. PageFillDataTableDynamic has no replacement: the connection and table belong to the object, and a Fixed Filter with a variable or PageSetDataGridFilter narrows the rows at runtime.
Position / Size and Visibility
Position / Size holds Top, Left, Width, Height and Z-index of the object. Visibility holds the Visibility expression and the Blink, Blink Speed and Blink Offset settings, as on other objects. A width of at least 700 px keeps the toolbar on one line.
Migration from the Data Table object
When a project that contains Data Table objects is upgraded or imported, every Data Table becomes a Data Grid. The connection string of the object becomes a Database Connection named after the SQLite file or the server and database, and a query on one table becomes the Table, the Columns, the Fixed Filter, the Default Sort and the Row Limit of the grid. A query that a Page Script assigned to a client variable in OnPageLoad is resolved from that script. Column types are set to Auto and detected from the table; editing stays off until an engineer switches it on.
A query the migration cannot express, or a query the Page Script narrows at runtime, is kept on the object and reported in the System Log and in the migration report file Database/DataGridMigration.json as Attention. Open the object in the designer, pick the Table or add a Fixed Filter, and the note disappears at the next save. The Tools folder of the source repository contains the DataGridMigrationDryRun tool that prints the same report for an exported project before the upgrade.