Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.filemaker > #549 > unrolled thread

Splitting amounts based on linked table

Back to article view | Back to comp.databases.filemaker

Contents

  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

#549 — Splitting amounts based on linked table

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.

[toc] | [next] | [standalone]

#551

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.

[toc] | [prev] | [next] | [standalone]

#558

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. 

[toc] | [prev] | [next] | [standalone]

#554

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

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  :o)

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.filemaker

csiph-web