You probably need a summary field with the calculation set to be the sum of Purchases::Quantity, then set up your report to perform the find on Purchases::barcode or set it up to group by barcode to see the subsummaries for each item
Many thanks for your prompt response. I'll give it a try and let you know how I go.
I've been able to gather the quantities using the group by barcode in a report using subsummaries. Many thanks for that suggestion.
In this case, is it even possible to have a dynamic calculation field in the "Products" table to do a sum of "Purchases::Quantity" based on the "Barcode" or is it something that will need some scripting?
Any advise or assistance would be much appreciated.
You should be able to define a field, say cQty, as a summary field. Then define it as a total of the Purchases::Quantity with the "restart summary for each sorted group" option checked. If you have your report set up to group by bar code you should get the quantity for each Barcode in that report in the cQty field.
Hi Mark, and many thanks for your constant help.
I've been able to create a cQty field in the "Purchases" table to "=Total of Quantity (running with restart), when sorted by Barcode". I can see in the table view of the "Purchases" table that it continues to add the quantities grouping by the "Purchases::Barcode".
The issue i'm struggling with is how do I go about having a field in the "Product" table which will reference to the "Purchases::cQty" where "Product::Barcode" = "Purchase::Barcode". Would I use a calculation field with "if" statements?
The whole idea which i'm trying to achieve is that when i'm looking at any given record in the "Product" table, I want to be able to see the total stock available. This total stock field will look up the "Purchases" table grouping by the "Product::Barcode" value (Primary Key) that I'm currently viewing and calculate the total quantity.
Maybe i'm going about it the wrong way but any help would be appreciated.