I have an Sales-table connected to Line-Items-table via id’s
My Sales can have 4 statuses – Quotation, Order, Invoice, Credit Note
The Line-Items-table is connected to the Products table.
I think everything totally normal.
Now I am somewhere in the system looking at a Product_ID.
Is it possible via SQL to calculate how many of this Product_ID are ”in order” ? (on Sales with Status: Order)
Or do I need a field in the line-items table that keeps track of the status of the corresponding Sale to do this calculation ? ( would like to avoid this)
(In FileMaker I could find all Sales with status Order – go to related line-item records - do a constrain found on the product id – and get a summary of Qty)