6 Replies Latest reply on Dec 21, 2011 9:39 AM by beverly

    Date format of drop-down calendar in text field


      I have a text field where I display the drop-down calendar to help with the entering of dates. I cannot format the field as a date field, because the users need to enter vague dates like "End of january" as well as "31.1.2012".


      Now the dropdown calendar always enters in the format "31/1/2012" when I really need "31.1.2012". Is there a way to get it to change the format? Thank you.


      PS: I repeat, the field is a *text* field and I cannot format it as a date field.

        • 1. Re: Date format of drop-down calendar in text field



          You can do this with an auto-enter calculation - choose replace existing contents and use the Substitute command.  The syntax would be something like Substitute ( yourfieldhere ; "/"; "." )  Then any slashes in the text would be replaced with periods. - Any other text in the field would be preserved. 


          You can test this by isolating a record that has the wrong format and using the "Replace" command till you get the results you want, then use that calc for the auto enter.  You can also test it with the Data Viewer if you are using FileMaker Pro Advanced (this is the best way to test)



          Karen Weaver



          Desert Dog Technology, Inc.

          1 of 1 people found this helpful
          • 2. Re: Date format of drop-down calendar in text field

            Well, yes, but then any slashes would be replaced with dots. But your answer seems to indicate that there is no way to make the drop-down calendar itself behave differently.


            Thank you for your advice.


            Yuan-Yuan Sun

            Switzerland Tourism

            • 3. Re: Date format of drop-down calendar in text field

              True that you cannot change the behavior of the drop-down calendar unless it is a date field.  There are some other options for what you are trying to achieve, if there are going to be other slashes in the text field that you don't want replaced.


              as one example, you could change the Substitute fundtion to a case statement - test for a date first, then substitute the slashes.  If the data entry is not a valid date, then don't substitute.  It's a more complicated function and will take more time and testing to get right, but could accommodate all the possible entries.


              You could also use more than one field for data entry - both a text and a date field, but only display the results in the text field - that way you could format the drop down calendar however you want.  I actually think this is a better way - because you could also control the text entries and make them consistent.


              I would recommend you give this more thought.  You will find that if your users can enter "anything" in the text field, they will - so one will enter "End of January" and another will enter "End of Jan" and another will enter Jan 31.  Then when you go to do a find or a report with this data, you wont find what you are looking for or you will get a bunch of useless junk.  You may think this is not necessary because you don't plan on using this data in a report - but if they need to enter it, they will need to find it, and cleaning it up later will be horrific.


              There are probably other ways you could solve this problem as well - custom functions, script triggers, etc.  Which is the best solution depends on what will be the best experience for the users, the best results for reporting and analyzing data, the type of entries they are making, etc. etc.  


              Food for thought

              • 4. Re: Date format of drop-down calendar in text field

                Hi Yuan-Yuan Sun,


                With dates, what is entered into the field is determined by your specific OS regional settings.  To FileMaker, a valid date is a valid date and it will accept  periods or slashes; both may exist in date records.  If you wish, you can display consistent dates (using Inspector) so they look the same but the underlying data will remain inconsistent.


                A bigger concern is "because the users need to enter vague dates like "End of january" as well as "31.1.2012".


                There is no software package out there which would allow this type of free-form date entry.  How could a computer decipher it?  I suggest instead that you use a true date field and instead provide the User a slightly different interface for date selection.  Provide buttons with options such as End Of Last Month, This Week, and then also provide a global date field to enter a specific date.


                Use script to set a true date field with their selections.  There is only so much that you can do for a User ... training them to properly enter data is just one of those things.  I can guarantee that, if you do not use a true date field, you will regret it later when you want to sort on the date, find a specific year, search for date ranges and so forth.



                • 5. Re: Date format of drop-down calendar in text field

                  I see both your point concerning true date fields. But if planning cannot be more exact in some cases than something like "3rd Quarter 2012" or "Winter 2012" there's no way I can force the user to put a real date. The alternative would be to use two fields. One if a real date can or must be provided and another for vague entries and use a calculation to decide which one is to be displayed on the report. Thank you both for your answers!


                  Yuan-Yuan Sun

                  • 6. Re: Date format of drop-down calendar in text field

                    Yuan-Yuan Sun, yes! In the case of things like genealogy or object provenance or other historical events, the date can be very vague.


                    Usually there is a YEAR and then text, even if it's a "circa" reference. But rather than decide which appears on the report, can you not put both? If the "pure date" is empty, it does not show. I'd use something like a merge with the fields (<<date>> <<text>> <<year>>) and slide right. That way nothing is missed in the "date" column.


                    Just my suggestion...

                    Beverly Voth

                    1 of 1 people found this helpful