Printing Related Records on One Report Line

[Matt Murray]

I have a two table FMP database (I will over simplify for efficiency's sake). The main table is a customer table with Name and Custom ID and the related table is a purchases table with date of purchase and the single item purchased. I would like to create a report that lists each customer and then on the same row the products the customer purchased.

For example, if customer John Smith purchased products A, B, and C I would like John Smith's row in the report to reflect A, B, and C on the same line as the customer's name, not via three lines for John Smith with one each having A, B, and C. Does my question make sense? Hope so. Any suggestions would be great.

[Breezer]

It can be done. Try this method from the Filemaker Knowledge Base, answer ID 2403:

To copy data to one field from multiple portal records do the following:

In the master database add fields:

Combine (Text)

GobalCounter (Global) - Number

KeyCounter (Calculation) - Number - Count(Relation::KeyID) - KeyID used as the relationship link field

Use these script steps:

Set Field[GlobalCounter, "1"]

Set Field [Combine, ""]

Go to Portal Row [ select, First ]

Loop

Copy [ select, "Relation::Fieldname"]

Paste [ "Combine"]

Set Field [Combine, Combine & "¦"]

Set Field[GlobalCounter, "GlobalCounter + 1"]

Go to Portal Row [ select, "GlobalCounter" ] - specify by field value

Exit Loop If [ "KeyCounter < GlobalCounter"]

EndLoop

What this script does:

First, the script "initializes" the GlobalCounter by setting it to 1, and then clears out the Combine field (so that it starts out empty). Then, starting at the first portal row, it loops through all the portal rows, copying the related Fieldname and pasting its value into the Combine field. Then, after each related value is copied into Combine, it appends a Carriage Return to the Combine field, so that the next related value appears on the next line. (This is optional). It finally exits when it has cycled through all the related records.