Displays data in a grid.
Component mode: Editable Grid
The user can edit cells whose column's field information is configured as editable in grid.
Editing is performed on a cell-basis and the procedure is called to save changes before another cell can start editing.
Appearance
Overview
Excel export
The excel export button is in the upper right corner. It is shown by default, but can be hidden by grid settings. It is not shown on small screen devices.
Row links
Row links are clickable icons that are displayed on each row. If there are more rowlinks than the maximum available space (adjustable by a setting), the ...-button is displayed which gives access to the rest of the rowlinks. If a row link is not relevant on every row, you can hide it on a per-row basis.
Checkboxes
You can choose to display a column of checkboxes. These allow for operations on multiple rows at once. In this example, the top button "Set tax status" is used to set the tax status of the cars that the user has checked.
Order
To sort by a column, you click it's heading. To reverse the order you click it again.
Grouping
The user can choose one column to group by. The column will then be removed from the grid, and it's values will instead appear as headings. In this example the "Fuel" column has been choosen. You can specify a default grouping in the grid settings.
Paging
The total number of rows is always shown (384 in this example). If it's too many to display at once, the grid will divide the results into pages. The default page size is 50 rows, but it can be customized. In this example the page size has been set to six rows. Hence the total number of pages is 384/6 = 64.
To navigate, there are clickable arrows for previous/next page, and a textbox where you can type an arbitrary page number (after the word "Page" in the screenshot). You can also use the PgUp/PgDown keys move to previous/next page, and Home/End for the first/last page.
Sums and other aggregates
You can choose to display a sum below any numerical column, which will be automatically computed by the platform. If you need other forms of aggregation (other than simple addition) you can provide the aggregate values manually.
Color and style
You can set the color for specific rows and cells. In this example the cells in the Brand column are given different colors for different brands, while the whole row is grayed out for cars with status = Sold. In addition to color you can set arbitrary css-style attributes for whole rows as well as individual cells. Using these attributes you can make quite remarkable changes to the grids appearance. Artistic restraint is advised.
Tooltip
You can set the tooltip text for individual cells, row links and columns headings. In this example the user gets additional information about the model by hoovering the mouse over the Model cell.
Extra text
You can provide extra text about a row in the grid, that shows up on demand. Existence of extra text is indicated by a down-arrow that appears in it's own column.
When you click the arrow, the text appears inserted below the corresponding row like this:
The extra text can include html formatting if needed.
Column groups
The columns of a grid can be assigned column groups. This can save horizontal space (in this example, the words "Owner" and "Previous owner" don't have to be repeated for each column).
Checkboxes (selecteditems)
You can use the grid setting Show checkboxes to enable checkboxes on a menu item.
The grid menu item can pass the ids of its selected rows, for historical reasons commonly referred to as selecteditems via links from the grid to other menu items. These menu items then access these ids as a comma-separated string.
Row Ids
Checkboxes use the values from the first grid column as row ids. Row ids must be unique unless they are NULL. Any row with NULL in the first column will not show a checkbox.
Warning
The row ids that you access through selecteditems are ID values of rows that were valid when the user loaded the grid. No validation is performed to ensure that the ID values are still valid, that their rows would still be visible if the user reloads the grid.
For example, you have a grid of unsent invoices and a top-link "Send all checked invoices". The user checks a few rows and then clicks the top link. Now, the checked invoices are sent, and their rows will no longer appear in the grid, but the grid still remembers the IDs of the checked rows. If the user checks a few more rows and clicks the top link again then the Send-menuitem will receive both the IDs of the invoices already sent and the new IDs. You can avoid this by using the execute component's special column admin_unselectall to clear all checkboxes.
Component settings
| Possible value | Description |
|---|---|
| xlsx | The document is generated as a Microsoft Excel 2007 (*.xlsx) document. InfoSql and style information will be rendered. |
| text | The document is generated as tab separated text. InfoSql and style information will not be rendered. This mode causes a warning when opened in Microsoft Excel 2007 and later versions. |
SQL
SQL Call: Retrieve grid data (mandatory)
Call made to retrieve the data to display in the grid.
Parameters
Resultset: Grid data
The contents of the first column returned is required to be unique for each row when used together with {selecteditems}.
Columns
<colname> optional string
The content to be shown in a grid column. The formatting of the value is determined by the connected field information. If the the field information has Allow HTML enabled in its Display Settings, the value will be treated as HTML of the Safe HTML level.
In case the field information is of type Hidden, the column will be excluded from the grid. This feature is useful for columns intended solely to provide values for use by links.
Row_ListViewTitle optional string
Specifies a custom row title for the list grid (that is, the mobile grid). If you do not specify at least one of Row_ListViewTitle, Row_ListViewText or Row_ListViewHtml then the first visible column will become the row title.
Row_ListViewText optional string
Specifies the body for a custom row description for the list grid (that is, the mobile grid). Usually used together with Row_ListViewTitle. Mutually exclusive with Row_ListViewHtml.
Row_ListViewHtml optional string
Specifies the body for a custom row description for the list grid (that is, the mobile grid). Can be used together with Row_ListViewTitle or by iteslf. Mutually exclusive with Row_ListViewText.
<colname>_DisplayValue optional any
If present, this column is used when sorting <colname> unless the <colname>_SortOrder column is also present.
<colname>_SortOrder optional any
<colname>_Tooltip optional string
At most one of _Tooltip and _TooltipHtml can be set for the same cell.
<colname>_TooltipHtml optional string
<br> will become a newline, and HTML encoding will be translated ("ä" will become "ä" for example). All other HTML styling will be ignored.
At most one of _Tooltip and _TooltipHtml can be set for the same cell.
<colname>_MenuItemId optional int
<colname>_LinkId optional int
Creates a link with the specified LinkId for the <colname> cell.
<colname>_PassingFields optional string
<colname>_Color optional string
Sets the text color of <colname> to the specified color. See Colors.
<colname>_Style optional string
<colname>_Icon optional string
Use the menu item "Admin>Theme>System icons" to register system icons.
<colname>_IconColor optional string
Color to use for the icon specified in <colname>_Icon. See Colors.
<colname>_IsEditable optional bit
Can be used to prevent editing of a grid cell in a column that would normally be editable. Use the Row_IsEditable column instead if you want to disable all cells on the row.
"Editable" must be set for the the field info, otherwise this column has no effect.
Row_Color optional string
Sets the text color of the entire row. See Colors.
Row_Style optional string
Row_AccessibilityTitle optional string
Text used to identify the row for screen readers. Row_ListViewTitle will be used as a fallback if this is not set.
Row_CheckboxDisabledReason optional string
Can only be set when Row_EnableCheckbox is set to 0.
Row_EnableCheckbox optional bit
It is not allowed to explicitly set this to 0 or 1 if the grid has show checkboxes disabled, even if it has been done dynamically.
Row_ExtraHtml optional string
The HTML starts hidden. The user can toggle the visibility.
The contents of the first column returned is required to be unique and not null for each row when using Row_ExtraHtml.
At most one of Row_ExtraText and Row_ExtraHtml can be set for the same row.
Row_ExtraText optional string
The text starts hidden. The user can toggle the visibility.
The contents of the first column returned is required to be unique and not null for each row when using Row_ExtraText.
At most one of Row_ExtraText and Row_ExtraHtml can be set for the same row.
Row_ExtraTextStartExpanded optional bit
If set to 1 the row's ExtraText/ExtraHtml will start expanded.
Row_IsEditable optional bit
Row_IsEditing optional bit
Row_ShowCheckbox optional bit
It is not allowed to explicitly set this to 1 if the grid has show checkboxes disabled, even if it has been done dynamically.
Resultset: Number of rows (optional)
Columns
<column with ordinal 1> mandatory int
Resultset: Aggregate rows (optional)
Columns
<colname> optional string
<colname>_Tooltip mandatory string
ADMINAggregate mandatory string
ADMINPhraseID optional string
Resultset: Dynamic field information (optional)
NOTE: This must be the third resultset or later. You must return a row count resultset in order to use this feature.
Columns
AdminFieldName mandatory string
AllowHtml optional bit
ButtonJavaScript<xxx> optional string
ButtonJavaScript<xxx>_Label optional string
CellAlignment optional string
The alignment of grid columns and InfoSQL values.
| Possible value | Description |
|---|---|
| center | Only applicable to grid columns. |
| left | |
| right |
ColumnTooltip optional string
DefaultValue optional string
DefaultValueSql optional string
Description optional string
EnabledJavaScript optional string
FieldGroupId optional int
FieldInfoId optional int
FieldLabel optional string
FieldType optional string
| Possible value | Description |
|---|---|
| boolean checkbox | Legacy alias. Use "checkbox" instead. |
| boolean dropdown | |
| chart | |
| checkbox | |
| checkbox tree | |
| colorpicker | |
| date | |
| datetime | |
| dropdown | |
| file | |
| file upload area | |
| heading | |
| heading with checkbox | |
| hidden | |
| html | Legacy alias. Use "html editor" instead. |
| html editor | |
| info text | |
| listbox | |
| multi-autosearch | |
| multi-listbox | |
| multi-picker | |
| multirow | |
| password | |
| picture | |
| radio buttons | |
| radio cards | |
| signature | |
| textarea | |
| textbox | |
| textbox with autosearch | |
| textbox with autosuggest | |
| textbox with dropdown | |
| textbox with popup | |
| time | |
| uneditable text |
Height optional int
IgnoreOnSave optional bit
InfoSqlLayout optional string
| Possible value | Description |
|---|---|
| Default | Inherit layout from menu item. |
| LabelAbove | Full width, label above. |
| LabelLeft | Label to the left. |
| NoLabel | Full width, no label. |
| Standard | Deprecated. Use LabelLeft instead. |
IsEditable optional bit
MandatoryJavaScript optional string
JavaScript that controls the mandatory status of the field, this overwrites nullchoice if set. This is only available to control types for which the mandatory JavaScript field is visible in the user interface.
MaxDate optional date
MaxDate optional date
MinDate optional date
MinDate optional date
MultipleFiles optional bit
NullChoice optional bit
NumberOfDecimals optional int
OnChangeJavaScript optional string
OutputFormat optional string
| Possible value | Description |
|---|---|
| Hyperlink | |
| MailToLink | |
| PhoneLink |
Placeholder optional string
Sql optional string
TempTableName optional sysname
TextDirection optional string
Which direction the script is written in. Not to be confused with the CellAlignment property.
| Possible value | Description |
|---|---|
| default | System default. Not useful unless you are trying to override an already explicit text direction on existing field information. |
| ltr | Left-to-right (for example English) |
| rtl | Right-to-left (for example Arabic) |
ThousandDelimiter optional string
TimePickerFrom optional string
TimePickerTo optional string
Width optional int
The width of the control.
At one point, this was a pixel value. Back when Softadmin used Verdana 10px, and before fields had width-categories. Now, it is just a value that is converted to a width category.
The possible values listed below are just suggestions. For example, both 1 and 30 will be converted to shortest, and both 500 and 9999 to longest.
| Possible value | Description |
|---|---|
| 150 | Medium-long |
| 30 | Shortest |
| 300 | Long |
| 500 | Longest |
| 60 | Short |
| 90 | Medium short |
VisibleJavaScript optional string
Resultset: File name (optional)
Columns
ADMINFilename optional string
SQL Call: Get edited row (mandatory)
The component makes this call just before saving changes to verify that the data in the row that the user is editing has not already been changed by another user.
A temp table #Row will be passed to the procedure, containing a single row with the values of the grid-row currently edited.
Decimal and numeric fields in the temporary table #Row will get their precision from the actual values during insert/update.
Parameters
Resultset: Grid data
The same columns must be returned in the same order as when the grid was originally loaded. You may optionally omit the ColumnName_xxx special columns.
Columns
SQL Call: Save edited cell (mandatory)
The user has edited a cell, and the grid should now either save the changes and return an updated row or reject the changes.
If the grid uses dynamic field information then the same dynamic fields should be returned from this call as from the original.
A temp table #Row will be passed to the procedure, containing a single row with the new values of the grid-row.
Decimal and numeric fields in the temporary table #Row will get their precision from the actual values during insert/update.
Parameters
Resultset: Updated row (optional)
The same columns must be returned in the same order as when the grid was originally loaded.
Columns
Resultset: Other actions (optional)
Columns
ADMIN_ErrorMessage optional string
Displays a user friendly error message to the user.
ADMIN_Dialog optional string
The dialog alias of a predefined dialog to show the user. Must be the first column in the result set table.
Use the menu item "Admin > Dialogs" to register new dialogs or find aliases for existing ones.
ADMIN_Force optional string
ADMIN_Message optional string
ReloadGrid optional bit
SQL Call: Validate parameters
Allows you to validate the SQL parameters before any other SQL is run in the component. This call is only made if the SQL is a stored procedure and Validate parameters is checked.
Parameters
Resultset: Messages (optional)
Columns
ADMIN_Force optional string
ADMIN_Message optional string
InfoSQL
Custom access control and logging
SQL Call: Custom access control and logging
Use this call to restrict which entries a user is allowed to view and edit, and to log which entries a user views.
Access to a menu item is normally controlled through functions and roles alone but some entities need more fine grained control. For example, a user may have access to the View Member menu item for normal members but not for members with a protected identity.
The menu items a user visits are always logged (in ADMINLogMenuItem) but for sensitive data you may need to log exactly what entries are viewed. Do the logging in this call as the common ways of viewing data (grid and InfoSQL) are not allowed to modify the database.
If you bind a scalar function instead of a stored procedure to this call then its name must end with '_GrantAccess'.
Resultset: Access permissions
Columns
GrantAccess mandatory bit
If 0 then an error will be logged as the user should not have been able to reach the menu item with the given parameters in the first place.
Querystring parameters
Examples
Simple editable grid
This editable grid uses the _DisplayValue-column to support its two dropdown columns, StatusId and UserIdAssignedTo.
CREATE OR ALTER PROCEDURE ToDo.Task_EditableGrid
@Action varchar(100) = NULL,
@ColumnName sysname = NULL
AS
BEGIN
DECLARE @TaskId int;
IF @Action = 'SaveCell'
BEGIN
-- Validate
IF (SELECT R.Status FROM #Row R) <= 0
BEGIN
SELECT 'Illegal Status' [ADMIN_Message];
RETURN;
END
-- Update all editable columns instead of switching on @ColumnName parameter.
UPDATE ToDo.Task SET
StatusId = R.StatusId,
UserIdAssignedTo = R.UserIdAssignedTo,
Task = R.Task,
@TaskId = T.TaskId -- Fetch the primary key so we can return the correct row as response.
FROM
ToDo.Task T
JOIN #Row R ON R.TaskId = T.TaskId;
END;
IF @Action = 'GetRow'
BEGIN
SELECT @TaskId = (SELECT TaskId FROM #Row);
END;
SELECT
T.TaskId,
T.Task,
S.StatusId,
S.Status AS StatusId_DisplayValue, -- Edit status as ID but show it as text.
S.SortOrder AS StatusId_SortOrder, -- Sort status based on its internal sortorder instead of by alphabet.
T.UserIdAssignedTo,
U.FirstNameLastName AS UserIdAssignedTo_DisplayValue
FROM
ToDo.Task T
JOIN ToDo.Status S ON S.StatusID = T.StatusId
LEFT JOIN SoftadminApi.[User] U ON U.UserId = T.UserIdAssignedTo
WHERE
(@TaskId IS NULL OR T.TaskId = @TaskId) -- Return all rows for normal call. Return single row for SaveCell and GetRow actions.
END
Best practice
Naming
Name your Editable grid stored procedure with suffix _SearchEdit or _EditableGrid.
When to use editable grid
Editable grid is an extension of grid, not a replacement for multirow controls in a newEdit.
If you want to convert existing multirows to editable grid then you have to rewrite much from scratch.
Editable grid can handle validation with ADMIM_Message and ADMIN_Force, but if you have a lot of dependencies it's probably not suitable to build an editable grid. It's a risk that you end up with duplicated code in both the Editable Grid and the InsertUpdate procedures. Also, you must save the row to the data base after you edit each cell, not all cells on the whole row at the same time. You are free to actually update anything on the save though.
Note that if you want to signal ADMIN_Message, ADMIN_Force or ReloadGrid, then you should only return that table, not the row that was edited. (With force and message, there is nothing to update and nothing new to show, and with ReloadGrid, you will get the new row with the rest of the reloaded data.)