Have your Cx Contact reports stopped working recently?

If your reports started failing after 25.x, this short article might be for you.

Schema Stability is King

If you've worked with Civica for a long time, you'll know that getting things done quickly isn't exactly their forte — if it happens at all, you might add.

For someone like me, who has spent a career building data platforms, warehouses, and ETL solutions, unnecessary change is painful - stability is king. In the rest of my life, I love change — I get bored easily — but when you're building ETL solutions, stability is everything.

At a recent Civica User Group, I learned from another Civica customer they had stopped populating the CxLog table. Honestly, it was one of the most disappointing things I'd heard in a while. Not only can we struggle to get fixes and improvements delivered, but now, if you're not watching closely, things can quietly disappear too.

Thankfully, thanks to some good work from another site that shared my frustration, it looks like some level of self-diagnosis functionality may return.

My biggest issue with losing the logs was simple: they gave you a decent head start when triaging problems. Often enough, they'd point you directly at the offending area before you even had to start digging properly.

I've worked with Cx since the beginning.....it was just a little boy — an irritating, belligerent, obstructive little thing… but one with enormous potential if channeled correctly.

In all that time, I've known few existing features actually change or be removed entirely: Offline Forms. True to their name were literally taken offline. Shame because we could have consumed them from a mobile/app rather than having to render fields ourself.

And then there’s RentCalenderYearId… IYKYK. (And if you don't know: yes, "Calendar" is still spelled incorrectly.)

For the web developers reading this — who else has had Copilot “helpfully” correct PageSzie to PageSize, only for you to have to change it back because, once Civica makes a spelling mistake… it tends to stick...but you kinda want that.

The point is this: existing functionality and schema should remain stable unless there's a very very good reason to change it.

Well, that stability shifted again recently.

ContactAssociate.AssociationTypeId became ContactAssociate.AssociationTypeIds.

One tiny insignificant little s.

No longer are we dealing with a single integer value.

AssociationTypeIds is now a CSV string designed to support one contact being linked to another in multiple personas.

The result?

Reports stop refreshing. Integrations fail. Sometimes catastrophically. Sometimes silently, which is arguably worse.

You might first notice it in your daily ETL process with an error like this:

Executed as user: MYSQLSVR\MYINSTANCE.Warning: Null value is eliminated by an aggregate or other SET operation.[SQLSTATE 01003] (Message 8153)Invalid column name 'AssociationTypeId'.[SQLSTATE 42S22] (Error 207).The step failed.

Or perhaps you've been slightly naughty and hardcoded SQL directly into a report — in which case the report simply refuses to open with a similarly cryptic error.

Association Types are commonly used for things like:

• Next of Kin

• Power of Attorney

• Permission to Discuss

• And various other relationship types

Lets not be dramatic Jim, if you're only extracting data, this isn't the end of the world. The fix itself is relatively straightforward.

You might currently have something like this:

select 
    ca.ContactId,
    string_agg(cd.FormattedName, ', ') as [Associate Name],
    string_agg(l32.Description, ', ') as [Associate Type],
    string_agg(ca.Comments, ', ') as [Associate Comments]
from 
    CXWarehouse.dbo.ContactAssociate ca
join 
    CXWarehouse.dbo.ContactDetail cd 
    on ca.AssociatedContactId = cd.ContactId
left join CXWarehouse.dbo.Lookup l32 
    on ca.AssociationTypeId = l32.LookupReference 
    and l32.LookupTypeId = 32
where 
    ca.EffectiveFromDate <= getdate() 
    and (ca.EffectiveToDate >= getdate() or ca.EffectiveToDate is null)
    and cd.EffectiveFromDate <= getdate() 
    and (cd.EffectiveToDate >= getdate() or cd.EffectiveToDate is null)
group by 
    ca.ContactId

And you'll now need to update it to something like this:

select 
    ca.ContactId,
    string_agg(cd.FormattedName, ', ') as [Associate Name],
    string_agg(l32.Description, ', ') as [Associate Type],
    string_agg(ca.Comments, ', ') as [Associate Comments]
from CXWarehouse.dbo.ContactAssociate ca
join CXWarehouse.dbo.ContactDetail cd 
    on ca.AssociatedContactId = cd.ContactId
outer apply string_split(ca.AssociationTypeIds, ',') ats
left join CXWarehouse.dbo.Lookup l32 
    on try_convert(int, ats.value) = l32.LookupReference 
    and l32.LookupTypeId = 32
where 
    ca.EffectiveFromDate <= getdate() 
    and (ca.EffectiveToDate >= getdate() or ca.EffectiveToDate is null)
    and cd.EffectiveFromDate <= getdate() 
    and (cd.EffectiveToDate >= getdate() or cd.EffectiveToDate is null)
group by 
    ca.ContactId

The real frustration isn't the code change itself.

It's that these changes arrive with little visibility.

I went looking through the release notes afterwards and genuinely couldn't find anything that clearly flagged this. If you spotted it in advance, I'd love to know where.

Given the amount of staffing change over the years, I do wonder whether the people who once fought hard for platform stability are no longer there.

With so many live customer sites now depending on these systems, surely changes like this are impact-assessed and properly communicated before release?

First Offline Forms.

Then Logs.

Now this.

At this rate, maybe I'll finally get my CalenderYearId fixed after all.