Defining and linking many to many relationships

[Thumperman]

Hi All--

I have created many databases but I am having a problem with how Filemaker treats many to many relationships in multiple databases. For example, assume I have Raw Material A, Raw Material B, Raw Material C, Part A, Part B, Part C, Part D, Part E, Product A, Product B, Product C. Products are composed of parts which are in turn composed of raw materials. Some raw materials belong to multiple parts and multiple parts belong to different products. To visualize this, see below:

Raw Material A & Raw Material B => Part B

Raw Material A & Raw Material C => Part A

Raw Material B & Raw Material C => Part C

Raw Material A => Part D

Raw Material C => Part E

Part A & Part E => Product A

Part A & Part B & Part D => Product B

Part C => Product C

I know that there should be a total of 3 databases: Raw Materials, Parts, and Products Databases. However, when I look at the products database, I want to see all of the parts for each product (a portal, I presume). However, my problem is how will this be for the parts database? There will be multiple products for each part which I would like to view all of the products that belong to each part. Also, there will be raw materials for each part (view in a portal). I know that it is not good practice to use repeating fields. (The export of such fields causes problems if you plan to leave FileMaker for another system). Is there a way to list all the products that belongs to each part in the parts database without entering that same part over and over for each product? Any suggestion is appreciated. Thanks!

Thumperman

[Kurt Knippel]

quote:

---

Originally posted by Thumperman:

Hi All--

I have created many databases but I am having a problem with how Filemaker treats many to many relationships in multiple databases. For example, assume I have Raw Material A, Raw Material B, Raw Material C, Part A, Part B, Part C, Part D, Part E, Product A, Product B, Product C. Products are composed of parts which are in turn composed of raw materials. Some raw materials belong to multiple parts and multiple parts belong to different products. To visualize this, see below:

Raw Material A & Raw Material B => Part B

Raw Material A & Raw Material C => Part A

Raw Material B & Raw Material C => Part C

Raw Material A => Part D

Raw Material C => Part E

Part A & Part E => Product A

Part A & Part B & Part D => Product B

Part C => Product C

I know that there should be a total of 3 databases: Raw Materials, Parts, and Products Databases.

---

You need to have the following:

Products

ProdParts (each record contains ProductID and a PartID)

Parts

PartMaterials (each record contains a PartID and a MaterialID)

Raw Materials

The ProdParts and PartMaterials are the many-many "join" files that you are missing.

[Thumperman]

Hi Kurt,

Thanks for the information. However, I need a little more specific information such as what fields I need in the 2 join databases, how to link the join database to the main database (prodpart to product & prodpart to part), and how to populate the join databases (if this is the case OR will this be done through the relationship)?

Thank you again for any suggestions you may have.

Thumperman

[Kurt Knippel]

quote:

---

Originally posted by Thumperman:

...I need a little more specific information such as what fields I need in the 2 join databases, how to link the join database to the main database (prodpart to product & prodpart to part), and how to populate the join databases (if this is the case OR will this be done through the relationship)?

---

Which part of this was unclear?

ProdParts (each record contains ProductID and a PartID)

PartMaterials (each record contains a PartID and a MaterialID)

You can populate the databases in whatever manner you wish, and makes sense in your solution. Your question did not give any details on what you are doing, only that you wanted to know how to join the files.

I would assume that you already have records for all of your Products, Part and Raw Materials. In your Products file you would have a portal into the ProdParts file and could simply enter (or pick from a Value List) the PartIDs of the parts that make up that product. Same with the Parts to Raw Materials tables.

[Thumperman]

I have created the 2 database that you suggested to join my 3 databases. The data in it are the exact data that you see above in my example. I'm just trying to test some sample data and make sure that it works before creating something big to load in tons of data. I have a products database with the fields Prod # and Prod Description, a parts database with part # and part description, and a raw materials database with rm# and rm description. I made up some products such as Prod A, Prod B, Prod C to populate the databases. I also populated the other databases in the same fashion. After your suggestion yesterday, I created the 2 join databases. However, I didn't know what fields to create. Are the fields in it created in the database called "product" and "part" or are the fields from the other database in which you will have 2 colons in front of the field (roduct) This is where I am getting confused. The only way that I know to link 2 databases is to have the same field in both database. So I created a product field in the prodpart database and linked it to the product field in the product database. I placed both fields on that layout and nothing shows. The record is empty. Am I defining the relationship wrong? Please help. Thanks again for your time.

[Thumperman]

I finally figured it out by reading all of the other questions and suggestions in the relationship and portal forum. I didn't realized that the Prodparts database contained a record for each product and it's parts multiple times (if product A is composed of parts A and parts B, then you need 2 records in the Prodparts database). This database would end up being very large if a record needs to be created for each part in the product.

If i'm seeing this incorrectly, please advise on a better solution. Thanks.

Thumperman

[Kurt Knippel]

quote:

---

Originally posted by Thumperman:

I finally figured it out by reading all of the other questions and suggestions in the relationship and portal forum. I didn't realized that the Prodparts database contained a record for each product and it's parts multiple times (if product A is composed of parts A and parts B, then you need 2 records in the Prodparts database). This database would end up being very large if a record needs to be created for each part in the product.

---

DING, DING, DING! We have a winner Alex!

This is why they call it a MANY to MANY relationship, you are relating many Products to many Parts. 1000 products each with 10 parts requires 10,000 ProdPart records.

Still join files tend to be small and quick as they usually only contain a couple of index fields and maybe some unstored calcs.

[Thumperman]

I finally have a prodpart database with part # and product #. However, when I go to create the portals in the product database to display the part information, only part # will be available(because I only have part # in the database). The other fields such as part description, qty per assembly, etc fields which are resident on my parts database can not be added to that portal. Does this mean that these fields will need to be added to the prodpart database? Selecting this field through a relationship established between the prod and part database will not give me the right solution. Do you have any suggestions? Please help. Thanks.

[BobWeaver]

A limitation with Filemaker is that you can't display a field that is more than one relationship away. There are several workarounds depending on exactly how you want to work with your data.

1. Create fields in the parts file that lookup data from the raw material file. These can then be displayed in the product file. However, this does take up more file space to store the duplicate data.

2. Use the parts file as your main working/display file and set up a reverse relationship back to the product file in order to get the data to display. However, this may not be a convenient way to work with your data.

3. Have a field in your product file to hold a part no. that you copy from the parts file. Then you can create a relationship on this field directly to the raw material file, so that you can display raw material data for a single part at a time. This is usually set up by selecting a part no. from a portal and running a script that copies that part number to the part no. field, and a separate portal based on this direct relationship will display all raw materials for the selected part.