Groups | Search | Server Info | Login | Register

Groups > comp.databases.filemaker > #558

Re: Splitting amounts based on linked table

Show key headers only | View raw

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

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

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. 

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