| Issue Number | 3093 |
|---|---|
| Summary | To find InscopeProtocols that have specific External Site IDs in the External Sites block |
| Created | 2010-02-19 12:33:10 |
| Issue Type | Improvement |
| Submitted By | Grama, Lakshmi (NIH/NCI) [E] |
| Assigned To | Kline, Bob (NIH/NCI) [C] |
| Status | Closed |
| Resolved | 2010-04-29 13:41:15 |
| Resolution | Fixed |
| Path | /home/bkline/backups/jira/ocecdr/issue.107421 |
BZISSUE::4769
BZDATETIME::2010-02-19 12:33:10
BZCREATOR::Lakshmi Grama
BZASSIGNEE::Bob Kline
BZQACONTACT::William Osei-Poku
Don't know if this is better done as an adhoc query or as a stand alone report, but I need to get a report where I get a list of trials, and the denormalized ExternalSiteOrgID value (ie the Org Name)for the following External Site IDs (and we should be able to change these IDs as we need)- NJ011, NJ066, NJ063) . Other criteria for the report
Trial should not be blocked
Trial should have CurrentProtocolStatus of Active, Approved-not yet
active, Temporarily closed
This is high priority.
BZDATETIME::2010-02-19 12:55:58
BZCOMMENTOR::Lakshmi Grama
BZCOMMENT::1
I need another field in the report that shows the External Site Name (as in the ExternalSiteName field).
BZDATETIME::2010-02-19 13:01:06
BZCOMMENTOR::Bob Kline
BZCOMMENT::2
Should I reindex the InScopeProtocols to add the ExternalID element to the query_term table (it's not included at present), or (assuming the urgency precludes waiting for the reindexing) should I look up the values in the external_map table (only two of the three values you gave are mapped there)?
BZDATETIME::2010-02-19 13:04:32
BZCOMMENTOR::Lakshmi Grama
BZCOMMENT::3
For now, let's not reindex. I need the report. You can add the field for indexing later.
BZDATETIME::2010-02-19 13:05:40
BZCOMMENTOR::Lakshmi Grama
BZCOMMENT::4
Sorry - I don't want the mapped value. There is a problem with the mapping that I need to adjust.
BZDATETIME::2010-02-19 13:12:10
BZCOMMENTOR::Bob Kline
BZCOMMENT::5
Not 100% sure I understand the last two comments completely, but I assume you want me to hold off doing anything until the mapping problem is resolved. I would guess that at least part of the mapping problem has to do with the fact that the external mapping table only finds two of the three IDs you gave in the original report request.
BZDATETIME::2010-02-19 13:18:36
BZCOMMENTOR::Lakshmi Grama
BZCOMMENT::6
What I want to know is what trials have the specific values in the ExternalSiteID element because there is a problem with the mapping. Two values are mapped to the same and that is incorrect, I think.
BZDATETIME::2010-02-19 14:56:26
BZCOMMENTOR::Bob Kline
BZCOMMENT::7
Well, now I know why we didn't have the ExternalID element indexed in the query_term table. We apparently had decided not to have the site import service store anything in that element. (I'll go back and dig through the issues to see if I can find any discussion of why if you think that's worth while; I don't recall off the top of my head.) The best I can think of is to crawl through all of the site information we've pulled in from all of the services we communicate with for site/status updates and identify the trials that way. If you want I could then try and match up which sites we mapped the external IDs to (by position, I guess?). Let me know how you want me to proceed.
BZDATETIME::2010-02-22 10:50:20
BZCOMMENTOR::Bob Kline
BZCOMMENT::8
Another modification made per phone conversation with Lakshmi: restrict report to external IDs with the type "CTEP_institution_code." The query has been implemented on Bach with the name "Request 4769." The reindexing has not yet completed, but the report is showing enough rows that you should be able to look at the report and let me know if you want any further changes made to it. I'll let you know when the reindexing has finished.
BZDATETIME::2010-02-22 12:33:32
BZCOMMENTOR::Bob Kline
BZCOMMENT::9
(In reply to comment #8)
> I'll let you know when the reindexing has finished.
Done.
BZDATETIME::2010-02-22 17:06:22
BZCOMMENTOR::Lakshmi Grama
BZCOMMENT::10
Can you add the ExternalSiteID value as the third column in the results
BZDATETIME::2010-02-23 08:07:05
BZCOMMENTOR::Bob Kline
BZCOMMENT::11
(In reply to comment #10)
> Can you add the ExternalSiteID value as the third column in the
results
Done.
BZDATETIME::2010-04-29 13:41:15
BZCOMMENTOR::Bob Kline
BZCOMMENT::12
Closed at status meeting per LG.
Elapsed: 0:00:00.001011