I have a requirement to set the default status code by specific workflow and state. The default status code is set in an environment variable.
Workflow A and State A = Status S0
Workflow B and State C = Status S2
Can this task be done using a Select statement in the appropriate environment variable. I would need to identify the workflow and state variable names.
Any help/suggestions would be appreciated.
One option would be to leverage the Work Rules Engine if that's an option for you. Otherwise, it's possible with a SQL Select statement. It's more cumbersome than it should be (IMHO) since neither workflow nor state information is available as a system variable for a document.
Here is some untested SQL that should get you the the state name of a selected document. Set it as Default and Update for an attribute. The same approach will get you the Workflow information. You could combine the 2 into a longer statement with Case elements or additional Where clause to filter against your own lookup table. HTH get you on the right track.
SELECT s.o_statename FROM dms_stat s, dms_doc d WHERE d.o_stateno = s.o_stateno AND d.o_projectno = $VAULT.CODE$ AND d.o_itemno = $DOCUMENT$
Please note that I post here on a voluntary basis and am not a Bentley employee.
Hello,
To have codes for according Workflow and State combination you will have to create a table containing Workflow, State and Code columns (I have named mine W_S_code). Here is a tested SELECT statement example of how to achieve it:SELECT W_S_code.code FROM dms_doc JOIN dms_stat ON dms_doc.o_stateno = dms_stat.o_stateno JOIN dms_work ON dms_doc.o_workflowno = dms_work.o_workflowno JOIN W_S_code ON dms_work.o_workflowname = W_S_code.workflow AND dms_stat.o_statename = W_S_code.State WHERE dms_doc.o_itemno = $DOCUMENT$ AND dms_doc.o_projectno = $VAULT$ ;
The text in bold should be changed based on your table and column names accordingly:
The database I have tested this on is created on SQL server. I cannot guarantee if this statement will work in Oracle as the names might be different.
Dear Goda, I was considering creating a lookup table for this task and thank you for your select statement. Our datasources are hosted by Bentley and believe we are using an SQL server.