Difference between revisions of "FieldSubselect"
Jump to navigation
Jump to search
old>Ln.jeel (Created page with '= SQL: Subselect = Enquiry in own or foreign database Properties * Type: Lookup * Groupable: No * Show in lists: Yes * Searchable: Yes == User …') |
m (8 revisions imported) |
||
(7 intermediate revisions by 2 users not shown) | |||
Line 27: | Line 27: | ||
Required: | Required: | ||
* | * SELECT query that returns a single record with a single value in the recordset | ||
Options: | Options: | ||
* | * Restrictions (when to display the value): | ||
** Always: Single, list and subitem mode | |||
** Items: Single mode only (hidden in list mode) | |||
** Lists: List mode only (hidden in single mode) | |||
** Subitem: Lists displayed in single items (child records) | |||
Notes for usage: | |||
* Values are not cached or stored | |||
* Consider activating INDEX for fields used in WHERE or JOIN clauses | |||
=== Configuration example 1 === | |||
==== Solution setup ==== | |||
Solution "Child items" (system name: "testchild") | |||
* Relation field "owner" (system name: "PARENT"). | |||
Solution: "Parent items" (system name: "testparent") | |||
* Date field "starting date" (system name: "DATO") | |||
==== Business requirement ==== | |||
Display the starting date (system name: "DATO") from the related parent table, linked by the local field owner (system name: "PARENT"). | |||
==== Subselect SQL statement ==== | |||
SELECT DATO | |||
FROM data_testparent | |||
WHERE :PARENT = data_testparent.DataID | |||
this is the shorthand version for | |||
... | |||
WHERE data_testchild.PARENT = data_testparent.DataID | |||
=== Configuration example 2 === | |||
==== Solution setup ==== | |||
Solution "Example" (system name: "example") | |||
* Status log activated | |||
==== Business requirement ==== | |||
Display the number of state changes for a data item | |||
==== Subselect SQL statement ==== | |||
SELECT CONCAT( COUNT(*), ' transitions') | |||
FROM data_example_statuslog | |||
WHERE data_example_statuslog.DataID = :DataID | |||
this is the shorthand version for | |||
... | |||
WHERE data_example_statuslog.DataID = data_example.DataID | |||
== Developer info == | == Developer info == |
Latest revision as of 11:52, 10 December 2021
SQL: Subselect
Enquiry in own or foreign database
Properties
- Type: Lookup
- Groupable: No
- Show in lists: Yes
- Searchable: Yes
User interface
Field in show mode
Field in edit mode
User interface as displayed in picture
Configuration
This field requires configuration before deployment.
Required:
- SELECT query that returns a single record with a single value in the recordset
Options:
- Restrictions (when to display the value):
- Always: Single, list and subitem mode
- Items: Single mode only (hidden in list mode)
- Lists: List mode only (hidden in single mode)
- Subitem: Lists displayed in single items (child records)
Notes for usage:
- Values are not cached or stored
- Consider activating INDEX for fields used in WHERE or JOIN clauses
Configuration example 1
Solution setup
Solution "Child items" (system name: "testchild") * Relation field "owner" (system name: "PARENT"). Solution: "Parent items" (system name: "testparent") * Date field "starting date" (system name: "DATO")
Business requirement
Display the starting date (system name: "DATO") from the related parent table, linked by the local field owner (system name: "PARENT").
Subselect SQL statement
SELECT DATO FROM data_testparent WHERE :PARENT = data_testparent.DataID
this is the shorthand version for
... WHERE data_testchild.PARENT = data_testparent.DataID
Configuration example 2
Solution setup
Solution "Example" (system name: "example") * Status log activated
Business requirement
Display the number of state changes for a data item
Subselect SQL statement
SELECT CONCAT( COUNT(*), ' transitions') FROM data_example_statuslog WHERE data_example_statuslog.DataID = :DataID
this is the shorthand version for
... WHERE data_example_statuslog.DataID = data_example.DataID
Developer info
- FeltTypeID: 510
- SQL datatype: Special
- Class name: FieldSubselect
- Field is allowed in inline tables