Add "Max" Logic to Record Selection

[MikeCopeland]

Using CR 11...
How do I apply a Max clause to the Record Selection statement? I have the following "select" clause:
//----------------------------------------------------------
if (({Clients.ClientLastName} <> "TEST")
and ({@inRange}) and ({ClientPacket.PacketKey} = 42)
and (isNull({ClientISP.HistoryChangeDate}))
and (isNull({FCDischargeSummaryReport.HistoryChangeDate}))
and ({@isTherapist}) ) then true
else false
//---------------------------------------------------------- This produces multiple output Detail records because there are multiple instances of records in one of the linked tables, and I want only the _maximum_ record from that table to be considered/used. I have analyzed the database extensively, and there is no unique link
to that table - but I can safely use the highest ID from that table.
I'm actually trying to emulate a "select Max(ID) from ClientStatus" (this table isn't mentioned in the above Record Selection clause), but I don't know how to do so...
Please advise. TIA

 

[dgillz]

Max of the entire report? Or max of a group?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If the phone doesn't ring, it's me".....Jimmy Buffet

 

[MikeCopeland]

Well, I want the data returned from the Record Selection to be only 1 record - right now I'm getting more than one. The data is comprised of fields from a number of linked tables, and a table at the end of one of the linked tables chains (there are several) has multiple instances that meet the selection criteria stated above. By looking at the table data in SQL, I can see that one of the records - qualified only by the Max of the ID field - contains what I need. Therefore, if I can somehow add a "Max" qualifier to something in the Select logic above (or elsewhere?), that should return only one of the records from that table...and I should see only 1 detail line.
That's what I'm trying to achieve. 8<}}

 

[lbass]

You could use the following logic in report->selection formula->GROUP:

{table.field} = maximum({table.field})

...to return only one record for the entire report.

Or,

{table.field} = maximum({table.field},{table.groupfield})

...for one record per group (as long as only one record meets that criterion).

-LB

 

[MikeCopeland]

LB, first, my apologies for not following up with your answer sooner, but my work with Crystal Reports is as a volunteer, just 3 hours a day. I was away from the system when your response came in.
Second, I'm not sure how to apply the solution with my data and report. Here's some data I have:
//........................................................
ID Key Facility
10 212902 610
20 212902 633
30 212902 615
40 212902 610
50 213015 611
60 213015 623
70 213015 623
//.........................................................
In this table I need to access the Facility for the greatest ID in each "set" of data - here there are 2 sets (212902 & 213015). This is the last table linked in this report, and I am only able to link via the Key field (which is a unique link to other tables). The data I'm acquiring is going into the Detail line, so I am seeing 4 lines
for 212902 and 3 lines for 213015, etc.
I've determine that I can use the Maximum ID record to get the Facility data (610 for 212902 and 623 for 213015), so I assumed I needed to use the Maximum function... I don't have this data Grouped, so I don't know how to apply your solution. Also, I don't know enough about CR to understand how to use your example in the Group selection.
Sorry to appear so dense on this (and other queries I make here), but my manuals and minimal experience with CR are making it difficult to grasp some of these things...
8<{{ TIA