Previous Function for Multiple Records 1

[betachristopher]

I have seen several posts similar to this, but I haven't found the solution for what I am specifically trying to do.

I have a report that lists data from patient visits. The visit name is one of the variables. Sometimes a patient repeats a visit. These are recorded as REPEAT in the {table.visit}. The client does not want to see REPEAT, they want to see the visit name that was repeated, and have another column that counts the number of times they came in for that visit. I am able to get the count with a running total, that's not a problem.

Below is the formula I have started with. The problem is when there are two consecutive REPEAT visits. If the previous record is also a REPEAT, I need it to look at the previous record to that.

IF {TABLE.VISIT} = "REPEAT" THEN PREVIOUS({TABLE.VISIT}) ELSE {TABLE.VISIT}

Any help is greatly apprecitated.

Using Crystal Reports XI with data stored on SQL server.

 

[lbass]

Please show some sample data and identify the fields. If there is a unique idea for each time someone comes in, please include that in your sample data.

-LB

 

[betachristopher]

Below is a data set for one patient. The second record is also stored in the table as REPEAT, but the formula was able to reference the visit from the previous record.

PAT# VISIT REPEAT VISIT_DATE DRUG SCREEN RESULT
9999 VISIT1 0 Jan 19, 2007 POSITIVE
9999 VISIT1 1 Feb 13, 2007 POSITIVE
9999 REPEAT 2 Mar 22, 2007 NEGATIVE

Thank you in advance.

 

[betachristopher]

I should also note that in this case the first record for this patient was referrenced, but this will not always be the case. After the VISIT1 there could be a VISIT2, and REPEAT visits could follow.

 

[lbass]

Replace Visit with a formula like this:

Whileprintingrecords;
stringvar visit;
if {table.visit} startswith "VISIT" THEN
visit := {table.visit} else
visit := visit;

If a patient's record always starts with a visit (not Repeat), then this should be all you have to do.

-LB

 

[betachristopher]

Thank you LB. This worked perfectly! I've not used variable declarations before, but looking at it now, I am thinking of a number of things I can use them for. Thank you so much.