Storing First Values in a Group

[BettyJ]

I need to find the first (and last - I got last) exam date of patient.

Office Visit Date Obs Value Exam Date

1/14/2012
2/24/2012 Normal 2/24/2012
3/20/2012
4/15/2012
5/17/2012 Normal 5/17/2012
6/18/2012

Can someone please advise on how this can be done? Thank you in advance.
BJ

 

[lbass]

Either insert a minimum and a maximum on the exam date at the patient group level, or write formulas like this:

minimum({table.examdate},{table.patientID}) //or maximum

-LB

 

[Madawc]

Another method is to group by patient and order by date. Suppress details, show the first in the header and the last in the footer. Or the other way round, sorts can ascend or decend. And header or footer can be suppressed if the two dates are the same.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[BettyJ]

Thank you for your replies.

Let me explain the scenario more. This is patient observation values (eg. weight) recorded during their office visits. Group 1 is Patient ID and group 2 is Date of Office Visit.

The dates shown above are calculated using a formula. Sometimes the observation is not recorded. Hence obs. date is null. I need both dates to be in the group footer.

lbass, since it is a formula, I could not insert a minimum. Also it is not coming directly from the table as I mentioned earlier. Is there any other way to do it (using formula)?

The problem is the null values. Is there a way I can store the first value?

 

[lbass]

Please show the content of all formulas.

-LB

 

[BettyJ]

Thank you lbass.

Report selection formula
{RPTOBS.HDID}in [61,2609]
RPTOBS is a view which contains observation value, date of observation, etc..
61 - Weight
2609 - Cervix Exam
-----------------------------------------------------------
@Observations
whileprintingrecords;
stringVar cervix_exam;
IF {RPTOBS.HDID} = 2609 and {RPTOBS.OBSDATE}={@DateOfficeVisit}
THEN cervix_exam:= Trim({RPTOBS.OBSVALUE})
ELSE cervix_exam:= cervix_exam;

stringVar cervix_exam_date;
IF {RPTOBS.HDID} = 2609 and {RPTOBS.OBSDATE}={@DateOfficeVisit}
THEN cervix_exam_date:= totext({RPTOBS.OBSDATE})
ELSE cervix_exam_date:= cervix_exam_date;
....
....
------------------------------------------------------------------
To display date in the group footer,
@Date_CervixExam
WhilePrintingRecords;
stringVar cervix_exam_date;
IF cervix_exam_date= ""
THEN ""
else
cervix_exam_date:=cervix_exam_date;

 

[lbass]

I dont' think you need variables for this. If you only need the dates in the group footer, then you can simply insert a minimum and a maximum on the following formula:

//{@obsdate}:
IF {RPTOBS.HDID} = 2609 and
{RPTOBS.OBSDATE}={@DateOfficeVisit} then
{RPTOBS.OBSDATE} else
todate({@null})

...where {@null} is a new formula that you open and save without entering anything.

This assumes that {RPTOBS.OBSDATE} is a date-type field, not a string.

If you need the exam values in the group footer, also, you could use a variable. Or, if you only want to display the rows for the first and last date in the detail section, you could use a group selection formula. Let me know which way you need to go, if the simple first and last date is not sufficient.

-LB

 

[BettyJ]

Thankyou lbass. Your @Null formula is definitely going to help me in the future. I have always used variables.

I was able to get both values at the footer by using a dummy date
At GH1
@initVar
datevar date_first_cerv_ex:=date("09/09/9999");

At GF1
@Date_CervEx_First
whileprintingrecords;
datevar date_first_cerv_ex;
if {@Date_CervixExam}<>"" then
if date_first_cerv_ex>date({@Date_CervixExam}) then
date_first_cerv_ex:=date({@Date_CervixExam});
date_first_cerv_ex;

I would like to know if you have any other suggestions on how this can be done.

I am also getting other observations like weight, BP in the Observations formula which is placed in the Details section.Group 2 is date of office visit. So in GF2, i need the first and last exam dates.

I just need the first and last values which is not null.
From my data in the first post, the results i need are
first 2/24/2012 Normal
last 5/17/2012 Normal

Also if only it is recorded once then both first and last will be the same.