8 Replies Latest reply on Jul 22, 2011 1:31 PM by FMNewbie

    Fixing time entries.

    FMNewbie

      Title

      Fixing time entries.

      Post

      FMPro Advanced 11; Mac OS

      Okay... so I have a db with various time fields with several thousand records.  Unfortunately, the data was imported from the old MS Excel files, the formats got all messed up.  The actual numbers are still there, but the same field in various records might be displayed as:

      2:34

      02:34

      0234:00

      002:34

      This might be wishful thinking, but is there a way I can write a nice easy script that will go through all the records and fix them all to dilplay in the 02:34 format?? 

        • 1. Re: Fixing time entries.
          philmodjunk

          Items 1, 2 and 4, no sweat, but how do you know that 234:00 should really be 02:34?

          (For 1, 2, and 4, getasTime is a function that can convert text into time and you can use Replace field Contenst with this function to convert text to time and put the result in a separate field.)

          • 2. Re: Fixing time entries.
            FMNewbie

            Hi Phil...

            I guess that's what I'm getting at -  is there a way to tell if 234:00 is actually 23:40 PM or 02:34 AM? There might be a way using the subsequent fields as they would be chronologically sequential (i.e. if field 1, 2 and 3 are 234:00, 235:20 and 235:50 then the times are PROBABLY 23:40, 23:52 adn 23:55; whereas if they're 234:00, 245:00 and 310:00 then they're PROBABLY 02:34, 02:45 and 03:10).  But how the heck do you write a script that figures that out???  Or am I just pretty much hooped?

            As for the rest of it, if I understand correctly you're suggesting I create a second field and have a script grab the contents of the first field (with a GetAsTime function) and have it convert to hh:mm format and dump the result into the second field?  I suppose a loop function would be needed to run through all the records as well?  Should I then use another script copy the contents of the second field back into the first field or is there an easier way to do it all in one script?? 

            The annoying thing is this is something I only need it to do once... just to "tidy up" about 8 time fields for 5000 or so records. Eeeeek!!

            Thanks...

            • 3. Re: Fixing time entries.
              philmodjunk

              You'll need to figure out on paper a way to do this just by physically examing the data. Once you have done so, you may be able to automate the process as part of a script. Otherwise, best you could do is set up the system to scann through the records and then stop on each such record by pausing the script so that you can examine the data, make a determination and then continue the script.

              Here's one possible idea:

              Define a calculation field set to return time that uses GetAsTime (importedTimeText) to convert the text into time.

              In a script that loops through the data, you can use the function getNthRecord to look at values in the preceding and following records to see what the values are in those records. (GetNthRecord ( fieldname ; Get ( RecordNumber ) - 1 ) will refer to the preceding record.)

              • 4. Re: Fixing time entries.
                FMNewbie

                Hi there... I haven't re-visited this problem in a while (I actually just decided to ignore it until now, LOL) but I'm up against it again.  I've been thinking about a way to do this, but I'm not sure how to go about it. 

                I'm thinking that I need to filter the fields to just numeric data: filter(TimeField1; 0123456789) and then use several IF calculations to look at the data and either change it or set a new field to a corrected result such that:

                (1) if the number string starts with 3 or greater it should actually start with a zero (ie 345 is 03:45 and not 34:50)

                (2) if the number string starts with a 1 or 2 but is only 3 characters long add a zero to the beginning (i.e. 123 is 01:23 and not 12:30)

                (3) if the number string is longer than 4 characters (i.e. 123400 should be 12:34:00) have it drop the last 2 digits since I don't need the seconds value.  I guess I would use the Middle function for this??

                (4) loop the whole script to run through all 3500 records or so and clean them all up.

                Short of waving a magic wand or sacrificing small animals to my computer, anyone have any simple suggestions for how to do this???

                • 5. Re: Fixing time entries.
                  philmodjunk

                  You wouldn't need a script to loop through the records.

                  Show all records

                  Use Replace Field Contents with the calculation option to produce your corrected values in a new field of type time. I'd still store the results as time--not text as that allows you do work with this value more easily in sorts, finds and calculations. This leaves the text field unmodified so that you can see how the results  compare to the original data so you can make adjustments and try again if needed.        

                  Here's a calculation that may serve:

                  Let ( [ T = Filter ( Yourtable::YourTimeText ; 9876543210 ) ;
                            T2 = Case ( Left ( T ; 1 ) > 2 ; "0" & left ( T ; 3 ) ;
                                              Length ( T ) < 4 and Left ( T ; 1 ) < 3 ; "0" & Left ( T ; 3 ) ;
                                              Left ( T & "000" ; 4 )
                                            ) ] ;
                            GetAsTime ( T2 )
                        )

                  • 6. Re: Fixing time entries.
                    FMNewbie

                    Hi Phil... thanks for the quick reply.  I just plugged that into a new test field and it still yeilds the same result when I import records from the old version of the db to the new one.  However, I think I may be onto the reason why.  All the time fields are auto-calculated the user to enter 123 and get a result of 01:23 using the following calculation:

                    Let ( [h = Left ( Self ; Length ( Self ) - 2 ) ;m = Right ( Self ; 2 )];Time ( h ; m ; 0 ))

                    So when I import the records from one file version to the other is it re-calculating a calcluated field and therefore giving me weird numbers??  Would I solve that by adding a filter(98765....etc) into that calculation somehow?  I feel like I'm getting closer to a solution...

                    • 7. Re: Fixing time entries.
                      philmodjunk

                      I can't really tell what you have here.

                      What I am suggesting is to take the values in your text field after they are imported and using Replace field contents on a different field to produce a time following the criteria you specified. THis shouldn't be affected by your import or by anything set up on the original text field as we are putting this data into a new field.

                      I Would definitely remove this expression or not allow auto-enter options during import if you want to import the original unmodified data.

                      • 8. Re: Fixing time entries.
                        FMNewbie

                        Ha!!  That works perfectly Phil.  Thanks again. :)