3 Replies Latest reply on Jun 20, 2009 11:30 AM by comment_1

    Capturing a validation error



      Capturing a validation error



      I'm trying to accomplish with a formula something I know can be done with an involved script. I'm concatenating parts of two fields, depending on length, to auto-fill a third field.The formula is attached to that third field. Here's the first stage of my formula. The calculation is "Case" because I know there will be several other tests and results:


      Let ( [ Q1 = Length(Last Name) ≥ 4 ; R1 = Left ( Last Name ;4 ) & Left ( First Name;1 )] ; 

      Case ( Q1  and EvaluationError(R1) ≠ 504; R1;  "ALERT"  )) 


      The issue is with EvaluationError and when it evaluates. The field in question is set to be unique and not empty as well as to consist of exactly 5 characters. It seems this calculation should return error 504 if "R1" is a non-unique value. Using Data Viewer I see it always returns "0". When the formula evaluates and ends up creating a non-unique string I get the standard Filemaker message.When does "R1" evaluate? I would assume, in runtime, before EvaluationError.

       Lately I've seen many elegant formulae that obviate the need for a script and the two lines above are just a test of "EvaluationError" before I write the whole thing (my attempt at the aforementioned elegant formula!). Error 504 is a runtime error and shouldn't necessitate EvaluationError being wrapped in Evaluate. Oh, the calculation works fine except for the duplicates issue. 

      I suspect I'm missing something about EvaluationError and I can't seem to find much on the subject. 





        • 1. Re: Capturing a validation error
             EvaluationError() evaluates an expression, i.e. a formula. The expression you are evaluating is the RESULT of evaluating R1. If you were on a record of Rick Whitelaw, the actual test performed would be:

          EvaluationError( "WhitR" ) ≠ 504

          and it would return true, because the error is 0.

          In any case, I don't think a calculation can pre-test for field validation triggering. What you can do is test independently for the uniqueness of the proposed value: define a calculation field that returns the short name, and a self-join relationship matching this with the previously assigned short names - then see if there is a related record. Or use a script.

          • 2. Re: Capturing a validation error



            I had assumed the EvaluationError function would evaluate Left ( Last Name ;4 ) & Left ( First Name;1 ) as an expression and return 504 in the field's native context. Now I know it's not possible. Scripting it will be.



            • 3. Re: Capturing a validation error

              RickWhitelaw wrote:

              I had assumed the EvaluationError function would evaluate Left ( Last Name ;4 ) & Left ( First Name;1 ) as an expression

              For this to happen, you would need to enter the expression as text, e.g.:


              EvaluationError( "Left ( Last Name ; 4 ) & Left ( First Name ; 1 )" )


              Of course, that wouldn't get you closer to your goal, because the expression itself contains no errors.