1 2 Previous Next 21 Replies Latest reply on Jul 27, 2012 4:19 PM by philmodjunk

    Date Mask



      Date Mask


      I want to be able to type either of the following into a Date field:


      and have FM 12 display it as 26/07/2012 The Table field is set to Date and the Layout field is a Calender drop-down if that makes any difference?

      It would also be good if FM ignored any "/" or "-" characters that the user entered into the field, is this possible?



        • 1. Re: Date Mask

          Can the user enter something like:

          10712 or 1712 o 01712 for 01/07/2012 ?

          • 2. Re: Date Mask

            Probably no need to be honest, as we are all used to typing either a 6 or 8 digit date in MS Access. Does that make it easier?



            • 3. Re: Date Mask

              As long as the user types in leading zeroes for single digit numbers a simple calculation can convert the digit string into a real FileMaker date in a date field--which is what I am sure raybaudi had in mind.

              Use a text field for data input and an auto-entered calculation on a date field like this:

              Let ( [ T = Filter ( textfield ; 9876543210 ) ;
                        L = Length ( T ) ;
                        Y = Right ( T ; L - 4 ) ] ;
                      If ( L = 6 or L = 8 ; Date ( Middle ( T ; 3 ; 2 ) ; Left ( T ; 2 ) ;  If ( Length ( Y ) = 2 ; 2000 ) + Y ) )

              With the OnObjectValidate Trigger, it is also possible to enter such strings directly into the date field and this trigger can then perform a script that makes this change before data validation kicks in and tells the user the date isn't formatted correctly.

              • 4. Re: Date Mask

                Hi Phil,
                Can I check a couple of things?

                The field type is 'Date' with the above string entered as a 'Calculated value' with the 'Do not replace existing value of field (if any)' unticked.
                The input field on the layout is an 'Edit box' and not a 'Drop-down calendar'

                I'm testing based on the above but I can't get it to work, the error that's generated is:
                The value of this field must be a valid date in the range of years 1 to 4000 and should look like "25/12/2003".

                Do I need to add the OnObjectValidate trigger or is that another option?


                • 5. Re: Date Mask

                  Read my post again as I described two options. The auto-enter calculation option uses two fields, a text field for data entry and a second field of type date with the auto-enter calculation that converts it into a date.

                  The OnObjectValidate trigger is a bit trickier to set up, but because it kicks in before the built in validation message can complain that the data is not in the correct format for a date, the script can then replace the input data with data that is correctly formatted. Such a script would use Set Field--not an auto-enter calculation to convert the data.

                  In both cases, this is just a "getting started" example. You may want to add in additional details so that a user can enter somthing like 1/1/12 and  have it also be correctly processed as a date.

                  • 6. Re: Date Mask

                    Ah ok, the first option would mean lots of additional fields as we use Dates on dozens of layouts. I'm guessing we would need to overlay the calculated field on top of the input field to display the date correctly on the layout?

                    The OnObjectValidate trigger using a single field in the Table and a single field on the Layout sounds like a better option. Could this be used with the 'Drop-down calendar' control object?

                    • 7. Re: Date Mask

                      Data entered from the drop down calendar won't have this format issue to begin with. I don't forsee any issues with that.

                      Here's how I would write the script:

                      #This script converts dates entered as DDMMYY and DDMMYYYY with no delimitters and converts them into FileMaker dates when the OnObjectValidatae trigger is tripped.
                      Set Variable [ $FieldRef; Value:Get ( ScriptParameter ) ]
                      If [ not IsValid ( GetField ( $FieldRef ) ) ]
                             Set Variable [ $DateText; Value:Filter ( GetAsText ( GetField ( $FieldRef ) ) ; 9876543210 ) // Get just the numeric characters entered. ]
                             If [ Let ( $L = Length ( $DateText ) ; $L = 6 or $L = 8 ) // correct number of digits entered ]
                                     Set Field By Name [ $FieldRef; Let ( Y = Right ( $DateText ; $L - 4 ) ;
                                                                                  Date ( Middle ( $DateText ; 3 ; 2 ) ; Left ( $DateText ; 2 ) ; If ( Length ( Y ) = 2 ; 2000 ) + Y ) ) ]
                             End If
                      End If

                      • 8. Re: Date Mask

                        Hmmm just played with that a bit more in a test file and realized that strings such as:


                        did not trip an error and produced a valid, but different date.

                        I can fix that by using this for the set field by name step:

                        Set Field By Name [ $FieldRef; Let ( Y = Right ( $DateText ; $L - 4 ) ;
                                                                                  GetAsdate ( Middle ( $DateText ; 3 ; 2 ) & "/" & Left ( $DateText ; 2 ) & "/" & If ( Length ( Y ) = 2 ; 2000 ) + Y ) ) ]

                        • 9. Re: Date Mask

                          And I forgot to explain why this is passing the field reference as a parameter and thus has to use GetField and Set Field By Name. You've mentioned that you have numerous date fields where you want to use this tool. By passing the table::fieldName as a parameter, you can use the same script for every date field where you want to use this same capability.

                          I'd pass the field Reference with this expression in the optional script parameter box:

                          GetFieldName ( YourTable::YourDateField )

                          This way, if you use manage | Database | Fields to change the field name, the correct field reference is still passed as a parameter to this script.

                          • 10. Re: Date Mask

                            Thanks Phil, I think I got all of that typed up and matched to yours but I'm still getting the same error. Running the Script Debugger is showing the paramater as "210801", which is the text I entered. Shouldn't this show the field name, Table::FieldName?

                            • 11. Re: Date Mask

                              Yes it should.

                              Sounds like you put


                              into the parameter box instead of


                              The quotes are needed.

                              But I strongly recommend that you use: GetFieldName ( YourTable::YourFieldName )

                              See this demo file if interested: https://dl.dropbox.com/u/78737945/DateMaskOnObjectValidate.fmp12

                              • 12. Re: Date Mask

                                That's right, I've copied your entire script and duplicated everything else but still returning the same error. The only difference now is that the field is left with a "?" after I tab out of it.

                                Script debugger is now showing the parameter correctly...

                                • 13. Re: Date Mask

                                  If you are not getting an error message, it would appear the field is wide enough to enter 6 or 8 digits but too narrow to display the formatted date produced by the script. Try making the field a bit wider.

                                  • 14. Re: Date Mask

                                    Yeah, tried that but it didn't make any difference. The same error is being generated too...

                                    1 2 Previous Next