combining field data on report

[ddiener]

I am trying to print a FM report that lists one field from five separate records in a single horizontal line similar to the "mailing label" layout. The next line(row) would contain data from the next five records and so on. The kicker (and why I can't use the mailing label template) is that I need to include some text at the beginning of each row. The row would resemble - Text, record 1 field A, record 2 field A, record 3 field A, record 4 field A, record 5 field A. The next row would repeat the pattern but with different text and the next five records.

I have tried everything I can think of including using GetNthrecord, exporting to excel and word but nothing provides what I need. Get Nth Record is close but it uses the FM Record ID# and I need to be able to sort the records and have the data correspond to the sorted data. Any ideas would be appreciated. Thanks.

[comment]

Get Nth Record is close but it uses the FM Record ID# and I need to be able to sort the records and have the data correspond to the sorted data.

I don't think that is correct. According to FM Help:

"GetNthRecord of the current table returns the Nth record of the found set according to how the current table is sorted."

[bruceR]

Note that it will return related data according to how the relationship sort order is defined. Any sort order attached to a portal will not be used.

[ddiener]

I tried several times to resort the data but the Nth results do not change. They seem connected to the order established when the fields were first created. I have only tried it as a field in the existing table. I am not sure creating a second table gets me anywhere closer.

In addition, I will need to display the field from each record in the entire database, approximately 328 records, on the same report. That makes 5 records to a row and approximately 65 rows.

I need it to look very similar to mailing labels, five to a row, but with addition text or a field containing text at the beginning of each row, i.e. 6 fields to a row.

Thanks for the imput.

[comment]

I suspect your calculation using GetNthRecord() is stored - that's why it doesn't react to changes in found set and sort order.

I am not sure this will solve your real problem, because I cannot imagine how you're going to calculate a common text for a group of 5 records with an arbitrary found set and sort order.

[onthebass]

Why not just use a script to loop through and create one record in a new table for every 5th record in the main table. This new record will contain a text field and five fields (one for each record in the main table), as the script is looping through the records in the main table it can set the value in the new record in the new table.