CDR Tickets

I've added Sharon as a watcher since she reported the issue.

It appears that there are multiple Spanish summaries claiming that they are a translations of two different English summaries. The SQL queries expect a one-to-one relationship between English/Spanish translations.
The program is trying to update the board name of the Spanish summary with the board name for the corresponding English summary. For this update to work the query must return only one English summary. That's what the message

Subquery returned more than 1 value.

is referring to.

The Spanish summaries with multiple English translations are:

• CDR62758 --> (711881 and 732670)

• CDR62864 --> (256695 and 706478)

• CDR62893 --> (256700 and 710434)

Once these data problems have been fixed the report will start working again.

Thanks, Volker. It's possible these summaries are reformats which would explain why there might be multiple versions in play. It looks like a couple of them are blocked (706478 and 710434).

I've added William to this issue.

William, could you please talk with Linda to determine if it would be feasible to remove the translation of element from any temp/in progress summaries and add it back in once she has blocked the earlier version? Please also remove the translation of element from the two blocked docs if possible. Thank you.

I've looked through an older issue for this report and it seems the blocked versions aren't causing a problem since those are excluded from the queries (I will double-check that). That would mean the one that's 'In process' is actually causing the problem.

It's confirmed, the query only looks at summaries with a status of 'Active'.

Please note, just because a summary title includes the word 'Blocked' doesn't mean its status is actually blocked.

Robin, is there anything else to do for this issue or can it be closed?

I'd like to keep this open until the data issue is resolved and we confirm that we can run the report successfully.

William, I think 711881 is the document that needs to be updated.

Sure. Will ask Linda to update it. Somehow,I missed your first message. Sorry.

Linda made the changes yesterday.

Now I'm puzzled. I can see the updates and I can see that the data (running a SQL query) looks good but the report is still failing.
I will have to look at this again.

In the English version of the summary CDR258005 (Chronic Lymphocytic Leukemia Treatment) there is an entry in the query_term and query_term_pub table stored that's incorrect.
The value for the summary board element should be 'PDQ Adult Treatment Editorial Board' but it's listed as 'PDQ Adult Treatment Editorial Board2013-10-28'.
This should get fixed by resaving the document and then the report will work (unless we find another data error).

I've been able to recreate the problem and I think I know how this happened:
On Oct 28th, 2013, the summary was updated. The user clicked the 'Current Date' macro to update the DateLastModified but the cursor was still on the Board field. The summary got saved and the query_term table gets updated during the save. When we pull up the summary again, the board name is pulled from the denormalized document title and not from the query_term entry. That's why one can't see the problem from looking at the document in XMetaL.

Anyway, Victoria re-saved the document and now the CRD report works again.

I will see if I can setup a better error message in order to find a problem like this more quickly if it comes up again.

I modified the following report:

• R12290: SummaryCRD.py

If the report will "detect" (detect as in "fail") that a summary is listing more than one unique board names the error message will now include the summaries causing the error.

This is how the change can be tested:
1) Modify a summary by entering bogus information for the board name value (but the correct CDR-ID)
2) Run the CRD report
--> the report will fail with the new error message

3) Resave the summary
4) Run the CRD report
--> the report will successfully run.

This is ready for testing on DEV.

Verified on DEV.

The first time I ran the report I saw the following error messages:

Failure Updating Spanish board names: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Summary with multiple board names exists: [[256754, 62843, u'PDQ Pediatric Treatment Editorial Board edited name'], [256754, 62843, u'Pediatric Treatment Board']]

Then, I ran it successfully (after resaving the summary). I wasn't expecting to see the Spanish error message, but both issues were resolved once I resaved the document so I think we're good. Thank you!

I agree that the 'Spanish' is a little misleading but the error does happen at the point when the board names for the Spanish summaries are being updated by extracting the board names of the corresponding English summaries.
By displaying the error message along with the board names for the summaries a user may recognize the incorrect string and may be able to fix the data right away. If I'm still needed for the 'fixing part' at least I won't have to search long to find the offender.

I get it. Thanks, Volker. Do you plan to promote this to QA, or should I mark it Awaiting Release now?

It will be moved to QA as part of our test to package up the CDR patch for CBIIT. However, if you would like to run another test before I can move it individually.

I don't think that's necessary. I'll mark it verified and add the awaiting release label.

Verified on DEV

I verified that this report is working on PROD.

It is difficult to verify the actual changes as I had only improved the error handling in case a data problem shows up again.