Oh how angry I get with myself sometimes. I am a very stupid person. Someone sends me an email entitled 'a quick question' and in my idiocy I start trying to answer the question and look up nearly 3 hours later to find I have consumed far more time than I should.
The actual question I was sent was.
How do I create an escalation rule to send an on screen notification to a person who has been newly assigned to a case? Let me elaborate a bit more. Say I have a Case now. However, I decided to re-assign it to someone else. Now I want that action to cause a notification to be sent to the newly assigned person for the case. Is that possible? Just cannot see how I can phrase the SQL Trigger.
I have understood this to mean we want a notification to be sent to a user to alert them to a new case (and then if the assigned user is changed the new user should be notified).
The main assumption that I have made is that the original Notification of "You've got a new case" has been created using a Quick Notification. These are the simple escalation rules created against the main entities in the administration>Customization area of the system.
The Quick or Simple Notifications for Cases use the vNotificationCases view. The view references a record in the escalation table and the cases table. The escalation table provides the information about the timing using the escl_datetime field. If the notification is snoozed then the escl_datetime is changed into the future and if the notification is dismissed then the escl_datetime field is set to null.
Once a notification is dismissed there is no way inside the notification mechanism for the notification mechanism to be reset. The escalation table also provides the information about who to notify (escl_userid). If the case is reassigned it only changes the case_assigneduserid field and not the escl_userid field.
The escalation table is linked to the case through the two fields
escl_tableid = 3 (This is the id of the case table held in custom_tables)
escl_recordid = 72 (The id of the record in the case table)
So if the case is reassigned then we would have to make a change in the escalation record to set the escl_datetime and to reset the escl_userid.
This could be done using a tablelevel script and a an UpdateRecord() event function. (But I am not going to talk about that here.)
It is however more appropriate to use a workflow rule to make the change. I am going to make the assumption that there is a reassign rule in your case workflow similar to the default case workflow in the Sage CRM demo system.
We can add a workflow action to the Global reassign rule. This action would be an action of type "execute SQL statement". In the case workflow rule action I would like to be able to write:
update escalations
set escl_datetime = getdate(),
escl_userid = #case_assigneduserid#
where escl_tableid = 3
and escl_recordid = #case_caseid#
causes an SQL Error
This is because it is rendered in SQL as
update escalations set escl_datetime = getdate(), escl_userid = Kylie Ward where escl_tableid = 3 and escl_recordid = 72
and generates the error as the escl_userid is a integer and I really needed #case_caseid# in this instance to return the actual id and not the translated name of the user.
My work around is a little bit ugly
update escalations
set escl_datetime = getdate(),
escl_userid = (select user_userid from vSearchListUser where User_Name = '#case_assigneduserid#')
where escl_tableid = 3
and escl_recordid = #case_caseid#
But this does seem to allow the originally assigned user to dismiss a notification about a case and then subsequently reassign it. The newly assigned user will then see the notification appear.
I am sure that I have made some glaring assumptions...
I have talked about Notification rules in some other posts: