Relationship Between Change and Workflow Process

Good day all.  I am trying to figure out how a Workflow Process is related to a change.  I am trying to write a report that will capture all of the open changes with the assignments that have not been completed yet.  I have a SQL query that can get most of everything, but I am having a problem getting the Identity of who the assignment is against (Activity Assignment related_id) to pull in the Identity keyed_name.  I understand SQL is not recommended, but I don't know how to do it with AML, C#, or Javascript.

Thanks for any help you can provide.

Innovator inn = this.getInnovator();

// finds the most recent generation of CAD which is existed in Part CAD
StringBuilder sql = new StringBuilder();
"SELECT Activity.keyed_name as Activity, AA.comments, AA.closed_on, AA.closed_by, AA.related_id, s_Change.item_number, s_Change.state, s_Document.keyed_name as Artifact, sd.keyed_name as Updated " + //, Identity.keyed_names,
// "FROM s_Change as Change " +
"FROM Workflow_Process " +
// "LEFT JOIN Workflow_Process ON Change.item_number = " +
"LEFT JOIN Workflow_Process_Activity ON = Workflow_Process_Activity.source_id " +
"LEFT JOIN Activity ON Workflow_Process_Activity.related_id = " +
"LEFT JOIN Activity_Assignment as AA ON = AA.source_id " +
// "LEFT JOIN Identity ON = AA.related_id " +
"LEFT JOIN s_Change ON s_Change.item_number = " +
"LEFT JOIN s_Document ON s_Change.affected_item = " +
"LEFT JOIN s_Document as sd ON s_Change.new_released_item = " +
"WHERE = 'SM-DCN-22-1136' AND Activity.active_date >= '1900'" // AND Activity.closed_date IS NULL
); // s_Change.state NOT LIKE '%cancel%' and s_Change.state NOT LIKE '%close%' AND s_Change.item_number LIKE 'sm%' AND

Item results = inn.applySQL(sql.ToString());

return results;

  • May be something like this

    <Item type='Workflow Process' action='get' select='active_date, name' orderBy='active_date'>
        <Item type='Workflow Process Activity' action='get' select='related_id' orderBy='related_id(active_date)'>
            <Item type='Activity' action='get' select='label,name,state,active_date'>
                <Item type='Activity Assignment' action='get' select='related_id(name)'></Item>