2

I have a content type with several fields which are updated manually over time by different users.

Let's say, for example, the content type name is Ticket and the fields in this content type are:

  • Ticket Status
  • Ticket Resolved Date
  • Ticket Escalation Date (default empty)

Every time a user changes a field, a revision is created automatically.

How can I create a report (using the Views module) to list the users who only change the field "Ticket Escalation Date" from empty to some value?

Note: The site exists for quite some time already and contains data. So a report about these cases that were created in the past is needed also, based on the current design and existing data.

3

Part 1 - Intercept these cases from now on

If you're willing to also consider using the Rules and Flag module, then it'll be a peace of cake to use Views for creating the view you're looking for. Below are the main steps to do so ...

Step 1: Create a flag to prepare for reporting such users

Using the Flag, create a non-global flag that will be used to get the users flagged that fit your specs (= users who only change the field "Ticket Escalation Date" from empty to some value).

Step 2: Use Rules to flag such users

Using the Rules module, create a rule to "automatically flag users" that fit your specs (= users who only change the field "Ticket Escalation Date" from empty to some value). Here are the specs of such rule:

  • Rules Event: after updating content (of type = ticket)
  • Rules Condition: only the field "Ticket Escalation Date" was changed from empty to some value (this will need some refinements from your part to actually specify such condition, refer to (*) for some guidance on this).
  • Rules Action: flag such user (with the flag from step 1). But make sure to use the userid of the updater, which may be different from the node author ... Probably you want to use "current user".

(*): Some more details on those Rules Conditions (ALL conditions must apply):

  • Add an "entity has field" (with field = Ticket Escalation Date), to make that field available in your rule. If such field (machine name) is only used for "tickets", then there is no need in the Rules event to refine with the "(of type = ticket)".
  • BEFORE saving the content (= value before the edit started) "Ticket Escalation Date" is blank.
  • AFTER saving the content (= value after the edits will have been saved) "Ticket Escalation Date" is NOT blank.
  • "Ticket Status" is unchanged (value BEFORE saving is equal to value AFTER saving).
  • "Ticket Resolved Date" is unchanged (value BEFORE saving is equal to value AFTER saving).

Step 3: Create your view

Using the Views module, create a view of "users", which were "flagged" with the flag from Step 1.

Part 2 - Report about similar cases in the past

What is explained in Part 1 can be used as a solution if it was put in place from the beginning (when the site was launched), or to intercept these cases from now on. For existing sites, you could "report" about similar cases using a custom SQL query (based on the current design and content of your Drupal tables), combined with using the Forena module (disclosure: I'm a co-maintainer of it). This module comes with quite some Community documentation. Here is a summary of this module:

Forena is built of the idea of using SQL to get data out of a database and use XHTML and CSS to format it into web reports.

Various DBMSs are supported, such as Oracle, MS Sql, Postgress, SQLite, MySQL, any PDO compliant DBMS ... And there are quite some reasons for considering Forena.

Visit the Forena HowTos page for some links (near the bottom) to some , and there is a live demo site of it.

Assume you have a query which looks like so (as in your comment to this answer):

SELECT r.title, u.name
FROM field_revision_field_expedite_date re, node_revision r, users u
WHERE r.nid=re.entity_id and r.vid=re.revision_id and u.uid=r.uid
GROUP BY re.entity_id
HAVING min(revision_id)

It'd be straight forward to transform the query results of this SQL in a report (and/or even chart if you'd want ...). Refer to the example in my answer to "How to retrieve data directly from a Drupal database" to get an idea of what it is like to use Forena, such as the SQL query and the source of the "custom report" there.

PS: not sure what you'd want to "do" with such list (created as explained in "Part 1"), but if at some point you want to reset/re-init such list, you could do so easily using the VBO module also (= unset the flag for all users in my View).

  • That will be good solution if it was put in design from the beginning, but at the moment site is there for quiet sometime and data is their and report is needed upon current design. – Rami Sedhom Dec 17 '15 at 16:51
  • Hm, that sounds like "solution (answer) to the problem (question), changes the problem (question)". You know what, give me some time to think about that NEW question. As Allan Bean, one of the Apollo astronauts would say: there HAS to be a way to do that (also). So thank you for the extra challenge ...! – Pierre.Vriens Dec 17 '15 at 17:01
  • I was able to do that on database level using below query: select r.title, u.name from field_revision_field_expedite_date re, node_revision r, users u where r.nid=re.entity_id and r.vid=re.revision_id and u.uid=r.uid group by re.entity_id having min(revision_id) . I'am trying to use Query UI (QUI) (which I just discovered) to make the report on the frontend. Still trying with this new module. – Rami Sedhom Dec 17 '15 at 17:26
  • 1
    Hm, hang on for a second ... I'm not familiar with the "Query UI" module (yet), but are you familiar with the "forena" module (drupal.org/project/forena)? So how about I extend my answer how you could (easily) use that query to build reports (and charts) with just a few mouse clicks (using Forena)? – Pierre.Vriens Dec 17 '15 at 18:03
  • @PierreVriens : go ahead please – Rami Sedhom Dec 17 '15 at 18:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.