6 Replies Latest reply on May 13, 2017 7:43 AM by hspiegl

    I'm being thick


      I think that I'm being thick - it's a little while since I've needed to create a new database and I've lost a few skills! I have databases with portals and related products but this one stumped me (it's probably very simple).


      I'm creating a product database with all the usual stuff, code numbers, descriptions, components, costings etc. These are items that we make. I've hit a mental blank - a number of the components that combine to make a product we also make. For instance Product A requires packaging element B & C; both of which we make and sell individually and separately. These need to be headline items because they have components themselves - it sort of goes in a loop. As an example you have a car which is a part number; it includes an engine which is also a part number. You can buy the car (including an engine) or just an engine (without the car, as a replacement). Tyres might have been a better example!


      What's my structure?

        • 1. Re: I'm being thick

          You appear to be describing a BOM--a Bill of Materials table. It can start out this simple, but becomes anything but simple as you build in the other details that you need to manage:


          Products----<BOM>-----Materials (----< means "one to many")


          The trick is to set up Products and Materials as two occurrences of the same table so that a material can also be a product.

          • 2. Re: I'm being thick

            Thanks!! Very helpful. Are there any any FMP examples that I can study?

            • 3. Re: I'm being thick

              I tossed together an example of one approach. As philmodjunk says this gets complicated in a hurry.


              One HUGE thing that you need to check for is to make sure nothing is being made from itself. BOMs can get pretty deep, (assemblies made from assemblies made from assemblies, etc) and sometimes a user will accidentally have the system making an assembly from itself. When this happens they will be in an endless loop.


              The printed version of Single level BOMs are easy to make but when your users ask for the multi-level BOM you'd better do lots, and I mean LOTS, of research before committing to delivery.


              Good luck!

              1 of 1 people found this helpful
              • 4. Re: I'm being thick

                I set up a recipes data base on my iPhone built around this concept. I might, for example, a recipe for an apple pie that lists "pie crust" as in ingredient. It then has an icon next to it in the ingredients portal that I can tap to open up the recipe for pie crust. In theory, I can drill down as deep as I need to.


                As DLarsen warns, you do have to be careful not to build a circular BOM definition in the data.

                1 of 1 people found this helpful
                • 5. Re: I'm being thick

                  Enterprise class ERP systems (think SAP, Oracle, etc) check for the circular reference as parts are being added to the BOM so the danger is diminished. They also employ recursive SQL to produce the Indented BOM. I doubt Filemaker would handle this type of SQL but I haven't tried it.


                  The trouble with trying to run complex manufacturing in FM is that the users will eventually ask for MRP (Material Requirements Planning).  Just thinking about the complexity makes my head swim. 

                  1 of 1 people found this helpful
                  • 6. Re: I'm being thick

                    Thanks to you both! I was being thick - after I read and re-read various threads I realised that I had already created a BOM previously and quite by accident! Previously I had set the 'Materials' as a value list. Seemed to work well.


                    On this project I saw the problem with the loop but assume that we have to be tough with control.


                    Thanks again!