Groups | Search | Server Info | Login | Register

Groups > comp.databases.filemaker > #551

Re: Splitting amounts based on linked table

Show all headers | View raw

In article <2012011914424122189-douga@fcstonecom>,
 Doug Anderson <douga@fcstone.com> wrote:

> 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.

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.

Back to comp.databases.filemaker | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Splitting amounts based on linked table Doug Anderson <douga@fcstone.com> - 2012-01-19 14:42 -0600
  Re: Splitting amounts based on linked table Bill <bbcollins@earthlink.net> - 2012-01-19 20:30 -0500
    Re: Splitting amounts based on linked table Doug Anderson <douga@fcstone.com> - 2012-01-23 10:05 -0600
  Re: Splitting amounts based on linked table HelpfulHarry@BusyWorking.com (Helpful Harry) - 2012-01-22 12:24 +1300

csiph-web