Jump to content

Printing Related Records on One Report Line


Matt Murray
 Share

This topic is 5770 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 5770 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Who Viewed the Topic

    2 members have viewed this topic:
    JMart  Lee Smith 
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.