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

    Date Mask

    brian.curran

      Title

      Date Mask

      Post

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

      260712
      26072012

      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?

      Thanks
      Brian. 

       

        • 1. Re: Date Mask
          raybaudi

          Can the user enter something like:

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

          • 2. Re: Date Mask
            brian.curran

            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?

             

            Thanks
            Brian. 

            • 3. Re: Date Mask
              philmodjunk

              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
                brian.curran

                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?

                Thanks
                Brian. 

                • 5. Re: Date Mask
                  philmodjunk

                  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
                    brian.curran

                    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
                      philmodjunk

                      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
                        philmodjunk

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

                        33222012

                        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
                          philmodjunk

                          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
                            brian.curran

                            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
                              philmodjunk

                              Yes it should.

                              Sounds like you put

                              YourTable::YourFieldName

                              into the parameter box instead of

                              "yourTable::YourFieldName"

                              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
                                brian.curran

                                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
                                  philmodjunk

                                  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
                                    brian.curran

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

                                    1 2 Previous Next