Seeking source table location for comments saved on incomplete tasks

[jpray]

Version: Windchill 13.0

Use Case: For a change status monitoring system based on SQL reports against the oracle source tables, we want to accommodate a specific department's practice of tracking in-progress efforts by saving and updating comments on open tasks.

Description:

As https://www.ptc.com/en/support/article/CS153902 makes clear, WFVOTINGEVENTAUDIT stores user comments for completed workitems, but it is not populated before they're complete. However, users can enter comments on a task and save it without completing it, and there is no alternate source mentioned in this article for comments on running tasks.

I have a complete change task tracking setup already built, and pulling comments on completed tasks is no problem.  I just can't figure out where the comments are if they're saved on a task with no entry in the voting event audit table.  My efforts to trace known comment strings in the source tables have come up empty, but our database has some grant issues that prevent my role from seeing tables added around Windchill 11.0, so either these are stored in a table I just can't see or I've been making some mistake in my broad search attempts.  Any help or pointers would be appreciated.

[TDT]

Hi @jpray,

When comments are added and the task is saved without being completed, the work item is updated.

However, when I search the entire database for the saved comments, I don’t get any results.
I suggest enabling a few listeners, such as Update and Insert, and checking the logs to see which classes are updating records when you only save the comments.
I’m not sure whether this will help, but it’s worth giving it a try.

[jpray]

This is what I've found, too, re: the workitem records being updated.  I had not considered the direction of listeners; it's outside my privileges, but if nobody else ends up knowing the answer, I can probably engage some help to try that.  Thanks!

[d_graham]

Is this what you're looking for.

I created a test workflow and entered a value in the comments field.  I then hit the "Save" button.

Next, I ran code that prints out the saved comment as it is stored in the dB table/column.

[TDT]

Hi @d_graham,

Have you used the following API to retrieve the comments?

WorkItem workItem= new WorkItem();

workItem.getContext().getTaskComments();

[jpray]

This is the type of information I'm seeking, yes, but I need to retrieve it via SQL from the source tables.  It's the database structure I need to understand better.  Thanks!

[TDT]

Hi @jpray 
I don’t think that comments saved without completing the task are persisted directly in a separate column.
When I checked the API, I could see that this information is being stored in BLOBs.

[jpray · Accepted solution]

It's not in a BLOB, but this got me pointed in the right direction to determine that the comments ARE on the WORKITEM object -- stored in the Base64-encoded data in the CONTEXT column along with some other info.  That explains why they weren't searchable.  I confirmed by tracking down a WORKITEM entry for an open comment known to have saved comments, and decoded the CONTEXT entry separately.  The comment was the first portion of the data, too.  I will have to do some testing to see if this is always the case, but that's the mystery solved.  Thanks!