10 Replies Latest reply on Nov 25, 2011 11:18 PM by LyndsayHowarth

    (date) function giving me error

    arasj3827

      Help...

      Im trying to get the following to work in a new calc field (result as text):

       

      "38" & year(date)&month(date)&day(date) &get(recordnumber). I get the following error:

       

      (date) table (or field...dont remember) not found.

       

      The only workaround I found was to create date field with get(currentdate) which then allowed the calc to work.

       

      Am I wrong...isnt (date) a unifersal function? The (date) works fine in a different table I have.

        • 1. Re: (date) function giving me error
          BruceHerbach

          Hi

           

          You probably want get(currentdate).  This returns the current date.  Date returns a date when the Month,day and year are provided. 

          So you might want to use it like this:

          date(month(get(currentdate));day(get(currentdate));Year(get(currentdate)))

           

          or in your original calc you could use:

          "38" & get(currentdate)  &get(recordnumber)

           

          Hope this helps

          Bruce Herbach

          • 2. Re: (date) function giving me error
            taylorsharpe

            When working with Dates, FileMaker thinks of each date as the number of days since 1/1/0001. 

             

            The Date ( # ; # ; # ) function allows you to convert a date to this number so that  Date ( 12 ; 25 ; 2011 ) is Christmas of this year, or as FileMaker thinks of it as 734496.  To test this out, just make a formula of:  Date ( 12 ; 25 ; 2011 ) * 1 and the answer will be 734496. 

             

            Also keep in mind the FileMaker uses the date standards you have set up for your operating system and date formats vary around the world a lot... so I am just talking about, and I am just thinking of typical American dates of MM/DD/YYYY. 

             

            One thing to keep in mind is that the Get ( CurrentDate ) function will return dates like 1/1/2011.  Usually when I am concactinating the date field, I also want the zeros in there for formatting results where the same numbers line up like in your example with Jan 1, 2011 and record Number 200, I would want   3801/01/2011200 instead of 381/1/2011200.  Actually, I probably would not even want the slashes and would look for something like 3801012011200.  Since your record number can vary in digits, I probably would do something like divide the record number by 100,000 (or whatever the max you think your database will ever get to) so the record numbers would line up such as 3801012011000200. 

             

            To get this result, this is the formula I would make:

             

                Let ( [

             

                F1 = Get ( CurrentDate ) ;

             

                F2 = Month ( F1 ) ;

                MM = If ( F2 < 10 ; "0" & F2 ; F2 ) ;

             

                F3 = Day ( F1 ) ;

                DD = If ( F3 < 10 ; "0" & F3 ; F3 ) ;

             

                YYYY = Year ( F1 ) ;

             

                RN = Get ( RecordNumber ) / 100000

             

                ; ]

             

                38 & MM & DD & YYYY & RN

             

                )

            • 3. Re: (date) function giving me error
              taylorsharpe

              Ooops, the RN isn't quite right.  You would have to do something like:

               

               

                  Let ( [

               

                  F1 = Get ( CurrentDate ) ;

               

                  F2 = Month ( F1 ) ;

                  MM = If ( F2 < 10 ; "0" & F2 ; F2 ) ;

               

                  F3 = Day ( F1 ) ;

                  DD = If ( F3 < 10 ; "0" & F3 ; F3 ) ;

               

                  YYYY = Year ( F1 ) ;

               

                  F6 = Get ( RecordNumber ) ;

                  F7 = Length ( F6 ) ;

                  RN = Case (

                          F7 = 1 ; "00000" & F6 ;

                          F7 = 2 ; "0000" & F6 ;

                          F7 = 3 ; "000" & F6 ;

                          F7 = 4 ; "00" & F6 ;

                          F7 = 5 ; "0" & F6 ;

                          F6 )

               

                  ; ]

               

                  38 & MM & DD & YYYY & RN

               

                  )

              • 4. Re: (date) function giving me error
                taylorsharpe

                Ooops, I swapped my semi-colon and bracket.... so:

                 

                    Let ( [

                 

                    F1 = Get ( CurrentDate ) ;

                 

                    F2 = Month ( F1 ) ;

                    MM = If ( F2 < 10 ; "0" & F2 ; F2 ) ;

                 

                    F3 = Day ( F1 ) ;

                    DD = If ( F3 < 10 ; "0" & F3 ; F3 ) ;

                 

                    YYYY = Year ( F1 ) ;

                 

                    F6 = Get ( RecordNumber ) ;

                    F7 = Length ( F6 ) ;

                    RN = Case (

                            F7 = 1 ; "00000" & F6 ;

                            F7 = 2 ; "0000" & F6 ;

                            F7 = 3 ; "000" & F6 ;

                            F7 = 4 ; "00" & F6 ;

                            F7 = 5 ; "0" & F6 ;

                            F6 )

                 

                    ] ;

                 

                    38 & MM & DD & YYYY & RN

                 

                    )

                 

                 

                By if you want things to sort by date, you might change the formula to:

                 

                   38 & YYYY & MM & DD & RN

                • 5. Re: (date) function giving me error
                  DavidJondreau

                  The more common way of adding leading zeroes is to use the Right() function:

                   

                  Right ( "00" & Month ( Get ( CurrentDate ) ; 2 )

                  • 6. Re: (date) function giving me error
                    taylorsharpe

                    OK, David... good idea.... obviously this brings up the question of whether the "If" function is faster or the "Right" function is faster and I would guess the "Right" function would be faster than a logical.  Assuming that is true, then using your formula would be a better way of getting a leading zero for the months and days.  Thanks for the suggestion and I learned something. 

                     

                        Let ( [

                     

                        F1 = Get ( CurrentDate ) ;

                     

                        F2 = Month ( F1 ) ;

                        MM = Right ( "0" & F2 ; 2 ) ;

                     

                        F3 = Day ( F1 ) ;

                        DD = Right ( "0" & F3 ; 2 ) ;

                     

                        YYYY = Year ( F1 ) ;

                     

                        F6 = Get ( RecordNumber ) ;

                        F7 = Length ( F6 ) ;

                        RN = Case (

                                F7 = 1 ; "00000" & F6 ;

                                F7 = 2 ; "0000" & F6 ;

                                F7 = 3 ; "000" & F6 ;

                                F7 = 4 ; "00" & F6 ;

                                F7 = 5 ; "0" & F6 ;

                                F6 )

                     

                        ] ;

                     

                        38 & MM & DD & YYYY & RN

                     

                        )

                    • 7. Re: (date) function giving me error
                      DavidJondreau

                      Faster? I doubt it makes a difference in 99.99% of databases. But it's clearer and more portable. Like with your last variable about the record number:

                       

                      Right ( "00000" & Get ( RecordNumber ) ; 6 )

                      • 8. Re: (date) function giving me error
                        taylorsharpe

                        Ahhh, I was only looking at the dates and not the RN (my bad).  Your suggestion applies there too.  So the formula would now be: 

                         

                            Let ( [

                         

                            F1 = Get ( CurrentDate ) ;

                         

                            F2 = Month ( F1 ) ;

                            MM = Right ( "0" & F2 ; 2 ) ;

                         

                            F3 = Day ( F1 ) ;

                            DD = Right ( "0" & F3 ; 2 ) ;

                         

                            YYYY = Year ( F1 ) ;

                         

                            RN = Right ( "00000" & Get ( RecordNumber ) ; 6 )

                         

                            ] ;

                         

                            38 & MM & DD & YYYY & RN

                         

                            )

                        /message/63697/edit

                        • 9. Re: (date) function giving me error
                          arasj3827

                          Thanks guys...

                          I initially thought that day/month/year(date) worked on its own without get(currentdate)...especially since I had it working on a different table. One would think it should work like that. However, after wrecking my brain I realized why it worked on the other table. I had a date field that was filled via a script with get(currentdate) and then the day/month/year(date) worked because the date field was populated..... Took me all afternoon to figure the simple answer...

                          • 10. Re: (date) function giving me error
                            LyndsayHowarth

                            Interestingly Date() in php gives you the date of today. THAT took a bit of getting used to

                             

                            - Lyndsay