
1. Re: Formula/Function
mikebeargie Mar 5, 2013 6:18 AM (in response to rogerslp1957)You can either script this (static values), or replace "qty used" with a calculation (realtime calculated sum).
a quantity used calculation in FM12 (using ExecuteSQL, requires no relationship) would look like:
ExecuteSQL(
"
SELECT SUM(Qty)
FROM WorkOrders
WHERE Location = ?
" ; "" ; "" ; "2506" )
You could also relate your work order and inventory tables based on Part Number, then do a plain calculation for quantity used in inventory like:
Sum(WorkOrders::Qty)
The disadvantage of realtime calculations is that the number will continue to increase (and thus calculate slower as time goes on), until you clear or archive your work orders table. the way around this is to create a script that uses "set field" to update values in your inventory table everytime a work order is changed.