# Find maximum value and print only associated record

I am trying to create a report where the latest contract amendment is found and displayed as a letter along with its associated work order. The problem is that I get multiple rows of the same data and the nuimber of rows appears to be assoicated with the number of amendments, but not always.

Below is the general groups of data:

Agreement    Work Order    Amendment
E00001                  1                       none
E00001                  2                       none
E00002                  1                       none
E00002                  1                           1
E00002                  2                        none
E00002                  2                           1
E00002                  2                           2
E00002                  2                           3
E00003                  1                        none

I have a formula that converts the amendment number of a letter (i.e., 1=A, 2=B, 3=C, etc)

I want the report to display:

E00001
WO# 1
WO# 2

E00002
WO# 1A
WO# 2C

E00003
WO# 1

What can I do to make this happen?

James0628

Group your report on Agreement and "Work Order", sort by Amendment, suppress the details, and output your WO# line in the "Work Order" group footer.  Then you will only see the last (highest) value for Amendment for each "Work Order".

James

Works perfect ... only now some of the adjacent rows are shaded where I had alternating rows shaded instead of using lines to separate rows. I moved the formula

If RecordNumber = 1 Then
crNoColor
Else If Remainder(RecordNumber,2)=0 Then
crSilver
Else
crNoColor

from the "detail" section to the "group footer" section. What do I need to change?
You need to count the records in the group footer or try using GroupNumber instead of RecordNumber

To count the groups
WhilePrintingRecords;
Global NumberVar MyRecordCount := 0;
''

WhilePrintingRecords;
Global NumberVar MyRecordCount;
MyRecordCount := MyRecordCount + 1;
''

In the group footer color formula
Global NumberVar MyRecordCount;
If MyRecordCount= 1 Then
crNoColor
Else If Remainder(MyRecordCount,2)=0 Then
crSilver
Else
crNoColor

or simpler
Global NumberVar MyRecordCount;
If Remainder(MyRecordCount,2)=0 Then
crSilver
Else
crNoColor

mlmcc

mimcc,

I put the formulas in the report header, gorup header for for work orders and group footer for work orders and no change. Here is an overview

Group Header #1 - Agreement Number
Group Header #2 - Work Order Number and formula above
Details - Supressed
Group Footer #2 - Fields to be displayed and formula above

Did I place the formulas in the wrong header/footer?

Thanks, RichardLH
Looks like the right places to me.

mlmcc

Should all formulas be in the report at the same time or one formula in a selected place? They all set Global NumberVar "MyRecordCount" and appear to cancel one another. Am I missing something, cause I still have some adjacent rows with the same shading and not alternating shading.
WHich sections show data?

How do you need it alternated?

mlmcc

The following sections show data:
RH
PH
GH1 - Agreement Number
GF2 - Work Order Number and Amendement Number and other associated data fields
I want to have alternate GF2 sections shaded
Put the counting formula in GH2

mlmcc
As you probably know, the reason that your original formula didn't work in the group footer is because you're checking the record number and there could be varying numbers of records between the group footers.

If you just want to "shade" alternate lines, I'd forget the counting and just turn a switch on and off.

Put a formula like the following in the report or page header to initialize your switch.  You probably want it in the page header to get the same on/off shading pattern on each page.

// Only output an empty string
""

The "" at the end is so that the formula doesn't actually produce any output, so you don't have to worry about suppressing it or anything.

// I check for "not shade" below because I just reversed shade
crSilver
Else
crNoColor

That should work.

James

Sorry I haven't responded for a while, I  am out of  town and not able to access Crystal Reports. I will be back the week of August 4 and will work to close this question.

Thanks.
Richard

James,

I like you idea of not counting and just alternating the shading.

I followed you example above and put the page header formula and group footer (where the data is) formula in and no shading shows up. Am I missing something? No erros detected in the formulas.

Thanks, Richard
James0628

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.