8 Replies Latest reply on Jan 28, 2015 8:05 AM by fixadmin

    Persistent Validation Fault




      I'm using FM13 to build a stock file which includes orders, deliveries and stock use. I've set a validation calculation on the parts used Quantity field so that no-one can book out a quantity used that's not in stock:


      Validation for field "Quantity" =

      Quantity   ≤   Stock Live::stock Quantity



      This works when data is entered initially.


      However if the Stock Live Quantity is 1, one is used  then a user then delete's the value in the parts used table, any subsequent attempt to add a quantity to that record triggers the validation warning.


      New records still pass the validation test but fail to do so if their quantity is deleted and re-entered. This can be worked around by boosting the stock quantity to but that is an artificial workaround that doesn't accurately affect stock.



      Has anyone had this problem with validation calculations or have I made an obvious mistake?


      Cheers, Jonny.

        • 1. Re: Persistent Validation Fault

          Just thought I'd update this re the Stock Live::Stock Quantity field. The live stock value is an un-stored calculation.

          • 2. Re: Persistent Validation Fault

            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?

            • 3. Re: Persistent Validation Fault

              Hi Mike,


              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!


              Cheers, Jonny

              • 4. Re: Persistent Validation Fault

                Jonny -


                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.




                • 5. Re: Persistent Validation Fault

                  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.

                  • 6. Re: Persistent Validation Fault

                    Hi Mike,


                    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.


                    Cheers, Jonny

                    • 7. Re: Persistent Validation Fault

                      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.

                      • 8. Re: Persistent Validation Fault

                        Hi folks,


                        Scripts it is! Back to the drawing board (or time steal the best bits off some starter solutions).


                        Thanks for your help!