2 Replies Latest reply on Sep 9, 2011 11:30 AM by st1300dave

    Assembly Parts Listing Table with Parts Table



      Assembly Parts Listing Table with Parts Table


      I have an operational Filemaker Pro 11 database of about 5000 parts used in various ways to make different assemblies for manufacturing. I would like to design an assembly database where I can discretely enter each part number - up to 35 for each assembly - and it will load from the relational Parts DB, the cost and part name. Brute force would have me adding 35 fields for part number, name and cost but that seems crude and not elegant. What am I missing? Should I use a portal or repeating fields? I am stuck here. Thanks.

        • 1. Re: Assembly Parts Listing Table with Parts Table

          It sounds like to want to add a Table called 'Assembly'.  One record in it is a unique assembly (or up to 35 parts, in your terminology).

          You then want a Join Table, call it AssemblyParts.  You need a Join table as I assume you can use the same part in many assemblies, so your relationship from Assembly to Parts is many-to-many.

          The Join Table has one record for each part in an assembly.  You could have Assemblies:

          Assembly Table

          001 Large Assembly

          002 Medium Assembly

          003 Small Assembly


          Parts Table

          001 Screw

          002 Nut

          003 Strut

          004 Bolt

          If Large Assembly has a nut and a bolt, and Medium Assembly has a nut and a screw, and small assembly has a nut, bolt, screw, and strut, the Join Table will have 8 records in 3 fields, AssemblyID and PartID and QtyRequired:

          Join Table

          AssemblyID  PartID    QtyRequired

          001              002          4

          001              004          2

          002              002          1

          002              001          6

          003              001          7

          003              002          1

          003              003          1

          003              004          3


          The relationships are:

          Assembly Table                       Join Table                     Parts Table

          Assembly ID  -------->     AssemblyID  PartID --------------> Part ID

          Then in the Assembly Table show a portal drawn from the Join Table, and it will show all the components and their quantities in that assembly.  If you want you can in the Parts Table draw a portal to the Join Table and it will show all the assemblies that use that part and what quantity each requires.

          You don't need, or want repeating fields.  And there is no '35' limit to this; you can have as many components as you like.

          Search the forum for 'Join Table' and you'll find lots of useful examples.

          • 2. Re: Assembly Parts Listing Table with Parts Table

            It worked great. My databases work great now. Many thanks. Cordially, Dave Wilson