Stored Procedures for Bulk Communications

Lets cut right to the chase....this is pretty specialist stuff. You either want to know it, or you are one of the 99.999999% of people out there who almost definitely don't know this even exists, or care.

But....if you are that 0.000001% of people who do, then wouldn't it be great to find the answer right here!

What on earth are Stored Procedures for Bulk Communications?

One of the most powerful aspects of Cx is the communications engine. It is centred around the Contact, that central entity that is one of the most important 'object's to care about in a Housing Management system. Cx lets you create a contact, and assign a phone number, and email, a postal address, maybe even a twitter or Facebook handle. You as the organisation want to communicate with your tenants (aka contacts), and your contacts want to be communicated to in a certain way.

Some will want email, some will want a letter, some want you to through something on their wall and it stick....who cares....the point is, you create contacts, with different means to get your message to them. That message could be the good old 'thank you for logging your repair', or 'you are a bit behind on your bills, get in touch'.

Maybe, which brings us to the point at hand you want to send a 'bulk' message out to all your contacts. The obvious example here is the quarterly rent statement, or a satisfaction survey. This is where bulk comms come in. You set up a 'Communication Definition', you then set up a 'Bulk Communication' and point to that Communication Definition. In the communication definition you link it to an entity type. This is the 'what' are you communicating about. It may be a Rent Account, or it may be a Repair Works Order, but you are communicating about a 'thing'....an entity.

How do I choose who to send it to?

Well, if its a one off, you would simply create your definition as an 'adhoc' definition, such that when you are looking a Rent Account in our example, you can choose Adhoc Communication from the object menu, and proceed to send one letter/email/text. But if you want to send to many, ie in bulk, you have to configure that in the Recipients screen below:

So you can firstly use the Cx Statement Builder. It takes a bit of getting used to, and the UI could be better, but its actually pretty functional in what can be achieved.

In this example, if we ran it, we would get a communications to all Rent Accounts where the Account Type is Rental, and that is on a current agreement.

Importantly, we don't actually care who the comm goes to here. We let the Recipient Rule associated with the Communication Definition decide. The bulk comm handles 'what' needs to be communicated about. The recipient rule might say 'Rent Agreement Holder' and so we just need to pick out which Rent Agreements we care about.

Why do we need Stored Procedures?

The inbuilt statement builder can only ever do so much....it can't handle complex logic. And so this is where we hook in to a store procedure. A stored procedure is a small block of sql code designed to return a specific output. We can use the stored procedure to return a list based on more complex logic such as those I've written recently, including:

• I would like to send a satisfaction survey to all tenants who had a repair done by contractor A, completed in the last 24 hours, where we've not sent a satisfaction survey already. And I'd like to do it by email.

• As above, but this time I'd like to send it by letter but only where I haven't sent one by email.

• I'd like to email all contacts who haven't logged on to the tenant portal in the last 3 months. I want just one letter. Of that, I only want one per day per customer.

• I'd like to email all contacts who havn't logged on to the tenant portal in the last 3 months.

Hopefully you can see how the use cases are endless.

Enough talk already....How do we do it?

In the case of bulk communications, we very specifically need to return a table, with one column call EntityId, and in it a list of the database IDs that you want to write to (about).

What's equally important is the communication definition 'Entity Type'. In this example the entity type is Rent Account, so any stored procedure is going to need to return a list of Rent Account IDs.

So to create a stored procedure that mirrors the one above in the statement builder where we return 'Current Rental Account' types, it would be achieved with the below stored procedure:

Note we have to cater for the XmlCriteria object passed in. You wouldn't typically need to use this, so I'll save this for a future post, but it has to be there or you'll get that ever familiar 'An error has occurred'.

I'll repeat the important bit, you have to return a column called 'EntityId'.

You could debate all day long whether the 'distinct' should be there. It's either good practice, or lazy, but ultimately, we only want to return one account once in this example. There's every chance due to data, or your code that you end up returning one row more than once, and so adding it in is generally a good fail safe. It shouldn't be used to mask poor T-SQL however.

Remember the actual Ids you return have to be the Entity that is linked to the comm....so if you were writing about Repair Works Orders, you'd need to return OrderId:

That's a wrap

So there we have it, in just a few minutes we've covered what bulk comms are and why you might use them, some good use cases for stored procedures, and how they actually need to look. I've included some basic skeleton code which you can take and expand on. I'd love to hear some of your case studies where you use bulk comm SPs....the more complex the better!

Granted this might not tickle your tastebuds, but in a world where data is everything, being able to not just use it, but really harness it to drive functionality is that competitive edge - better service, more efficient, reduced errors. Keep an eye out in the blog for some equally exciting topics!

usp_MySite_Bulk_RentStatements.txt

Download TXT • 1KB