2 Replies Latest reply on Sep 7, 2012 2:08 PM by BrandonTerrell

    Script Help - Invalid Date Entry

    BrandonTerrell

      Title

      Script Help - Invalid Date Entry

      Post

      I have a solution with roughly 10,000 contact records that includes a Birthdate field. About 3000 of these actually have data in them that was imported from an outside source file in the format DD-Mon-YYYY, which Filemaker is not able to interpret as a valid date usable in calculations it would seem.  I also have some records with a date that was manually entered in Filemaker in a proper format, so some are fine.  I am trying to write a script that can go through each record and, if it has a date entered, determine first if it is in the invalid format or not and if so take the data and re-enter it in the field in the format MM/DD/YYYY that Filemaker is capable of working with.  I feel like this should be possible with the functions available, but I cannot myself figure out the proper steps to take to make a script capable of doing this, especially considering the month is in abbreviated text format, some days are single digit and some are double, and that sometimes the year is listed helpfully as YYYY but sometimes only as YY (meaning any YY entry of 12 or less would be in 2000 and any year over 12 would be in 1900).  If anyone more function-savvy than me is capable of coming up with something that would work, or if there is in fact an easier way to do this I am not aware of, I would greatly appreciate it.  Thanks!

        • 1. Re: Script Help - Invalid Date Entry
          philmodjunk

          First, you can pull up a found set of just the records with invalid dates so that you have a smaller set of records to work with and a simpler script.

          In a scripted or manual find, you can put a lone ? in the date field and find all records where the value in that field is not valid.

          Then you can use Replace Field Contents with a calculation like this to correct the invalid dates:

          Let ( [ d = Leftwords ( YourTable::DateField ; 1 ) ;
                    m = Ceiling ( Position ( "JanFebMarAprMayJunJulAugSeptOctNovDec" ; Left ( MiddleWords ( YourTable::DateField ; 2 ; 1 ) ; 3 ) ;1 ;1 ) / 3 );
                    y = GetAsNumber ( RightWords ( YourTable::DateField ; 1 ) ) ];
                  Date ( m ; d ; If ( y > 12 ; 1900 ; 2000 ) + y )
                )

          Note that if this is a one time only batch fix that you need to do, you don't really need a script as you only have two steps to perform here and they can be done manually.

          Note also that Replace Field Contents can change the values of 100's of records in one go and can't be undone. It's a good idea to save a back up copy of your database before trying out a "batch update" like this so that you can toss out the file and revert to your back up copy if you don't get the results you expect.

          • 2. Re: Script Help - Invalid Date Entry
            BrandonTerrell

            Brilliant, you are a lifesaver Phil.  I forgot that a "-" dileniates a new word for the text functions.  I really appreciate your help, as always.