Groups | Search | Server Info | Login | Register
Groups > comp.databases.filemaker > #558
| From | Doug Anderson <douga@fcstone.com> |
|---|---|
| Newsgroups | comp.databases.filemaker |
| Date | 2012-01-23 10:05 -0600 |
| Message-ID | <2012012310051997402-douga@fcstonecom> (permalink) |
| References | <2012011914424122189-douga@fcstonecom> <bbcollins-4DEE12.20304419012012@70-3-168-216.pools.spcsdns.net> |
| Subject | Re: Splitting amounts based on linked table |
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
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