Suppress Duplicate Records

[gdkz]

I have a report that groups records first by room number, then by student name and must stay in this order. A student may appear in multiple rooms and I would like to show each student only once in the entire report regardless of how many rooms they appear in.
Example:
Room 1
Student 1
Student 2
Room 2
Student 7
Student 10
Room 3
Student 2 (This record should be suppressed)
Student 8
Room 4
Student 8 (This record should be suppressed)
Student 11

How can I accomplish this task? Any ideas?
Thanks

 

[lbass]

You should always identify your CR version. From an older thread, it looks like you are using 8.5. If so, create a SQL expression {%firstrm}:

(
select min(A.`roomno`) from table A where
A.`student` = table.`student`
)

Then go to report->edit selection formula->record and enter:

{table.roomno} = {%firstrm}

This should return only the first record (by room no) per student.

-LB

 

[gdkz]

Yes, 8.5, thanks for the reply. I am not using an ODBC data source, but an Active Data defintion file (ttx). My record source is an XML file.

Thanks,
Greg

 

[lbass]

The only other method that I know of would be to collect the students in a stringvar and then check for the presence of the current record in the variable--but in 8.5, you would be limited by the 255 character limit in the string.

-LB

 

[mgason]

well I do not know about in 8.5 but in XI you can just right click the student field in your report and select "format field" then on the common tab check the "suppress if duplicated" checkbox and you are done.

If you can not do that in 8.5 the word upgrade rears its ugly head ;-)
Its only about $99

mark

 

[lbass]

mgason--that would work if the students were listed consecutively, but they are not.

-LB

 

[gdkz]

Thanks again for the responses. I had considered the an array. I am going to have to find a way to mark the first instance in the record set, so as I can use that field to suppress the subsequent fields.
Thanks for the help!!!

PS: Looking forward to upgrading.

Thanks,
Greg