13 Replies Latest reply on Sep 24, 2015 6:12 AM by nicolai

    Imported dates are backwards and text

    AndrewTaggart

      Hi,

       

      I have exported a database from Bento as an Excel file and imported it into Filemaker 12.

       

      There are date fields and date time fields that have come out looking like:

       

      Date: 2003-12-12

      DateTime: 2011-03-29T12:00:00

       

      I have mangaged to change the "-" to "/" but when I convert the field from text to date all I get is "?"

       

      1, How can I make this field into a date field?

      2, And the same for the Date time fields?

       

      I have tried the Bento migration tool but it crashes even though address book is turned off and I am using Yosmite.

      I have tried to widening the field but still get ?

       

      Help please

        • 1. Re: Imported dates are backwards and text
          nicolai

          You can use this formula to set your date field. Replace the date string with your imported field value:

           

          Let (

          [

          myDateString = "2011-03-29";

          myValueList = Substitute ( myDateString ; "-" ; "¶" );

            myYear = GetValue ( myValueList ; 1 );

            myMonth = GetValue ( myValueList ; 2 );

            myDay =GetValue ( myValueList ; 3 );

            result = Date ( myMonth ; myDay ; myYear )

          ];

          result

          )

           

          For the timestamp add substitute for T and :  as well

          • 2. Re: Imported dates are backwards and text
            richardsrussell

            FileMaker Pro thinks that the format of date fields should be whatever your computer's default system setting is at the time the file is created. I myself much prefer the ISO 8601 standard, which is what you're inheriting from Bentō (EG, "2011-03-29"). You can prime your file to receive dates in that format by resetting your system preferences to the yyyy-mm-dd sequence and creating a clone of your FMP file (File > Save a Copy As ... > clone (no records)). The clone will expect dates to arrive in that format, even after you switch your system preferences back to what you normally prefer.

             

            Once the dates are successfully imported, you can format them to appear any way you want using the data inspector:

            Date Inspector.jpeg

            • 3. Re: Imported dates are backwards and text
              AndrewTaggart

              Thanks Guys,

               

              I'm a complete novice on FM, Nicolai can you explain how I go about replacing theta string with the formula?

               

              Andrew

              • 4. Re: Imported dates are backwards and text
                nicolai

                The calculation to process the timestamp (DateTime):

                 

                Let (

                [

                  myDateString = "2011-03-29T12:00:00";

                  myValueList = Substitute ( myDateString ;[ "-" ; "¶" ]; [ "T"; "¶"]; [ ":" ; "¶" ] );

                  myYear = GetValue ( myValueList ; 1 );

                  myMonth = GetValue ( myValueList ; 2 );

                  myDay =GetValue ( myValueList ; 3 );

                  myDate = Date ( myMonth ; myDay ; myYear );

                  myTime =  Time ( GetValue ( myValueList ; 4 ); GetValue ( myValueList ; 5 ); GetValue ( myValueList ; 6 ) );

                  myTimestamp = Timestamp ( myDate ; myTime );

                  result = myTimestamp

                ];

                result

                )

                 

                Replace the string "2011-03-29T12:00:00" with the value from your field. You can extract date and time into separate fields if you need to, just assign myDate or myTime to the result

                 

                You can import your dates and DateTime into text fields and use replace to move the processed date into a proper date field.

                 

                Enter the date field on the layout, select "Records/Replace Field Contents", select Replace with calculated result and specify formula. In the formula remember to replace the string with the value from the field where data was imported.

                 

                Another option is to set the second field to auto-enter and use the same calculation.

                 

                Any questions, just post them here. That's that the forums are for.

                 

                Nicolai

                • 5. Re: Imported dates are backwards and text
                  erolst

                  Nothing really to add to your excellent explanation, except maybe to clarify this:

                  nicolai wrote:

                  […]

                  Let (

                  [

                    myDateString = "2011-03-29T12:00:00";

                  […]

                  )

                   

                  Replace the string "2011-03-29T12:00:00" with the value from your field.

                  That should probably better read:

                   

                  Replace the string … with a reference to your field, i.e.:

                   

                  myDateString = "2011-03-29T12:00:00";

                  myValueList = Substitute ( Table::field ;[ "-" ; "¶" ]; [ "T"; "¶"]; [ ":" ; "¶" ] );

                   

                  Andrew –

                   

                  the first line was just to have a test value while developing the function, and is not required in the production version.

                   

                  Nicolai –

                  nicolai wrote:

                    myTimestamp = Timestamp ( myDate ; myTime );

                    result = myTimestamp

                  ];

                  result

                  )

                   

                  Isn't that a bit much referencing , and wouldn't

                   

                    result = Timestamp ( myDate ; myTime )

                    ] ;

                    result

                  )

                   

                  be enough to allow for convenient checking of other intermediate variables?

                  • 6. Re: Imported dates are backwards and text
                    nicolai

                    Thanks erolst, I am not very good at explaining things, unfortunately.


                     

                    Isn't that a bit much referencing

                     

                     

                    It definitely is, but I did not know if OP need to extract date, time or timestamp ( or may be to separate date and time).  So calculation could be changed easily by assigning a different let parameter to result,  e.g. if Andrew only needs date to be extracted, he can change the last let parameter to

                    result = myDate

                    ];


                    On the other hand, you are probably right. It mostly makes calculation longer and more confusing


                    I am not that organised usually and in the middle of a busy day I would end up writing all in one long ugly line. Probably, a week later swearing bitterly at the developer who wrote it this way



                    • 7. Re: Imported dates are backwards and text
                      erolst

                      nicolai wrote:

                      Isn't that a bit much referencing

                      It definitely is, but I did not know if OP need to extract date, time or timestamp ( or may be to separate date and time).  So calculation could be changed easily by assigning a different let parameter to result,  e.g. if Andrew only needs date to be extracted, he can change the last let parameter to

                      result = myDate

                      ];


                      On the other hand, you are probably right. It mostly makes calculation longer and more confusingg bitterly at the developer who wrote it this way

                       

                       

                      I was aware of these reasons that led you to use that format.

                       

                      (I usually start without such a line and only add it if something acts up …)

                       

                      I just meant that you have overdone it a bit with two lines of “quasi”-result and result …

                      • 8. Re: Imported dates are backwards and text
                        AndrewTaggart

                        Hi Guys,

                         

                        That seems to have worked great, all my date and time fields are now formatted correctly!

                         

                        i have one other issue, I had two fields that were CheckBox but in the import they are again text with either a "1" or "0"

                         

                        Can I do anything to turn them into CheckBox?

                         

                        Thank you again for your help.

                         

                        Andrew

                        • 9. Re: Imported dates are backwards and text
                          erolst

                          AndrewTaggart wrote:

                          i have one other issue, I had two fields that were CheckBox but in the import they are again text with either a "1" or "0"

                           

                          Can I do anything to turn them into CheckBox?

                           

                          It's common practice to use a field of type number for a field that expresses yes/no, true/false etc. (in short, a Boolean value).


                          A checkbox set is nothing more than an input and formatting device; to regain the checkbox format for your field, create a value list that just has the value 1, format your layout field object as style “checkbox” and attach that value list.

                          • 10. Re: Imported dates are backwards and text
                            nicolai

                            Oh, I see what you mean now. I usually do not put calculations in the calculational part of let (just realised that it sounds a bit funny) and try to keep them in let parameters. There is no good reason to justify doing it this way, it just a silly style. For me it is easier to read and debug, as instead of returning the result I can return a calculated parameter.

                            • 11. Re: Imported dates are backwards and text
                              erolst

                              Never mind, everybody got their personal style –and what is a bit of redundancy among friends?

                               

                              Good to see you contributing here, btw.

                              • 12. Re: Imported dates are backwards and text
                                beverly

                                You can create a Value List. I use the 'name': One and set it to "1" (no quotes). Format the field in layout mode, to be checkbox and use the newly created value list. Checked = 1, unchecked = "".

                                 

                                If you wish, create a value list of 0¶1 (two values). I name this one: boolen_VL. Then you can use it for radio style. Checkbox would show both 1 & 0 and both can be checked.

                                 

                                Do you need to show a "yes/no" or other value to replace the 1/0? If so, and it's number field, you can specify "boolean" in the Inspector. it allows you to name the values shown. Underlying is still the 0/1 value in the field. You are still showing the "label" set for the boolean.

                                 

                                beverly

                                • 13. Re: Imported dates are backwards and text
                                  nicolai

                                  One more thing, some credit due. I got the idea from an answer on stackoverflow by user michael.hor257k:

                                   

                                  http://stackoverflow.com/questions/31831442/filemaker-how-to-parse-a-path

                                   

                                  He used a ValueList to break apart a file path, which is quite smooth.