12 Replies Latest reply on Jun 5, 2010 5:50 AM by smcfeeters

    GetAsDate failing 10.0v3

    smcfeeters

      Summary

      GetAsDate failing 10.0v3

      Description of the issue

      GetAsDate has started failing as of June 1 2010.  My system date settings are yyyy-mm-dd and as such GetAsDate("2010-06-03") would return 2010-06-03.  Now it returns ?I have tried this on Filemaker Pro Adv under Mac OS X 10.6.3, and on Filemaker Pro under Mac OS X Server 10.6.3 - the results are the same - returning ?, on both systems. If I do Get(CurrentDate), the system returns 2010-06-03 If I switch my system settings to US (d/m/y), GetAsDate starts working.  Unfortunately the data streams I need to process all have the date in yyyy-mm-dd format.    

        • 1. Re: GetAsDate failing 10.0v3
          raybaudi

          Hi

           

          When your system settings are yyyy-mm-dd, save a copy of your DB as Clone and that calculation will work.

          • 2. Re: GetAsDate failing 10.0v3
            smcfeeters

            While saving a copy as a clone causes the clone to function properly, it does not address the issue with my production database.  I simply cannot start from scratch every "n" months.  I have 65 tables with1000's and 1000's of records in this database.

            • 3. Re: GetAsDate failing 10.0v3
              philmodjunk

              You should only have to do this fix once.

               

              Perhaps you can set up a script to import all your data into the clone and set it to run overnight or something.

              • 4. Re: GetAsDate failing 10.0v3
                smcfeeters

                I have no confidence that the problem will not happen again.  The database has been running for almost 11 months, with the date function working properly.  It has been running on a server, so it has no direct user interaction, yet suddenly it stops working.   While I have tried the clone and have seen that the date function works again, why would I believe it will continue to work?  If I was "changing" something so the problem will not re-create itself I could understand, but simply rebuilding the entire database and trying again seem like just wishful thinking.   Am I missing something here?

                • 5. Re: GetAsDate failing 10.0v3
                  comment_1

                   


                  smcfeeters wrote:

                  Am I missing something here?


                   

                  Probably - but then so are we. FWIW, I canot reproduce the issue, and it doesn't make sense anyway:  if the file knows how to get "2010-05-03" as a valid date, there's no reason why "2010-06-03" should pose a problem. I'd suspect some corruption has occured.


                  • 6. Re: GetAsDate failing 10.0v3
                    philmodjunk

                    This issue sounds related to this known bug:

                     

                    Get(CurrentDate) function within FMP 10 Send Mail script step resolves as US formated date only

                     

                    I believe that's why Daniele recommended this fix.

                     

                    Edit note: and you don't have to "rebuild your entire solution" just save a copy as a clone (no records) and import your data back into the clone. This can be done with a script so that you don't have to chain yourself to the computer and stare at progress bars all day. If you can run this when the database is not being accessed and changed, you can run the scripts and swap files pretty easily.

                    • 7. Re: GetAsDate failing 10.0v3
                      philmodjunk

                      I agree with Comment: "I'd suspect some corruption has occured."

                       

                      A corrupted index will be rebuilt by the import into a clone and this might be what you are dealing with here. You could try just rebuilding the index on that field--which can be done without making a new copy of the file.

                      • 8. Re: GetAsDate failing 10.0v3
                        smcfeeters

                        Thanks for the help.

                         

                        I will build a script to move the data over to the clone.  

                         

                        Personally I don't understand (and I hate when I don't understand),  how this is happening.   I can simply open the Data Viewer in the production database and type GetAsDate("2010-06-03") - evaluate now and get ?, yet the same query in the clone works?  No tables, relationships or anything involved, just a simple function. 

                        • 9. Re: GetAsDate failing 10.0v3
                          comment_1

                           


                          smcfeeters wrote:

                           

                          I can simply open the Data Viewer in the production database and type GetAsDate("2010-06-03") - evaluate now and get ?, yet the same query in the clone works?  No tables, relationships or anything involved, just a simple function. 


                          That would be consistent with the production file using a date format other that YYYY-MM-DD. But then you would get the same error with ANY date in this format - not just dates after June 1 2010.

                           


                          • 10. Re: GetAsDate failing 10.0v3
                            raybaudi

                            It is sure that my english isn't good but I understand that his DB started to fail on June 1 2010, not that an earlier date NOW works.

                            • 11. Re: GetAsDate failing 10.0v3
                              comment_1

                              Well, if that's true, then all they need to do is switch the file options back to "Always use current system settings" and investigate who/what caused the change.

                              • 12. Re: GetAsDate failing 10.0v3
                                smcfeeters

                                I have cloned the database and re-imported the data - and the database is again operational.

                                 

                                Let me explain setup for this solution:

                                There is an application database that is distributed to users, and there are 2 repository databases that sit on a server.  One of these repository databases just houses forms, while the other houses to data for this solution.  Part of the role of the data DB is to go to the internet every morning and download the current foreign exchange (FX) rates.  These rates are then parsed and added to an historical FX table.

                                 

                                When a users creates a new order in the system and it involves multiple currencies, the system gets the most current FX rate from the FX historical table and applies it to the order.  

                                 

                                This system has been in use since August 2009.

                                 

                                This week I started to notice some new orders with NO FX rates.  When I went to the FX historical table, all the entries up to June were good, but for June the date fields all contained "?".  When I looked at the script that imports the FX entries I determined that the GetAsDate function was returning "?" instead of the date.  Even using the Data Viewer and just entering GetAsDate("2010-06-02") returned ?

                                 

                                As recommended on the board, I created a clone of the database.  I ran the FX import script and everything was fine.  Last night I ran a script to import the data from the production database to the clone and now I'm running again.

                                 

                                For the record, the File Option are "Always use current system settings", and have not been changed.  In fact the server hasn't been touched or rebooted for over 30 days.  I'd really like to understand how this problem evolved so I can avoid it in the future.