In MS Access I would accomplish this by simply making a summary query,
but such a thing doesn't exist in Filemaker. I'm using FM 11.

I have two tables as follows.

Table1
cust
BCID
Segment
Amount

Table 2
BCID, Name, Percent

These two tables are joined by BCID. Its a many to many relationship.

I want output that shows table1.cust, table1.segment, table2.name,
table1.amount * table2.percent

Simplified assume the following data.

Table1
cust BCID Segment Amount
TestCo 1 XYZ 100
ABCCo 1 ZZZ 100

Table 2
BCID Name Percent
1 John .50
1 Jane .50

In access if I join these two tables into a summary query I can get the
following output.

TestCo XYZ John 50
TesCo XYZ Jane 50
ABCCo ZZZ John 50
ABCCo ZZZ Jane 50

But I'm having a difficult time visualizing the Filemaker way to get
this to work.

In article <2012011914424122189-douga@fcstonecom>,
Doug Anderson <douga (AT) fcstone (DOT) com> wrote:

To do a many-to-many relationship you need an intermediate Join table,
that holds the IDs for both sides of the relationship. You can use the
Join table to do calculations peculiar to each of the instances and to
display data from both of the linked tables.

However, I am at a loss to understand the significance of the BCID in
your setup. It does not appear to be a unique identifier of anything.

General practice is to assign a unique entity identifier to each record
in a table, normally an automatically generate serial number. Structural
relationships are usually based on these unique identifiers. You can
base relationships on other fields of course, but that is the usual
practice.

There are some useful white papers and other resources on the FileMaker
web site that will help you understand these ideas better, and of course
there is the FileMaker user guide that comes as part of the electronic
documentation with your setup, as well as the on-screen Help.

In article <2012011914424122189-douga@fcstonecom>, Doug Anderson
<douga (AT) fcstone (DOT) com> wrote:
Since there's no actual individual Record for each of those report lines
(each Record in both tables is used in multiple report lines), there's no
way you can simply create a Report layout to achieve this.

You could have a third Table with individual report Records which pulls
the data from the other Tables, but you would have to Script the creation
of the appropriate ReportTable Records.

It's probably easier just to Script the report - loop through the records
/ Found Set in Table1, sub-loop through the related records from Table2
concatenating the required report line data into a new Text Field for the
Report.

Helpful Harry )

On 2012-01-20 01:30:44 +0000, Bill said:

In reality both tables do have unique identifiers, but neither
identifier exists in the other table. I just simplified my data and
didn't bother showing fields that weren't relevant to my problem.

It sounds like the only solution is to create an intermediary table.