1 2 Previous Next 15 Replies Latest reply on May 2, 2011 12:43 PM by margotjacqz

    data entry of month/year only

    margotjacqz

      Title

      data entry of month/year only

      Post

      I can't imagine this is a unique situation but can't find any guidance... Essentially this is about noting work expereince, as from a resume. The table (linked to people) includes fields for company, date start and date end. The date start/end fields are best viewed as month and year

      I'd like to be able to enter mm/yyyy - but can't figure out how to get that accepted as a date.

      Or, I am not certain I even need these to be date fields; but they must be consistant. If it's entered as text, how to set up a validation? (I suppose it's like the phone. I see the scripts, but haven't quite figured out how that works either)

        • 1. Re: data entry of month/year only
          philmodjunk

          mm/yyyy isn't a date, a date requires a day.

          Whether you actually need a date here depends on what you will do with this information. If you plan to sort on this field or find all records for a given range of months or all with a month/Year less than or greater than a specified month, year, then a date field makes sense. Otherwise a valdidated text field might be easier to work with.

          If you use a field of type text, you can set up a validation such as:

          Let ( [ month = GetasNumber ( Leftwords ( Self ; 1 ) ) ;
                    Year = GetasNumber ( RightWords ( self ; 1 ) ) ]
                  Patterncount ( self ; "/" ) = 1 and 
                  month > 0 and Month < 13 and
                  Year > 1940 and Year < Year ( Get ( CurrentDate ) )
                 )

          If you want to keep the field of type date, you might use a drop down calendar and this auto-enter calculation:
          Self - day ( self ) + 1

          Format the field to display only the month and year.

          With FileMaker 11, you can also use the OnObjectValidate trigger to perform a script to take mm/yyyy and change it into a date before the field validates as a date:

          Set Field [ YourTable::YourdateField ; Let ( [ month = GetasNumber ( Leftwords ( Self ; 1 ) ) ;
                    Year = GetasNumber ( RightWords ( self ; 1 ) ) ] ; Date ( month ; 1 ; Year ) ]

           

          • 2. Re: data entry of month/year only
            LaRetta_1

            Hey Phil, a few issues:

            Your calculation is missing a semi-colon after the closing bracket on the list portion of the Let() and you are mixing Year and Year() which breaks the calc.  I might suggest:

            Let ( [
            month = GetAsNumber ( LeftWords ( Self ; 1 ) ) ;
            Yr = GetAsNumber ( RightWords ( Self ; 1 ) )
            ] ;
                   PatternCount ( Self ; "/" ) = 1 and
                    month > 0 and Month < 13 and
                    Yr > 1940 and Yr < Year ( Get ( CurrentDate ) )
                   )

            But also, you cannot use xWords to split by the front slash because FM sees the front slash (between two numbers) as a single word.

            • 3. Re: data entry of month/year only
              margotjacqz

              Being able to sort would be very handy, so the final validation option looks like the place to start.

              • 4. Re: data entry of month/year only
                philmodjunk

                Thanks LaRetta, I forgot that one of the changes from older to newer versions is that / is no longer a word separator. (the words functions would have worked find in Filemaker 5.5 Embarassed)

                For entering just Month/Year and getting a date, I think I'd use a triggered script for most cases if I have FileMaker 11.

                With the OnObjectValidate triger, I could run this script to insert 1 for the day to convert it into a date.

                Set Variable [$month ; GetasNumber ( Left ( YourTable::YourField ;  Position ( YourTable::YourField ; "/" ; 1 ; 1 ) - 1 ) )]
                Set Variable [$Year ; Let ( L = Length ( YourTable::YourField ) ; GetasNumber ( Right ( YourTable::YourField ; L - Position ( YourTable::YourField ; "/" ; L ; -1 )))]
                Set Variable [$Year ; If (  $year  < 1000 ; $year + 2000 ; $year ) ]
                If [  $month ) < 1 or  $month > 12 or  $year  < 1900 or  $year  > Year ( get ( CurrentDate ) ]
                      Show Custom Dialog ["Data must be entered with this format: Month / Year"]
                      If [ Get ( LastMessageChoice ) = 1 /* Ok was clicked */]
                         Exit Script [False]
                      Else
                         Set Field [ yourtable::yourfield ; "" ]
                      End If
                Else
                    Set Field [YourTable::yourField ; Date ( $month ; 1 ; $Year ) ]
                End IF

                • 5. Re: data entry of month/year only
                  margotjacqz

                  Sorry I never stopped to thank you, both. FWIW I finally got a chance to tackle this and took a slightly different route. realizing I had more different ways data could be badly entered, I used a filter :

                  Set Variable ($moyr; Filter ( Get ( ActiveFieldContents ); "0123456789"))

                  Assuming four probale result formats, mmyyyy myyyy mmyy myy. I have a collection of four parsing statements, before tests for values and a final Date($month;1;$year) or error dialogue . (I don't have Advanced so cant dopy and paste so forgive abbreviated script and typos) .

                  An ODD thing is happening.

                  The two even length based choices:

                       IF length ($moyr) =6 / Set Variable ($month=left ($moyr;2) / Set variable ($year = Right ($moyr);4)

                  or        IF length ($moyr)=4 / Set Variable ($month=Left $moyr;2) / Set variable ($year = Right ($moyr);2)

                  enable the expected result.  The two odd ones, below, do not. So if someone enters 4/2011 or 12-10 (etc) the script fails.

                       IF length ($moyr) =5 / Set Variable ($month=left ($moyr;1) / Set variable ($year = Right ($moyr);4)

                  and      IF length ($moyr) =3 / Set Variable ($month=left ($moyr;1) / Set variable ($year = Right ($moyr);4)

                  Anyway, it works basically and I am moving on to the next complications. just putitng it out there as an end of the month puzzle.

                  • 6. Re: data entry of month/year only
                    philmodjunk

                    Hmmm, it would help to see your actual script and while Advanced makes this easier, you can still post the actual script if don't have advanced.

                    1. You can upload a screen shot by clicking the edit link to your original message and uploading it there
                    2. You can upload a screen shot to a file share site and post the download link to a new response you post here.
                    3. You can print a script to a PDF, open the PDF and then select and copy the script as text from the opened PDF.

                     

                    I don't see any errors exect for your very last step and it could be a typo instead of the actual error. The last Right function should be:

                    Right ( $moyr ; 3 )

                    • 7. Re: data entry of month/year only
                      margotjacqz

                      Ah, Sorry I had overlooked that print button. (And, it is a typo, should have been 2.) Here's the full script. It's kind of brute force, but I am still getting acquainted. Stipulate that the date entry might include a variety of possible month- year combinations. Want a date value for sorting purposes. On testing this I find that entries such as 04/2011 or 04-11 return the expected result, but 4-2011 and 4.11, do not.  pointing to $month of length 1.

                      Run on object validate.

                      Set Variable [ $moyr; Value:Filter ( Get ( ActiveFieldContents ); "0123456789") ]
                      If [ $moyr="" ]
                        Exit Script [ ]
                      End If
                      If [ Length ($moyr) = 6 ]
                        Set Variable [ $month; Value:Left ($moyr; 2 ) ]
                        Set Variable [ $year; Value:Right ($moyr;4) ]
                      Else If [ Length ($moyr)=5 ]
                        Set Variable [ $month; Value:Left ($moyr; 1 ) ]
                        Set Variable [ $year; Value:Right ($moyr;4) ]
                      Else If [ Length ($moyr) =4 ]
                        Set Variable [ $month; Value:Left ($moyr; 2 ) ]
                        Set Variable [ $yr; Value:Right ($moyr;2) ]
                        Set Variable [ $year; Value:If ($yr < 50; $yr+2000; $yr+1900) ]
                      Else If [ Length ($moyr) =3 ]
                        Set Variable [ $month; Value:Left ($moyr; 1 ) ]
                        Set Variable [ $yr; Value:Right ($moyr;2) ]
                        Set Variable [ $year; Value:If ($yr < 50; $yr+2000; $yr+1900) ]
                      End If

                      If [ ($month <13) and ($year >1960) and ($year<2030) ]
                        Set Field [ Date ( $month ; 1 ; $year ) ]
                        Exit Script [ ]
                      Else
                        Show Custom Dialog [ Title: "something's wrong here. "; Message: "Enter Valid MM YYYY (or yy) format. Between 1960 and 2030"; Buttons: “OK” ]

                        If [ Get ( LastMessageChoice )=1 ]
                           Set Field [ " " ]
                           Exit Script [ ]
                        End If
                      End If

                      • 8. Re: data entry of month/year only
                        philmodjunk

                        The problem appears to be due to the fact that you are starting with text and then turn around and treat the values as numbers. That messes up the If step where you compare the values in the variables to numbers.

                        There are some style differences here, but it's the inclusion of the GetAsNumber function to coerce the text into number format that makes the difference.

                        Set Variable [ $moYr; Value:Filter ( MonthYearIntodate::DateText ; "0123456789" ) ]
                        Set Variable [ $Len; Value:Length ( $moYr ) ]
                        If [ $Len = 6 ]
                               Set Variable [ $Month; Value:GetAsNumber ( Left ( $moYr ; 2 ) ) ]
                               Set Variable [ $Year; Value:GetAsNumber ( Right ( $moYr ; 4 ) ) ]
                        Else If [ $Len = 5 ]
                               Set Variable [ $Month; Value:GetAsNumber ( Left ( $moYr ; 1 ) ) ]
                               Set Variable [ $Year; Value:GetAsNumber ( Right ( $moYr ; 4 ) ) ]
                        Else If [ $Len = 4 ]
                               Set Variable [ $Month; Value:GetAsNumber ( Left ( $moYr ; 2 ) ) ]
                               Set Variable [ $Year; Value:GetAsNumber ( Right ( $moYr ; 2 ) ) ]
                               Set Variable [ $Year; Value:If ($Year < 50; $Year+2000; $Year+1900) ]
                        Else If [ $Len = 3 ]
                               Set Variable [ $Month; Value:GetAsNumber ( Left ( $moYr ; 1 ) ) ]
                               Set Variable [ $Year; Value:GetAsNumber ( Right ( $moYr ; 2 ) ) ]
                               Set Variable [ $Year; Value:If ($Year < 50; $Year+2000; $Year+1900) ]
                        End If
                        If [ ($month <13) and ($year >1960) and ($year<2030) ]
                               Set Field [ MonthYearIntodate::Date2; Date ( $Month ; 1 ; $Year ) ]
                        Else
                               Show Custom Dialog [ Title: "Date format Error:"; Message: $MoYr & " became: Month: " & $Month & " Year: " & $Year; Buttons: “OK” ]
                        End If

                        • 9. Re: data entry of month/year only
                          margotjacqz

                          Phil, Thanks. I see what you are saying. Still don't understand why text works in some cases and not others, but I'll go with it. (And dam'd if it doesn't work !) Super and I hope others will benefit.

                          • 10. Re: data entry of month/year only
                            philmodjunk

                            When you compare text, "5" > "13" is a true statement for the same reason that Beet sorts after Abracadabra. It uses "alphabetic sort" rules.

                            Much of the time in FileMaker, you can just use text like it's a number, but this is one of the cases where you can't.

                            • 11. Re: data entry of month/year only
                              LaRetta_1

                              I haven't been following this thread but if you have true dates (and you should) then why are you going to all of this trouble when "the date start/end fields are best viewed as month and year"

                              Is all you want to do is VIEW the dates as April, 2011?  That can be handled at the layout level by itself.  Dates are very complex and varied.  It is best to always use dates and let FM generate its internal validations and formatting (according to OS).  Anything less than this will cause you problems.  You can forget performing searches, using non-equijoin relationships and layout displays in various forms.

                              Really ... is all you want this for is to display?  Truly, you should use data type of dates here.

                              • 12. Re: data entry of month/year only
                                margotjacqz

                                Laretta,  This is about starting with data ENTRY is month/ year and creating a true date.  It was easier to do this than to have to remember to add the 1 when copying information. Wanted to have date field to be able to sort, and possibly find. Seems to be working perfectly.

                                • 13. Re: data entry of month/year only
                                  LaRetta_1

                                  I see now that you specified 'entry' in other locations throughout the thread.  I was just going by the one sentence.  And since you hadn't yet responded, I thought you might still be stuck.  Thanks for clearing it up. I was only trying to help. Smile

                                  • 14. Re: data entry of month/year only
                                    philmodjunk

                                    And your help is both welcome and appreciated. Smile

                                    1 2 Previous Next