In addition to the other custom attributes that can be added to RiskVision objects users can also add a dropdown list that is populated based on an SQL query. This list can be added to any object in RiskVision, but it must first be enabled.
To enable the SQL dropdown list:
Log in to RiskVision.
Create a role and enable the Tenant Configure and Tenant Configure UI permissions.
Assign the currently logged in user to the role.
Log out and back in again.
Click Configure UI in the top right corner and click OK.
Open the object you wish to add the dropdown to.
Drag and drop a custom string attribute into the object.
Export the UIComponent-Ticket.xml, append the customization file as below, and save the file:
<attribute id="customAttributes.string3" displayName="CustomAttribute" tooltip="" required="false" hidden="false" editable="true" changedOnly="false" type="sqloptionscombobox" queryName="assetOptions" sendObjectId="true" origin="user" order="13"></attribute>
Import the UIComponent-Ticket.xml file.
Navigate to the %AGILIANCE_HOME%\Tomcat\webapps\spc\WEB-INF\classes\sqlQueries.xml file.
Place the below query at the bottom of the page and save it:
<query name="assetOptions">
<sql><![CDATA[
SELECT DISTINCT asset.NAME FROM agl_ticket tkt
INNER JOIN agl_ticketobject objAsset
ON objAsset.ticket_id = tkt.ticket_id
INNER JOIN agl_asset asset
ON asset.asset_id = objAsset.object_id
WHERE tkt.ticket_id = :aglModelId
]]></sql>
</query>
Restart the tomcat service.
Log in to RiskVision again.
Navigate to the object in step 6.
- Click on the dropdown of the custom string from step 7 which will display data based on the query written in sqlQueries.xml.
