9 Replies Latest reply on Nov 19, 2016 5:27 AM by beverly

    Date field wildcards

    jdevans

      I'm scripting a find on a List view. I've created a script that is designed to "filter as you type." It works well, until I get to a date field. I've got a handler for the date that uses a Let statement, and Case inside that. I have the let store var1 to the final search criteria. This fires as the user types, putting the cursor back at the end of whatever they've typed.

       

      However, I have the Case portion of the Let statement fill in the rest of the date search based on whatever the user has typed up to that point in time.

       

      The highlighted section below is failing. I get an error 500. (Date value does not meet validation entry options). I've tried using several wildcards to find for example, any date that begins with 12/2. The error 500 happens apparently because FM doesn't like 12/2*/* as a search criteria for a date. I've tried using "#/*" which gives error 500, as well as "@/*" which also give error 500. Not sure what to do to narrow down the dates based on 4 character date, with the first slash falling at position 3.

       

      Let (

        [ var1 = Drawing_Numbers::x_release_date_filter ];

       

       

        Case (

       

       

        var1 = "0" ;

        "*/*/*";

       

       

        Length ( var1) =1 ;

        var1 & "/*/*";

       

       

        Length ( var1 ) =2 and

        Left ( var1 ; 1 ) = "0";

        Filter ( var1 ; "123456789" );

       

       

        Length ( var1 ) =2

        and Position ( var1 ; "/" ; 1 ; 1 ) =2 ;

        var1 & "*/*";

       

       

        Length ( var1) =2 ;

        var1 & "/*/*";

                    

                     Length ( var1 )= 3 ;

        var1 & "*/*";

       

       

        /*this looks for 3char date with 1st slash at 2nd pos- (1/2) */

                     Length ( var1 ) =3 and

                     Position ( var1 ; "/" ; 1 ; 1 ) =2 and

                     Left ( var1 ; 3 )  ≥ 4;

                     var1 & "/*";

       

       

        /*this looks for 4char date with 1st slash at 3rd pos- (12/2) */

                     Length ( var1 ) =4 and

                     Position ( var1 ; "/" ; 1 ; 1 ) =3 ;

                     var1 & "*/*";

                    

        /*this looks for 2nd slash at 4th pos- (1/2/) */

                     Length ( var1 )= 4

        and Position ( var1 ; "/" ; 1 ; 2 ) =4;

        var1 & "*";

       

       

        /*this looks for 4character date with 1st slash at 3rd         

                      pos- (12/2) */

        Length ( var1 )= 4

        and Position ( var1 ; "/" ; 1 ; 1 ) =3;

        var1 & "/*";

       

       

        /*this looks for 1st slash at 2nd pos- (1/) */

        Length ( var1 )= 4

        and Position ( var1 ; "/" ; 1 ; 1 ) =2;

        var1 & "/*";

       

       

        Length ( var1 )= 5

        and Position ( var1 ; "/" ; 1 ; 1 ) =2

        and Position ( var1 ; "/" ; 1 ; 2 ) =5;

        var1 & "*";

       

       

       

       

        var1

       

       

        )

       

       

      )

        • 1. Re: Date field wildcards
          erolst

          You're not realising the potential of Let(); see ...

           

           

          Let ( [

            var1 = Drawing_Numbers::x_release_date_filter ;

            len = Length ( var1 ) ;

            lenIsTwo = len = 2 ;

            lenIsFour = len = 4 ;

            firstSlashAt = Position ( var1 ; "/" ; 1 ; 1 ) ;

            firstSlashIsAtTwo = firstSlashAt = 2 ;

            secondSlashAt = Position ( var1 ; "/" ; 1 ; 2 )

            ] ;

           

            Case (

           

              var1 = "0" ; "*/*/*";

           

              len = 1 ; var1 & "/*/*";

           

              lenIsTwo and Left ( var1 ; 1 ) = "0";

                Filter ( var1 ; "123456789" ) ;

              lenIsTwo and firstSlashIsAtTwo ;

                var1 & "*/*" ;

              lenIsTwo ;

                var1 & "/*/*" ;

           

              len = 3 ;

                var1 & "*/*" ;

              // this looks for 3char date with 1st slash at 2nd pos- (1/2)

              len = 3 and firstSlashIsAtTwo and Left ( var1 ; 3 ) ≥ 4 ;

                var1 & "/*" ;

           

             // this same test is repeated two paragraphs down, but with the correct result

           

              // this looks for 2nd slash at 4th pos- (1/2/)

              lenIsFour and secondSlashAt = 4 ;
                var1 & "*" ;

           

              // this looks for 4character date with 1st slash at 3rd pos- (12/2)

              lenIsFour and firstSlashAt = 3 ;

                var1 & "/*" ;

             // see two paragraphs up ... this is the correct version

           

              // this looks for 1st slash at 2nd pos- (1/)

              lenIsFour and firstSlashIsAtTwo ;

                var1 & "/*" ;

           

              len = 5 and firstSlashIsAtTwo and secondSlashAt = 5 ;

                var1 & "*" ;

           

              var1

           

             )

          )

           

           

          The less clutter you have, the easier you can spot a mistake

          1 of 1 people found this helpful
          • 2. Re: Date field wildcards
            jdevans

            wow you are right. I'm really making this hard on myself. Thanks!!!

            • 3. Re: Date field wildcards
              erolst

              Look again to see the error in your original calculation.

              1 of 1 people found this helpful
              • 4. Re: Date field wildcards
                jdevans

                It may take me a while to find it. If you know, I would really appreciate you pointing it out.

                • 5. Re: Date field wildcards
                  erolst

                  jdevans wrote:

                  you pointing it out.

                  In fact, I already did, as a comment where I deleted the paragraph in bold; you had the same test twice, but with different results, and the result of the first instance was – I think – wrong, and the second test was never reached.

                   

                  Another problem: look at the len = 3 section; the first test is only len = 3; if that is true, then all the other tests will never be run. You should re-arrange that like …

                   

                  len = 3 and some condition ;

                    someResult ;

                  len = 3 and someOtherCondition ;

                    someOtherResult ;

                  len = 3 ;

                  defaultLen3Result …

                   

                  Also, you could nest the len = x sections using another Case(); for the above:

                   

                  len = 3 ;

                    Case (

                      someCondition ;

                        someResult ;

                      someOtherCondition ;

                        someOtherResult ;

                       defaultLen3Result

                  ) ;

                   

                  len = 4 …

                  1 of 1 people found this helpful
                  • 6. Re: Date field wildcards
                    jdevans

                    Thanks for pointing out all these things. I really appreciate it.

                     

                    One thing that keeps popping up is the error 500. Apparently, Filemaker is very specific about the use of wildcards in date searches. So, things like 12/2*/* throws an error 500. 12/2@/* doesn't work, and neither does 12/2#/*.

                     

                    I would hope that it would find 12/20/anyYear, 12/21/anyYear, 12/22/anyYear and so-forth, but it doesn't. Doing a manual Find on this field using the Find mode button in the Status Bar yields the same result.

                     

                    Stumped on using wildcards for date ranges like this.

                    • 7. Re: Date field wildcards
                      erolst

                      jdevans wrote:

                      [...]  I would hope that it would find 12/20/anyYear, 12/21/anyYear, 12/22/anyYear and so-forth, but it doesn't. Doing a manual Find on this field using the Find mode button in the Status Bar yields the same result.

                      Don't use any wildcard character in addition to a value - and if you only have the day and month parts, dont use a second slash!

                       

                      12/31 works fine, 12/31/ does not.

                      1 of 1 people found this helpful
                      • 8. Re: Date field wildcards
                        jdevans

                        Thanks erolst,  you helped me a lot. I have it working about as well as it can now. I also went in and did a Replace field contents on the date field and used the GetAsText function on it. I also adjusted the script that generates this date field in the first place and have it creating it using the GetAsText. Seems to be less prone to date-related errors.

                        • 9. Re: Date field wildcards
                          beverly

                          I've read through the entire thread. IF i need very precise searches, I may have the 'date' parsed to three separate fields (m, d, y). This allows the searches and in some cases the sorts needed. Mostly the "yr_mo" auto enter field is beneficial to the point I need (along with the date field itself)

                          Year ( myDate ) & Right ( "00" & Month ( myDate ) ; 2 )

                          beverly