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:

• Quick Notifications' on new primary entities
• Using the ##Codes