Just thought I'd update this re the Stock Live::Stock Quantity field. The live stock value is an un-stored calculation.
It's going to be very difficult to say without knowing more about how your solution is configured. How is the "Stock Quantity" field calculated? You say it's an unstored calculation; how it is derived? What is the relationship between the two tables?
Can you post a sample file?
Stripped down sample file attached, if you go to the 2nd record in the "usage record" layout there's a part (no. 123) in the portal with no quantity. I'm getting a validation warning when I try to put 1 in despite there being 1 available in stock.
If you make a new line for part 123 via the portal or directly in the "Parts Used" table it will let you add 1 but will get stuck if that value is committed, removed then re-entered.
The Stock Live quantity is an un-stored sum of deliveries minus parts used:
Sum ( Delivery Line::quantity recieved ) - Sum (Parts Used::Quantity) + Stock Live::Stock Adjustment
Thanks so much for having a look!
Your issue is coming from the way you've defined your summary calculation. Take a look at how the calculation evaluates from the portal:
Compare that with how it evaluates from the context of the related table:
Huh? Why is it doing that? Well, notice your Graph:
And let's see how the calculation is defined:
See the clause that says, "Sum ( Parts Used::Quantity )"? It's based on the join from Stock Live to Parts Used. But the relationship is only on the part number. Therefore, every record in Parts Used that has the same part number as the current Stock Live part number will be included - and subtracted from the total.
Look again at the screen shot from the Parts Used context. You have two records with part number 123. Even though they have different usage record IDs, they're still being included in the calculation, because they have the same part number. Therefore, FileMaker is saying, "Hey, you've already used that quantity - you're out."
To fix it, you need to include the usage record ID in the calculation / relationship so it's filtered for just the current order / usage record. That should get it to validate properly.
You have a circular thing going on here:
Stock Live quantity is an unstored calc that depends on the Parts Used::Quantity
but parts used::quantity's validation depends on stock live quantity
The best way to resolve this is not to rely on field-level calculations and validations for this but do stock movements as part of a scripted workflow so that data is set at the exact right moment instead of relying on when FM refreshes the unstored calculation results.
Thanks for looking into it.
The stock live calculation is meant to sum the quantities from all parts used.
I made a delivery record to generate a stock quantity of 2. One was used in the first parts used record that you refer to. The second should still be available and the stock quantity when I uploaded the file calculates to 1 in stock.
As it is I still can't add a value there but you can make a new line that uses 1 stock unit.
Frustratingly I've just tested this with the data viewer running it runs fine but the problem begins again when the value is not being watched.
As Wim pointed out, your logic is circular. This creates a race condition in the calculation engine and the results can be unpredictable. You need to script a validation process that stores the quantities you need and process the entry restrictions against that. Not only will it be more reliable, it'll be faster.
Scripts it is! Back to the drawing board (or time steal the best bits off some starter solutions).
Thanks for your help!