1 2 3 Previous Next 33 Replies Latest reply on May 5, 2017 7:57 AM by Magnus Fransson

    Will Date Handling Be Better in FMP 16?


      I just completed an integration to an existing FMP dashboard application and was again amazed how rudimentary FMP's date handling is for a multi-hundred dollar database product.


      To wit, when I insert a date that is the same format as FMP's own "ExecuteSQL" into a date field (with no complaints from FMP), then try to do an ExecuteSQL to retrieve those same dates, what do you think I get? Nulls, naturally!  OK, FMP has dumped yet another chore in my lap, but why can't it handle dates for me?


      Thanks to all the previous postings, I now have a custom function (calculation) that converts the ExecuteSQL date (2017-5-2) to a typical FileMaker date (5/1/2017) so FileMaker's own ExecuteSQL can actually....read the date. Seriously?


      Plus, now that I need to use a CF to help FMP understand (and use) its own date formats, that means I'm also in charge of configuration management for any application that uses the same logic. That is, I would need to copy the "CF" to the other application.


      I hope 16 finally has the product handling dates for us behind the scenes with a consistent date format.


      FMI: Please forget "New Features! New Features! New Features! Instead, FileMaker should do more for us with existing functionality, not dump basic chores in our laps!


      However, based on the coming features I've read about thus far for 16, I'll be skipping that version, too.

        • 1. Re: Will Date Handling Be Better in FMP 16?
          Magnus Fransson

          Hi FMP Dude,


          For me FileMaker accepts 2017-05-02 without hesitation. But that is because of where I live. FileMaker does its best to interpret what is typed in the field and is making heavy use of the local/country setings on your computer when doing so. Most "end users" appreciate that they can type dates as they are used to.


          But as a developer I agre that it sometimes makes it difficult to comunicate with external systems. The best thing you can do is to relax, accept the fact and make heavy use of the Date(month;day;year) function.


          With best regards Magnus Fransson.



          I live in Sweden, and it just so happen to be that tha locale date format, is identical to the ISO-standard.

          1 of 1 people found this helpful
          • 2. Re: Will Date Handling Be Better in FMP 16?

            fmpdude wrote:



            However, based on the coming features I've read about thus far for 16, I'll be skipping that version, too.


            I think you have a very valid point on the dates, especially the interactivity between SQL dates and native dates.


            But the quoted statement strikes me as odd.  Just reading from the roadmap (FileMaker Product Roadmap ), you're not interested in using any of these? (and I'm just mentioning those that are marked as "Next' and that I will use right away, even if they are not among my own personal pet features - even if only one or two of these makes into FM16)


 — Ability to create a window that allows users to open other windows or files without having to first close the card.

            PDF support in FileMaker WebDirect and FileMaker Server — Print PDFs of layout and data in FileMaker WebDirect. Save information as PDFs, using scripts performed on FileMaker Server.

            JSON functions — Provide a set of predefined functions to parse and generate JSON data exchanged with other data sources, such as web services, that have REST APIs.  --> this one could be big, even if we're not integrating with APIs, we can use JSON and Arrays to pass complex bits of data around our solution, no more jumping through hoops for multiple parameters / script results...

            Enhanced cURL options — Provide cURL options to configure HTTP/HTTPS calls that work with REST APIs.

            External script steps Use a script step provided as part of a third-party plug-in to extend the feature set of FileMaker Pro. Supports FileMaker plug-ins in iOS apps created with the iOS App SDK.

            OAuth 2.0 support for accounts — 
Provide a secure way to authenticate FileMaker users via a third-party identity provider for simplified credential management.

            FileMaker WebDirect scalability
 — Improve client capacity using multiple FileMaker WebDirect worker machines.

            3 of 3 people found this helpful
            • 3. Re: Will Date Handling Be Better in FMP 16?

              To build on Wim's point, if you are running Microsoft Windows the Single Document Interface (SDI) is going to be huge. Developers have been asking for this for years. That alone is enough for me to jump on the next version ASAP.

              • 4. Re: Will Date Handling Be Better in FMP 16?

                Seems like we always get posts that read:


                "My {insert poster's pet peeve here} is still a problem so I'm not upgrading"


                About the time that a new version comes out.


                And like Wim, it always strikes me as short sighted.


                The only time the different date formats between FileMaker proper and FileMaker SQL affects my work is when I get a date result from ExecuteSQL. At that point the parsing needed to get data I can convert back into a date is trivial. Sure, I use use a CF, but only as a matter of convenience. I could easily get what I need from FileMaker native functions if the CF was not available for some reason.


                The date format issue doesn't normally arise in the query because I use the optional parameter feature to insert my dates into the query. FileMaker then takes care of the formatting difference for me.

                2 of 2 people found this helpful
                • 5. Re: Will Date Handling Be Better in FMP 16?

                  Dates  Hmm  Let me mention INTERNATIONAL   and at the same time give a large irony alert!


                  First off was that the 1st of May or the 5th Jan that you were referring to?


                  Sorry to be international but hey we have this problem outside the USA>  Nobody uses the American date scheme other than umm USA  Ok maybe one or two dependancies.


                  "In the United States, our date format begins with the month and ends with the year (MM/DD/YYYY), and this arrangement is unique. In most of the rest of the world, the day is written first and the year last (DD/MM/YYYY), although in some places like China, Korea and Iran, this order is flipped (YYYY/MM/DD)."


                  This is why FileMaker and nearly every other data scheme uses a serial number with a start date to convert it to the native language. They of course all got together and started on the same day and use the same calculation base right!


                  For example the SQL Date time is made up of two parts. The first is the number of days since 1900-01-01. The second is the number of clock ticks since midnight. Each clock tick is 1/300 seconds. So the 0 date has always been 1900-01-01 00:00:00.000 since there was a SQL Server and probably before (when it was still sybase)  Where as FIlemaker isn't  the same. Oh and don't get me started on some tho have one base 0 for one programme and a different one for others.



                  So every were you go date and time are messed up between systems countries and even developers!  I for one (from an international perspective) love the way you can make such cool use of Day, month and year in FileMaker . 


                  2 of 3 people found this helpful
                  • 6. Re: Will Date Handling Be Better in FMP 16?

                    No, I really don't need a single one of these new features.


                    However, if FMI would implement any of the 60 or so things I do need to be more productive (like, hey, search and replace in scripts, a version 1.0 feature), I'd be first in the upgrade line.


                    Thanks Wim.

                    • 7. Re: Will Date Handling Be Better in FMP 16?

                      I didn't mean my posting as an "near upgrade-time complaint", Phil.


                      I was just baffled that I need to do additional work to help FMP handle its own date formats and was posting a serious suggestion as an obvious version 16 feature that needs to be (have been there) there.


                      I'm a bit perplexed about your statement that the date issue "only happens with ExecuteSQL". My reply to that seems like the logical reply: date handling should be handled consistently, internally, by FMP, not the user handling functionality holes, or special cases, that (apparently) nobody at FMI thought were important.


                      The word for today (for FMI) is ... "abstraction".


                      ("....suppressing the more complex details below the current level...")


                      Abstraction (software engineering) - Wikipedia



                      • 8. Re: Will Date Handling Be Better in FMP 16?

                        Right, locale is important, too. Agreed.


                        In FileMaker, if there is a way to specify date locales, so that FMP uses consistent dates for a given country, I don't know about it. 


                        Standard Date and Time Format Strings

                        • 9. Re: Will Date Handling Be Better in FMP 16?

                          As a developer, for me the following is making my day:


                          Layout Objects window — View a hierarchical list of all objects that are on a layout; select, hide, and name objects; and change the stacking order.

                          • 10. Re: Will Date Handling Be Better in FMP 16?

                            add to the complexity: a user may define the datetime formats on the OS-level. FileMaker tries to use that.


                            However that does NOT negate the need for a date that is International (YYYY-MM-DD) which is also alpha- and chronologically sortable!



                            2 of 2 people found this helpful
                            • 11. Re: Will Date Handling Be Better in FMP 16?

                              You should re-read my post as I did not say what you say that I said.


                              Key phrases that you missed:


                              ...it only affects MY WORK...

                              ...When I get a DATE RESULT from ExecuteSQL...

                              • 12. Re: Will Date Handling Be Better in FMP 16?
                                Mike Duncan

                                You can just use SQL to cast your dates in a format friendly to FMP...


                                SELECT '' || YOUR_DATE_FIELD FROM YOUR_TABLE



                                • 13. Re: Will Date Handling Be Better in FMP 16?

                                  YYYY-MM-DD does not need to converted "from ExecuteSQL" (after results). There are ways to 'format' the date as a SQL function that returns the date as FileMaker likes:

                                  mm/dd/yyyy or dd/mm/yyyy or yyyymmdd (depending on your location settings)



                                  1 2 3 Previous Next