Stored Procedures for Conditional Alerts

Jim Walker
Aug 10, 2023
5 min read

Updated: Mar 13

We recently learned about stored procedures and how these can be created for bulk communications. This week we're going to look at conditional alerts. If you've not met conditional alerts yet, you should definitely go and read more about them.

What is an Alert?

Chat GPT sums it up well:

An alert is a notification or message generated by a system or application to draw attention to a particular event, condition, or situation. Alerts are designed to provide timely information to users, administrators, or other relevant parties so that they can take appropriate actions or make informed decisions.

Ok, so what is a Conditional alert?

A conditional alert is a clever alert. It does all the above, but only if it needs to based on a given logic. Alerts about an overdue item only needs to come up until that item has been completed. Alerts need to be added or removed manually by a user, but with a conditional alert you define the alert, you define the logic as to when it should arise, and the system will evaluate when to trigger the alert.

Note that its not quite as amazing as it sounds because the 'evaluation' sadly isn't in real-time. In real-time it would be far too demanding on the database to evaluate whether alerts should trigger, so this is done via a system schedule at a fairly high frequency e.g. every few hours.

You have two options when setting up conditional alerts. 1) using the statement builder and 2) using a SQL routine. Ultimately, if you can do it with the statement builder, you probably should. The only caveat to that is that you don't always know what a standard data tag is doing behind the scenes. Sometimes its nicer to know exactly what your routine is going to return.

Contact Alerts

Assuming we want to hook in to a SQL routine, the setup screen would look like the below. Notice the box titled 'SQL routine', along with a beautifully named stored procedure: usp_MySite_Alert_Contact_Deceased. Now I could have called my procedure ThisIsAProcedureToReturnDeceasedContactsWheneverYouGetAsked...but that would be long, untidy and just bad. it also doesn't say usp which is always helpful to know its a 'user stored procedure'. You will similarly hopefully use these for finance integration, arrears policy, bulk communications and many more, so it will help to name things nicely for ongoing support maintenance. Any self respecting DBA won't allow anything else.

The procedure for this alert is shown below. At this particular site, we record the fact that a tenant has deceased using a Contact Type. The purpose being to alert the user who can then take appropriate steps whenever dealing with the account.

This procedure selects distinct ContactId values from the Contact table based on certain criteria. Here's a breakdown of the procedure:

The procedure starts with the AS BEGIN statement, which indicates the beginning of the procedure's code block.
The SELECT statement retrieves ContactId values from the Contact table.
It uses an inner join with the ContactType table to apply additional conditions.
The join condition is c.ContactId = ct.ContactId.
There's a subquery: (select LookupReference from lookup where lookuptypeid = 34 and Description = 'Deceased') used in the join condition. It retrieves a LookupReference value from the lookup table where lookuptypeid is 34 and the Description is 'Deceased'.
The subquery ensures that the Contact must have a related ContactType with the given conditions to be included in the result.
Additionally, we want to know only those where the alert is 'current'. This is why we apply further conditions: ct.EffectiveFromDate <= getdate() The effective from date should be less than or equal to the current date.
(ct.EffectiveToDate is null or ct.EffectiveToDate >= getdate()) The effective to date should be null or greater than or equal to the current date. This allows for ongoing or open-ended effective dates.

The above 'EffectiveFrom/EffectiveTo' pattern is used across the whole of Cx. I've written hundreds of reports now from Cx, and in 90% of cases, you will want to use this pattern to extract rows which are current. There will obviously be exceptions.

The word distinct is not strictly necessary in this scenario, but it is a failsafe. We only want to return contacts once. If working with a larger query where looking across to rent accounts or agreement episodes, it's common to end up with multiple rows, and so this is a failsafe. Again, it's not an excuse for lazy coding.

Asset Alerts

Lets now take a look at the same but for Asset Alerts.

Here is the code for the SQL Server stored procedure named usp_MySite_Alert_Asset_Demolished.

Contact Groups

I've not included it here for fear of pointless repetition, but if creating a conditional alert for Contact Groups, you simply need to have a stored procedure that returns a single column called ContactGroupId.

Evaluating Conditional Alerts

Conditional alerts whether built using the statement builder or a SQL routine require re-evaluating which is done via a System Scheduler called Evaluate Conditional Alerts. Set this up at a sensible frequency....perhaps several times a day, or every hour if really needed. Avoid getting it down to minutes as this is just an unnecessary pull on database and CPU.

I'm still not getting excited about them...sell it to me one last time!

We've looked at how to use SQL Stored Procedures as the code behind a conditional alert. If you're following along and happy in principle but not sure what to do with the stored procedure code, please get some tech support either from your in-house support teams or externally before diving in to SQL.

If you are cloud hosted with Civica or your hosting environment is locked down, you may not even have permission to install the stored procedure. In that case, you just need to log a support call with what you want done.

But the reason for this blog post is also to highlight how useful they can be, for example:

An alert to tell you that a property has a gas service overdue so that if anyone speaks to the tenant, they can raise this.
An alert to highlight an excessive level of arrears on an account.
Alert to tell you that a tenant portal user hasn't logged in in the last 6 months so you can ask why.
Alert to the user that we don't hold a phone number or email.
Alert to say there is an open or recent complaint.
Alert to identify an asset is in a defect period and repairs should be referred to the builder.
Alert to flag to the user that the property is owned by a 3rd party and to refer repairs to x

Hopefully this will help you to set up your conditional alerts. I'd love to hear from you in the comments of any cool things you've done with alerts.