3 Replies Latest reply on Feb 6, 2014 8:43 AM by philmodjunk

    Changing table fields from Date to Year only contents

    gregdc

      Title

      Changing table fields from Date to Year only contents

      Post

           OK an Elementary Question:  Working In FM13 table has 4 fields defined as Date type, about 1000 records.  The client now wants these 4 fields to be Year only.  Table is used in portal for data entry and 3 reports, so lots of things will change.   

           What is an easy way to change the content of these 1000 records to match the new requirement?   Is there a script that I can write that would

           1. do it in place

           OR

           2. do I need to copy the records out to a backup table, change the original table field definitions and then write a script to read through the backup table by Primary Key and rewrite the data in original table using its Primary Key?

            

           I have never written a script like either one of these so would really appreciate some code examples.  

            

           Thanks, this forum has always been a great help to me as I get started with FM. 

        • 1. Re: Changing table fields from Date to Year only contents
          IT_User

               Questions:

               Do you want to keep the old dates and just extract the year, or do you want both the old dates and the year?

               Will people be entering in the year themselves in the future?

                

               You can always use the Year calculation to get the year from a date.  Or for just visual purposes, you can go into the Inspector on the Data tab, under Data Formatting, where you can switch everything to "None" except for the year.

               For at least testing purposes you can create a calculation field, that would say Year ( dateFieldBeingUsed ), and see how that turns out.

          • 2. Re: Changing table fields from Date to Year only contents
            gregdc

                 In this case the client just wants to enter the Year and not the full date.  So loosing the old full date while keeping the Year is what they want.   It is funny because while they were entering full MM/DD/YYYY in all 4 fields, there are already hidden calc fields that do the YEAR(datefield) to be used in a couple of the reports.  

                 The big thing for them is moving forward they just want to enter YYYY.  So all the old data needs to be changed over.

            • 3. Re: Changing table fields from Date to Year only contents
              philmodjunk

                   Add  Number field to your table. Use Replace field contents with the Year function to extract the year and copy it into this new field.

                   Change the original date field to type number.

                   Use another Replace Field Contents to copy the year from the new field into the date field you just changed into a number field.

                   Delete the new field once you see the correct year in place of the date.

                   Since Replace Field Contents can modify every record in your table and cannot be undone, you may want to save a back up copy of your file before trying to use it for the first time.