Maximizing Communication Impact with Information Field Responses

In this post I show you how you can pull information field responses in to Communications

Remove manual entry?, improve communication quality? reduce error? be more efficient?

It’s been a while since I posted on here. 2024 didn’t start quite as I expected but we’re going to finish on a high.

As a family we were incredibly lucky to not lose anyone to Covid, and in our small metaphorical and literal bubble, life was simpler. I had been grounded by BoJo and his chums. This did flip the way I work from being fully on site to being fully remote, and it’s never really changed.

One of the most rewarding aspects of consultancy for me has never been my customers paying their invoices at the end of the month, but the opportunities that being around users presented. Such as walking through an office to do a workshop on x, but being able to say ‘did you know you can do that?’ on a very separate subject.

I will always remember the day when a Project Manager at a large London Borough performed a Paste Transpose in Excel in front of me during a workshop. As a self confessed expert in Excel who thought he knew it all, I was elated. Blew my mind! Thank you Nafisa! I almost offered to work for free that day….almost! 😀

This is why days like the Civica Cx PEG (User group) are superb. You get to go out for the day, meet familiar faces and some new, learn about what others are doing with the product and of course pay a small fortune in railway station coffees.

And so this brings me to the topic in question. A CR was logged with Civica to allow you to pull Information Fields through to a Communication. Terminology aside, what this means is that when you start a case (a workflow) you capture snippets of information, often from the tenant. Now it is often useful or necessary to pull that information through on to a letter or email that you then send on to the tenant or other parties. I recently set up a damp and mould case for a Scottish LA and they captured things such as which rooms it’s in, who lives there, where you do dry your clothes etc. This information could be handy to be snapshot on to a ‘ thank you for reporting…’ email as a record back to the tenant of what was done and said. The use cases are endless.

It really doesn’t seem like a lot to ask for the system to do this… the lowest rung on Mazlows hierarchy even, but it doesn’t do it. I’m sure if Civica had a hundred developers sat around doing nothing, it would have been done by now, but equally, changes have to go through the release schedule, so even if it were to get done, for most sites, it will be at least 2 years until you actually get it anyway.

Alas, this request did not get a lot of love from the group, not because it’s not worthwhile, but because it would have meant ditching some more desirable changes. It will come in eventually.

Anyone who knows me will know I love SQL, and stored procedures give you an amazing level of extensibility. I was asked this week if an Alert stored procedure could look at another database…yes…absolutely, why haven't you done it already!? I’ve done similar to create alerts for users who have not recently logged in to a tenant portal. Another example they would be prime for is an alert to show in Cx the status of UC Verification in another system.

Back on to the topic of information fields…

You can create a stored procedure that returns one information field easily enough - to use on your comms (letters/emails/texts) . And you can create many of those. Once you’ve done the first it’s a 10 minute job to replicate. But that means going to your database person, maybe some spec’ing etc.

But did you know you can also create one beautiful single stored procedure that will return ANY information field?

I’m very honest about Cx. Some things are great, some things are poor, like any system I guess (except SQL! :-) ) When it comes to stored procedures (which are amazing) one brilliant thing Civica included is to pass in to the procedure where it was called from. So, we can set up multiple conditional data tags, but each calling the same single procedure. Each time, we use the name of the conditional data tag as a way to determine the field to return. So, create one procedure and you can easily just add more information fields in the front end. As and when required.

So, assuming we have a Case setup, and against that case, you have some tasks. and on those tasks you have a task with a few information fields like this:

Inside each one, you will need to set an appropriate Data Tag Name (like you do already of course!).

In this one I've called the Data Tag DetailsOfRequest. This is how we know where to go and get the information from.

There's nothing else to do on the Case. We now need to add Conditional Tags (Civica - next time just call them Custom Tags) to the Communication Definition but firstly, get your DBA or whoever to apply this to your Cx database. This is the clever stored procedure which needs to be there first:

usp_iStride_DataTag_InformationField.txt

Download TXT • 15KB

Then, against a 'Case' Entity based communication definition, create your conditional tags where you want them, as shown below:

When you add them, make sure to set the Tag Name to match the Tag Name from the information field and set the stored procedure to be usp_iStride_DataTag_InformationField for every tag.

Finally, in your letter, email or text definition, add your tags like any other:

and that's it, generate your comms and they pull through the information field answers:

There’s certain assumptions you need to make to avoid one-to-many situations as Cases can have many tasks.

You probably need to assume firstly that cancelled or bypassed tasks can be ignored. If a task occurs more than once on the same case you probably only want to return the latest information field. If you change the name, add or remove an information field, expect it to not magically find the new one. Cx couldn’t do that either. Config changes should always be impact assessed.

There is also a maximum length of 256 characters that a conditional tag will return but practically, forms are more appropriate if you're capturing that length data anyway.

Do let me know if you find this useful or if you’d like to extend the functionality further. Similarly, I love a puzzle, so if you're stuck with anything, tell me and i'll see if I know the answer.....trickier the better!

Also if you’d like to see posts on any other topics, do get in touch.