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.
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.
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
Thanks Phil, I will give this a study. I am down to printing the count sheets for my solution.
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.