2 Replies Latest reply on Mar 25, 2009 8:10 AM by robomacman

    Trying to Build Inventory for Tracking and Ordering

    robomacman

      Title

      Trying to Build Inventory for Tracking and Ordering

      Post

      I am trying to create a database that will allow me to do the following three things:  1.  List ITEMS that I can 2INVENTORY 3and prepare an ORDER report.

       

      The ITEM TABLE is pretty straightforward. It has 150-200 items. 

       

      The ORDER TABLE has Order ID, Order Date, To Order, Amount (Item::Price * To Order) and Total (Summary Field:  Amount) 

       

       

       

      How to I create a trackable Weekly Inventory and Generate Weekly Orders based on the numbers generated from the ITEM worksheet without having to select each item individually?  I really don't want to enter each item on the Inventory Table just to do this.

        • 1. Re: Trying to Build Inventory for Tracking and Ordering
          obeechi
            

          What do you want to put in your inventory table? Is this where you will have transactions recorded? Like, 10 came in, 8 came out? ... or ... 

           

          Item has many transactions

          Order has many transactions -- inventory is just a count

          so the transactions table is a join table between Item and Order

           

          Where do you want to work from when creating transactions?

          Each transaction record is going to need a foreign key that echos the item ID from the item table, and

          Each transaction record is going to need a foreign key that echos the order ID from the order table

           

          I'm probably missing something here ...  

           

          • 2. Re: Trying to Build Inventory for Tracking and Ordering
            robomacman
              

            The Inventory basically tracks what we have on hand.  I will be able to calculate usage within a time frame.

             

            I have the three tables

            ORDER   Order ID

            ITEM      Item ID  with On Hand and To Order fields 

            JOIN      Order ID, Item ID, Inventory ID and Inventory Date

             

            Since I absolutely do not want to recreate the entire items list each time, I used a portal to link the Item List with a cartesian sign "X".   Consequently, when I enter something for either the On Hand field or To Order - it changes the information in the other record as well. which of course, I don't want.

             

            I was able to create a portal on the ITEM TABLE to view previous inventory counts, but of course, this won't work correctly until the Join Table works correctly.

             

            Where do I go from here?