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