Omitting (or finding) records on the basis of the value of a summary field in a sub summary part
I have an inventory file which tracks location, product information, batch information (specifics and expiry dates).
Each location has a defined set of products with varying quantity requirements defined at the product level.
For each product at a location, the required set can be made up of the same product via any combination of batchs. Batch data is recorded for each location.
Each location is auditted and the quantities of each batch are tallied.
I have a summary field in the table LocationContent::ProductTotal which sums the quantity of the product from all batches in that location.
I need another field that performs a calculation that is basically defined as the quantity requirement for a product for a location, 'LocationRequirement::ProductQuantity' minus the 'LocationContent::ProductTotal' field above to give the replacement amount. (this tells the staff member how many of product X to put in the location). It does not matter which batch any product is topped up with so long as the batch is recorded and tracked. I have a calculation field as defined above LocationContent::ProductReplacement
I need a report which lists all products in a location/s requiring product replacement.
So far I have a list layout from table 'LocationContents'. The layout has a sub-summary part sorted by Product::Name. The sub-summary part has the field 'LocationContent::ProductReplacement'. So hopefully for a location I can see a list of batches and quantities for each product, categorised by product and with a total quantity to top up for the whole product set.
But wait, there's more...
Most of the time, most locations do not need much replacement. Most of the product replacement values are zero. I need to eliminate these from the found set and only display Products for which the summary of their batch records is less than the requirement for the product for that location.
Any input warmly received,