4 Replies Latest reply on Oct 4, 2013 10:29 AM by dcassano

    FIFO Inventory System

    dcassano

      I was asked to create an Inventory Database, no problem. I used the starter solution provided in FMP12. I modified it to fit our known needs, wrote scripts, and created reports. Now I need to create a FIFO Inventory database that selects the oldest issued purchase order for that item, record it and do a “count down” until that p.o. is at zero units, then select the next, etc. I also need an inventory report that will demonstrate the unit count available, by applicable p.o., total actual cost which will give me the Total Stock Value. I have looked through various web sites and even checked youtube for some direction on how to get this done. Can anyone offer some help or direction on creating a FIFO Inventory System. Thanks Debbie

        • 1. Re: FIFO Inventory System
          LyndsayHowarth

          HI Debbie,

           

          The way I interpret what you are asking for is to be able to create a running balance of stock so that at any point in history you can show the balance... is that correct?

           

          What you seem to be describing that you need is a script which does... step by step... the finding of the oldest record and recording the inventory balance at the time.

          Such a script would have something like:

          - show all records

          - sort by date

          - go to last record (or first depending on the order you sorted date)

          - set field... to the result of a calculation

          ....

           

          What you can also do is use Summary fields which can give you total, count, max, min, avg (and many more) which can also give you running values. These Summary fields become useful when you want to breakdown the balance by day, month, year, category or transaction. They are placed in sub-summary layout Parts and only show and recalculate when sorted by the specified field, when grouping is required. For running balances they are generally placed in the body part.

           

          Consider:

          - running balances will only be true to the found set of records. If you find only March 13 records.. then it assumes the inventory restarts at zero. You must either show all records or have a place where the balance carried forward is recorded... or perhaps a relationship to the data that looks at the entire set of records Feb 13 or earlier.

          - you can achieve the same thing with a relationship to an instance of the same table by Date>balance::Date + PONo >balance::PONo... where the balnace is simply the sum of the credits- the sum of the debits.

           

          Lots of ways to do this... I hope I have hinted at a few... and not confused you.

           

          - Lyndsay

          1 of 1 people found this helpful
          • 2. Re: FIFO Inventory System
            dcassano

            Thank you Lyndsay. I will start working on this and let you know how it went. Not confusing, very helpful and considering the 50 views this has received I am guessing I am not the only person faced with this challange.  Thank you again.

            • 3. Re: FIFO Inventory System
              beverly

              Lyndsay may have given you enough information. Let me see if I understand your needs:

               

              1. you have P.O.s which puts the items into INVENTORY, with qty and cost

              2. the next P.O. may have an item which is priced differently than the last time the same item was ordered

              3. you need the items put into INVENTORY in a way that when they are "sold/used" the item is sold at the cost/price set at the time it was put into inventory until that item is no longer in stock at that cost/price and gets the same item at the next price (by date) until all items are exhausted.

               

              I will tell you how I've done something similar, but not using FIFO. My client wanted to "average" the last 5 P.O.'s for an item to get the cost/price per unit of the item, then apply a markup. That seems to be sufficient to set the price sold of each item enough, so that the client didn't loose money on items sold for less than purchase.

               

              But you may need to "accurate" on selling at the same price you purchased the item, thus the FIFO.

               

              Some "heavy" scripting (definitely!) needs to be done. When selecting any item for sale, a script would need to look at the item (by date) and get the items NOT sold, then look at the qty needed for the sale. Step through the not sold item and mark the qty sold as needed per price. Set the invoice (bill of sale) with the qty/price.

               

              Beverly

              1 of 1 people found this helpful
              • 4. Re: FIFO Inventory System
                dcassano

                That is also helpful. The first task is to write a script that will assign a po # from the Projects Area (purchasing) to the Shipping/Receiving Form for that particular record. Here is my script:

                pick a po script.bmp

                 

                The problem I am having this that this script changes the first record in ShippingReceiving instead of the record I am trying to update. How do I get the current record that I "froze" in the beginning of the script to be the one that gets updated?