3 Replies Latest reply on May 3, 2014 5:00 PM by erolst

    Inventory field help from store to store

    vcoctel

      Hi

      I have 2 questions

       

      1.- I have my inventoriy of clothes with a value list of multi colors and multi sizes, related to each product. Now I need to add the location (store) there the inventory is located, so I can see were I have the product, so If I need that product in the store 1, I can see in whitch store i can take it from.

      The question is how can i do to make a formula, so when I pass a product from the store 1 to the store 3, It sums in the store 3 and to substract it from the store 1.

       

      2.- How can I relate a field so I can only make an invoice of what i have in stock? If I dont have in stock I dont want that it lets me choose the product.

       

      Thank you so much

        • 1. Re: Inventory field help from store to store
          wimdecorte

          What you are missing is an inventory table (I am going to assume that your invoices / invoices line items table can double as inventory movements).

          And you'll need to add a locations table

           

          Don't try to calculate the # of items in stock dynamically based on the past invoices.  Each time something is sold, the inventory should be updated so that the "quantity on hand" is updated and set statically.

          • 2. Re: Inventory field help from store to store
            vcoctel

            Thank you for your help. I already have an inventory table whitch is

            relates to The products (is in blue) and to The inventory transactions.

            Also I have related the yellow table (locations) to the products.

            I want that the products of the invoices discount from the inventory, but

            also I need to have some tables or I do not how souls I do it, to put the

            products I'll be reciving and to transfer from a store to another store.

            Thank you

             

            El Saturday, May 3, 2014, wimdecorte <noreply@filemaker.com> escribió:

             

                 <https://fmdev.filemaker.com/index.jspa>   creato da wimdecorte<https://fmdev.filemaker.com/people/wimdecorte>in *Advanced

            Discussion* - Visualizza la discussione completa<https://fmdev.filemaker.com/message/146682#146682>

             

            • 3. Re: Inventory field help from store to store
              erolst

              vcoctel wrote:

              I have 2 questions

              1.- […] when I pass a product from the store 1 to the store 3, It sums in the store 3 and to substract it from the store 1.

               

              2.- How can I relate a field so I can only make an invoice of what i have in stock? If I dont have in stock I dont want that it lets me choose the product.

               

              Both requirements are (relatively…) easy to implement if you have the right structure in place; they even come sort of naturally.

               

              Here's one way to do it: Assume tables and fields like (without listing the respective primary key) …

               

              Items --< ItemsInStore (itemID_fk, storeID_fk, quantity) >-- Store

               

              … and …

               

              TransactionTypes --< Transactions (transactionTypeID_fk, customerID_fk/storeID_fk (the transfer sender/receiver) --< TransactionLineItems (itemID_fk, {storeID_fk,} quantity, processed?, sign)

               

              TransactionTypes could be e.g. Purchases (+1), Invoices (-1), TransferIn (+1), TransferOut (-1).

               

              This would give you a framework in which to purchase, sell and transfer items (from supplier / to customer / between stores); sign your line items quantities as per TransactionType, and use scripts to process the unprocessed line items, i.e. update their ItemsInStore quantities.

               

              Since each transaction must happen within the context of a store, you can constrain the possible item selections to those where Store -- ItemsInStore::quantity > 0.