8 Replies Latest reply on Apr 10, 2017 7:35 AM by wimdecorte

    multiple versions of an item in inventory

    ulku

      Hello

       

      I am trying to set up a database for our inventory. We are a printshop and need to keep track of multiple version of on item.These are posters each printed in an edition of, lets say 20. So at the moment i have the info related to a poster like title or size.But now i need to have more information about each of the 20 in an edition. For example : number 5 of 20 of the poster with the title 'XYZ' was sold to 'NAME' or is in location 'X'

       

      Now my question is how do i assign information to one specific variation of an item.

       

      I am very thankful for any tips

       

      Thank you

        • 1. Re: multiple versions of an item in inventory
          mikebeargie

          Really you're looking at the need for a second table here.

           

          Let's say you have three tables total:

           

          Products

          ProductVariations

          Inventory

           

          The would be related by primary (unique to each table) and foreign (reference other table) keys.

           

          Products --< Product Variations --< Inventory

           

          Products::PrimaryKey = Variations::ProductForeignKey

          Variations::PrimaryKey = Inventory::VariationForeignKey

           

          Once you've established those relationships, you can then place the different variations of a main item in the variations table, and the Products table stores the common information that is the same for all variations.

           

          Inventory would then relate to each variation, with the ability to keep a running transaction log of adding or subtracting stock via inventory records related to that variation.

           

          You can then add summary calculations at the variation and product level to show total in stock, total sales, etc...

          1 of 1 people found this helpful
          • 2. Re: multiple versions of an item in inventory
            kazznfx

            Sounds like you're in want of a few fields, but won't know for sure until we understand your structure a bit.

             

            Do you have a single table of products? With one record for each poster?

             

            or do you have a table of product types with one record for all posters of a specific type? That will affect our path forward.

            1 of 1 people found this helpful
            • 3. Re: multiple versions of an item in inventory
              fmpdude

              I would recommend you take the Lynda.com FileMaker database online class. This class is separate from the other Lynda.com FileMaker classes.

               

              Many of the issues here that people pose "code fixes" for are database design problems. IOW, code fixes aren't the real "fix". Instead, fixing the database design is the 'fix'.

               

              If you learn database fundamentals and use a good ERD tool, you'll be a major step up.

              1 of 1 people found this helpful
              • 4. Re: multiple versions of an item in inventory
                ulku

                at the moment i have multiple record where each contains data that is common to a poster edition. for example: size, title, number of prints in this poster edition (lets say 20 of poster 'A'). right now i have started adding field like '1/20, 2/20, 3/20 and so on. but these field seem to be not specific to poster 'A' but rather relate to all the records

                • 5. Re: multiple versions of an item in inventory
                  philmodjunk

                  Some businesses can manage inventory by working with groups of products that are all treated identically and interchangeably. Others, like yours need a record for each item as each is treated as a unique separate item.

                   

                  As mike said, you need to add another table related to the table that you already have. Trying to record that additional data in a field is not a good idea.

                  1 of 1 people found this helpful
                  • 6. Re: multiple versions of an item in inventory
                    kazznfx

                    I agree that you may just need a bit more info on relational database design. it's hard to put it all in a post, but Mike probably made a good start. If that post doesn't make sense to you then I think you might want to start a little more basic.

                     

                    There are probably good tutorials on the basics of FM and I am a STRONG proponent of Lynda.com. I'm a self-taught developer and Lynda was a huge resource for me.

                     

                    You will be very glad you took the time to understand the keys... it will change your life.

                    1 of 1 people found this helpful
                    • 7. Re: multiple versions of an item in inventory
                      ulku

                      Thanks everyone for the quick response.

                       

                      Very helpful, if only for me to know what i am up against.

                       

                      so next stop: lynda.com

                      • 8. Re: multiple versions of an item in inventory
                        wimdecorte

                        Also, check up on the concept of a SKU (stock keeping unit).  If you are selling or need to track what quantity of what version of a product is coming in or going out of inventory then you need a separate SKU for each version.

                        2 of 2 people found this helpful