Creating an Escalation Rule on ANY Table in CRM

I wanted to share something with you that I learned this week. Being on the integrations team I spend lots of time working on customisations that help our integration partners to deliver really great out of the box integrations with their own products.  One thing I was asked this week was whether or not it was possible to create an escalation rule that would (in Layman's terms) check the status of an integration and if the integration failed to alert the user.

So in essence they wanted to create an escalation rule which checks ERPIntegrations.ERPI_SyncStatus periodically, for a particular ERPI_IntegrationID. If the ERPI_SyncStatus = 'fail', then set an on-screen notification for particular users. 

Our integration partner felt that this would be of huge benefit to customers who are sometimes unaware of the sync failing.

This article http://community.sagecrm.com/partner_community/b/sage_crm_development_team_blog/archive/2011/05/26/escalate-your-campaigns.aspx showed me how to add the notifications rules for my ERPIntegrations table.  

So, following what I had learned from the above article I created a view to append my ERPIntegrations table to the existing Escalations table.

CREATE VIEW v_ERPFail
AS
SELECT *
FROM ERPIntegrations left join escalations on escl_recordid= ERPI_IntegrationID
and escl_tableID = 10155

 The 10155 is the bord_tableID from the Custom_Tables table where BORD_Name = 'ERPIntegrations' (make sure you check this in your own system as it could differ depending on what customisations have been done)

So far so good, however I found that when I went into Administration - Advanced Customisations - Escalations and tried to display my table it wouldnt appear in my drop down selection box. 

I thought about why it might not be appearing and wondered if it has something to do with translations or if I had to add a caption or something. In the end i asked Jack (he's our extensability consultant). He told me about a column bord_WorkflowIdField on the customtables table that he thought needed to be populated in order for the table to appear in the dropdown for escalations.

So I tried it. Heres my SQL:

update Custom_tables set Bord_WorkflowIdField = 'ERPI_workflowid'

where Bord_Name = 'ERPIntegrations'

And guess what?? It worked!!!!

So I was on to my final hurdle of creating the trigger SQL clause. I used the above article to guide me.  My trigger looks something like this:

(1 = #U AND lower(v_ERPFail.ERPI_SyncStatus) = 'Fail') AND ((Escl_EscalationId is NULL ) OR (Escl_WorkflowRuleId <> 10149) OR ((Escl_WorkFlowRuleId = 10149) AND Escl_Datetime < #T AND Escl_UserId = #U))

You will still need to go and create your actions. See my sample on screen notification below which alerts the user that the sync has failed:

The column to choose is escl_datetime and the table is escalations.

I added a translation to my message that will be displayed with the name of the integration that failed.

Hope this saves you some time :-)