000029435 - Query to retrieve information about cross reference relationships in RSA Archer 5.5.2 and later

Document created by RSA Customer Support Employee on Jun 14, 2016Last modified by RSA Customer Support on Jul 2, 2019
Version 3Show Document
  • View in full screen mode

Article Content

Article Number000029435
Applies ToRSA Product Set:   RSA Archer
RSA Version/Condition:  5.x, 6.x
Platform:  Windows
IssueThis article provides a query to retrieve information about cross reference relationships in RSA Archer.

SQL query for RSA Archer 5.5.2 or later

SELECT FD .field_id, FT.field_name,FT.field_desc,
MT.module_name ,MT1.module_name as RefApp, FT.field_name, FDC.default_grid_display , FDC.max_value ,
FDC.min_value , FDC .display_control_id, DC. display_control_name, ASO.aso_status_name , FDC.allow_lookup
FROM tblIVFieldDef FD
JOIN tblLevel L ON L .level_id = FD .level_id
JOIN tblModule M ON M .module_id = L .module_id
JOIN tblModuleTranslation MT ON MT.module_id = M.module_id
JOIN tblIVFieldRelationship FR ON FR .reference_field_id = FD .field_id
JOIN tblIVFieldDef FD1 ON FD1 .field_id = FR .related_field_id
JOIN tblFieldTranslation FT ON FT.field_id = FD.field_id
JOIN tblLevel L1 On L1 .level_id = FD1 .level_id
JOIN tblASOStatus ASO ON ASO .aso_status = FD .aso_status
JOIN tblModule M1 ON M1 .module_id = L1 .module_id
JOIN tblModuleTranslation MT1 ON MT1.module_id = M1.module_id left outer
JOIN tblIVFieldRelationship FR1 ON FR1.related_field_id = FD1.field_id left outer
JOIN tblIVFieldDefCrossRef FDC ON FDC.field_id = FD.field_id left outer
JOIN tblIVDisplayControl DC ON DC.display_control_id = FDC.display_control_id where FD .field_type_id = 9