1 Reply Latest reply on Nov 18, 2016 9:28 AM by philmodjunk

    Look up relationships

    walterjump

      I am relatively new to FileMaker. I have done look up relationships be fore where there is a one field to one field relationship. I need to do a relationship of many to one. I am developing a database that has one table for building an item  This consists of many elements(between 5 and 20). I want to relate the various part needed to an elements table that contains the description, ID, cost, cost units, etc.. The manufacturing table is linked by ID of part to ID of elements table. The manufacturing table has a list of elements to use in manufacture, but different grades can be used for different items. For example the manufacturing table has coating1, coating2, coating3, etc. The ID in these fields is supposed to link to the ID in the elements table and then look up the description, cost, etc and copy it into the manufacturing table. When I try this it will only look up the first item and then copies that information into all the lookup fields (coating1 cost, coating2cost, etc). What do I need to do to get each manufacturing ID looked up separately and the appropriated information copied into the manufacturing table?

        • 1. Re: Look up relationships
          philmodjunk

          You need to replace the fields, coating1, coating2, etc with a related table of coatings. Then you can specify different coatings in each such record and use that info to look up data from elements.

           

          Manufacturing----<Coatings>-----Elements

           

          A commonly used data model is the "bill of materials" or BOM. It looks like this:

           

          Product----<BOM>-----Materials

           

          A set of BOM records represents the "recipe" for manufacturing one unit of product, recording the Material ID's and Quantities needed for each material. It's not uncommon for Product and Materials to be two occurrences of the same table such that any given Material can in turn have it's own BOM.