5 Replies Latest reply on Jan 21, 2013 10:20 AM by philmodjunk

    Join Tables



      Join Tables


           I think I have a Many to Many join setting.  I have a table of inventory items.  I have a table inventory locations. One inventory item will be stored in multiple places, i.e. liquor storeroom, main bar, banquet bar, service bar.  And there are main inventory items in the liquor storeroom. 

           As I read this, I would use a join table.  The JOIN TABLE would have a pKey to that table.  Then two foreign keys, one to Inventory and one to Storage.  

           What I am not sure of is the mechanics to simplifying the creation of this information.  When I create the inventory item, would a portal to assign the storage locations do the task?

        • 1. Re: Join Tables

               There are two basic methods for tracking inventory:

               1) Keep an updated count of each item in each location simply by periodically counting the amounts present for each item at each location.

               2) Track inventory by logging changes in inventory rather than the total on hand. The database then computes the total on hand by adding/subtracting the resulting inventory changes.

               Which method do you use?

               In terms of relationships, it would appear you have these tables:

               Inventory::__pkItemID = Inventory_Location::_fkItemID
               Location::__pkLocationID = Inventory_Location::_fkLocationID

               This makes Inventory_Location as the join table where you either log each inventory count or each change in inventory.

               On an Inventory layout, a portal to Inventory would list all records in the Join Table for one item and fields from Location can be included in the portal row to show the name of that location. That would list all counts made for that item or all inventory increases/decreases for that one item.

               On the other hand, a portal to that join table placed on the Location layout will list all records in the join table for that location. A filter on such a portal could show only the most recent count.

          • 2. Re: Join Tables

                 I am not looking to account for transactions in inventory change.  In the restaurant industry it is not something done.  It can sort of be done with wine inventory, but VERY few do it as there is not cost benefit.

                 I need to print count sheets.  So there is liquor in the Storeroom under the control of the purchasing dept.  There is liquor in the banquet storeroom that the Banquet Captain controls.  There is liquor in each bar that the room managers are responsible.  Each type of liquor carried needs to appear on the count sheet for that area. 

                 When the purchasing agent creates the items, entering all if the information that creates ONE record in the inventory table.  Do I put a portal on the layout to the location table?  So in the portal he notes the appropriate storage areas for that item of inventory.

            • 3. Re: Join Tables

                   I do this from a Layout based on the inventory table so that for one item, you can quickly specify the locations. This can be made to work from a list view layout so that you can see all of the items in inventory as a list.

                   You might try adapating the "checkboxes" format in this demo file to your use: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

                   Note that these checkboxes are actually buttons that add/delete records from the join table when clicked. This would appear to be what you need here as you can

              • 4. Re: Join Tables

                     Thanks Phil, I will give this a study.  I am down to printing the count sheets for my solution.

                • 5. Re: Join Tables

                       It may be possible to print "count sheets" from your join table or it may work better to print them from Inventory. The join table won't list an item at a given location unless you have a record in the table for that item and location where this may not be an issue from an Inventory based layout.