One more post in the Show Usage series š
I was trying to see whether I can get a list of all Data Validation rules in a Planning application so that I can get rid of some of them real quick.
Think about all these series as my rants against PBCS (and in some cases ON-PREM)
I can run the below given query to find out what are my existing DV rules and which forms they are associated with.
SELECT
B.OBJECT_NAME "Form",
A.NAME "DV Rule",
A.ROW_LOCATION "Row Location",
CASE
WHEN A.COL_LOCATION >26 THEN
CHR(ROUND(((A.COL_LOCATION - 1) / 26),0) + 64)||CHR(MOD ((A.COL_LOCATION - 1) ,26) + 65)
ELSE
CHR(64+A.COL_LOCATION)
END AS "Column Location",
CASE
WHEN A.ENABLED=1 THEN
'YES'
ELSE
'NO'
END AS "Enabled",
CASE
WHEN A.ROW_LOCATION=-1 AND A.COL_LOCATION=-1 THEN
'Grid Rule'
WHEN A.ROW_LOCATION=-1 AND A.COL_LOCATION<>-1 THEN
'Column Rule'
WHEN A.ROW_LOCATION<>-1 AND A.COL_LOCATION=-1 THEN
'Row Rule'
ELSE
'Cell Rule'
END AS "Rule Type"
FROM HSP_FORM_DV_RULE A,
HSP_OBJECT B
WHERE A.FORM_ID=B.OBJECT_ID ORDER BY 1;
And here is the result.
I don’t know how the Row location is populated, it is not the row that is shown on the Webform, it is not the count of POV Dims+Col Dims + Row number, not the expanded number of Rows. (I did get lost while trying to figure out the ROW_LOCATION logic)
I’ve added a fail safe in the SQL when the column number goes beyond 26 (A~Z).
If you’ve a column number goes beyond 676, the SQL won’t show correct results. (I hope you are not working at a place where you’ve a Webform which goes beyond ZZ!!!!)
If you want to see what each rule looks like, that is stored under HSP_FORM_DV_CONDITION
