FieldSubselect
Revision as of 14:57, 20 February 2014 by old>Admin (→Configuration)
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:
- 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 :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