12 Replies Latest reply on Apr 26, 2012 12:49 AM by job

    Portal, BOM, and order form

    job

      Hello,

      I hope that someone can help me with this. I am new to Filemaker and databas in general.

      I have tried to search for some answers, but I uncertain what to search for.

      I am trying to build a sort of BOM, bill of material list, from which I will generate new price list of certain product groups.

       

       

      I have built a BOM like structure that seems to work, and I have also build a customer database that also seems to work, but now I want to connect these together.

       

      The smallest part that I have is a “part” (1). These range from screws and bolt to larger stuff.

      One or more “parts” forms a “spare part” (2)

      One or more “spare parts” form a “sub assembly” (3)

      One or more “sub assemblies” form an “assembly” (4)

       

      “Parts” (1) do only have information about itself, price, supplier and such info.

      (1) is connected to (2) trough another table called x_lines.

      So the layout for “spare parts” (2) has a portal that lets me choose which parts (1) is included, and the result is stored in x_lines.

       

      This is repeted for each step further up in my BOM.

      So if I choose to change the price for a screw, it is directly shown on the end price for an assembly.

      I can also re-use or change any spare parts or subassemblies that I made for a faster progress.

       

      So far, so good…

       

      But I also tired to connect this to an order form. I have customer data connected to the order form, and I would like to be able to add a portal and choose an item from any of these four tables.

      If I connect “order_lines” ID to assembly then I can only choose an assembly.

      What I would like is a drop-down list, that let me choose which type of part I need, if it is a (1) part, (2) spare part, and so on. And then lets me choose that ID from that table based on the part name from the same table.

       

      Which would mean that I would have a portal that displays information and prices from up to four different tables at the same time?

      Is that even possible and how do I do that?

       

      I am thankful from any and all feedback regarding this!

       

      best regards,

      Johan

        • 1. Re: Portal, BOM, and order form
          gregmartin64

          Are you far into this system? If not, I would probably look at the database design more than anything else, otherwise you may find yourself running into simlar issues.

           

          If you look at your four tables, they probably share similar properties, which means you could use one table, and have an additional field which indicates whether it's a single part, sub part, or made up of other parts, or it's a header for a complete assembly. You would then have a table which tells you how to build an assembly from the parts in this table. This is called a recursive association and is ideal for building up assemblies of widgets.

           

          Hope that makes sense.

           

          Greg

          • 2. Re: Portal, BOM, and order form
            Vaughan

            job wrote:

             

            Hello,

            I hope that someone can help me with this. I am new to Filemaker and databas in general.w

            I have tried to search for some answers, but I uncertain what to search for.

            I am trying to build a sort of BOM, bill of material list, from which I will generate new price list of certain product groups.

             

            As Greg suggested, a different data structure is requored. Most likey a recursive structure will be needed which will allow unlimited assembly and sub-assembly relationships -- or at least not an arbitrary limit.

             

            This is NOT a simple solution to be building even for an experienced developer!

             

            You'll need a table of Assemblies, and another table to join them, say call it Amounts. The recursive part arises because the join table links two occurrances of the Assemblies table together.

             

            The Assemblies table needs to first be populated with the most basic parts. These are then joined to make the new Assembly records. Nee Assembly records can again be made up of other Assemblies and more basic parts. And so on, unlimited.

             

            A basic part will therefore be defined as any Assembly that is not made up of any other assemblies.

             

            Building an interface to make sense of this will be part of the challenge!

            • 3. Re: Portal, BOM, and order form
              mbraendle

              Have a look at the infinite hierarchies technique video and example files by Matt Petrowsky on filemakermagazine.com.

               

              Actually, the approach is top-down  contrary to what Vaughan suggest.

               

              We use the same technique for our classification and to produce a classification StarTree:

               

              http://www.clicaps.ethz.ch/fmi/xsl/startree_en.xsl

              1 of 1 people found this helpful
              • 4. Re: Portal, BOM, and order form
                gregmartin64

                That's a nice link, I'd probably still use a recursive association in this case ( this is off the top of my head, so would need a little more thinking about ) BUT

                 

                You would have a parts table as follows

                 

                Part No:100000  Description: Screw                Parent:100004 Price Per Unit:£?.??

                Part No:100001  Description:Woodboard         Parent:100004 Price Per Unit:£?.??

                Part No:100003  Description:Shed Coverging   Parent:100004 Price Per Unit:£?.??

                Part No:100004  Description:Sub-Assembley for making part of a shed Parent:100006 Price Per Unit:£?.??

                Part No:100006 Descripion:Kit                         Parent:-1 Price Per Unit:£?.??

                Part No:100007 Description:Nails                     Parent:100006 Price Per Unit:£?.??

                Part No:100008 Description:Window Coverging Parent:100006 Price Per Unit:£?.??

                 

                You can then use FileMaker to break down a Kit, Sub-Assembly or Part either at data entry or the time of stock picking, or ordering ( normally you'd want to order a complete kit but you may also just need one widget ).

                 

                You can go futher with this set up and create a "assembly" table which would then allow you to say

                 

                Garden Fixture

                You need 2 x Part No:100006 ( which expanded would contains all the sub parts based of the parts with a matching parent Part No )

                You need 1 x Part No:100000 ( all though contained in the sub-assembly, you might need some extras ).

                 

                Once you've built an assemly, then it would recursively move through the parts table and be able to calculate and pick all the items you'd need to build or order a complete assembly. It would also mean that any cost changes down the line would be reflected in the over all bill and material.

                 

                This is only one ( but I insist, not very well thought out here), way of doing it of course! And also means that you can then just list the kits and parts to get everything you need.

                1 of 1 people found this helpful
                • 5. Re: Portal, BOM, and order form
                  Vaughan

                  MartinBraendle wrote:

                   

                  Actually, the approach is top-down  contrary to what Vaughan suggest.

                   

                  You're forgetting that I'm from "down under". LOL

                   

                  Greg, implementing anything OTHER than a recursive structure, even though it seems overly complex right now, will be making a rod to beat yourself with.

                  • 6. Re: Portal, BOM, and order form
                    gregmartin64

                    Definately Vaughan. The recursive method I described above is used well on an old stock system I wrote with clipper/dbf a few years ago ( actually 10 years ago ),  I can't remember all the detail of the methods, but for kits/assemblies/parts and it works well. Hope the weather is good down under, it's raining here in the UK ....... again ......

                    • 7. Re: Portal, BOM, and order form
                      Vaughan

                      Ha! It's been raining here for most of the week. Windscreen-wipers-on-full rain, not the drizzly stuff they get in Melbourne.

                      • 8. Re: Portal, BOM, and order form
                        mbraendle

                        Good joke, Vaughan.

                         

                        Well, with top-down I meant that one has first to enter the higher classes (or parts), then add or associate the subclasses (or subparts). This goes pretty fast, since one always enters the items in the portal of the subclasses (or narrower terms), which creates the higher-lower association (and also the reverse one) automatically.

                         

                        I think we should apply for a weather channel in the FM TechNet Developer forum . Here my report for Switzerland: Typical April weather, changing a lot, but mostly rainy. Rather cold temperatures, around 5-10 centigrades. We even had 5-10 cm of snow over Easter in the east subalpine regions (800m altitude).

                        • 9. Re: Portal, BOM, and order form
                          Vaughan

                          BOM is the Bureau of Meteorology -- the weather people here in Australia. So we are still on topic.

                          • 11. Re: Portal, BOM, and order form
                            gregmartin64

                            I'm impressed. Scroll to the bottom and they give you another 91! Good find :lol:

                            • 12. Re: Portal, BOM, and order form
                              job

                              First of alI I would like to thank all of you for taking the time to answer!

                              I am REALLY new at this and I see that this might be to much for me right now.

                              I trying to build and populate this databas inbetween my ordinary work assignment.  

                               

                              I have not gotten that far, so a total rebuild would still be possible. As you guessed, my tables share almost all properties between them. They are basiclly a copies of each other, except for level 1 which has more information. I do however need this database a.s.a.p. so I guess that I have to try and build something new parallell to my first attempt.

                              With a bit of luck I might be able to export / import at least the most basic components and suppliers. 

                              I'm really impressed by FileMaker and its capabilities. We should have bought this years ago, or at least some type of database.

                               

                              Special thanks to MartinBraendle for the video link. That was extremly useful since a video is easier to understand (at least for me).

                               

                              I will take all of your infomation into consideration when building our new database.

                               

                              Best regards,

                              Johan