3 Replies Latest reply on May 5, 2016 12:31 PM by mathepac

    How would you set this up...


      Here is the scenerio....


      I have the retail store master SKU list. Lets call this INVMST table, a sub set of this list is taken from the store and then loaded into a Vending Machine. Let call this INVVEND, products are then sold from the vending machine.


      My Vending solution is 4 tables, INVMST, INVVEND, TRANSACTIONS, and STORE INFO.


      I only need a sub-set of INVMAST, this is Category 100. I want to scan the barcode of a product from the INVMST and copy this info to INVVEND. How do I best represent the relationship to accomplish this? Will a join table between the INVMST and INVVEND be needed or Just straight relation based on the Category? !

        • 1. Re: How would you set this up...

          Why are you scanning the barcode?  Is this for a sale, return, adding to inventory or removing from inventory?  Usually copying data from one table to another indicated a possible better structure is needed.

          • 2. Re: How would you set this up...

            To broadly answer, Yes to all questions...


            in an effort to reduce a time consuming and currently manual process of counting what Inventory is in the VM, and then reconcile what has been sold at the end of the month or when the VM is empty. There is an existing POS system which the store works from, from which I have the Inventory Master list of all products sold within the store. A small sub-set of products are then sold from the VM. Yes, this is off-line to the store POS, no connections what so ever.


            The Goal in-mind was to gather all the products to fill the entire VM, since each products and each bay is barcoded, the products have SKU, or UPC barcodes. The bay location was manually barcoded. The reason behind the scanning was to allow anyone to do the task accurately, as right now there is a real struggle with reporting accurately what goes into the VM with the manual process. Scan each product and then scan the bay it goes into. This should give me an accurate count of the products that I start with.


            I wanted to use the basic Inventory Starter Solution (possibly, if not roll my own solution ). I realized there must be a better structure needed. So, the model in mind is the INVVEND table is the list of all the products in the VM. The TRANSACTION table records the date/time of the count of products going in and out.


            My INVMST table has 700K products, and I only need 1 Category from it, that count is around 155K. Those would be what is sold in the VM. Whats is sold in the VM is not all 155K but maybe fraction of that much. I thought I would be able to use my INVMST on FMServer, and have the rest of the solution on an iOS device with a barcode scanner. What is the best way then to try and accomplish this? is there a simpler way to do this? sorry for the long reply

            • 3. Re: How would you set this up...

              It strikes me that the answer to the problem is to use the "Location" attribute in the starter solution, or if this this is used for some other purpose, use another unused field or add a field. Validate "Location" against a table or list. This will eliminate redundancy of the second INVMST table.


              You may need to expand the TRANSACTIONS table to reflect "Location Transferred To" or "Location Sold From", default to null values or something like "Stock" and "POS".


              Is there only the one VM? Can you record a Row and Column for the VM as a stocking location?  Is the POS counted to the FM Inventory solution?


              HTH or offers food for thought. Redundancy is difficult to manage. The difficulty with two data-bases is having two data-bases.

              1 of 1 people found this helpful