RETRIEVING FIRST RECORD ONLY

[shaunk]

The tables have the following relationship:

APPOINTMENT(one) ------> CLINICIAN(many)

They are linked by Appointment-ID in each table.
How do I tell Crystal to retrieve only the first record
in the CLINICIAN table for every APPOINTMENT so that there is only one detail record for each Appointment.

Also, are there special variables to indicate if a record is the first in a Group, the first record in the result set or the last record in the result set.

Thanks

 

[Ngolem]

So...any clinician can be associated to an appointment?

You could choose the Maximum(table.clinician) or Minimum(table.clinician) but then your data would never vary wrt clinician

There is a way!....create an unlinked subreport that puts all the clinicians into a shared array (assuming that there are less than 1000)

with the Clinician table removed you will only get one detail item/appointment

then create a formula in the main report that takes this shared array and randomly (CR 8.0+ has a RND function) select one of the clinicians from this array and assign it to the Appointment.

2 problems solved....no extra records and everyone gets a shot at the appointment!

Hope this idea helps.....Jim

 

[shaunk]

That's right.....any Clinician can attend an appointment.

Where abouts do I utilise Maximum(table.clinician) or Minimum(table.clinician). I can't see it as an option in the Select Expert. Do I have to edit the SQL to achieve this?
Thanks...but I think shared arrays are a bit advanced at this stage of my Crystal Development.

 

[dgillz]

Setup a running total count field, evaluate on every record, and reset on change of field {appointment} or change of group {appointment} if you are grouping.

Then rt click the details section and conditionally suppress the entire details section if {#Count} >1.

WARNING: Conditional suppression will not keep the suppressed records from evaluating in any groups or grand totals you have. To do this you need to use running totals or variables in conjunction with conditional suppression. Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[Ngolem]

You don't use the select expert....you actually compose the formula itself

the results of the select expert are show in the menu selection

Report|edit Selection Formula|Record

the Shared Array suggestion is not all that tough. Have you worked with subreports at all?

I'll coach you through it if you want

 

[Nimrauko]

Why don't try with an accumulated var? How this...

- In Group Header create an invisible formula (@Initialize), write:

whileprintingrecords;
numbervar numrecords := 0;

Whit this you initialize the var numrecords every time change the Appointment-ID.

- In Detail, create a second invisible formula (@countrecord), write:

whileprintingrecords;
numbervar numrecords;
numrecords := numrecords + 1;

Whit this you count the number of records in the group.

- Also in Detail line format, mark Supress, press X-2 and write:

whileprintingrecords;
Evalutateafter({@countrecords});
numbervar numrecords;
if numrecords > 1 then
(
true
)
else
(
false
)

Whit this you supress all the detail lines, but don9t the first.

I hope this help you.

 

[shaunk]

Thankyou NIMRAUKO and DGILLZ. Some interesting techniques I can employ later. However, I wish to avoid bringing the second CLINICAIN record over at the Server level and I think I can achieve this best by using the record selection option of Maximum etc.
NGOLEM - I'm sitting at home away from work for two days so I can't test this yet but..
What's the differnce between using Formula Editor from the Select Expert to edit the record selction and using "Report|edit Selection Formula|Record"..thanks for your time.

 

[Ngolem]

"What's the differnce between using Formula Editor from the Select Expert to edit the record selction and using "Report|edit Selection Formula|Record"..thanks for your time."

more control...on the designer's part ....IMHO

 

[dgillz]

I 2nd Ngolem's opinion on use of select expert VS Report-Edit Selection Formula-Record/Group. Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office