2 Replies Latest reply on Mar 19, 2009 3:28 AM by comment_1

    Keeping track of inventory that expires



      Keeping track of inventory that expires


      I'm working to create an inventory database for perishable goods. 


      My biggest problem is that I need the item number to stay the same, but maybe with a -number to specify which batch was shipped.


      For instance:


      Item number 254 'Milk'


      254-01 would be Milk that expires in march

      254-02 would be milk that expires in april


      Does that make sense? Is it possible or is there a better way to manage this?


      Also helpful would be for Filemaker to auto select the oldest product before the newer product when creating invoices.


      Thanks in advance! 

        • 1. Re: Keeping track of inventory that expires

          As a secondary thought would it be easier:


          I have 20 perishable items.


          If the part number stayed constant (ie 1001-Milk), but there was an attached list view that showed the all the batches.


          When the part was entered on the invoice, it would also reference the 'oldest' batch that still had inventory available, possibly in the description or as a separate column on the invoice?


          I'd appreciate any thoughts on this matter!




          • 2. Re: Keeping track of inventory that expires
               You should use two separate tables for this: a table of Products, with only general information about each product (e.g. ProductID = 254; ProductName = "Milk" ), and another table for the actual inventory (e.g. InvenoryID = 4567; ProductID = 254; Quantity = 20; ExpiryDate = 3/1/2009), with each batch being a separate record.