I've spent days experimenting with this with no luck. The fact that I have a separate IN line items and OUT line items tables seems to make it more complicated (however, I can't help this as I'm making these changes to an existing database).
That's the issue in a nut shell. If you could merge Shipping Line Items and Receiving Line Items into a single table, this would be much easier to set up. Neither table has all the info you need as you need a balance on hand for a given product at each location. Just because this is an existing database doesn't mean that this cannot be done, but it would require that you move carefully and be able to take the database down long enough to run an upgrade and then put it back up.
You could modify either of these two tables by adding a relationship and some calculations to compute quantities on hand for each product broken down by location.
Receiving Line Items::Product ID = Shipping Line ItemsByLocation::Product ID AND
Receiving Line Items::Location ID = Shipping Line ItemsByLocation::Location ID
Shipping Line ItemsByLocation is a new table occurrence of Shipping Line Items.
You could define cQtyShipped in Receiving Line Items as:
Sum ( Shipping Line ItemsByLocation::Quantity Shipped )
Define a summary field, sTotalReceived, as the total of Quantity Received.
Define cOnHandbyLocation as a calculation field:
GetSummary( sTotalreceived ; Product ID ) - cQtyShipped
Now you can set up a list type summary report with no body part--just a SubSummary part when sorted by Product ID. Place the Product fields and cOnHandbyLocation in this sub summary part and make sure to sort your records first by LocationID, then by Product ID. Place a second sub summary part when sorted by Location ID above this sub summary part and place the Location fields in it to add the Location sub heads.
Thank you! This has pointed me in the right direction.