Pull Related Field To Summary Field 1

[123FakeSt]

I'm working with CR10 with OLE to SQL2K...

I have a report pulling maximum noteID for each Invoice as a summary field (for I need the detail to make calcs on the invoice line detail). The report is grouped by invoice.

I would like for it to return the text of the maximum (most current) ID, not the ID itself.

Basically .. "Get the note text for the maximum note ID in each Invoice group"

I had no luck with the SQL Expression Fields...

Thx


The early bird gets the worm, but the second mouse gets the cheese.

 

[dk87]

Can you sort by Invoice, then note ID? If so, suppress the detail lines, and show the details and note text in your group footer.

 

[123FakeSt]

Well because they are both one to many joins from Invoice, it would cause my invoice detail calculations to compound by the number of notes.

The early bird gets the worm, but the second mouse gets the cheese.

 

[dk87]

Ok, so in your detail records, use a formula to check for max(NoteID). You will need to use a variable to make sure you don't overwrite it with invalid data.

In your invoice group header:

Formula: ClearGroup1

WhilePrintingRecords;
Global StringVar strInvNotes;
strInvNotes := '';

In your Detail Section:

Formula: DetailProcessing

WhilePrintingRecords;
Global StringVar strInvNotes;

If {Table.NoteID} = Maximum({Table.NoteID}, {Table.Invoice}) then
strInvNotes := {Table.NoteText} else
strInvNotes := strInvNotes;

In your invoice group footer:

Formula: PrintInvNotes

WhilePrintingRecords;
Global StringVar strInvNotes;
strInvNotes;