6 Replies Latest reply on Aug 10, 2009 7:43 PM by staffordavid

    Find Nonstandard Dates in preparation for converting files

    staffordavid

      Title

      Find Nonstandard Dates in preparation for converting files

      Post

      I am trying to convert a database containing over a dozen files from FMP 5.5 to 10.0v3. I had trouble with the files after my first try, so I am back to square one. In the kb article Conversion & Migration: Things To Think About Before Converting Your File(s) To the latest version of FileMaker Pro I found this little gem: "Furthermore, FileMaker Pro no longer supports nonstandard date separators. In other words, dates like 3*21*03 and 3$21$03 that were accepted as valid dates in previous versions of FileMaker Pro; will not be recognized as valid dates in current versions of FileMaker Pro. It is recommended that a Find and Replace be done on any affected date field so that the proper date separator is used."

      How do I Find and Replace? I think I have used spaces, hard spaces, hyphens, and slashes, and perhaps asterisks as separators on quite a few records. What are the standard date separators?

        • 1. Re: Find Nonstandard Dates in preparation for converting files
          philmodjunk
            

          The standard date separators are "/" and "-" I believe.

           

          I've just recently completed a 5.5 to 10 transition myself. Feel free to ask about any other problems you've encountered during the conversion.

           

          I wonder if you could strip out all the problematic date separators if you defined a new date field and used Replace field contents to copy the original date field contents into the new field. If that worked, you could then use a second Replace to copy them back.

           

          If that didn't work, I'd use replace to copy the dates to and from a number field to strip out the formatting.

          • 2. Re: Find Nonstandard Dates in preparation for converting files
            comment_1
              

            staffordavid wrote:
            What are the standard date separators?

            The standard date separators are (1) whatever your OS is using as the date separator for the short date format (unless you are using a file created on another system, and it's set to use its own stored formats), and (b) a forward slash / (this is always recognized as a valid separator).

             

             

            I'd suggest you convert the dates in the source version. It's been a while, but a calculation field (result is Date) like =

             

            Int ( Datefield )

             

            should normalize all entries to a common separator.


            • 3. Re: Find Nonstandard Dates in preparation for converting files
              staffordavid
                

              Thanks. Sounds like it is safest to use the forward slash. But it seems to me that I first need to put all the records with faulty date formats into a found set so I can see whether what I try works or not. Any ideas on how to find them? Can I write a script that searches fields, and if they are date fields then applies the right format to the data already there?

              • 4. Re: Find Nonstandard Dates in preparation for converting files
                philmodjunk
                  

                Use Comment's suggestion. (Mine works, but his uses fewer steps.)

                 

                You could do it this way:

                 

                Find all your records

                Click in the date field

                Selecte Replace Field Contents

                Specify a calculation and enter Int(yourdatefield)

                Do the replace.

                 

                This should strip out all the user entered formatting and revert everything back to standard delimitters.

                • 5. Re: Find Nonstandard Dates in preparation for converting files
                  comment_1
                    

                  If you use a calculation field, it will convert all dates - and you'll still have the original for comparison.

                   

                  Once you are satisified with the result, you can change the calculation field to type Date, and delete the original date  field (provided the calculation was a stored one). Be sure to have a backup in any case.

                  • 6. Re: Find Nonstandard Dates in preparation for converting files
                    staffordavid
                      


                    Thanks.  In case someone is looking to do something similar, a decription of what I ended up doing may be helpful (using comment's suggestion of using calculation fields rather than replacing seemed to give me a little more of a backup).  Here are the steps I took:

                     

                    1. Duplicate all date fields.
                    2. Change duplicated date fields to calculation, [date field], date type.
                    3. OK.  Let it process the changes.
                    4. Define fields: Change date field copies to date.
                    5. Copy over options from date fields to date field copies (or write them down).
                    6. Change date fields to calculation, [date field copy], date type.
                    7. OK.  Let it process.
                    8. Define fields:  change date fields back to date, and reconstruct options.
                    9. For some reason, it needs to process again.
                    10. If it's OK, delete date field copies that you made in step 1.