Workflow / Views: Specific state date

Question

I need a column (field) in a view where you can see the date of a specific state (Workflow state).

For example: A content type news has three workflow states: new, reviewed, published. Now I need a view where you can see each news (also ones that haven't entered that state!) with a field where the date is shown when it was new, reviewed and published (sortable). The Workflow module only provides "Previous time", but that's not specific to a state.

Any ideas?

I know how to program a views handler for custom table/fields but my problem is that the node -> workflow_node_history has an 1:n relation.

drupal 6, workflow 1.5, views 2.x

Example:

Title   | Date new     | Date Reviewed | published
---------------------------------------------------
Title1  | 01.03.2012   | 13.03.2012    | pending
Title2  | 02.05.2012   | 16.05.2012    | 20.05.2012
Title3  | 20.05.2012   | pending       | pending

Answer 1

The Views PHP Module provides a custome field that is click sort enabled

Value code example:

....
$result = db_fetch_object(db_query("SELECT stamp FROM {workflow_node_history} WHERE nid = %d AND sid = %d", $row->nid, $sid));
if(isset($result->stamp)) {
   return $result->stamp;
}
return 0;

Output code:

<?php 
print date('d.m.Y', $value);
?>

Enable click sort: sort numerically

Answer 2

Add to the Filter criteria: Where current state = reviewed

Add the current field: Workflow: current time

That should return you the time of the state change to reviewed, for all nodes who are currently on the state "reviewed". If you want to show the nodes that haven't entered the state "reviewed", then just show all the nodes, and their current workflow state timestamp.

Disclaimer: I answered this on D7 so I don't know if that field and filter are available in D6.