6 Replies Latest reply on Sep 4, 2014 11:06 AM by philmodjunk

    How do you fix formatting in timestamp field?

    earthman

      Title

      How do you fix formatting in timestamp field?

      Post

           I'm trying to create a script that finds records with dates from todays date and beyond.  Unfortunately the field that contains the date I need is a timestamp field.  The data comes from county records formatted in a way that filemaker doesn't like. The problem formatting is as follows: The Month abbreviation is spelled with 3 letters. i.e. Mar, Feb, Jun, etc.  . . The day, w 4 character year, are numbers with no slash marks between.  Then comes the time: 12:00PM.  I have manually gone in to a couple of my timestamp fields and typed in a correctly formatted date and my script works fine.  
            
           Does anyone know how I can take a bunch of improperly formatted timestamp fields and format them correctly so that my script will work?  I've tried changing the Data in excel.  Even though excel can read and sort the, County Records, formatted data, I haven't been able to get excel to format the timestamp fields in a way filemaker use.  Any tricks or shortcuts would  be greatly appreciated.

      TimeStamp_Bad_Format.png

        • 1. Re: How do you fix formatting in timestamp field?
          philmodjunk

               Is that really a timestamp field or a field of type text?

               If this is imported data, I would suspect that this is not a timestamp field but actually a text field. This is an important detail when it comes to how to fix this.

          • 2. Re: How do you fix formatting in timestamp field?
            earthman

            In my database it's a time-stamp field.  I import this data from county records and in import it from Excel.  The data comes in exactly like this: Sep 11 2014 12:00AM     I need it to be in a time-stamp field because the script I run incorporates  ≥//  to find dates equal to or greater than today's date.  I'm trying to convert the imported data to a format that "FilemakerPro 12" likes.  I've tried this:

            Let ( [ str = Substitute ( AucDate ; ["-" ; ¶ ] ; [ " "; ¶ ]  ) ;
                      m = Ceiling ( Position (  "janfebmaraprmayjunjulaugsepnovoctdec" ; GetValue ( str ; 1 ) ; 1 ; 1 ) / 3 )  ;
                      d = GetValue ( str ;  2 );
                      y = GetValue ( str ; 3 ) ;
                      t = GetValue ( str ; 4 ) ];
                      GetAsTimestamp ( m & "/" & d & "/" & y & " " & t )
                   )

            But I could only get it to work in a text field . . . . . which does me no good?

            Any attempts at the various types of field validation haven't worked they just reject the data on import and the critical field in question comes up blank.  Any insight you can share on this subject would be greatly appreciated . . . 

            THANK YOU!!!!

             

             

            • 3. Re: How do you fix formatting in timestamp field?
              philmodjunk

              I understood that you needed a timestamp and was not suggesting otherwise. It's just that the data shown clearly could not be imported into a field of type timestamp as FileMaker will not recognize that format as a combined date and time value of a timestamp.

              Validation options won't do this because they are intended to reject data that fails the validation text as you have discovered.

              But your calculation should produce exactly what you need if you import into a text field named AucDate and then set this up as a calculation field with Timestamp as the result type or as a TimeStamp field with this expression as an auto-enter calculation. But you need two fields, one of type text into which you import the date text from Excel and the timestamp field that gets the converted data. (and you have to enable auto-enter options during the import.)

              And here's a trick that I learned from LaRetta right here in the forum: AucDate can be a global text field and the auto-enter calculation still works to enter converted data into each record's timestamp field as long as you enable auto-enter options.

               

              • 4. Re: How do you fix formatting in timestamp field?
                earthman

                OK . .  I have a newly created time-stamp field.  And , BTW, I'm out of my realm here . . . Which "Auto-Enter"  check box do I select in order for this field to be able to accept the converted data.  Do I check Calculated value then Specify a date format???  How about The Prohibit Modification of Value . . check box . . .  ????

                On the ogiginal Text Field "AucDate" into which I'm importing the date text from Excel  . . . This is the Calculation I'm using:

                Let ( [ str = Substitute ( AucDate ; ["-" ; ¶ ] ; [ " "; ¶ ]  ) ;
                          m = Ceiling ( Position (  "janfebmaraprmayjunjulaugsepnovoctdec" ; GetValue ( str ; 1 ) ; 1 ; 1 ) / 3 )  ;
                          d = GetValue ( str ;  2 );
                          y = GetValue ( str ; 3 ) ;
                          t = GetValue ( str ; 4 ) ];
                          GetAsTimestamp ( m & "/" & d & "/" & y & " " & t )
                       )

                . . .Is this going to get the format close enough for use in a Filemaker timestamp field??

                Finally . . . .  How does the newly created time-stamp field get the converted data.  Do I create a script to bring data from one field to another within a record? . . . I'm sorry for these totally beginner questions. . . . I really appreciate your expertise . . 

                Thank you,

                Mark

                 

                 

                 

                • 5. Re: How do you fix formatting in timestamp field?
                  philmodjunk

                  You'd click the calculation option and paste your calculation into the resulting Specify calculation dialog:

                  Let ( [ str = Substitute ( AucDate ; ["-" ; ¶ ] ; [ " "; ¶ ]  ) ;
                            m = Ceiling ( Position (  "janfebmaraprmayjunjulaugsepnovoctdec" ; GetValue ( str ; 1 ) ; 1 ; 1 ) / 3 )  ;
                            d = GetValue ( str ;  2 );
                            y = GetValue ( str ; 3 ) ;
                            t = GetValue ( str ; 4 ) ];
                            GetAsTimestamp ( m & "/" & d & "/" & y & " " & t )
                         )

                  Then, during import records, be sure to click the check box that enables auto-enter options. This pops up in a funky little dialog box AFTER you click import.

                  • 6. Re: How do you fix formatting in timestamp field?
                    philmodjunk

                    And if you prefer, there is another approach with this same calculation (which works perfectly on your data, BTW). You can import the date as text into a text field and then use Replace Field Contents with this same calculation to take the text in AucDate and put a date/time into your timestamp field.