4 Replies Latest reply on Jul 2, 2014 6:44 AM by ChadAdams

    Create more than one record

    papawheelie

      Every week I take a physical count of all my product.

      Product is one table

      Physical count is another table.

      "one product to may counts"

      Is it possible for me to create a button on the Physical count table. That would create one record for every product from the product table.

      (product label has one record for each product)

      My product is stored in different locations so one product could be in several locations. If I duplicate records I would then have to erase the qty in each location with many chances of errors.

      End goal is to create a new record for each product every week to determine which products to replenish.

      User would click button new set of records would be created then move from one location to the next entering data.

      I am really new to this and hope I explained correctly.

        • 1. Re: Create more than one record
          erolst

          papawheelie wrote:

          My product is stored in different locations so one product could be in several locations.

           

          What does this mean?

          • 2. Re: Create more than one record
            papawheelie

            I store product in different locations within a building.

            Product is stored at one location for immediate sale. There is back up product in a store room. There is more product stored in refrigerator.

            within these location product is sorted in different formats which I can then sort product to match each format. by vendor in one location plu # in another location, and category in another location.

            • 3. Re: Create more than one record
              erolst

              Well, in a basic form, you should have one table with storage locations, plus a join table that combines a specific storage location with an item and lets you specify a quantity. This would be where you add/subtract quantities for sales and orders.

               

              Then you can simply sum up all quantities from tat table by product and e.g. store them with the product record itself, and/or create a replenishments list etc.

              • 4. Re: Create more than one record
                ChadAdams

                papawheelie wrote:

                 

                Is it possible for me to create a button on the Physical count table. That would create one record for every product from the product table.

                 

                If I'm understanding correctly it sounds like you are taking inventory, and you want to prepopulate a physical count record for each product so as you walk around and tally the products there is already a record created for you to enter those counts.

                 

                There would be a couple of ways to do this depending on your situation.

                 

                One way would be to do an import from the product table to the physical count table.  The only field imported would be the product primary key in the product table into the product foreign key in the physical count table.  This import would give you one new "empty" physical count record for (and linked) to each product record.

                 

                Another way would be to create a looping script that went through your product records and created a physical count record for each product record as it went. Something like:

                 

                Go to record/request (first)

                Loop

                  Set Variable $productID = Product::ID

                  Go to Layout - PhysicalCount

                  New Record

                  Set field PhysicalCount::Product ID = $product ID

                  Go to Layout - Product

                  Go to record/request (next, exit after last)

                End Loop

                 

                Based on your explaination to erolst it is still unclear to me whether you need to store the data by location, or whether you were explaining the physical process and you just need the total physical count, but are not recording location counts in your system.  The methods above are assuming there is not a location table (that's not to say that perhaps you wouldn't be well served to restructure things and have a location table).