Complicated Problem...Need Your Expertise

[Soleil525]

My report contains three tables: Claimant, Immunity and SurvTest. Each patient may have multiple immunity Surveillance shots (each shot represents a record). So in the SurvTest table, patient A might have the following record:

Protocol Type Vaccine Actual_Date Next F/U Date
Immune Vacc TB 01/01/2001 02/01/2001
2nd Shot Vacc TB 02/01/2001 03/ 01/2001
3rd Shot Vacc TB

At runtime, the user will enter a date range base on the Next F/U Date field to select the record. Let say that the user enters the Next F/U Date range of 02/28/2001 to 03/31/2001, I want to show the latest record, which is Third Shot Vacc TB base on the following conditions:
If the Actual_Date is empty(this means that the patient has not come in for the Third shot) and the Next F/U Date is fall within the date range then display:
Protocol Type Vaccine Next F/U Date
Third Shot Vacc TB 03/01/2001(how do I show this date since it is stored in the Second Shot record).
So basically, I want Crystal to check the Actual_Date field of the latest record (Third Shot), if the Actual_Date field is null, then look at the latest Next F/U Date, which is 03/01/2001(store in the Second Shot) and if this date is within the date range then display the latest record with this date 03/01/2001. This report was originally created in Visual Fox Pro and my user want to re-create this in Crystal. I’m using CR 8.5 and Oracle backend. Your help is greatly appreciated.

Bryan

 

[kmcclung]

Are the fields sorted so that the latest record is always last, as shown in your example? If so, you may try creating a formula like this:

if isnull({datefield}) then
previous({datefield})
else
{datefield}

This would check if the datefield (Next F/U Date) is null. If it is, then use the previous datefield and if not, use the datefield associated with that record.

Hope this helps!!!

 

[Soleil525]

Thank you for the respond. Yes, the fields are sorted just as you say. I enter the statement "IsNull({SurvTest.Actual_Date}) into the Record Selection Formula Editor. This will give me the latest records because only the latest records will have a null value in the Actual_Date field. To display the Next_F/U_Date I create a formula as you suggested: if IsNull({SurvTest.Actual_Date}) then Previous({Next_F/U_Date}) I place this formula on the report, nothing show. For the example above, 03/01/2002 should have showed as the Next_F/U_Date for the record. Do you or anyone else have any other suggestion? Again, thank in advance for your help. Bryan

 

[kmcclung]

Is the date actually Null or is it blank? That may be why the condition isn't being met.

Let me know.

 

[Soleil525]

How do I check for both values. I know with string, I can write a statement like this to handle both:
If IsNull({Employee.Name}) or {Employee.Name} = ''(quotation)

Thanks

 

[kmcclung]

I believe you should be able to do the same thing with a date field. You may want to trying "browsing the data" to see if it displays any blanks (it will usually be the first item in the list). If there is a blank field, select it and click Paste to see how Crystal inserts the field.

Let me know if that works - I'm curious for future reference as well.
Kathleen

 

[Soleil525]

Kathleen,
There is no blank date on top of the list. So I think the Actual_Date field will either contains a date or Null value (no blank). I think to check for blank date you will have to write something like {datefield} = Date(0,0,0). Well, I have already try this, not working either. Any other suggestion? Thanks

 

[IdoMillet]

Bryan,

Try the following:

Join SurvTest to itself bason on
Next F/U date = Actual Date
(obviously, there should be other
join conditions such as same Patient).

Do this with an OUTER JOIN so that
Actual Date can be Null and the record
would survive.

Then add a condition that Actual_Date
(on the OUTER JOIN Side) is Indeed Null.

That should solve the whole thing in
one step.

Cheers,
- Ido ixm7@psu.edu

 

[kmcclung]

Is the field a true date type or datetime or something else?

 

[Soleil525]

Ido,
Currently, this is the join between the three tables:
C.Claimant_ID = I.Claimant_ID and
I.Immunity_ID = S.Immunity_ID

C = Claimant, I = Immunity, S = SurvTest

You suggest that I do this:

C.Claimant_ID = I.Claimant_ID and
I.Immunity_ID = S.Immunity_ID and
S.Actual_Date(+) = S1.Next_F/U_Date
Is this what you want me to try? Please confirm.

Kathleen,
The date field is a true date time field but I convert to date field for the report.

Please help me thank you all
Bryan

 

[kmcclung]

Can you run a query against your database to determine if you have any date fields that are either = '' or = ' '? Depending on how the date gets entered into the database, depends how it is stored. Ie. whether a blank value is passed, versus nothing is passed, or even a fictious field 1/1/99 or 0/0/00.

 

[Soleil525]

Kathleen,
when I query the DB for the ACTUAL_DATE = '' no row selected. However, if I say ACTUAL_DATE IS NULL, then I get the data back. This leads me to beleive that if the patient has not come in for the shot, the ACTUAL_DATE field contains a NULL value not BLANK. Is this what you want me to try?

Thanks,
Bryan

 

[IdoMillet]

Bryan,

In order to do what I suggested, you need a
slight change from:

C.Claimant_ID = I.Claimant_ID and
I.Immunity_ID = S.Immunity_ID and
S.Actual_Date(+) = S1.Next_F/U_Date

to

C.Claimant_ID = I.Claimant_ID and
I.Immunity_ID = S.Immunity_ID and
S.Next_F/U_Date outer join to S1.Actual_Date

this would allow records with Next_F/U
that doesn't match an actual date to
survive the join. Then you simply select only those records that DIDN'T find a match by
using

AND ISNull(S1.Actual_Date)

Cheers,
- Ido ixm7@psu.edu

 

[kmcclung]

Is the "Convert NULL Field to Default" checked under the Report Options?

Help description: "Selecting this check box forces the program to convert any null values to the database field default. Some databases treat a null as zero, some as a blank, and some as a special null value"

May have something to do with why your report isn't recognizing the null values.

And what is the "Convert Date-Time Field:" option set to?

 

[Soleil525]

Kathleen,
No. The "Convert NULL Field to Default" is off. I convert the date-time field to date only since I don't need to show the time.

Ido, I'll give it a try and post the result.

Thank You All,
Bryan

 

[Soleil525]

Ido,
This method is not working either...

 

[IdoMillet]

Send me an e-mail message with the full SQL statement.

You can copy and paste it from Database, Show SQL Query...

Cheers,
- Ido

ixm7@psu.edu

 

[Ngolem]

can I suggest another approach?
aliasing SurvTest into 2 tables SurvTest(2nd) is for the second shot F/U date and SurvTest(3rd) is for the test for a null Actual Date

You have tables linked as follows:

Claimant --> Immunity --> SurvTest(2nd) --> SurvTest(3rd)

with the the record select condition

{SurvTest2nd.Protocol} = "2nd Shot" and
{SurvTest2nd.next F/U Date} in {?reportDateRange} and
{SurvTest3rd.Protocol} = "3rd Shot" and
isnull({SurvTest3rd.Actual_Date})

I think the outter join isn't necessary since it sounds like the "3rd shot protocol record exists but the Actual_date is null.

of course other links are necessary to join the aliased tables on the basis of client and immunity

Jim

 

[IdoMillet]

You can't rely on 2nd shot and 3rd shot (they could very well be 1st and 2nd).

That's why you need an outer join
Claimant --> Immunity --> SurvTest --> SurvTest2

because the condition on the join between SurvTest and SurvTest2 is that
SurvTest.Next_F/U_Date = SurvTest2.Actual_Date

The idea is to find all those cases were such a match fails
Hence the condition of IsNull(SurvTest2.Actual_Date).

Cheers,
- Ido
ixm7@psu.edu

 

[Ngolem]

"because the condition on the join between SurvTest and SurvTest2 is that
SurvTest.Next_F/U_Date = SurvTest2.Actual_Date "

No...the two tables would not be joined on the basis of date. The tables would be joined on the same basis as was the join between Immunity and SurvTest2


Immunity Survtest2 Survtest3

Field 1 ---> Field 1 ----> Field 1
Field 2 ---> Field 2 ----> Field 2
Actual_date Actual_Date
next F/U Date next F/U Date

if the filtering of the dates takes place in the record select ie. {SurvTest2nd.next F/U Date} is filtered by the parameter input range and {SurvTest3rd.Actual_Date}
must be null

Jim