4 Replies Latest reply on Oct 23, 2015 1:56 PM by jimscott77

    Data source changed date format

    jimscott77

      I regularly import data from an outside data source(DS) in the form of a csv file.  That DS changed how they displayed date formats from 'm/d/yyyy' (fmt1) to 'yyyy-mm-dd' (fmt2).

       

      The data loads fine but the following calculation fails to work with fmt2 but does work with fmt1..

       

      DateEnding =

      fmt1 '12/5/2014'

      fmt2 '2014-12-05'

       

      rptDate with fmt1= If(IsEmpty(DateEnding);"Active";MonthName(DateEnding) & ", " & Year(DateEnding)) =  "December, 2014"

      rptDate with fmt2= If(IsEmpty(DateEnding);"Active";MonthName(DateEnding) & ", " & Year(DateEnding)) =  "?, ?"

       

      - DateEnding is a date field

      - rptDate is how I want the information to show on my report(s)

      - if DateEnding is empty I want the word "Active" to appear on the report.

       

      Is there a way to use both date formats at the same time

        • 1. Re: Data source changed date format
          jbrown

          I'm not sure that FileMaker's date fields will accept the YYYY format. This field would best be a text field.

          You could do some work to transpose the YYYY date format into a date field.

          There are some great text functions that pull characters from a text field. The Right, Left, Middle functions would be used to turn 2015-03-22 into 3/22/2015. Then once it has been transposed, you can stick it into that DateEnding field.

          Text functions

          Question: Is "rptDate with fmt1" a field?

          • 2. Re: Data source changed date format
            jimscott77

            Jeremy, thanks for responding.

             

            I guess there's a couple of thing that are throwing me,

             

            1) dates in the YYYY format (fmt2) show up in the DateEnding field, which is a date field.  Is Filemaker showing the text values of what's there even though it doesn't recognize the date value?  My experience with other databases is that they do not allow non recognized values to be entered into a date filed.

             

            2) In trying to resolve the problem I found some examples where people were using fmt2 as dates, though I'm not sure they were using them as a date field, perhaps just as a text field.

             

            If the format's not recognized by Filemaker then converting the values would seem to be the only choice.

             

            Thanks,

             

            Jim

            • 3. Re: Data source changed date format
              jbrown

              jimscott77 wrote:

               

              Jeremy, thanks for responding.

               

              I guess there's a couple of thing that are throwing me,

               

              1) dates in the YYYY format (fmt2) show up in the DateEnding field, which is a date field.  Is Filemaker showing the text values of what's there even though it doesn't recognize the date value?  My experience with other databases is that they do not allow non recognized values to be entered into a date filed.

               

              2) In trying to resolve the problem I found some examples where people were using fmt2 as dates, though I'm not sure they were using them as a date field, perhaps just as a text field.

               

              If the format's not recognized by Filemaker then converting the values would seem to be the only choice.

               

              Thanks,

               

              Jim

              1. It seems that when you import a .csv file and put a date field written as "2015-01-01" into a date field, the date field accepts the format. Typing in that format, and you'd get an error (at least in the US version of FIleMaker!). Once it is in there, you can't find for a date in that format or do a proper sorting.  (I did some import and sorting, finding with this format of date).

               

              I guess I'd do an auto-enter calc, replace existing value so that when an import is done, the  2015-01-01 gets translated to "1/1/2015" so that FileMaker can use the field and its value appropriately

              Set up the auto-enter, replace existing calculation to be:

              Let ([

              _Start = DateEnding;

              _Year = Left (_Start ; 4);

              _Month = Middle (_Start ; 6; 2);

              _Day = Right (_Start ; 2)

              ];

               

              Date (_Day;_Month;_Year)

              )

               

              That should take the just-imported date and reformat it to be the 'correct' FM format.

              • 4. Re: Data source changed date format
                jimscott77

                Thanks again, looks like converting the format will be the way to go.  Best regards, Jim