Project Open Custom E-Mail Notifications - Part 2

In the first part of this series, I talked about building a query that will show all of the workflow tasks that are in-process. That is to say, I demonstrated a query that shows every step that represents a non-closed step in a workflow. That query, with a few modifications, will let us send an e-mail to anyone who has a workflow step assigned to them.

The second query we'll need is one that shows what items are closed. That will let us send an e-mail to inform the customer (the individual who opened the ticket) to tell them that their ticket has been closed.

Now, you might not want to do this. For example, your workflow might include a survey that the user fills out in the just before automatically closing the ticket (as this PowerPoint presentation shows). If you don't display a survey, you might at least reassign the ticket back to the requestor so they can confirm the work was done correctly. If you do either of those things, then you obviously wouldn't want to send an additional e-mail. My example assumes that you would like to use e-mail to ask your customers to fill out a survey.

The closed workflow step query involves the following tables:

1. wf_tasks: This table holds the tasks that make up the work flow.
2. wf_cases: Tracks step types and focuses the scope of the query against wf_tasks.
3. wf_workflows: Defines the workflow and lets us get the human friendly title for the workflow.
4. wf_transitions: Defines the names of the workflow steps and lets us get the human friendly name of the workflow step.
5. im_tickets: Holds the master information for the ticket, including the ID of the individual who entered it.
6. parties: Holds the e-mail ID for the individual who opened the ticket.

Based on the above tables, I crafted this query:


SELECT a.task_id, a.case_id, b.object_id, b.state, c.description, d.transition_name, f.email
FROM wf_tasks a, wf_cases b, wf_workflows c, wf_transitions d, im_tickets e, parties f
WHERE b.case_id = a.case_id
AND c.workflow_key = a.workflow_key
AND d.workflow_key = a.workflow_key
AND d.transition_key = a.transition_key
AND a.transition_key LIKE '%close%'
AND e.ticket_id = b.object_id
AND f.party_id = e.ticket_customer_contact_id
ORDER BY task_id DESC


It looks pretty straight forward until you get to the WHERE clause's LIKE operator for transition_key. This bit will vary installation by installation and potentially workflow by workflow. In my Project Open installation, we've tried to come up with a standard (remember better living through standards?) that says the workflow's last step before "E" should contain the word "closed". If you can't enforce that standard, or if you'd like to use this solution to add workflow e-mailing to an existing Project Open system, you could issue this query to see what the existing workflows and transition keys are:


SELECT DISTINCT(workflow_key || '-' || transition_key) AS thekeys
FROM wf_tasks
ORDER BY thekeys

You can evaluate the results and add them to the query above. For example, if you see that you need to add the transition key 'This Is Really Weird', you could change the query above to look like this:


SELECT a.task_id, a.case_id, b.object_id, b.state, c.description, d.transition_name, f.email
FROM wf_tasks a, wf_cases b, wf_workflows c, wf_transitions d, im_tickets e, parties f
WHERE b.case_id = a.case_id
AND c.workflow_key = a.workflow_key
AND d.workflow_key = a.workflow_key
AND d.transition_key = a.transition_key
AND (a.transition_key LIKE '%close%'
OR a.transition_key = 'This is Really Weird')
AND e.ticket_id = b.object_id
AND f.party_id = e.ticket_customer_contact_id
ORDER BY task_id DESC


You might also see situations where you have to exclude a workflow_key/transition_key pair. For example, if you have a workflow called 'My Unusual Example' that has a step 'This is close to the end', you could add a NOT operator to the query above to exclude that step -- since it's obviously not a step that represents a closure.

With these two queries out of the way, I'll show you one more table that we need to take into account before we can design our solution.