7 Replies Latest reply on Dec 13, 2016 6:54 PM by kyleharry

    Multiple date validation





      Could you help me with this?



      I’d like to make the validation for my date fields with format: dd/mm/yyyy



      Field 1: In Date: The date the car is delivered to my garage

      Field 2: Fix Date: The date the car is fixed at my garage

      Field 3: Out Date: The date the car is delivered back to the customer.



      I want to make sure FileMaker validates the dates when someone enters the data into those fields, and show error messages if they don’t match the requirements:



      1. The date in Field 2 must be after the date in Field 1. The car cannot be fixed if it’s not delivered to the garage.

      2. The date in Field 3 must be after both the date in Field 1 and the date in Field 2 (if it’s entered). The car must be delivered back to the customer after it’s been delivered to the garage and with or without being fixed.



      I’ve tried calculations:

      Fix Date - In Date >= 0

      Date (Fix Date) - Date (In Date) >=0

      Day (Fix Date) - Day (In Date) >=0

      but none of them works.



      Any suggestion or tip will be much appreciated.



      Thank you and have a good day.

        • 1. Re: Multiple date validation

          The key is that validation is looking for a 1 or 0 for true (Validate) or Empty for false (not valid)


          So you need to use a case function or similar as per attached


          Table level validation is a bit ugly. Using a script you can create a more intuitive validation although not as table level robust perhaps? e.g script trigger on field validate .. if Fix date < in date, custom dialog message and then take them back to field or similar

          • 2. Re: Multiple date validation

            here you are

            • 3. Re: Multiple date validation

              Thank you, HammerOz and siplus. They both work.


              However, FileMaker only checks the validation when I add New Record only, not right after the date has been entered as I expected.


              Is it possible to make FileMaker check the field right after I've entered data and moved to the next field? I need to do so because my file has a lot of fields which require that kind of validation, and if it's not possible, if an user entered wrong data in several fields, there would be several error messages and the user would have to check several fields when he/she wants to add another record.

              • 4. Re: Multiple date validation

                Siplus' version would not  let you leave the out date empty. I have changed it.


                I have attached a script trigger to each field that is run on object exit.


                Down the track, a popover with a calendar / date picker that only allowed the next appropriate date might provide the best experience. For example http://www.modularfilemaker.org/module/mcalendar/

                • 5. Re: Multiple date validation

                  Thank you so much, HammerOz. Your file does exactly what I need.

                  I look at the script and don't really understand how it works but it does solve the problem, so I'll just use it anyway.

                  • 6. Re: Multiple date validation

                    No problem. Right click on one of the fields and choose set script triggers. The commit records script that is triggered simply forces filemaker to commit your field to the system and therefore validate. This script step is used a lot !

                    • 7. Re: Multiple date validation

                      Hey there - this is a great solution. Thanks!


                      Do you know way to reduce the dialogue boxes? At the moment it first displays my custom dialogue as set in validation with the "Revert Record" and "OK" option. "OK" seems superfluous as it will just keep popping up the same dialogue. The second dialogue asks me to confirm the revert to last entered data with "Revert" and "Cancel". This last step and the "cancel" button are redundant.


                      Any way to reduce just to on dialogue "The date entered must be on or after the Start Date" Button: "OK"?