12 Replies Latest reply on Nov 16, 2011 12:52 PM by EvanEffa

    Converting Date Formats to ISO compliant version - How?

    EvanEffa

      Title

      Converting Date Formats to ISO compliant version - How?

      Post

      Sorry of this is too much a newbie question.  I am not planning on becoming a FM Pro programmer and would be most grateful for a little bit of hand-holding here. ( I have searched the forum for an answer without success...)

      I use HanDBase on my iPhone to capture Patient care billing Data.  (FM Go is too slow.) I then take the HanDBase data & convert it to a CSV version of the dbase & import it into Filemaker Pro 11 giving me data that can be sent to my billing secretaries in a more acceptable format (I send them a pdf by email).  This works quite well except for a mixed set of dating formats. (I live in Canada where we are subjected to a wide variety of ambiguous dating formats which only complicates things.) 

      Currently the 'Date of Service' field data is appearing as a proper date in the dd/mm/yyyy format. (FM Pro is importing this data properly and accurately BTW)  (The simple fix would be to get HandBase to capture the data in ISO compliant format but HanDBase does not support that format.)

      Anyways, I would like to have FM Pro convert these dates into the ISO format (yyyy-mm-dd).  I would be happy to accept another calculation field; say "DateConvert" as the DateofService output which I can then configure to be the PDF output format.

      I also have dates (Date of Birth) written into the Database from the Hospital database format  of ddMMMyyyy (e.g. 12May1957 ).  These appear as simple text in the database fields.  I would like to be consistent & have these dates appear in the ISO format of yyyy-mm-dd.

      Could someone suggest a resource to show me how to do this or if it's not too much trouble, give me step by step instructions as to how to accomplish these conversion tasks?

      (BTW: This will be an ongoing database in that new data is being entered all the time & needs to be converted as a repeating task.)

      Thanks very much for any help you can offer.

      -evan

        • 1. Re: Converting Date Formats to ISO compliant version - How?
          philmodjunk

          Use real date fields, not text wherever possible. Date data in fields of type date are stored as an integer that counts the number of days from 12 / /31 / 000 to the date stored. The format displayed in a report then becomes a data format setting you can specify for that field.

          Enter layout mode and select the date field.

          Use the data formatting section of the inspector's data tab to specify the desired format.

          If you can't import the data into a date field, such as your 12May1957 example, you'll need to use a calculation field to convert it into a date:

          Let ( [ D = Left ( DateText ; 2 ) ;
                    Y = ( Right ( DateText ; 4 ) ;
                    Mname = Middle ( dateText ; 3 ; Length ( dateText ) - 6 ) ;
                    M = Ceiling ( Position ( "janFebMarAprMayJunJulAugSepOctNovDec" ; Left ( Mname ; 3 ) ; 1 ; 1 ) / 3 ) ] ;
                    Date ( M ; D ; Y )
                ) //Let

          Choose Date as the result type for this calculation and you can format the displayed date as described at the start of this post.

          Note: this calculation assume two digit numbers for all day values so leading zeroes are needed for days 1 - 9.

          • 2. Re: Converting Date Formats to ISO compliant version - How?
            EvanEffa

            Hi Phil,

             

            Thanks for your instructions.

            I do have the Date of Service data entered as 'Date' type data under Field Type.  (it is in the dd/mm/yyyy rather than the Excel style dd-mm-yyyy if that matters?) I looked again though & realized that HanDBase allows me to enter the Date data in the proper ISO format of yyyy-mm-dd but that this gets converted by the Desktop version of the program to the dd-mm-yyyy format & then to CSV etc.

             

            I have tried your suggestion before.  When I go to Layout Mode & change the Date Format from 'As entered' to the '2003-12-25' option, the dates in the field are replaced by a '?' character.  Changing the leading zero option has no effect on this.  Any  suggestions as to why this is not working?

            When I try to convert the Text Field dates under the 'DOB' field (Formatted as Text as ddMMMyyyy) I see the following:

            I go to Browse mode & click on the DOB field > Field Type > Calculation and enter your formula specifiying result as  'Date'.  It gives me an error message: 'The specified field cannot be found.' while highlighting the first 'DateText' occurrence in the formula.  I tried changing this to 'DOB' in case this was the problem but no success.

            I may be dim but I'm not seeing the solution readily.

             

            Thanks for your help.

             

            -evan

             

             

            • 3. Re: Converting Date Formats to ISO compliant version - How?
              philmodjunk

              Any  suggestions as to why this is not working?

              Resize the field to make it wide enough to correctly display the date. The format change is producing data that needs more space to correctly display.

              I tried changing this to 'DOB' in case this was the problem but no success.

              Each instance of "DateText" needs to be replaced with the name of your text field. There are 4 such references in the calculation.

              • 4. Re: Converting Date Formats to ISO compliant version - How?
                EvanEffa

                Thank you Phil.  I really appreciate your help.

                 

                One issue solved, one to go...

                I got the Text to Numerate date conversion to work (I just had to remove an extra Bracket on the second line of the formula where it read:

                Y = ( Right ( DateText ; 4 ) ;  Thank you very much for that.

                 

                As for the first date issue though;  Giving it a very large column width made no difference. I am still seeing a lonely question mark instead of a ISO compliant date.  Any other suggestions?

                 

                -evan

                 

                 

                 

                • 5. Re: Converting Date Formats to ISO compliant version - How?
                  EvanEffa

                  Is it possible that the FM Pro has a bug in the date format options?

                   

                  I have the dates listed as dates & they are being sorted appropriately acording to their dd/mm/yyyy formatting.  I have tried reimporting these with  dd-mm-yyyy separators but still get the '?' response.

                   

                  -evan

                  • 6. Re: Converting Date Formats to ISO compliant version - How?
                    EvanEffa

                    I just tried exporting the dates from HanDBase in mm-dd-yyyy format & tried to convert them to ISO format in FM Pro -> same result: a '?' for each entry in that field.

                    Is there an equivalent calculation field that could 'Manually' switch the order of the digits to change the field entries from dd-mm-yyyy to yyyy-mm-dd?

                    I don't mean to be so lazy asking for the code but if i could find an easy reference to the programming syntax I'd be wiling to write this calculation field code myself.

                     

                    thanks.

                     

                    -evan

                    • 7. Re: Converting Date Formats to ISO compliant version - How?
                      philmodjunk

                      It sounds like this data is not being recognized by FileMaker as an actual date and is instead treating it as text.

                      If you open manage | database | fields and find this field's field definition, can you confirm that this field is really of type date and not of type text?

                      Does mm/dd/yyyy match your current system date settings for your computer?

                      If you type a date into this field in the mm/dd/yyyy format do you get an error when you exit the field? (Examine it with and without the data formatting enabled.)

                      • 8. Re: Converting Date Formats to ISO compliant version - How?
                        EvanEffa

                        Hi Phil,

                        My Computer system settings (Mac running OsX 10.7) have dates in the yyyy-mm-dd format.


                        I confirm that the imported Date of Service field ('Date_Pr') has field property set as date.

                        When the field format is set to: 'As entered' and when I try entering a new record in the 'Date_Pr' field it will reject anything but the system settings' preference of yyyy-mm-dd.  All the imported dates on the fields above the new record are displayed in their imported format of dd-mm-yyyy.

                        When I switch to field format specified as  2003-12-25 I am obliged to enter new dates in the yyyy-mm-dd format and the old imported dates are displayed as '?'.

                         

                        It looks to me that I will need to write some sort of calculation field to convert the imported data to the yyyy-mm-dd format & to conform with the system and FM Pro settings...?

                         

                        -evan

                         

                         

                        • 9. Re: Converting Date Formats to ISO compliant version - How?
                          philmodjunk

                          The system settings are affecting the import. This part of FileMaker is a bit buggy and I've seen other reports of issues encountered that aren't totally dissimilar from this.

                          I think you may have to import your date data into a text field and then use a calculation to convert it into a date:

                          Let ( dte = YourDatefield ; date ( left ( dte ; 2 ) ; Middle ( dte ; 4 ; 2 ) ; Right ( dte ; 4 ) ) )

                          Note, there's a trick I learned from LaRetta that can do the conversion during the import process you can try if you are interested:

                          Define two global text fields for each of your two date fields. On the actual date fields, define the calculations we have produced here as auto-enter calculations that take the text in the global date field and compute the needed date. Then, during import, map the imported dates to import into these global text fields and enable the auto-enter option in the last dialog to appear before the import starts up.

                          This leaves your date data in date fields where you can specify the desired display format in a format that permits editing the dates in these same fields.

                          • 10. Re: Converting Date Formats to ISO compliant version - How?
                            EvanEffa

                            This will work just fine.  I created a calculated field column called 'DOS correct';  it imports the previous fields to the same old field but also gives me a calculated field with the new correctly formatted dates...but,  the formula you have offered is converting the import data is if the import data was in the mm/dd/yyyy format giving me dates in the future (2013 etc.) when it picks up a date like 21/10/2011.

                            Could you suggest an amended formula with this corrected?  I think this will have solved the problem for me.

                             

                            Thanks again.

                             

                            -evan

                            • 11. Re: Converting Date Formats to ISO compliant version - How?
                              philmodjunk

                              Apologies, got my days and months switched around. (This Yank was thinking mm/dd/yyyy instead of dd/mm/yyyy). Embarassed

                              Let ( dte = YourDatefield ; date ( Middle ( dte ; 4 ; 2 ) ; left ( dte ; 2 ) ; Right ( dte ; 4 ) ) )

                              The parameters for the date function, no matter what your system settings, must be in month ; day ; year order.

                              • 12. Re: Converting Date Formats to ISO compliant version - How?
                                EvanEffa

                                Thank you very much Phil.

                                This totally solves my problem. Smile (I should probably learn the syntax for these calculations but I really am only using Filemaker as a portal to outputting a custom form and probably will have little need to learn this...(Until next time maybe)).

                                Thanks again.  Your help has saved me days of research & floundering in frustration.  I really appreciate it.

                                 

                                Cheers.

                                 

                                -evan