Missing documents report

[GMAN33]

Hi All

Tough question to explain but will try

I have a group of patients that participate in a clinical trial. They complete a case report form (CRF) that contains information in regards to the trial.

As the programmer, I have created the DB that each page of the CRF is called PAGE1, PAGE2, etc...there are 50 of these pages.

When we receive the CRF pages in house, we enter in the data and the page is marked internally within the DB as received.

I need to create a report on what is missing and has not been received. This is an Oracle DB

What would be the best approach to doing this? I have the tell the report that I will be getting PAGE1 thru 50 at some point but I can't get that from the DB because we don't keep page tracking.

I am using CR10 Pro

Thanks

 

[lbass]

Do you have a field that displays the document page number? If so, is it a string that appears like "PAGE1", or is it a number?

-LB

 

[GMAN33]

It is a string but there is also a unique page ID that is created that is available in both tables

One table holds the PAGES (CRF book) and the other table contains the pages received. I was going to try a join with != but not sure if that would work

I have them grouped by patient ID

Thanks for the quick response

 

[lbass]

Use a left join FROM the CRF book table to the table (table2) that contains the pages received. Then use a record selection formula of:

isnull({table2.pagereceived})

-LB

 

[GMAN33]

Thank you LB for the suggestion. For some reason it is giving me no results

I have STUDIES_TABLE-->RECEIVED_PAGES_TABLE<---PAGES_TABLE in addition to the forumula

Will have to try different combinations

Thanks again

 

[lbass]

You can't have two tables pointing to the same table. Please explain how the studies table fits in, i.e., what fields in the studies table are you using?

-LB

 

[GMAN33]

You always have to start out with the studies table because this is where you select what study you want to use. There is a Study ID that is in all three tables.

 

[GMAN33]

There are two ID fields that are related to each separate page in both the RECEIVED_PAGES and the PAGES_TABLE. When a page is received it is given another field called RECEIVED_PAGE_ID.

I would like to have a listing of all of the pages that are expected and have a checkmark next to each of the pages received. This would be done for each patient. But since this RECEIVED_PAGE_ID is not created until it actually receives a page, I would think that maybe you have to have a variable in the report that states...PAGE01 is expected, PAGE2 is expected...etc...just not sure how to do this in CR

 

[lbass]

When you respond, please use the convention {table.field} to identify your tables/fields.

Try this linking:

studies->equal join->pages->left outer join->received pages

Then use the record selection formula I mentioned earlier.

I'm having Internet connex problems, so may not be able to respond immediatly.

-LB

 

[GMAN33]

Thanks LB but still giving me trouble.

Could I create another container field that states something like

If table1ID <> table2ID then table1ID

Not sure how this would be written...thank you for your continued patience

 

[GMAN33]

Here is what I would like to see is the Not Received column

CRF BOOK RECEIVED PAGES NOT RECEIVED
PAGE1 PAGE1
PAGE2 PAGE2
PAGE3 PAGE3
PAGE4 PAGE4
PAGE5 PAGE5

The CRF book is what they are supposed to get for each patient, the received pages is what has come into the system. It has the same PAGEID(TABLE2.PAGEID)that is present in the CRF Book table (TABLE1.PAGEID) but it also has TABLE2.RECPAGEID generated because it has come into the system. There is no TABLE1.RECPAGEID. The two tables are linked by the PAGEID field

Any suggestions or not clear?

 

[lbass]

You should use the table name instead of Table1 or Table2. Create a left outer join FROM {CRFBook.PageID} to {Table2.RecPageID}. Do not add any join to the page ID in Table2. Then you should be able to place {Table2.RecPageID} to get your Column 2 above, and then create a formula for the "Not Received" column:

if isnull({table2.RecPageID}) then {CRFBook.PageID}

-LB

 

[GMAN33]

Thanks LB...for some reason this does not want to work for me. I will have to keep trying it out...will keep you posted...thank you again for the very hard efforts