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();
sql.AppendLine(
"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.id, Identity.keyed_names,
// "FROM s_Change as Change " +
"FROM Workflow_Process " +
// "LEFT JOIN Workflow_Process ON Change.item_number = Workflow_Process.name " +
"LEFT JOIN Workflow_Process_Activity ON Workflow_Process.id = Workflow_Process_Activity.source_id " +
"LEFT JOIN Activity ON Workflow_Process_Activity.related_id = Activity.id " +
"LEFT JOIN Activity_Assignment as AA ON Activity.id = AA.source_id " +
// "LEFT JOIN Identity ON Identity.id = AA.related_id " +
"LEFT JOIN s_Change ON s_Change.item_number = Workflow_Process.name " +
"LEFT JOIN s_Document ON s_Change.affected_item = s_Document.id " +
"LEFT JOIN s_Document as sd ON s_Change.new_released_item = s_Document.id " +
"WHERE Workflow_Process.name = '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;
|
