9 Replies Latest reply on Dec 11, 2008 6:53 PM by adcoleman

    Moving mm/dd/yyyy into separate fields

    adcoleman

      Title

      Moving mm/dd/yyyy into separate fields

      Post

      I'm working with an FMPro 9 db in which dates have been entered into a text field as mm/dd/yyyy (eg., 12/21/2008).
       
      I need to move this data into three separate text fields: one for month, one for day, one for year. I have created those fields. How do I get the relevant numbers (but not the slashes) into the appropriate fields?

        • 1. Re: Moving mm/dd/yyyy into separate fields
          raybaudi
            

          Hi quickstudy

           

          let say that the name of that text field is: MyDate

           

          The calculations for Year, Month and Day are:

           

          Year = Year ( MyDate )

          Month = Month ( MyDate )

          Day = Day ( MyDate )

          • 2. Re: Moving mm/dd/yyyy into separate fields
            Orlando
              

            Ho quickstudy,

             

            Welcome to the forum and thanks for you post.

             

            In FileMaker there are a number of date functions, you can use these to extract each segment of the date, the ones you want to use are Day ( date ), Month ( date ) and Year ( Date ).

             

            If you want to do a blanket replace on all records to move the values into the new fields, I have called then simply Day, Month and Year, and then no longer use the whole date field then you would be best to set up a script that will loop through each record setting the individuall fields. Something like this

             

              #

              Go to Record/Request/Page [ First ]

              Loop

              Set Field [ TABLE:: Day : Day ( TABEL:: Date ) ] 

              Set Field [ TABLE:: Month : Month ( TABEL:: Date ) ] 

              Set Field [ TABLE:: Year : Year ( TABEL:: Date ) ]  

              Go to Record/Request/Page [ Next ; Exit after last]

              End Loop

              #

             

            After this has run all you dates will be split out.

             

            I hope this helps 

            • 3. Re: Moving mm/dd/yyyy into separate fields
              adcoleman
                

              Thanks, Orlando and Raybaudi, for the input. I'm not clear about the following: Currently the data is in a text field, entered as (example) 12/21/2008. How will this script recognize which of these numbers is the month, the day, and the year? Is this intelligence simply built into FMPro?

               

              Also, Orlando, I note that in your proposed script you have two different spellings of table: TABLE and TABEL. Is this purposeful, or a typo?

               

              Set Field [ TABLE:: Day : Day ( TABEL:: Date ) ]  

              • 4. Re: Moving mm/dd/yyyy into separate fields
                Orlando
                  

                Hi quickstudy

                 

                TABEL is a type sorry.

                 

                As for your text field, could you change it to a date field? Give it a try on a copy and check a handful if you are at all unsure, but this should work fine.

                • 5. Re: Moving mm/dd/yyyy into separate fields
                  TSGal

                  quickstudy:

                   

                  Thank you for your posts.

                   

                  Yes, it was a typo.  TABEL and TABLE are the same.

                   

                  Even though the information is put into a Text field, the date functions used (Month, Day and Year) will see the information as a date and translate the information properly.

                   

                  Another way of doing this is to put your cursor in the Month field, pull down the Records menu and select "Replace Field Contents..."  When you do this, a new dialog box appears.  Select the last option, "Replace with calculated result:"

                   

                  In the next dialog box (Specify Calculation), you will see a list of your fields on the left side and a list of calculation functions on the right side.  Scroll down the list of functions on the right side until you see " Month (date) ", and double-click on it.  In the large area in the bottom half, you will see:

                   

                  Month ( date )

                   

                  ... appear, with "date" being highlighted.  Leave it highlighted.

                   

                  In the fields list above, find your text field and double click on it.  Your formula should now say something like either:

                   

                  Month ( TextField )

                  or

                  Month (Table::TextField )

                   

                  Of course, "TextField" is the name of the text field with the date in it, and "Table" is the name of your current table.

                   

                  Click "OK" to leave the Specify Calculation dialog box, and then click the "Replace" button to replace the calculated value across all records.

                   

                  If you now browse through your records, you will see that the month number now appears in the Month field for each record.

                   

                  Follow the same steps for the Day and Year fields.

                   

                  If you run into any difficulty, or want clarification for any of the above steps, please let me know.

                   

                  TSGal

                  FileMaker, Inc. 

                   

                   

                  • 6. Re: Moving mm/dd/yyyy into separate fields
                    raybaudi
                      

                    Hi quickstudy

                     

                    "How will this script recognize which of these numbers is the month, the day, and the year? Is this intelligence simply built into FMPro?"

                     

                    Yes, you'll find that FileMaker is a very smart guy ! ;)

                    • 7. Re: Moving mm/dd/yyyy into separate fields
                      adcoleman
                        

                      Tried your method, TSGAL. Went as you said it would until I clicked OK after setting up the calculation. I got the message "Everything worked as There are too few parameters in this function."

                       

                      This happened when I tried your method in all three fields: Month, Day, and Year.

                      • 8. Re: Moving mm/dd/yyyy into separate fields
                        adcoleman
                          

                        Sorry -- spoke too soon. The Replace Field Contents process accepts the calculation you propose. But nothing happens -- all three fields remain empty after this procedure.

                         

                        All four of these fields are text fields-- don't know if this matters. 

                        • 9. Re: Moving mm/dd/yyyy into separate fields
                          adcoleman
                             My mistake. Re-read your method, followed it precisely, and it worked. Thanks.