1 2 Previous Next 15 Replies Latest reply on Nov 13, 2009 4:46 PM by philmodjunk

    Specific date format in Timestamp

    thingmaker

      Title

      Specific date format in Timestamp

      Post

      Hello, wondering if someone else has run into this. Having people in Europe and US causes date issues as they are formatted differently. So, instead of 4-5-09 we use 4-April-09 to avoid confusion. When using timestamps though it only uses the numbers. Is there a way to format the timestamps? Thanks. 

       

      Filemaker 9

      Mac OS 10.5.8

      Intel machine  

        • 1. Re: Specific date format in Timestamp
          bbarreto
            

          Is the timestamp being used just for display purpose? If so then you can use something like GetAsDate ( GetAsText ( YourTable::Timestamp ) ) to pull the date out of the Timestamp and then use your Date Format options on that.  If this is the case you can then display it as a Merge field and use GetAsTime ( GetAsText ( YourTable::Timestamp ) ) to pull the time portion out and show that as well.  Or perhaps you can use these together in a Custom Function which returns Text having the date formatted the way you want.  Your Custom Function may look like this with TS as the variable:

           

          CF_FormatTimeStamp (TS) = 

          Let (
          [
              myDate = GetAsDate ( GetAsText ( TS ) );
              myTime = GetAsTime ( GetAsText ( TS ) );
              myDay = GetAsText ( Day ( myDate ) );
              myMonth = MonthName ( myDate );
              myYear = GetAsText ( Year ( myDate ) )
          ]
          ;

          myDay & "-" & myMonth & "-" & myYear & " " & myTime

          )

           

           

          Hope this helps






          • 2. Re: Specific date format in Timestamp
            thingmaker
              

            Thanks. I looked up GetAsDate and I guess it is used in a calculation field. I have created scripts but I am not familiar with Calculations.

             

            In looking at what you wrote I assume that this calculation is applied to a text record and refers to a timestamp record which I assume to be named TS as in your calculation? I just need a little clarification, thanks. -Derryl

             

            • 3. Re: Specific date format in Timestamp
              philmodjunk
                

              There's a much simpler way to split a time stamp into separate date and time fields so you can format them:

               

              Define a time stamp field called TS.

               

              Define a calculation field cTStime

               

              TS

               

              Select Time as its result type (drop down in lower left corner)

               

              Define a calculation field cTSdate

               

              TS

               

              Select Date as its result type.

               

              In both cases, the name of the time stamp field is all you need to enter for the specified calculation. The result type setting does the rest.

               

              You can also apply date and time formats directly to the time stamp field. Simply select the field in layout mode and select format | Date... Then select a time format. (For some reason the date format doesn't affect the time stamp field until you also specify a time format.)

              • 4. Re: Specific date format in Timestamp
                thingmaker
                   Amazing! Thanks Phil, I never knew about having  to do the secondary time format step. I just aways thought the format date for timestamp was broken! Thanks again. -Derryl
                • 5. Re: Specific date format in Timestamp
                  philmodjunk
                    

                  I thought it was "broken" too until I started playing with the settings to verify that my post was accurate.

                   

                  Purely by accident, I discovered that if I applied both formats (date and time) to a time stamp field, I could format the displayed data. If I removed either format, changing it to "format as entered", the other format (date or time) no longer applied as well.

                  • 6. Re: Specific date format in Timestamp
                    thingmaker
                      

                    Phil, thanks with the help formatting the date field. The next part of the issue was creating a field generated by an (approval) button that says   "11-Nov-09 - BG"  

                     

                    Basically a datestamp with the person's initials that authorized it. The problem is, is it can't be a date field and have the initials in it, and if it is generated by a script there is not customizing of the date. Or is there a way to generated custom dates in script?  I tried your method in message #4 but no results. Any hints would be appreciated. Thanks. -dr

                     

                    • 7. Re: Specific date format in Timestamp
                      philmodjunk
                        

                      You can break down the date into it's parts and format the parts, building up a text result that displays what you ask. Alternatively, you can create the visual effect you describe with merge fields.

                       

                      Use the text tool to start a block of layout text.

                      Choose Insert | Merge Field to insert the date field.

                      Type in the hyphen and spaces

                      Choose Insert | Merge Field to insert the "initials" field.

                       

                      You'll see something like this:

                      <<YourTable:: DateField>> - <<YourTable::initialsField>>

                      You can select format date for this block of layout text to specify a date format for the merged date field.

                      In browse mode, you'll see: 11-Nov-09 - BG

                       

                      All in one field:

                      Right("0" & Day(datefield) ; 2 ) & "-" & Left(monthname(datefield);3) & "-" & Right(Year(datefield);2) & " - " & initialsfield

                      • 8. Re: Specific date format in Timestamp
                        thingmaker
                          

                        Thanks Phil, very helpful. Using the merge on a text block is something I didn't know about. So, in your example the date field & initials field are pre-existing and are necessary to pull the data from somewhere. Seems redundant to have everything on the page twice though. The Help doesn't appear to have anything about hiding fields. 

                         

                        Also, in your second 'All in one field' example, where does this go? if you put it in a text block it just shows it verbatim. Thanks again. 

                        • 9. Re: Specific date format in Timestamp
                          philmodjunk
                            

                          You would put it in a calculation field.

                           

                          "Seems redundant to have everything on the page twice though. "

                           

                          Once in separate fields for editing and once as a combined object for viewing?

                          I wouldn't use either method to display this data on an edit layout myself, I'd just put the two data fields next to each other. I'd use one of the two options I described on report layouts for slightly cleaner printing.

                          • 10. Re: Specific date format in Timestamp
                            thingmaker
                               Thanks Phil, in these cases I don't won't people to edit the fields. For example I have a Verify button that when pressed inputs into a field the current date + the user. It's easy to do as a script using the normal date format but needing the EU date format is what is causing grief. Same issue with timestamps in note fields, not much flexibility with the dates. -Derryl
                            • 11. Re: Specific date format in Timestamp
                              philmodjunk
                                

                              If you are setting the info with a script you can  use set field to enter the data into the two separate data fields.

                               

                              When using set field, the individual data fields do not have to be placed on the current layout to work.

                               

                              It is possible to modify the "all in one" expression to simply enter the data into a single text field. I'm recommending against that because there are many situations where you may need to continue to treat that data field as a date--not text.

                              • 12. Re: Specific date format in Timestamp
                                thingmaker
                                  

                                Phil, the timestamping of individual fields works great now. But I am still stuck on the other timestamp. For example I have a log window to record notes on transactions. The process is that before someone types, they hit the button next to it which inserts a timestamp with their initials. This is easy to do as a script if you use the conventional date format. But since you can't format the large text field as a Date field I assume changing the timestamp appearance has to be done with a script.

                                 

                                So it seems that I need a script that

                                - somehow pulls up the timestamp info (date only)

                                - converts it into text

                                - Chops it into pieces and

                                - re-outputs it in the correct order with the other text concatenated on to it.

                                 

                                Am I on the right track?  

                                 

                                 

                                • 13. Re: Specific date format in Timestamp
                                  philmodjunk
                                    

                                  You can do this as an auto-enter:

                                   

                                  Right("0" & Day(GetCurrentDate) ; 2 ) & "-" & Left(monthname(GetCurrentDate);3) & "-" & Right(Year(GetCurrentDate);2) & " - " & initialsfield

                                   

                                  In place of "Initialsfield" put whatever expression you are currently using to add the user's initials.

                                   

                                  Note that this is a DATE not a time stamp. You'd have to include a Get (currentTime) somewhere in the above expression if you want the time included as well.

                                  • 14. Re: Specific date format in Timestamp
                                    thingmaker
                                       As a script would this go in "Insert Calculated Result"?  It says a specified table can not be found. I added the table name and field in front with :: but got more syntax errors. Thanks. 
                                    1 2 Previous Next