Comparing multiple detail records 1

[Dross]

Thanks to LBass the formula below works great. I can compare whatever record I want against another. Now the requirements changed and they want to compare multiple records. For instance I have records 1 and 4 comparing and they love it. Now they want 1 and 4, 2 and 5, 3 and 6... It is not a set number. I could have 10 records or 100 records. All must display.

So I currently display : Yesterday: 1.6 Today: 1.8

Now they want:
Recommended: 1.6 Current: 1.8
Recommended: 1.9 Current: 1.1
Recommended: 1.6 Current: 1.6
Recommended: 2.1 Current: 1.8

Thoughts?


//{@reset} for the client group header:
whileprintingrecords;
numbervar curr;
numbervar prev;
numbervar cnt;
if not inrepeatedgroupheader then (curr := 0;prev := 0;cnt := 0);

//{@accum} to be placed in the detail section and suppressed:
whileprintingrecords;
numbervar curr;
numbervar prev;
numbervar cnt := cnt + 1;
if cnt = 2 thencurr := {table.value};
if cnt = 3 thenprev := {table.value};

//{@result} to be placed in the client group footer:
whileprintingrecords;
numbervar curr;
numbervar prev;
"Yesterday's Value: "+totext(prev,0,"")+"Today's Value: "+totext(curr,0,"")

 

[lbass]

You have not provided the context for this.

-LB

 

[Dross]

You are correct, I made assumptions. The original thread is:

http://www.tek-tips.com/viewthread.cfm?qid=1651178

 

[lbass]

That doesn't really describe what the new desired situation is. I can't tell whether you want a rolling comparison of each record with the record three positions later, as your example shows: 1-4,2-5,3-6, or whether you are saying that it could be 2-3, 4-8, 5-7, i.e., any two values. How about showing sample detail data, too. It might help if you explained WHY they want to do this? What is the purpose of the report?

-LB

 

[Dross]

It will always be 3 positions so it will always be 1-4, 2-5, 3-6...

The report is to track medical dosage. so 1 value is what is recommended by the system and comparing against the actual dose administered by the nurses to see if they are using the system recommendations or still trying to dose medicine by the old method.

The data looks like this:

Patient Name 1.9 7/18/2011 10:04 AM
Patient Name 2.8 7/18/2011 10:04 AM
Patient Name 1.9 7/19/2011 11:04 PM
Patient Name 1.4 7/19/2011 11:04 AM
Patient Name 1.4 7/18/2011 10:04 AM
Patient Name 1.9 7/18/2011 10:04 AM
Patient Name 5.6 7/19/2011 11:04 PM
Patient Name 1.4 7/19/2011 11:04 AM

So I am comparing the last recommended dose to the current dose given but due to how the data is stored I have to do the record to record comparison.

 

[lbass]

So where would the results be displayed in relation to the sample data you showed? Can you add that to the display please?

-LB

 

[Dross]

The report is grouped by Patient. I am hiding the details to stop any confusion. Below I am showing the detail rows and then the Group Footer, but eventually I only want to show the group Footer with all the comparisons.


Patient Name 1.9 7/18/2011 10:04 AM
Patient Name 2.8 7/18/2011 10:04 AM
Patient Name 1.9 7/19/2011 11:04 PM
Patient Name 1.4 7/19/2011 11:04 AM
Patient Name 1.4 7/18/2011 10:04 AM
Patient Name 1.9 7/18/2011 10:04 AM
Patient Name 5.6 7/19/2011 11:04 PM
Patient Name 1.4 7/19/2011 11:04 AM

Patient Name Current Dose: 1.9 Recommended Dose: 1.4


I need the footer to look like:
Patient Name Current Dose: 1.9 Recommended Dose: 1.4
Patient Name Current Dose: 1.9 Recommended Dose: 1.9
Patient Name Current Dose: 1.4 Recommended Dose: 1.4

 

[lbass]

Well, that sequence is really 1-4,3-6,5-8. Please clarify whether there are two separate fields for dose: currentdose, recommendeddose. If there is only one field, what makes one instance and actual versus a recommended?

-LB

 

[Dross]

You are right, my head was spinning with the changes they asked for. Thanks for the catch, good to have a 2nd set of eyes.

There are 2 separate fields for current and recommended (they get documented separately in the system and that is why the detail rows show like they do).

So the Current does gets inserted into the DB with a lookup values of 22255 and Recommended dose gets documented with a value of 22256.

That is how I can tell them apart.

 

[lbass]

I thought I was following this, but I see that your datetimes are not in a sorted order, so what field are you sorting by to determine the order and how does it display? Please add this to the sample data.

-LB

 

[Dross]

Sorry, I just jammed them up there as a sample. They are sorted in date order and then the lookup so it is always date and then current and reccomended in that order.

 

[lbass]

Please fix the sample and desired results--so we are both on the same page.

-LB

 

[Dross]

I think this makes a bit more sense. Thanks for your help on this.


Patient Name 1.9 7/18/2011 10:04 AM
Patient Name 2.8 7/18/2011 10:04 AM
Patient Name 1.9 7/19/2011 11:04 PM
Patient Name 1.4 7/19/2011 11:04 PM
Patient Name 1.4 7/20/2011 8:04 AM
Patient Name 1.9 7/20/2011 8:04 AM
Patient Name 5.6 7/21/2011 6:04 PM
Patient Name 1.4 7/21/2011 6:04 PM

Patient Name Current Dose: 1.9 Recommended Dose: 1.4 <-- GROUP FOOTER

I need the footer to look like:

Patient Name Current Dose: 1.9 Recommended Dose: 1.4
Patient Name Current Dose: 1.9 Recommended Dose: 1.9
Patient Name Current Dose: 1.4 Recommended Dose: 1.4

 

[lbass]

I'm still uncomfortable with basing this simply on the order of records. There must be some other factor that ties the two records together that are being compared, e.g., is it the particular medication? Otherwise this just doesn't make sense to me.

-LB

 

[Dross]

There is no other factor. The medication is the same. All I have is the order of records and that is why I am hung up. The nurse puts some parameters into an application and gives them the dosage. At that time it is now the recommended dose. The nurse puts in the latest current dose which is what should be given next time. It is how this application works and the order of records will always be the same.

 

[lbass]

I just don't get the logic here, so I'm going to second guess you one more time--sorry in advance. But if you are comparing recommended and current, why wouldn't the comparison be between the last recommended dose and the next actual dose, i.e.

R: 2.8 A: 1.9 record 2-3
R: 1.4 A: 1.4 record 4-5
R: 1.9 A: 5.6 record 6-7

Also, what happens to records that don't have a corresponding one--what do you want to see?

-LB

 

[Dross]

It's not a problem, I appreciate your help. The comparison from records 1-2, 3-4...etc. will not work due to the way the application works (apparently reporting off the data was a second thought). Once the recommended dose is calculated from lab results, a record is inserted AND a 2nd record is inserted marking it current dose. The nurse then can adjust the dose or not once administered. So the only way to get an actual comparison is to take the recommended dose and skip to the dose which was actually given in this pattern. The pattern will always be the same.

I am not sure what you are asking on the 2nd question. I think you are asking if I have the sequence 1-4,3-6,5-8 but no 8? Then the display would stop at the 3-6 comparison. If it brought back nulls or just blank character strings I could suppress.

 

[lbass]

Try the following:

//{@accum} to be placed in the detail section and suppressed:
whileprintingrecords;
numbervar cnt := cnt + 1;
numbervar curr;
numbervar prev;
numbervar rec;
stringvar array x;
if remainder(cnt,2) <>0 then
prev := curr;
if cnt <= 12 then (
if remainder(cnt,2) <> 0 then
curr := {table.currentdose} else
curr := curr;
if remainder(cnt,2) = 0 then
rec := {table.recommendeddose} else
rec := rec
;
redim preserve x[cnt];
if prev<>0 then
x[cnt] := "Current Dose "+totext(prev,0,"")+" Recommended Dose "+ totext(rec,0,"") else
x[cnt] := "";
);

//{@display} to be placed in the group footer and formatted to "can grow":
whileprintingrecords;
stringvar array x;
local numbervar i;
stringvar y := "";
for i := 2 to ubound(x) step 2 do(
y := y +
(
if x <> "" then
x+ chr(13) else
x
));
y

Add a reset formula to your group header:
whileprintingrecords;
numbervar cnt;
numbervar curr;
numbervar prev;
numbervar rec;
stringvar array x;
if not inrepeatedgroupheader then (
cnt := 0;
curr := 0;
prev := 0;
numbervar rec := 0;
stringvar array x := "";
0
);

-LB

 

[lbass]

Oops. The line that says:

if cnt <= 12 then (

...should be:


if cnt <= count({table.patient},{table.patient}) then (


...assuming there is a group on the patient field. The "12" in my original post was just a plug in a mockup I used.

-LB

 

[Dross]

I can honestly say I have never seen a formula set like this in the years I have been doing this. I still need to do some testing but it looks like it will work. Thanks for all your help! This is a post worth keeping for later.