FieldSubselect

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