7 Replies Latest reply on Jul 13, 2009 1:37 AM by CarlaS

    NEED HELP - Validation by calculation for date fields

    CarlaS

      Title

      NEED HELP - Validation by calculation for date fields

      Post

      I am trying to apply validation by calculation to date fields where users cannot enter future dates and can only enter dates within 90 days prior to the current date.  I can't figure out the calculation I need to enter in the field definition validation.

        • 1. Re: NEED HELP - Validation by calculation for date fields
          comment_1
            

          Try:

           

           

          Get(CurrentDate) - 90 ≤ Datefield and Datefield ≤ Get(CurrentDate)

           


           

          • 2. Re: NEED HELP - Validation by calculation for date fields
            CarlaS
               Thanks for the help.  The calculation seems right, but it only works when leaving (or committing) the record. Is there a way that the users could get the error message after entering each date as they may enter many dates before leaving they record.  Also, this calculation doesn't allow the user to leave the field blank once they have entered into the field. 
            • 3. Re: NEED HELP - Validation by calculation for date fields
              comment_1
                

              I am afraid that's just how validation by calculation works - on record commit. The reason for this, I think, is that a calculation may refer to other fields - so users get a chance to fill all before validation runs.

              Why do you need multiple dates with the same validation in a single record?

               


              CarlaS wrote:
              Also, this calculation doesn't allow the user to leave the field blank once they have entered into the field. 

               

              If you want them to be able to clear the field once a value has been entered, try:

              not Datefield or Get(CurrentDate) - 90 ≤ Datefield and Datefield ≤ Get(CurrentDate)

               


               


              • 4. Re: NEED HELP - Validation by calculation for date fields
                CarlaS
                  

                Users are entering dates in a production schedule.  The production schedule has 44 possible steps 44 dates.  Users don't enter dates into all of these fields, but they do enter dates into applicable date fields for an individual project.

                 

                I'm using Version 10.  I was reading somewhere that there may be a way in Version 10 of having the error mesage to appear after entering each date using a script.  Do you know anything about that?

                • 5. Re: NEED HELP - Validation by calculation for date fields
                  comment_1
                    

                  CarlaS wrote:
                  I was reading somewhere that there may be a way in Version 10 of having the error mesage to appear after entering each date using a script.  Do you know anything about that?

                  Yes, in version 10 you can attach a script trigger to a field, so you could 'validate' the entry by running a script upon field modification.

                   

                   

                  I believe you should split the schedule steps into individual records in a related table. This won't solve the validation delay issue, but it will save you the trouble of entering the same validation formula 44 times and/or attaching a script trigger to 44 fields - along with many other issues in the future.


                  • 6. Re: NEED HELP - Validation by calculation for date fields
                    CarlaS
                      

                    Thanks for the suggestion on creating a related table for the production schedule.  I believe I'll do it.  I created this database (with 9 relational databases) in 1998 using v5. Client has had updates every year since then, but I could never get them to upgrade FileMaker.  Finally, this year they upgraded!  Converting from V5 to V10 is a big change, so I'm guessing I'll have more questions for the forum.

                     

                    Thanks!

                    • 7. Re: NEED HELP - Validation by calculation for date fields
                      CarlaS
                        

                      Hey Comments,

                       

                      Thanks again for the suggestion on creating a related table for the production schedule.  I have implemented it and it is working great.  Just stopped back in so I could give you a Kudos!

                       

                      Carla