inconsistent data in ost_ticket__cdata

cwatson

osTicket 1.16.3
PHP version 8.0.21
Ubuntu 20.04
Staff side of the UI

We are running some reports that include data from custom lists in osTicket but for some reason, in the custom list field, some rows contain just a number, while others are of the format number,description. This is even for the same list entry:

How does the __cdata field work and what should it contain for these list references? Should I be referencing some other table using the number part of the field content as a foreign key reference or will fully populating those 'partial fields' with the full description fix this and it wont recurr.

It's been going on some time and it seems also to play havoc at time with custom queries within the application so any advice you can give would be very welcome.

KevinTheJedi

cwatson

Maybe the older records are using and older format whereas the ones showing the correct data is using the new format? Try updating the records on those old entries and see if they show the correct format.

Cheers.

cwatson

Thanks Kevin,

I'll update the records with the complete fields (which will work in reporting of course) and we'll keep an eye on future reports to see if we see the problem recur.

Thanks for the quick response, Chris.

cwatson

Hi Kevin,

I re-read your comment so took an existing ticket and changed the value selection, then put it back again but it was still not as I expected.

A can of course fix up the ost_ticket__cdata table but I think the problem is still there.

Chris.

KevinTheJedi

cwatson

That is interesting. Is it two different fields?

Cheers.

cwatson

Hi Kevin,

I did a little more digging and it does seem that tickets created after July 1 2022 this year in our DB have only the number and not the format <number>,<description>. Not sure but we may have made the upgared to 1.16 from 1.15 at around that time (?!?)

Did something change in the past where now you are only storing the Id of the list entry in the ost_ticket__cdata<custom field based on a custom list>? In which case, what would be the appropriate database join to perform to get the list description from the Id held in the __cdata column?

I can probably figure it out but it would be good to know if you did chjange something in terms of that column so that I know I will find a 'correct' fix for my query.

Thoughts?

Thanks, Chris.

KevinTheJedi

cwatson

I'm not too sure, you'd have to look at the change logs. I remember we fixed issues with Choices fields but not sure about List fields.

You'd need to reference the ERDs on our documentation to see if it shows those relationships, if not, you'd just have to look at the tables yourself. But you'd essentially need to lookup the field the data is meant for, lookup the associated list in the list table, lookup the list items in the list_items table, and then lookup the appropriate list item to get the text value.

Cheers.

cwatson

Ive attached an Excel file with SQL statement and results as that might help clarify?

listitemissue.zip

62kB