2 Replies Latest reply on Jun 15, 2014 3:46 PM by keywords

    Inventory Report

    probable

      Mac OS 10.7.5

      FileMaker Pro 13.0v1

       

      I want to show the current inventory of about thirty items. The items are containers of material of different descriptions. My inventory report needs to show the number of containers remaining.

       

      I’ve put the beginning inventory numbers in a table with item codes, brands and descriptions, but that could be changed. I call that table “Items”

       

      I haven’t decided where to put additions to the inventory, but it seems they should go in a table by themselves. I’ll probably call that table “Additions.”

       

      The daily use of the items is in its own table that is the heart of the database and is quite convenient. It’s called “Daily.” Although the use is entered as the amount taken from each container (the net quantity per container is not regular), the count of containers used is accumulated in summary count fields. FWIW, the structure that yields the counts is described in the next paragraph.

       

      In the Daily table, each record has a stored field with the code of the item used, a stored field with the quantity of material used, and also unstored, calculated fields for all the thirty-odd items. Each calculated field compares the stored item code to one of the item codes. If there is a match, the calculated field captures the quantity of material that is stored in that record. The calculated fields that do not correspond to the stored item code are blank. There is a summary count field for each of the calculated fields I’ve just described.

       

      So I have the Items table with the initial inventory quanties, the Daily table with the use or removal of containers from inventory, and will probably have another table called Additions to hold additions to inventory. Other than using SQL, is there a way to make a report that will show the item totals of the initial inventory, the quantites removed from inventory, and the additions to inventory?

       

      I haven’t succeeded in combining the initial inventory numbers with the counts of use.

        • 1. Re: Inventory Report
          erolst

          Why don't you just use a Transactions table, instead of one called Daily? Each record is one transaction stock intake or outtake for a given item.

           

          You can then find (relate, isolate) any given day by a Find (global field, portal filter).

           

          probable wrote:

          In the Daily table, each record has a stored field with the code of the item used, a stored field with the quantity of material used, and also unstored, calculated fields for all the thirty-odd items. Each calculated field compares the stored item code to one of the item codes. If there is a match, the calculated field captures the quantity of material that is stored in that record. The calculated fields that do not correspond to the stored item code are blank. There is a summary count field for each of the calculated fields I’ve just described.

           

          You really should NOT have thirty-odd fields calculated unstored fields in your Daily table (or Transactions table), plus one summary field for each. Think what needed to happen when you introduce a new Item …

           

          There is a perfectly good Items table to hold the current stock level for each item – meaning you have ONE field –  and I suggest you make that stored and set it via script with each transaction.

           

          Maybe for consistency's sake, add one inital stock input transaction for each item with the stock level you want to start out with.

          probable wrote:

          Other than using SQL, is there a way to make a report that will show the item totals of the initial inventory, the quantites removed from inventory, and the additions to inventory?

          People used FileMaker for these things before there was ExecuteSQL(), …

           

          If you want to track stock movements, use the Transactions table with a sub-summary by item, and use summary fields for the primary ID (count of transactions) and quantity. You can display the current stock level (provided you set it as suggested) from the related Items record.

           

          On that note, it is recommended to use internal serial ID (incrementing numbers or UUIDs) instead of any item codes; i.e. use meaningless IDs instead of natural, meaningful ones.

          • 2. Re: Inventory Report
            keywords

            Big tick to all that from erolst. A Transactions table such as that can handle both additions and usages of items, as stated. If you really need to, you can also have a separate purchase table which stores data about the purchase above and beyond the items themselves (if, say, a single purchase from a supplier was for several different items). Similarly, depending on what other data you want to record, you may need a separate daily activity table (perhaps to store details of a job which used a number of different items).