Displays a dynamic database generated tree
SQL to retrieve root nodes and child nodes to other nodes.
Possible value | Description |
---|---|
0 | Not dragable |
1 | Dragable |
Possible value | Description |
---|---|
0 | Not dropable |
1 | Dropable |
Possible value | Description |
---|---|
0 | Not expandable |
1 | Expandable |
Possible value | Description |
---|---|
0 | Node can't be reordered |
1 | Node can be reordered with drag & drop |
Possible value | Description |
---|---|
0 | Node starts collapsed |
1 | Node starts expanded |
The text color for the node. See Colors.
Possible value | Description |
---|---|
0 | Not dropable |
1 | Dropable |
Allows you to validate the parameters supplied by the user before any other SQL is run in the component. This call is only made if the component has visible parameters, the SQL is a stored procedure, and Validate parameters is checked.
Stored procedure run when a 'drag and drop' event occurs (only if nodes are dragable and dropable).
SQL that can have several resultsets that are displayed at top of component.
Sets the text color of <colname> to the specified color. See Colors.
Color to use for the icon specified in <colname>_Icon. See Colors.
Json to generate a row of meters. See the documentation for the Detailview component for more details.
Possible value | Description |
---|---|
Center | Center aligned. |
Left | Left aligned. |
Right | Right aligned. |
Possible value | Description |
---|---|
boolean checkbox | Legacy alias. Use "checkbox" instead. |
boolean dropdown | |
chart | |
checkbox | |
Checkbox tree | |
colorpicker | |
date | |
datetime | |
dropdown | |
file | |
heading | |
heading with checkbox | |
hidden | |
html | |
info text | |
listbox | |
multi-listbox | |
multi-picker | |
multirow | |
password | |
picture | |
radio buttons | |
textarea | |
textbox | |
textbox with autosearch | |
textbox with autosuggest | |
textbox with dropdown | |
textbox with popup | |
time | |
uneditable text |
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. |
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.
Possible value | Description |
---|---|
Hyperlink | |
MailToLink | |
PhoneLink |
InfoSQL can declare JavaScript used by the menu item.
Example
SELECT
'thirdPartyApi.showMap(street, city, country)' AS JavaScript,
StreetAddress AS street,
CityName AS city,
CountryName AS country
FROM
...
Possible value | Description |
---|---|
Current | This is the current step. |
Done | This step has been completed successfully. |
Failed | Something went wrong in this step. |
Future | This step is later on in the process. |
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'.
Simple treeview procedure used when the database model has a straight forward child-parent structure. In the example drag and drop is supported by all nodes and reorder is not but that can easily be changed by modifying the columns [CanDrag], [CanDrop] and [CanReorder].
CREATE PROCEDURE Example.Node_TreeView
@Id int = null
AS
BEGIN
------------------------------
-- Fetch nodes for the tree --
------------------------------
SELECT
-- Id and display text
N.NodeId AS [Id],
N.NodeName AS [NodeText],
-- Node settings
CONVERT(bit, 1) AS [CanDrag],
CONVERT(bit, 1) AS [CanDrop],
NodeInfo.CanExpand,
CONVERT(bit, 0) AS [CanReorder],
CONVERT(bit, 0) AS [Expanded],
-- Passing fields
N.NodeId,
-- Dependson
CONVERT(bit, NULL) AS [ShowSomeKindOfLink]
FROM
Example.Node N
-- Check if there are child nodes
LEFT JOIN (
SELECT DISTINCT
NC.NodeIdParent
FROM
Example.Node NC
) Parents ON
Parents.NodeIdParent = N.NodeId
CROSS APPLY (
SELECT
CONVERT(
bit,
CASE
-- Only nodes with children are expandable
WHEN Parents.NodeIdParent IS NOT NULL THEN
1
ELSE
0
END
) AS [CanExpand]
) NodeInfo
WHERE
-- When @Id is NULL we only want nodes that have no parent
(
@Id IS NULL AND
N.NodeIdParent IS NULL
)
OR
N.NodeIdParent = @Id
ORDER BY
N.NodeName
-- Defines root-node properties
IF @Id IS null
BEGIN
SELECT
null AS [Id],
'Root' AS [NodeText],
convert(bit, 0) AS [CanDrop]
END
END
Basic treeview procedure when having a tree consisting of different kinds of nodes, say for example some kind of category at the top level and actual nodes at the other levels. Reorder is not supported.
CREATE PROCEDURE Example.Category_TreeView
@Id varchar(100) = null
AS
BEGIN
----------
-- Init --
----------
-- Prefixes used to distinguish the different kind of nodes
DECLARE
@CategoryPrefix varchar(10) = 'C',
@NodePrefix varchar(10) = 'N'
-- Parse relevant information from our @Id parameter
DECLARE
@CategoryId int,
@NodeIdParent int
-- @Id specifies a CategoryId (e.g. C7, C12, ...)
IF @Id LIKE @CategoryPrefix + '%'
BEGIN
SELECT
@CategoryId =
-- The parameter should be NULL if nothing is specified after the prefix
NULLIF(
-- Remove the prefix from @Id, leaving the CategoryId
STUFF(
@Id,
-- Start from the beginning of the string
1,
-- The number of characters to replace should be the length of the prefix
LEN(@CategoryPrefix),
-- Replace with the empty string
''
),
''
)
END
-- @Id specifies a NodeId (e.g. N17, N32, ...)
ELSE IF @Id LIKE @NodePrefix + '%'
BEGIN
SELECT
@NodeIdParent =
-- The parameter should be NULL if nothing is specified after the prefix
NULLIF(
-- Remove the prefix from @Id, leaving the NodeId
STUFF(
@Id,
-- Start from the beginning of the string
1,
-- The number of characters to replace should be the length of the prefix
LEN(@NodePrefix),
-- Replace with the empty string
''
),
''
)
END
/*
Fetch nodes for our tree, different modes depending on the level
*/
----------------
-- Root level --
----------------
IF @Id IS NULL
BEGIN
SELECT
-- Id and display text
@CategoryPrefix + ISNULL(CONVERT(varchar(100), C.CategoryId), '') AS [Id],
C.CategoryName AS [NodeText],
-- Node settings
CONVERT(bit, 0) AS [CanDrag],
CONVERT(bit, 1) AS [CanDrop],
NodeInfo.CanExpand,
CONVERT(bit, 0) AS [CanReorder],
CONVERT(bit, 0) AS [Expanded],
-- Passing fields
C.CategoryId,
CONVERT(int, NULL) AS [NodeId],
-- Dependson
CONVERT(bit, NULL) AS [ShowSomeKindOfLink]
FROM
Example.Category C
-- Check if there are child nodes
LEFT JOIN (
SELECT DISTINCT
N.CategoryId
FROM
Example.Node N
) Parents ON
Parents.CategoryId = C.CategoryId
CROSS APPLY (
SELECT
CONVERT(
bit,
CASE
-- Only nodes with children are expandable
WHEN Parents.CategoryId IS NOT NULL THEN
1
ELSE
0
END
) AS [CanExpand]
) NodeInfo
ORDER BY
C.SortOrder
-- Defines root-node properties
SELECT
null AS [Id],
'Root' AS [NodeText],
convert(bit, 0) AS [CanDrop]
RETURN
END
------------------------------------------------------------------------------------
-- The other levels, either nodes for a category or nodes with a specified parent --
------------------------------------------------------------------------------------
SELECT
-- Id and display text
@NodePrefix + ISNULL(CONVERT(varchar(100), N.NodeId), '') AS [Id],
N.NodeName AS [NodeText],
-- Node settings
CONVERT(bit, 1) AS [CanDrag],
CONVERT(bit, 1) AS [CanDrop],
NodeInfo.CanExpand,
CONVERT(bit, 0) AS [CanReorder],
CONVERT(bit, 0) AS [Expanded],
-- Passing fields
CONVERT(int, NULL) AS [CategoryId],
N.NodeId,
-- Dependson
CONVERT(bit, NULL) AS [ShowSomeKindOfLink]
FROM
Example.Node N
-- Check if there are child nodes
LEFT JOIN (
SELECT DISTINCT
NC.NodeIdParent
FROM
Example.Node NC
) Parents ON
Parents.NodeIdParent = N.NodeId
CROSS APPLY (
SELECT
CONVERT(
bit,
CASE
-- Only nodes with children are expandable
WHEN Parents.NodeIdParent IS NOT NULL THEN
1
ELSE
0
END
) AS [CanExpand]
) NodeInfo
WHERE
-- At the next to topmost level we specify a CategoryId and want only nodes for that category
(
@CategoryId IS NULL
OR
N.CategoryId = @CategoryId
) AND
-- At the next to topmost level @NodeIdParent is NULL and the column should also be NULL.
-- At the other levels we have a @NodeIdParent and want only nodes with that parent.
(
(
@NodeIdParent IS NULL AND
N.NodeIdParent IS NULL
)
OR
N.NodeIdParent = @NodeIdParent
)
ORDER BY
N.NodeName
END