Help relating 3 cascading databases to create an invoice

[Jem]

For some reason I am just blanking on how this should work. Here's the setup:

In FileMaker Pro 5.5 (or 6)...

I have 3 databases. (1) INVOICE (2) PRODUCT (3) CONSTITUENT

Each product may be made up of one or more constituent pieces that have a distinct weight.

Each Invoice would be made up of one or more Product that each have a distinct price.

What I need is a *lookup* that gets the product name and price from the PRODUCT database and puts it into the INVOICE database. That's the easy part, which I've already done. I also need the product weight... which is derived from the CONSTITUENT parts.

The challenge is that I need the PRODUCT weight to be a summary of the all the CONSTITUENTS that make up that product.

So say "Product A" costs $100 and is made up of "Constituent B", "Constituent C", and "Constituent D" which weigh 3, 5 and 7 lbs respectively.

When I add "Product A" to the invoice I would like it to indicate a price of $100 and a weight of 15 lbs (based on the values derived from the Consituents that are a members of product A).

The reason for having consituents at all is because each product makeup may change requiring more or fewer consituents in the future... and each constituent weight may also change. Further, each constituent may be part of one or *many* different products... so being able to calculate weights of products "on-the-fly" from the individual constituents that make it up would be the most efficient and accurate way to calculate these values... especially across a large number of different products.

What I'm hung up on is which way to set up the relationship between the product and the constituents?

I had envisioned "Product A" having a match field that listed a unique ID for each of the consituents that makes it up... but how to get those 3 or more IDs to relate to the CONSTITUENT database has me stymied. Seem like a "many to many" lookup, and I'm not familiar with that.

Eventually, I would like to derive a "packing list" for the Invoice that would include ALL of the constituents for all of the "Products" included on the invoice.

Any help pointing me in the right direction... even if only conceptually would be appreciated.

Thanks!

[Jem]

Further info in case it helps...

I'm blanking on the best way to do that in terms of setting up the relationship. The following might help visualize what I'm looking for.

PRODUCT DB:

- Product 1, $10.00 [Constituents A,B,C]

- Product 2, $15.00 [Constituents A,B,C,E]

- Product 3, $30.00 [Constituents A,B,C,D,E]

- Product 4, $ 5.00 [Constituents B,C]

CONSTITUENT DB:

- Constituent A, 3 Lbs

- Constituent B, 5 Lbs

- Constituent C, 7 Lbs

- Constituent D, 2 Lbs

- Constituent E, 1 Lbs

- Constituent F, 4 Lbs

So if I selected "Product 2" on the invoice I would want it to SUM the weights for only those constituents that relate to that product (A,B,C,E) which would yield a weight value of 16.

And thus the line item on the invoice would read:

Product 2 $15.00 : 16 Lbs

A bonus would be to capture each constituent individually on a list, so that it could be processed for inclusion on a packing list.

For instance, if one ordered Product 2 and Product 4, the packing list would read:

(1) Constituent A

(2) Constituent B

(2) Constituent C

(1) Constituent E

Make sense?

I'm just blocking on the best way to indicate that a certain product is made up of more than one constituent. Maybe I need more than 3 databases?!?!

[comment]

You should have one more file to serve as the join table between Products and Constituents (which is a many-to-many). Basically, it just records the ProductID and ConstituentID of each join, but being in version 6, you will probably need to add some more fields to "tunnel" the data from Constituents, so that each Product record can sum it.

[Jem]

This is helpful, and makes sense conceptually.

I'll try this avenue and see if it works...

Cheers,

Jem