1 2 Previous Next 19 Replies Latest reply on Jan 23, 2012 12:34 PM by philmodjunk

    Displaying same date string in different formats?

      Title

      Displaying same date string in different formats?

      Post

      I have a situation where I would like to display the same date field (as a text string...more on that in a minute) in two different ways.  How can I best do that?  I can figure out how to modify the format of the field contents easily, but I want this date visible in two ways at the same time (different layouts).

       

      Background:  I have a date that I need to display in a big list view, and I want it to show up as MM/DD on this view.  This field is a clickable button which launches a small popup window with a calendar picker for choosing the date.  On this popup I want to date displayed by MM/DD/YY to match what they are picking on the calendar.  So far, nothing too bad, except...

       

      I also want this date to be possible to display the text "N/A".  So in this same date picker window, there is a button to set the field as the text string "N/A".

       

      My architecture is a 'end_date' field as date, a 'display' field as text, and a script to handle setting the value in the display field.  The button on the picker calls the script with a parameter to set the 'display' text field to "N/A".  I would like to only use these fields; this table is already huge and I don't want to add additional fields if I can help it.

       

      I have tried using a merge field to display the 'display' field, and then use the custom display of data settings there to format the quasi-date, but it doesn't seem to effect the display at all and still just shows the entire text string (which is 'MM/DD/YY').  I can't even pick 'Date' number types if I just show the field straight from the table, as it is a text field (the options are grayed out).  Yes, I could modify the script easily to just make the text 'MM/DD', but then on the picker box window the user would only see those two fields after selecting a new date.  I want them to be able to see the "N/A" text if they click that button, so I have implemented it such that the 'display' field is on top of the 'end_date' picker field, with the 'end_date' field being enterable in Browse mode.  That way they SEE the display field, but they INTERACT with the date field.

       

      Any suggestions? 

       

      Thanks,

      J

        • 1. Re: Displaying same date string in different formats?
          philmodjunk

          Using text for your date instead of a true date field usually causes more problems than it solves.

          With a field of type date, you have access to built in features of FileMaker for formatting and inputting dates that will not be available to you in a text field. You can also sort and search a date field in ways specific to dates that are not possible with text fields.

          Please explain why this is important to you:

          I can figure out how to modify the format of the field contents easily, but I don't want this date visible in two ways at the same time (different layouts).

          Why would a different format for the same data be an issue? If it is, it's easily solved by specifying the same format on each layout where you use the field and then you can keep the date as a date instead of as text. The "N/A" can even be put in place as layout text that is hidden by conditional formatting unless the date field is empty. This would do everything you as except you wouldn't have a button for "N/A" on the date pop up.

          If you truly must set this up as text with an "N/A" button, you'd need to create your own pop up window or use a plug in (if one exists that can do this) to get that pop up with the N/A button.

          • 2. Re: Displaying same date string in different formats?

            Regarding the quote you made in my original post...oops, that should have been "DO want this date visible...".  I changed the original post.  So on one page I want them to see the date in the form MM/DD/YY, but on another layout I want them to see it as "MM/DD".  I also only want to use one display field on BOTH of these layouts, so that when they pick the "N/A" check box they get that visual feedback immediately.

            And I want the two forms of the field because on the main layout where it will be visible the layout is already very wide, and having to include space for a four digit year would be cumbersome.

             

            Attached are some sample shots of the user interface where they would be picking a date.

             

            It needs to be a text field in order to display the "N/A" case.  That means I can't store the calc field as a 'date' value.  (A bit of rearchitecting; I am using a calc field for the 'display' field now, but still have the issue of how to display it in two different ways.)

             

            Any underlying functions ARE operating on a date field, so sorting and searches are working on a date formatted string.  The entry of a date is done using the calendar picker (or typed in).

             

            I will look into using conditional formatting as you suggest.

            • 3. Re: Displaying same date string in different formats?
              philmodjunk

              So on one page I want them to see the date in the form MM/DD/YY, but on another layout I want them to see it as "MM/DD".

              With a field of type date, you can simply specify different date formats to do that.

              You can do what you want with two fields

              Field 1 is text and stores N/A or is empty.

              Field 2 is the formatted date field, formatted as needed for each layout. You specify formats for your dates in the data tab of the inspector where you can include a two digit date for one instance of the field and leave off all year info for another.

              You can place Field 2, with a transparent fill pattern on top of Field 1 so that the user sees either a date or N/A.

              Script triggers and/or auto-enter calculations on these two fields can ensure that field 1 and field 2 never both store data at the same time. (entering data in one can clear the other, for example.)

              In your screen shot, you can put the N/A field on it twice, once formatted as a check box as shown, the second formatted as an edit box with a field behavior setting that prevents field entry. Place it on top of the date field so that it covers the date field except for the icon for the calendar pop up.

               

              • 4. Re: Displaying same date string in different formats?

                I think I get what you are saying.  I have 3/4 of what you suggested above working, but it is the part about auto-enter calculations that is bugging me.  (I would like to avoid script triggers; I had a somewhat heated conversation with a co-worker about how to do this and he would really like to avoid scripts for this.  :))

                How do I get the Date formatted field to clear it's contents if the 'N/A' checkbox is set?  For the 'Display' field, which is a calculation type, I can use a case statement.  But it doesn't seem like you can do the same thing with a field that is storing a date...can you?  If I could use the date picker UI widget as the INPUT to a calc field, that would work.

                 

                So I think I am missing something from your description.

                 

                Thanks,

                -  J

                • 5. Re: Displaying same date string in different formats?

                  Oh!  I think I might have it.  Give me some more time to fiddle...

                  • 6. Re: Displaying same date string in different formats?
                    philmodjunk

                    What is the objection to using a script trigger?

                    The main advantage to auto-enter calculations for this is that what you set will apply to all uses of the field on all layouts. Triggered scripts require that you consistently use the same triggers every place where it's possible to edit the data in one of these two fields.

                    Using an auto-entered calc:

                    Let's say your two fields are named "EndDate" and "NA"

                    For EndDate,

                    make this auto-enter calcualtion:

                    If ( IsEmpty ( Na ) ; Self )

                    and clear the "do not replace existing value..." check box.

                    For NA, use the same logic:

                    If ( not EndDate ; Self )

                    Note that this locks you out of entering data in one of the two fields if the other has data in it. You have to clear the first field before you can enter data into the other one. Script triggers can handle this in a more user friendly fashion as that gives you the option to clear field 1 when you first enter field 2 or you can pop up a message asking confirmation of the change and so forth.

                    And sometimes it makes sense to use both methods--the triggers to make it nicer for the user, the auto-enter calc for your peace of mind--knowing that if you missed a spot for a trigger, you are still coverd by the auto-enter calc.

                    • 7. Re: Displaying same date string in different formats?

                      OK, I have about 3/4 more done...  :)

                       

                      Here is my date field, auto-entered value (overwrite existing values): 

                           Case (Date_NA = 1; "" )

                       

                      And my Display field (calculation field, not auto-enter):

                           Case (
                          Date_NA  = 1 ; "N/A";
                          ""
                      )

                       

                      And the N/A field is just a number field defined by a checkbox using a value list of "1". 

                       

                      I get most states to work out correctly.  The only problem comes in when NA is already set and then you click to set a date.  It goes ahead and sets the date.  (And clearing the NA checkbox also appears to clear both fields.  Odd.)

                       

                      You just posted something while I was typing this; going to go read it...

                       

                      • 8. Re: Displaying same date string in different formats?

                        Ah, using a auto-enter calc in the NA field, and the 'Self' command.  I hadn't thought of those (not familiar with Self at all, frankly).  That is clever.  I was wondering how you could get the NA field to clear.

                         

                        Still running into a problem:  if there is a date in DATE, then I can't mark the NA field ON.  (This is with the NA field state empty to start with.)  I probably don't have my calcs right yet in my Display field.  More testing...

                        • 9. Re: Displaying same date string in different formats?

                          Yeah, having issue with DATE field containing a value, and trying to toggle NA from empty to "1" (i.e. checking the checkbox on the UI).

                          -----------

                          DATE_END     (auto-enter calc, deselected 'Do not replace...'):

                               Case ( IsEmpty(Graphics_Date_NA); Self )

                          -----------

                          NA     (auto-enter calc, deselcted 'Do not replace...'):

                               Case  ( not Date_End; Self)

                          ---------

                          DISPLAY    (calc field, result text):

                          Case (
                              Graphics_Date_NA  = 1; "N/A";
                              ""
                          )

                          ------------

                          Should this also be in an auto-enter calc for Display?

                           

                          Hmmm...OK, makes sense that I have this problem as I am checking for "not Date_End" before assigning self...I think I want NA to override date_end.  It works fine if they delete the value in Date_End before checking NA.

                          • 10. Re: Displaying same date string in different formats?

                            Bah, I keep thinking I need a 'Set field' type command that I can put into the checks.  NA should override and wipe clear Date_End; likewise if they set a date that was previously checked as NA, NA should clear and the Date should be active.  It doesn't seem to be working like that.

                             

                            This may never come up as an issue; they may never be changing their minds after an initial decision is made.  But this is how it would make sense to operate, just in case they DID change their minds about setting this date.

                             

                            When do the auto-calcs evaluate?  As soon as a change is made to a field, or only after the entire auto-calc of a field is finished?  I suppose that the field won't know what to change itself to until the auto-calc is done.

                            • 11. Re: Displaying same date string in different formats?
                              philmodjunk

                              My last post seems to have disappeared. There's no need for the extra calculation field to display the value. Check out this demo file. Script triggers handle the need to clear the date when N/A is clicked quite nicely: http://www.4shared.com/file/MyMqIZKO/ExclusiveFieldsTest.html

                              • 12. Re: Displaying same date string in different formats?

                                Thanks for the sample file. 

                                 

                                It looks like your non-trigger version has the same behavior I ran in to, so at least I am not too far off base.  :)

                                 

                                -- J

                                • 13. Re: Displaying same date string in different formats?
                                  philmodjunk

                                  You could set the check box field up as a button with this script:

                                  If [IsEmpty (YourTable::NA) ]
                                    Set Field [Yourtable::endDate ; ""]
                                    Set Field[YourTable::NA ; "N/A"]
                                  Else
                                     Set Field [YourTable::NA ; "" ]
                                  End IF

                                  • 14. Re: Displaying same date string in different formats?

                                    I think I like the behavior better if I remove the auto-entered calc from the NA field and leave the Date_End as you describe.  Then at least the N/A checkbox will override the Date_End field.  You still have to clear the checkbox before making a new date, but that kind of makes sense.

                                     

                                    Thanks again, Phil.

                                     

                                    --  J

                                    1 2 Previous Next