5 Replies Latest reply on Jul 7, 2014 2:04 PM by keywords

    Update stock totals

    barenose

      I'm sure there's a simple solution to the but I'm having a brain fart today.

       

      I have a simple solution that (should) calculates how much stock is left after something is sold. I start out with the amount of stack, then each sold item deletes the amount sold.

       

      I made the starting amount of stock a global number then create a calc to delete SOLD from STOCK, i.e. REMAINING - STOCK - SOLD. However, the global gives me the wrong information in each record.

       

      Thanks!

        • 1. Re: Update stock totals
          mikebeargie

          Why is that global? a global value is the same for all records, you need a calculation that is unique for each product (stock).

          • 2. Re: Update stock totals
            taylorsharpe

            I'm not sure I would make it a global... I would probably make it a calculation field for the Items or Products table with a relationship to the table that the inventory transactions are in so you can sum up the inventory coming and going.  Then again, I would probably do this with an ExecuteSQL calcluation.  Make sure it is an unstored calculation. 

            • 3. Re: Update stock totals
              DanielShanahan

              Like Mike and Taylor, I suggest not using a global field.  I also think a calculation field would be problematic.  You'll need to update your stock at some point - either manually or via a script process - so I would have your stock quantity as a number and use a script to update it. 

               

              Once you've counted the stock you've sold, you'll need a way to mark those records so you don't count them again.

              • 4. Re: Update stock totals
                mikebeargie

                For methodology, I normally implement a "transactions" table and relate it to "products".

                 

                That way I can tie "transactions" to sales or invoices to have a record of what products were sold (outgoing), as well as have an interface to enter in inventory received (incoming).

                 

                From there, it's a simple ExecuteSQL() calc, or relationship with a quick sum() calc, to get my "in stock" number.

                 

                The transactions table also allows for a lot deeper metrics tracking (IE graphing sales of a product over a time period), and also provides a solid log for administrative review.

                 

                Using a single static field value for your stock is problematic as it can't be rolled back if someone accidentally overwrites it. Think of how much of a problem it would be if someone changed that in-stock number from 5 to 10 when they received 10 items, but you actually had 15 in stock.

                • 5. Re: Update stock totals
                  keywords

                  Isn't a simple transaction table the answer? In other words, have a table that tracks your products, where each record is a type of item you have for sale (eg. thingummies), and a separate transaction table that tracks sales and purchases, where each record is either an acquisition or a sale. Your initial stock will simply be a record in this table—48 thingummies, say. Back in your product table you have a calc that tracks the comings and goings by referencing the relevant transaction records.