Set default status code by workflow/state

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:

    • W_S_code - the name of the table with codes
    • W_S_code.workflow - a column that contains workflow names
    • W_S_code.State - a column that contains state names
    • W_S_code.code - the column that contains the code of the combination.

    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.