I have a Crystal report that needs to display the data from the first record regardless of the number of records (usually 1 to 13 records). This field is placed at the bottom of the report and without any control it will always display the last record. This field indicates the container number being used for blood transport and the first record will always be the most current record.
views:
118answers:
2
+1
A:
Hi Byron, welcome to SO.
What is your data source? (Access database, SQL Server, etc.)
The ideal solution would be to modify the database query to:
Sort the records in some meaningful fashion.
Bring back only one result to your Crystal report.
Performing the work in this way may not make much of a difference for this particular report, but if you run several of the reports will start to become important.
Best regards,
Adam Bernier
2010-02-13 05:42:48
Thanks Adam,The database is a SQL Server. The particular records I'm concerned about are container numbers in a string field. The record I want displayed is the one with the most current activity. Since this data is already present near the top of the form, I simply want to have it displayed at the bottom of the page. Is there a good way to assign a variable to the data in record 1, then display it somewhere else, ignoring records 2, 3, etc? Thanks.
Byron
2010-02-13 18:21:39
@Byron: If changing the database query is not possible, you could alternatively create a formula field and use either `MIN()` or `MAX()` to determine which is your most current record. You could then display only the result of the formula field, and hide the other records. I hate to even say this, as this makes the problem much more complex than it need be. Maybe that doesn't matter now, but might start to matter as you write and maintain more code.
Adam Bernier
2010-02-13 19:01:26
Adam, I have used the min(), max() before, but your suggestion makes me think. Combining the record with it relavent record time, then sorting for the most current record may do it. I was really hoping for some easy formula where I could just define the record number I wanted. Thank you for your help.
Byron
2010-02-13 21:27:58
A:
Put a formula in your Report header that contains: shared numbervar myVar := {MyTable.Field};. Duplicate that line for each field you want to display (with the appropriate data type). In the Report footer, for each field, create a formula containing shared numbervar myVar;
Graham
2010-02-15 15:08:16