Matrix Editor is useful to edit DB Values in a pivot table.
It also handles n:n connection editing without real DB field (Key values only)
The Matrix Editor detects duplicates (values on the same position) and shows an error message.
Usage: the source object can be Virtual or an Existing Table.
The Matrix Editor will save data on the Connected Object's main table.
It will remove key pairs if the DATA set to NULL is a specific position.
The component detect data changes, only the changed values are saved.
the entry wich will be shown in the DATA area.
Note: it can be virtual: e.g. "SELECT -1 AS MATRIX_INDICATOR"
Number of cells can be limited. e.g: large virtual DataSets.
It is receommended to use Filters to limit Data on huge Data
If this property is set: only keys will be saved in Base Table: classical n:n connections.
This entries will be used when creating row aggregations.
Row sorting influenced by the Connected Object's OrderBy entry
This entries will be used when creating column aggregations.
Columns are automatically sorted.
Sample Query:
In this query MATRIX_INDICATOR does not exist in base table, it will be used as an indicator to see
the connection is exist. (key values in the DATA_TECHNICALPLACES2OBJECTS table).
For an SQL based Filter we introduced String fields to be able to search on them:DT.TECHNICALPLACENO
SELECT
BDP.OBJECT_ID OBJECTREFERENCE,
DT.TECHNICALPLACENO,
TP.TP_ID TECHNICALPLACEREFERENCE,
BDP.PRODUCT_ID AS PRODUCT,
DATA_TOP.MATRIX_INDICATOR
FROM
(SELECT OBJECT_ID, PRODUCT_ID FROM BDP
WHERE OBJECT_ID IN (SELECT DISTINCT OBJECT_ID FROM REASONTOOBJECT WHERE ENTRY_ID=567)
GROUP BY OBJECT_ID,PRODUCT_ID) BDP
CROSS JOIN (SELECT DATA_ID AS TP_ID FROM DATA_TECHNICALPLACES) TP
LEFT OUTER JOIN
(SELECT OBJECTREFERENCE,
PRODUCT,
TECHNICALPLACEREFERENCE,
-1 AS MATRIX_INDICATOR
FROM DATA_TECHNICALPLACES2OBJECTS) DATA_TOP
ON DATA_TOP.OBJECTREFERENCE=BDP.OBJECT_ID
AND DATA_TOP.PRODUCT=BDP.PRODUCT_ID
AND DATA_TOP.TECHNICALPLACEREFERENCE =TP.TP_ID
LEFT OUTER JOIN DATA_TECHNICALPLACES DT
ON DT.DATA_ID =TP.TP_ID