4 Replies Latest reply on Aug 30, 2011 11:51 AM by WarnerBros

    Changing large amounts of data

    WarnerBros

      Title

      Changing large amounts of data

      Post

      Hello,

       

      I have a database with over 200,000 records in it. I imported these records, and there is a date column that uses this format: YYYY-MM-DD. Filemaker has not recognized this format as a date; therefore, I have been unable to sort out the dates that I dont want to view.

      How can I change the column or get Filemaker to recognize this format?

       

      Thanks,

       

      Sam

        • 1. Re: Changing large amounts of data
          philmodjunk

          Take a look at Replace Field Contents. Using the calculation option, you can probably fix this issue. Replace Field Contents does the same modification calculation on all records of your found set. Since this is a major change that you can't undo, it's a good idea to make a back up copy of your file just before trying it out.

          If this is a text field, I suggest defining a date field for this date and using Replace field contents to convert the date into a compatible format and move the data into a date field so that FileMaker can manipulate this value as a date rather than text.

          • 2. Re: Changing large amounts of data
            WarnerBros

            Hi Phil,

             

            Sorry for my ignorance but could you possibly be more specific. When I tried to do the change you described above I ended up inputting the same date for every record. What is the calculation I would use if the field I have is set up as a text and has all dates set up in the format I originall mentioned (YYYY-MM-DD)?

             

            Thanks,

             

            Sam

            • 3. Re: Changing large amounts of data
              philmodjunk

              You use the calculation option in the Replace field contents so that it uses the current value of the text field to produce a date and enter it into the new date field.

              Make a back up copy

              Do a show all records to get all records into your found set.

              Click/tab into the new date field

              Select Replace Field Contents from the Records menu

              Select the calculation option.

              Enter a calculation similar to this:

                 Let ( d = YourTable::YourTextField ; Date ( Middle ( d ; 6 ; 2 ) ; Right ( d ; 2 ) ; Left ( d ; 4 ) )

              Just substitute your table and field names in place of YourTable::YOurTextField.

              I am assuming that all values for day and month are two digits long and that there are no spaces or other characters in the text field.

              • 4. Re: Changing large amounts of data
                WarnerBros

                That was amazing thanks so much!!!