7 Replies Latest reply on Aug 3, 2012 1:09 PM by philmodjunk

    Inventory in Multiple Locations

    TKnTexas

      Title

      Inventory in Multiple Locations

      Post

      I am updating an inventory solution for a restaurant.  I want to improve the count sheets.  The problem mostly impacts the alcoholic items.  Items are stored in the storeroom in back of the house.  And there will be product at the main bar.  There may be a service bar and/or a banquet bar. 

      In order to show up on count sheets I need multiple records?  I was thinking a join-table of product-location.  I have read about them.  When I did they immediately popped up as a solution to this problem.  But implementing this is what has me hung up.  Any assistance would be appreciated.

        • 1. Re: Inventory in Multiple Locations
          philmodjunk

          What is your current database structure? There's more than one way to manage inventory so we need to know the starting point before suggesting ways to handle multiple locations for the same inventory item.

          • 2. Re: Inventory in Multiple Locations
            TKnTexas

            Currently one record for each product.  When taking inventory in other than the main storeroom, we just flip the pages.  It would be best to print count sheets for each area.

            • 3. Re: Inventory in Multiple Locations
              MarcMcCall

              I dont really see whare you need to neciserrily have multiple tables of locations.  You could simplify it by having the diferent counts on each record of the product by adding a few field to the table.  Then Just have different layout for each count sheet reflecting the different location.  This will allow you to keep all informnation pertaing to each liquor in one place.  When you look at say Burban, it has the 3 fields for Store room, Main Bar, Service Bar, and so on.  Then Just have a print out for each one.  If you want to keep track of the history, take a look at http://www.nightwing.com.au/FileMaker/demosX/demoX01.html this will capture the field data everytime it is changed.

              • 4. Re: Inventory in Multiple Locations
                TKnTexas

                Not multiple tables, but multiple records.  One record per product-location.

                • 5. Re: Inventory in Multiple Locations
                  philmodjunk

                  My question was asking what Tables and relationships you have in place.

                  One method is to setup two tables. One table has one record for each inventory item. A related record logs each change to the inventory count and allows you to set up a "ledger" like view of all changes made to your inventory. Such a table can record the location where each change was made and you can set up summary reports that give you a total for each item with sub totals for each location.

                  A similar approach is set up the same way, but instead of recording the amount the inventory changed in the related table, you just record the current inventory. Again, a summary report can compute both totals and a break down by location.

                  Which method is best depends on the procedures you want to implement for tracking inventory and, if you use three tables, they are not mutually exclusive. Both allow to see current inventory counts plus be able to examine how your inventory levels change over time and at each location--useful information for setting restocking levels.

                  Either method avoids adding a table for each location, though you might link in a locations table where you have exactly one record for each location.

                  • 6. Re: Inventory in Multiple Locations
                    TKnTexas

                    My apologies.  Currently there is a table for inventory product, one record per item.  In a separate table there is the inventory count.  One record for each item counted.  There are no calculations for a perpetual on hand count.  Unlike retail this is not feasible.  I buy a case each of carrots, tomatoes, onions, and celery.  I sell veggie platters with celery and carrots.  There are carrots, celery, tomatoes, and onions on the salad bar.  I use all of these when I cook items, ie soups, stews, etc.

                    The question is count sheets.  I have these items tomatoes, celery, and carrots stored in the main cooler.  Onions are stored in the dry goods storeroom.  Some of all of these are kept in the prep kitchen and on the main cook line.

                    With regard to beverages:  I may have several kegs of beer in the main cooler in the liquor room.  I will have at least one behind each bar I have.

                    When I take my inventory count sheets to the main storeroom I have nearly every item listed one the sheets.  These are printed in the order the storage areas are laid out.  These count sheets in the production areas will not necessarily be as extensive or in the same order.  Because I knew my count sheets, I could find items quickly.  But that is not the efficient way to do them.

                    Ideally the main storeroom can be counted with one set of count sheets.  At the end of the business day only the production areas are counted. These sheets may or not have all the same items, but they are laid out differently.

                    In the storeroom liquor is laid out alphabetically by type.  Behind the bar the bottles are grouped by what is grabbed most often.  Probably overexplained.

                    • 7. Re: Inventory in Multiple Locations
                      philmodjunk

                      What you describe is the second option in my last post where you log the current inventory counts instead of the changes to them.

                      This looks like something where you can script the production of records in the "count" table, which can be printed out or displayed to provide your count sheets and then updated to enter the actual counts.

                      One new detail that I hadn't considered was recording the locations where a given item is expected to be found.

                      Inventory::InventoryID = Inventory_Location::INventoryID

                      Locations::LocationID = Inventory_Location::LocationID

                      sets up a many to many relationship so that you can list multiple items at a given location and multiple locations for a given item. This would be so that you can print out or display a list of all items to count at a given location without also listing the items that are never at that location.