6 Replies Latest reply on Dec 12, 2008 9:32 AM by Orlando

    Moving mm/dd/yyyy into separate fields -- not solved yet

    adcoleman

      Title

      Moving mm/dd/yyyy into separate fields -- not solved yet

      Post

      Spoke too soon. Using TSGal's method, what happened is this:

       

      Any mm/dd/yyyy Date textfield entry that was complete by Date field standards -- that is with a day date, a month date, and a year -- did get moved, although with 01 changed to 1, etc., in both the month and day fields.

       

      For any entry in which either the date or the month was set at 00/, the three separate fields now contain question marks -- even if the month and year were included in the entry.

       

      Help wanted. 

        • 1. Re: Moving mm/dd/yyyy into separate fields -- not solved yet
          raybaudi
            

          "the three separate fields now contain question marks"

           

          00/01/2008 and 01/00/2008 aren't dates !

           

          So FileMaker can't interpret that field and keep the 3 values.

           

          Now can you say us what "real" date you want for:

           

          00/01/2008

           

          AND

           

          01/00/2008

           

          ? ?

          • 2. Re: Moving mm/dd/yyyy into separate fields -- not solved yet
            Orlando
              

            Hi quickstudy

             

            Reading you other post I think I understand where this has gone wrong. If you publication does not have a set date then you entered "00" instead of a day, is that right? Do you always add a zero when a day or month is not specified? If so try this calculation in your Replace to see if it helps.

             

            Note that this calculation assumes there is a zero in place of a missing day or month. 

             

             

              Let ( [ 

             

              DateArray = Substitute ( "01/12/2008" ; "/" ; "¶" ) ;

             

             

              Month = GetAsNumber ( GetValue ( DateArray ; 1 ) ) ;

              Day = GetAsNumber ( GetValue ( DateArray ; 2 ) ) ;

              Year = GetAsNumber ( GetValue ( DateArray ; 3 ) )

             

             

              ] ;

             

              // Modify this for Day, Month and Year 

              If ( IsEmpty ( Day ) or Day < 1 ; "" ; right ( 0000 & Day ; 2 ) )

             

              )

             

            So the above will extract the day, leaving the field blank if it is zero, and will input a leading zero if your day is entered in a single digit. When doing Month and Year use modify the If line as below.

             

            Month 

              If ( IsEmpty ( Month ) or Month < 1  ; "" ; right ( 0000 & Month ; 1 ) ) 

             

            Year 

              If ( IsEmpty ( Year ) or Year < 1  ; "" ; right ( 0000 & Year ; 4 ) ) 

             

            I think this should do it, post if anything does not make sense or if it does not work.

             

            As a side note, add a pop-up value list for selection of Day and Month.

             

            To be doubly safe also I would suggest making your individual fields Number fields with validation to stop users entering numbers greater than in a day or month. i.e. 1-31 and 1-12.

             

            To do this, under the Fields Options in Define Database, click on the Validation tab and look at the "Require:" options, tick "Strict Data Type" and select "Numeric Only" and then tick and specify "In range:"

              

            This will be beneficial if other people are using the system and will make sure you have accurate values with the additional calculation you are adding.

            • 3. Re: Moving mm/dd/yyyy into separate fields -- not solved yet
              adcoleman
                 To answer Danielle's question -- Now can you say us what "real" date you want for:

               

              00/01/2008

               

              AND

               

              01/00/2008 . . .

               

              I'd never have an entry that had a day but no month. So there would never be a 00/01/2008 entry. 01/00/2008 should result as Jan. 2008; 01/01/2008 should result as 1 Jan. 2008; and 00/00/2008 should result as 2008.

               

              Yes, I have always entered  two zeros (as above) wherever an entry has either no day or no month. So all my entries till now have the above format in the current Date field.

               

              Here's what I achieved poking around last night: Leaving my entry fields as text fields, and using the Left and Right functions, I pulled the Month data (Left, 2 characters) and Year data (Right, 4 characters) into those respective fields. Piece of cake.

               

              Couldn't figure out how to use this function to isolate and specify characters in the middle. The Day characters are actually the 4th and 5th characters. I tried Left (Date ; 5-3), but that subtracted the two characters I want to keep; and Right ( Date ; 7-5 ) gave me the last two numbers of the year date.

               

              If there's a Left/Right calculation that will give me the /dd/ data in that field, I'll then have all the current mm/dd/yyyy data separated into those three fields. That will complete step one.

               

              By the way, those fields seem to want to remain text fields. I tried converting them to numeric fields, per Orlando's suggestion, but this resulted in question marks in the fields. That doesn't happen when they're text fields. However, they are attached to value lists, with the range specified, the number of characters also specified, and even a little error message for a wrong entry.

              • 4. Re: Moving mm/dd/yyyy into separate fields -- not solved yet
                Orlando
                  

                Hi quickstudy

                 

                There was a slight error with the replace function I specified in my last post, it should read:

                 

                  Let ( [ 

                 

                  DateArray = Substitute ( TABLE:: DateField ; "/" ; "¶" ) ;

                 

                 

                  Month = GetAsNumber ( GetValue ( DateArray ; 1 ) ) ;

                  Day = GetAsNumber ( GetValue ( DateArray ; 2 ) ) ;

                  Year = GetAsNumber ( GetValue ( DateArray ; 3 ) )

                 

                 

                  ] ;

                 

                  // Modify this for Day, Month and Year 

                  If ( IsEmpty ( Day ) or Day < 1 ; "" ; right ( 0000 & Day ; 2 ) )

                 

                  ) 

                 

                And modify for Month and Year as described in my last post. 

                 

                This will split the date into its three parts and return the specified part depending on which part you specify in the If line. It will work based on what you just described with your current data entry, this will also leave the individual day field blank if the date you input was 12/00/2008.

                 

                Let me know if this works for you or not. 

                • 5. Re: Moving mm/dd/yyyy into separate fields -- not solved yet
                  adcoleman
                    

                  Thanks, Orlando.

                   

                  Per my previous post, all I now need to do is get the day data from my current Date field into my Day field. I already have the month and year data transferred/imported into those respective fields.

                   

                  If there's a way to use the Left or Right function for this, that seems simplest.

                   

                  Otherwise I would want a version of your calculation to use for just the Day data, including an If for the 00 entries. What would that look like? 

                   

                  • 6. Re: Moving mm/dd/yyyy into separate fields -- not solved yet
                    Orlando
                      

                    give this calculation a try.

                     

                      Let ( [ 

                     

                      DateArray = Substitute ( TABLE:: DateField ; "/" ; "¶" ) ;

                     

                      Day = GetAsNumber ( GetValue ( DateArray ; 2 ) ) ;

                     

                      ] ;

                     

                      // Modify this for Day, Month and Year 

                      If ( IsEmpty ( Day ) or Day < 1 ; "" ; right ( 0000 & Day ; 2 ) )

                     

                      ) 

                      

                    This will extract just the Day for you.