Note: You've posted your question in the FM Server portion of the forum instead of the FM Pro section. (see tabs at top of screen).
Posts in Fm PRo Forum get a lot more views so you are more likely to get help when you post a question of this type over there.
You might search this forum for threads with the phrase "Inventory Ledger" for a look at how to set up a "ledger" system where you can enter a new record each time product is received and each time product is removed. The result works just like a bookkeeping ledger with "in" and "out" fields instead of "debit" and "credit" fields. To compute Qty in stock for a given product, to total up all the "in" amounts and subtract all the "out" amounts using summary fields and/or the Sum function. A script can also refer to these same fields to update a number field for Qty in stock in your product table--which can produce a more responsive layout when reviewing a list of products and their current inventory levels.
Here's the fields you might use in your join table to turn it into a ledger:
TransDescription (text--optional but allows you to document the type of inventory change)
cBal (Calculation: In - Out )
srBalance (Summary: Running Total of cBal, restart totals when grouped by ProductID)
sBalance (Summary: total of cBal.)
Whoops, sorry about the wrong forum.
It's an easy mistake to make given the interface design provided by RightNow. (And one that ModMan--our forum moderator--has requested that they improve...)
Let's say I accepted your proposition and will go with the inventory ledger option. I will, however, need to add a WarehouseID to the ledger table (the core problem I was trying to solve with this question is how to handle multiple warehouses) So when a shipment comes to a specific warehouse (it always comes to one specific warehouse), all the ledger entries will have this warehouse's ID. So when I need the amount of certain product in certain warehouse, I'll just sum up all the balances for the given ProductID and WarehouseID. I could easily write a script that does it, but I have a feeling this can also be done with a summary field. I don't quite understand how the summary fields work yet, especially the grouping part. Could you give me a hint as to how these summary fields should be set up? Thanks.
I will, however, need to add a WarehouseID to the ledger table
Yes, you will. I accidentally omitted that field in my example.
When you sort records on a field such as a ProductID or a WareHouseID, you group the records by that common value so you can set up a sort order that groups the records first by ProductID and then again by WarehouseID or you can reverse the fields and sort by WarehouseID and then by ProductID.
Either way, you can use a summary field to get the total product for a given ID in a given warehouse as well as using the same field to get a total on hand for a given product from all your warehouses combined.
You can add a sub summary layout part to a list view layout, specify "ProductID" as the "when sorted by" field and if you sort your records by productID, your sBalance field will give the total product for each group of records with the same ProductID. Put the same field in a sub summary part "when sorted by" Warehouse ID and include that field in your sort order, you can get a total of records present in a given warehouse.
And if you remove the body layout part--just keeping the sub summary parts, you can get a report with one line for each productID grouped by warehouse with subtotals in each line.
Here's a tutorial on summary reports you can play with if you are interested: Creating Filemaker Pro summary reports--Tutorial
Thanks, I'm not yet entirely sure how to set the summary fields to be sorted by two different fields, (there seems to only be one field you can sort by in the summary field options) but I think I'll figure it out. But you write about using these summary fields in listview based reports. Is there any way to put them in portals?
For example, I have a Product Management layout, and I want a portal on it that shows which warehouses the product is stored in and in which quantities.
Likewise, on my Warehouse Management layout, if I select a layout, I want to see a list of all products that are stored in it and their quantities (it's gonna be a long list, I know)
In a summary report like I have described, you do not use the running total options. That's why I describe two summary fields--one with those options--for use in a ledger style layout and one that does not have them--for use in a summary report.
You can set up different sub summary layout parts and use the same summary field to get different subtotals. Take a look at the summary report tutorial as it illustrates this and how, by playing games with the sort order, you can actually get different reports from the same layout.
Such a summary report can't be displayed in a portal, but you can get the results you want if you define relationships that will support what you need. When you refer to a summary field in a related table, you get a value based on all related records instead of a group in a found set like you do from the summary report layout. Aggregate functions such as Sum () and Count () can also be used to get the same results.
You could put a button on your products layout that pops up the summary report in a new window or just takes you to that layout. WIth the right layout design and scripting, the report can list records only for that product and you can get one row for each warehouse with the totals in each.
You can also add relationships to your basic set up like this:
Product::anyField X AllWareHouses::anyField
AllWareHouses::WareHouseID = StockByWareHouse::WareHouseID AND
AllWareHouses::gProductID = StockByWareHouse::ProductID
AllwareHouses and StockByWareHouse are added occurrences of WareHouse and StockInWareHouse respectifvely.
gProductID is a field defined in WareHouse with global storage specified.
Add an OnRecordLoad script trigger to your product layout with this script:
Set Field [WareHouse::gProductID ; Product::ProductID]
With those relationships, you can put a portal to AllWareHouses on your Product layout with the non-running total summary field from StockByWareHouse plus a field from AllWareHouses to identify the WareHouse and you'll get your portal with counts for each warehouse.