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.
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.