7 Replies Latest reply on Nov 30, 2012 5:38 PM by LyndsayHowarth

    How do i link my inventory and sub inventory graphs in a relationship?

    Angie

      I have 2 files set up, Ïnventory for finäl product"and "sub inventory for parts that make up my final product".

      I am trying to set it up so, when i receive 1 item in my final product inventory, 1 of each sub item comes out of stock in my sub-inventory.

        • 1. Re: How do i link my inventory and sub inventory graphs in a relationship?
          LyndsayHowarth

          Hi Mr/Ms Corporation,

           

          I would firstly suggest that using the template files as a starting point is confusing you.

          You might also consider putting these tables into a single file as it will make your developement more stable as references between files can be lost with filename changes and file relocations.

           

          I have attached a file which is a re-work to that effect. I would suggest a renaming of some of the tables and layouts to reflect what you are actually trying to do. This is not in fact 'Inventory' but rather Products and their Components. Inventory is the stock-levels of those components.

           

          What you are missing in this scenario is the actual transactions. You do want to relate the Inventory and sub-Inventory tables... but it is the actual transactions which will effect the stock amount in each inventory. There is the matter of adding stock as well as removing it.

           

          The way the template handles this is prone to errors. You could extend it by scripting the reduction of the stock on hand for each component but there is no history kept of the adding or removing of stock. You would be better off having a Stock table which records the additions and reductions in stock... each with it's own record complete with date and other relevant info... rather than just the changing totals. Often an invoice items table doubles for this purpose.

           

          HTH

          - Lyndsay

          • 2. Re: How do i link my inventory and sub inventory graphs in a relationship?
            Angie

            Thanks for your input Lindsay, however, I never used a template to create this inventory.

            My files are named Product, and sub inventory.

            I think my problem is in the actual formulas.

            Thanks anyways

            • 3. Re: How do i link my inventory and sub inventory graphs in a relationship?
              LyndsayHowarth

              hathorncorporation wrote:

              Thanks for your input Lindsay, however, I never used a template to create this inventory.

               

              Really???

              This pic shows templates (starter solutions) on the RHS and your solution on the LHS. The Inventory file on the bottom right is almost identical to yours including most of the field names.

              starters.png

              chart.png

               

              Your file is clearly created from a template.

              hathorncorporation wrote:

              I think my problem is in the actual formulas.

              Possibly the formula is wrong... but so is that whole methodology and my previous response attempted to provide an alternative that is a lot less prone to problems including user error.

               

              Please do not insult my intelligence nor disregard my experience and expertise.... if you actually want help to create a viable database.

              • 4. Re: How do i link my inventory and sub inventory graphs in a relationship?
                Angie

                The actual files called "Product"and Sub Inventory"  with the pics I added, and  I selected from inserting fields, which some fields I added.(template u referring to?)

                I am new at this, and just wanting to figure out how to have my components (sub-inventory) and Product inventory link, so that, when a final product is received (made) 1 of each component (sub inventory) is taken out of stock.  As well as, when I receive a component (sub inventory) I can add it into stock.

                There is no need to be rude, I did not insult your intelligence, patience and clear explanations are always appreciated when dealing with newbies.

                I will go online & try to get assistance from a tutorial. I was asked to figure this out from my employer, with no knowledge of filemaker.

                Thanks anyways

                • 5. Re: How do i link my inventory and sub inventory graphs in a relationship?
                  LyndsayHowarth

                  Hi Angie,

                   

                  My intention was not to be rude and I apologise. I felt you had told me an untruth and ignored the recommendation I made in my first response. I do attempt to be clear and constructive and helpful but my time is limited and sometimes I forget myself.

                   

                  I had hoped you would see the benefits of the merged file I sent because I feel you will make the job more difficult for yourself if you maintain separate files.

                  I had also hoped you might ask for more info about how to keep a more accurate stock history.

                   

                  The problem with knowing those templates... as they have been around for a few versions... is that I also know their weaknesses. The current stock level calculation was a lazy fix for a much more complex problem and could only ever be usable with a very small catalog. You could never entirely trust the value it holds.

                   

                  When you go to the Help Menu in FileMaker there are links to some very useful resources. There are also some good presentations on YouTube. There are also some excellent resources on many consultants web sites which sometimes a bit more advanced. There are also some good resources in the Technical Resource Centre (Tab at the thop of this page) and in particular I suggest you read :

                                                 Fundamental Concepts of Relational Theory (Last Updated: 2003)        

                   

                  I don't always bite so please let us know the full scope of what you've been asked to do and perhaps I can do something for you...

                   

                  - Lyndsay

                  1 of 1 people found this helpful
                  • 6. Re: How do i link my inventory and sub inventory graphs in a relationship?
                    Angie

                    Thank you Lyndsay.

                    I have become more confused as I watch videos, and unsure what steps to take next.

                    I have signed up for a free 7 day trial with Lynda.com, as well as contacted a consultant to hopefully obtain some basic training in this software.

                    The reasoning for my 2 tables, is the counts for final product & components, need to be separated, as these tables are only a dabble of the stock & products we manufacture.

                    Which I will add after I have figured this out.

                     

                    Thanks for your input

                    • 7. Re: How do i link my inventory and sub inventory graphs in a relationship?
                      LyndsayHowarth

                      Hi Angie,

                       

                      I am suggesting that you will infact need 3 tables.... but that you have them in the same file.

                       

                      The main Relationship between a Product Table and It's Components Table is essential and you will probably then need others with these 2 base-tables being used.

                       

                      The next table you need is a "Stock_Movement" table. (This is the one I would have called 'Inventory') This table is where a new record is created every time a component is used or a new delivery arrives. The adding of the records can be automated in a number of different ways using scripts.

                      A relationship between the Components table and the Stock_Movement table would be based on the Key Component ID and a matching field in the Stock_Movement table.

                      The value of the Current stock of each Component would be calculated by

                      "Starting number +sum(Stock_Movement::NumberIN) - sum(Stock_Movement::NumberOUT)"

                       

                      You would then create a script for when a Product is purchased that adds a record in Stock_Movement with a NumberOUT for each of the Components.

                      The Stock_Movement relationship from Components would also allow you to create a NumberIN record for deliveries or NumberOUT record in case a component was damaged or stolen.

                       

                      There is too much info out there. The best stuff for beginners has usually been what FileMaker produces themselves. The Lynda.com option is a good idea. It will take you thru the basics of understanding FileMaker from a user point of view as well as a designer point of view. I've been at this for maybe 25+ years and I would never say I know it all so don't get too overwhelmed.

                       

                      HTH

                       

                      - Lyndsay