If product ID 0510 had 0 stock, which of product LLLL 3 would be kept in the list?
They would all stay on the list
See enclosed example.
You do a search on HideMe = 0 and only display those records.
I changed the attachment to be more efficient when dealing with lots of data, given that it does have to search on an unstored calc.
You'll need to use either a self join relationship that matches by product or an ExecuteSQLquery to get the total stock for a given product. This then enables you to set up find criteria to omit any record with a stock value of zero and a total product stock > 0.
Thank you Phil,
I have set up the self join relationship and am able to find all those products where
Product = Product
and ProdID ≠ ProdID
but, how do I calculate the "total product stock"?
Thank you siplus, great example.
I'm still having trouble with the "ProductStock", perhaps it has something to do with "Stock" being a calculation from a Transactions table
Your item record needs a field for in stock. When received it is set to a 1. If sold, damaged, missing etc the number is set to 0.
The record always exists for bookkeeping etc.
However, when you list the available stock you simply search for a 1 which removes the not in stock items for your view.
This number can be a calculation based on other fields on the record as a simple example:
isempty ( date sold ) = 0 ; 0
; isempty ( date damaged ) = 0 ; 0
; isempty ( date returned to mfg )= 0 ; 0
; isempty ( date given away )= 0 ; 0
; isempty ( missing ) = 0 ; 0
This simple calc allows tracking based on other fields.
I beg to differ.
you should leave the product record alone and do all your stock stuff somewhere else.
Ideally you have locations, stock at locations, transactions (productID; locationID, quantity; InOrOut) etc
You've got me confused.
Was your reply in response to ...
I'm still having trouble with the "ProductStock"
... or to my original question?
I was replying to the concept of having records not show.
There should be a table of product descriptions, prices etc. and a table of inventory received which links to that table. This is the table I would do what I suggest. Or use a third table for invoice items....
Price List table
Inventory Received table
Invoice Items table
The Inventory and Items tables might link to Price List Table which shows total receied and total sold and balance on hand etc.