2 Replies Latest reply on Aug 10, 2011 5:40 AM by GraemeNimmo

    Trying to perform a stock-take of sorts



      Trying to perform a stock-take of sorts


      Hello everyone,


      I have just been asked to set up a system that will allow one department to calculate how much of their stock they have sent out to customers.


      The way things are currently set up is as follows:

      Each order has multiple records, each with an item identifier and the quantity desired, there is another table that has a record for each item that is dealt with, along with the number of items currently in stock, a count of how many have been sent and a count of the total number of the number currently in stock and how many have been sent out.

      I am trying to get my head around how to calculate the number of items in stock by querying the orders table and summing up how many of each item have been sent out.

      I am more than willing to admit that I have no real experience with FileMaker or database tools, except for what I studied as part of my degree a few years ago.


      Any advice, hints and tips would be greatly appreciated.




        • 1. Re: Trying to perform a stock-take of sorts

          You might want to set up a "ledger" for this. Think of items received as deposits and items sent out as withdrawels. Each type of item is a different 'account'.

          You can set up a table with at least these fields

          ItemID (link to other tables to access name, description type data)
          TransType (Shipped, Received, discontinued, shrinkage, etc.)
          cBal  (calculation: QtyRcvd - QtyRemoved)
          sTotal (Summary, running total of cBal, restart totals when sorted by ItemID)

          Every time you ship out items, make a new record and log the Date, QtyRemoved, and TransType. Every time you receive or manufacture new products, make new records and log Date, QtyRecvd, and TransType.

          Keep your records sorted by ItemID on a list or table view layout and you can see your current totals on hand for each item as well as how your inventory levels are changing over time.

          • 2. Re: Trying to perform a stock-take of sorts

            Thanks for that PhilModJunk,

            Sorry for not getting back sooner, I had a few "easy" tasks thrown at me on Friday afternoon after posting that question which I am now completing.
            I have managed to implement a variation of what you suggested which seems to be working brilliantly.

            I have modified things slightly in that I have two separate tables for orders and resupplies, the ordering one has details on the customers we are shipping to and the resupply one simply has a field for the date that the supplies were received and the quantities of each that we now have.

            I did wind up spending the better part of this morning hunting for why the running total didn't reset with each ProductId, but that appears to have been because I was sorting it by the ProductID from the products table and not the ID in the Orders table.

            Thanks again for that, made me look quite intelligent and capable, until I then pointed out that I got some help from the Filemaker Forums...