2 Replies Latest reply on Oct 17, 2013 7:16 AM by philmodjunk

    Multiple criteria validation

    RonJennings

      Title

      Multiple criteria validation

      Post

           I'm trying to figure out a field validation calculation based on all of the following criteria:

           1) Validation only occurs if the record was created on or after a specified date (I think I've got that part worked out but possibly getting bollixed up by syntax (parentheses & commas)).  This is necessary because they have 50K records and are just now adding this field but they need to be certain it gets entered from now on. Browsing or modification of data in other fields of records created prior to the specified date can't be allowed to cause the database to start flipping out with validation messages (which will be turned on).

           2) Value cannot be negative

           3) Value can equal 0 OR be equal to or greater than the value of Field A (but not be between 0 and the value of Field A). Field A is already validated as equal to or greater than 0 and must be entered based on the same date criteria mentioned in #1.

           If the value entered violates any of the conditions, I want the user notified immediately, not when they try to leave the record.

            

           Side question, is there a SIMPLE way to do conditional field colors?  I mean "if value is negative or 0 then background color is red" It seems like something as basic as this should be an option in the Graphic section of the Inspector or something under Data. I know there are some coding options for it but they seem unnecessarily long ways to skin a basic cat.

           Thanks in advance for any help/tips.

        • 1. Re: Multiple criteria validation
          JonJ

               Hi,

               The second point is easy... if you have FileMaker 11 or higher, conditional formatting is a breeze. Just right-click on the field in layout mode, and select 'conditional formatting' (see http://www.filemaker.com/11help/html/edit_layout.10.14.html for more details).

               The first point is more complex...

               Field validation mostly occurs on record commit (one exception is date format in date fields). If you want to notify users immediately that their entry is liable to fail validation, you may need to use script-triggers to keep a track of what's being typed (this is filemaker 10 and up, see http://help.filemaker.com/app/answers/detail/a_id/7465 ).

               You can trigger a script to run when someone types in the field, and/or when someone tries to exit the field, and block the action if you're not happy with the result (I don't know if you have any experience with web design, but it's very similar to how people use javascript to validate HMTL form fields).

               The advantage of this is that it's immediate, the disadvantage is that it is tied to a given layout object... if you fail to format all your layouts (or create a new layout and forget to set up the script triggers) then invalid data can be entered.

               Therefore, I'd persist with the field-level validation, as the final backstop, but use the script triggers to give users quick feedback before they commit/save the whole record.

               In terms of a validation that meets your needs, it would be something like:

          if(
          // always valid (returns '1') if older than this date 
          createDate < date( 12; 25 ; 10) ; 1 ;
          
          // now list the other criteria...
          case(
              // 1. not negative -- fail (return '0')
              self < 0 ; 0 ;
          
              // 2. can be zero
              self = 0 ; 1 ;
          
              // 3. can be greater then or equal to another field
              self >= otherField ; 1 ;
          
              // default backstop-- fail
              0
              )
          )

               Be aware that really complex validations can take a lot of tweaking to make sure that they *always* gets it right, and doesn't block an unusual, but valid, entry or allow an unusual, incorrect, entry. They can also slow down large-scale operations like imports and replace-field-contents, as there's extra work for FM to do.  

               Regards,

               Jon.

          • 2. Re: Multiple criteria validation
            philmodjunk

                 With FIleMaker 11 and newer, you also have the OnObjectValidation script trigger that you can use. This option can be very nice as it is tripped before any build in validation (even on date fields) and you can use Exit Script [False] to cancel out the user event that tripped the trigger.

                 Thus, you can start up a script that checks for invalid data, corrects the same, uses show custom dialog to inform the user of their error in a much more flexible fashion than a custom validation message and with options specific to your layout design or some combination of the two.

                 And you can still have the field option validation in place as JonJ suggests (and I agree with) as an "insurance policy"

                 And to make clear about your concerns about 1), when you add a new field validation option and/or a script trigger controlled validation, this will not affect existing data unless/until such a record is edited so you may not have to do anything special for your existing records unless you want to disable the validation for the older records.