2 Replies Latest reply on Nov 7, 2011 8:23 AM by philmodjunk

    Validation

    Brucey

      Title

      Validation

      Post

      I have a database that requires the following validation applied to it.

      When I enter the date of birth, I want that field to be validated so a message appears saying, "You must be aged between 17 and 64.", if a particular person is under 17 and over 64 years old.


      I also have an age field defined, which calculates the age of a person once the date of birth is entered, but I cannot get it to validate and produce the above message.

      Any solutions appreciated.

       

      Regards

      Brucey

        • 1. Re: Validation
          LaRetta_1

          Hi Brucey,

          Using another calculation to calculate the age, which must be based upon the DOB field itself, and then asking for validation from that calculated age would produce a circular reference.  Try validation as:

          Let ( [
          now = Get ( CurrentDate ) ;
          then = DOB ;
          thisYear = Date ( Month ( then ) ; Day ( then ) ; Year ( now ) ) ;
          age =  Year ( now ) - Year ( then ) - ( thisYear > now )  // must subtract 1 because not yet birthday this year
          ] ;
          age  ≥ 17 and age  ≤ 64
          )

          • 2. Re: Validation
            philmodjunk

            Take your age calculation and use it as a validation rule on the birthdate field. An age calculation field must be unstored to update correctly as time passes so it can't be set up as an auto-enter field unless you only want to record the person's age at the time the record was created.

            Your validation rule can be written like this:

            Let ( [ Today = Get ( currentDate ) ;
                       age = Year ( Today ) - Year ( birthdateField ) - ( today > date ( month ( birthdatefield ) ; Day ( BirthdateField ) ; Year ( Today ) ) ) ] ;
                       age > 17 and age < 64
                  )

            Note: If you copy this expression onto your clipboard, the inequality symbols will not paste correctly into FileMaker and you'll need to correct them before this expression works.