1 2 Previous Next 27 Replies Latest reply on Mar 13, 2012 6:10 AM by willrollo

    Add the 'th' or 'st' to 1 December or 4 December etc

      Title

      Add the 'th' or 'st' to 1 December or 4 December etc

      Post

      I know that FM has a custom section to alter the format of the date fields, but is there anyway to add the st, nd and th to the numbers as I think it looks a lot better in a letter to have 1st December 2012 as opposed to 1 December 2012...

        • 1. Re: Add the 'th' or 'st' to 1 December or 4 December etc
          philmodjunk

          You could with a calculation field, but I don't see a way to do that with layout based data formatting.

          Let ( [ Dte = Datefield ;
                     d = Day ( Dte ) ] ;
                     If ( d < 4 ; Choose ( d ; "" ; "1st" ; "2nd" ; "3rd" ) ; d & "th" ) & " " & MonthName ( dte ) & " " Year ( dte )
                  )
                 

          • 2. Re: Add the 'th' or 'st' to 1 December or 4 December etc

            that looks quite tricky! Might leave it for moment as still tackling the formula you gave me for the 1st /2nd week of month..

             

            Let ( [ D = YourDateField ;
                       m1 = D - Day ( D ) + 1;
                     ] ;
                     WeekOfYear ( D ) - WeekOfYear ( m1 ) + 1
                   )

             

            My datefield is called Date required. I have tried this but "A number, text constant, field name or “(” is expected here."

            Let ( WeekOfYear (Date Required) - WeekOfYear (Date Required - Day ( Date Required ) + 1) ))

             

            I know I should have added this to the original post but they are linked and thought it would be easier for you!

            • 3. Re: Add the 'th' or 'st' to 1 December or 4 December etc

              Fixed it - was being a bit thick. Can I convert the result from a digit to a word? (1 - first, 2 - second etc)?

              • 4. Re: Add the 'th' or 'st' to 1 December or 4 December etc
                philmodjunk

                Yes, you'd edit the Choose function to substitute in the words instead of "1st" ; "2nd" ; "3rd" and you'd toss out the If function--using a list of 32 values in the Choose function instead. (Choose works from a zero based index--which is why you have an extra value with "" at the beginning of the list.)

                And if you have Filemaker 11, you need not put this calculation inside a script or a calculation field to use it. You can put it inside a let function as part of a conditional format expression to load the value into a variable for display as merge text on your layout.

                • 5. Re: Add the 'th' or 'st' to 1 December or 4 December etc

                  Thank you

                  I havent looked into this let function stuff - I do have FM11. I have put this date formula into a calculation for a script but not sure how to mix text and date format. I am getting this as a result (when doing the formula to create "first week of December" etc

                   this is formula: If (Date Exact = "Approx" ; "the " & WeekOfYear(Date Required) - WeekOfYear (Date Required - Day ( Date Required ) + 1)  & "week of "  & Month(Date Required) ; Date Required)

                  I get this at the moment "by the 0week of 12

                  If  ::date exact  is "approx", I want the text to read whatever the date has been input by the user (into ::date required), eg, if ::Date Exact has got 'Approx' entered into it, then the ::Date required field, which at the moment reads 10 December 2012 (from a drop down calendar), then I want the text to read, ready by the 2nd week of December, and we will let you know. If Exact is typed in, then I want the text to read "ready by ::Date required. Hope this makes sense! Note added issue of putting the 'th' after 10!

                  May not read answer tonight as going to bed!

                  • 6. Re: Add the 'th' or 'st' to 1 December or 4 December etc
                    philmodjunk

                    Let has 3 uses:

                    1. To simplify expressions so that long table::Field names need only be typed in once
                    2. To speed up calculations by making certain evaluations only once
                    3. To assign values to $variables as a side effect of evaluating the entire let function.

                     

                    Using your expression, change it to:

                    If (Date Exact = "Approx" ; "the " & WeekOfYear(Date Required) - WeekOfYear (Date Required - Day ( Date Required ) + 1)  + 1 & " week of "  & MonthName(Date Required) ; Date Required)

                    I've also inserted a space before the w to change "week of " to " week of ".

                    • 7. Re: Add the 'th' or 'st' to 1 December or 4 December etc

                      That works perfectly. Is there anyway I can now use a Let or Choose function before this calc to make '1' = First, '2' = second, etc?

                      Do I create a conditional format expressionas you sgeested and then use it inthis formula? HAve no idea where to start with this! 

                      Thank you again for your conistant support...Will

                      • 8. Re: Add the 'th' or 'st' to 1 December or 4 December etc
                        philmodjunk

                        let has to use identifiers that have legal syntax. That means something that starts with a letter or a dollarSign, not a numeric digit. That would make for very confusing calculations if you could use the numeral 1 and have it represent the text "first".

                        First, you'll need to decide if you really want to use the variable in place of a calculation field with this expression. There are trade offs with each method.

                        The calculation field can be used in multiple layouts and should you need to change it, you can change the definition to see it update on all the layouts where you used it.

                        A variable used as Merge text with a conditonal format expression loading it with a value simplifies your table by avoiding the need to add yet another calculation field to it, but if you copy and paste the layout objects to multiple layouts and then discover that you need to change how it evaluates, you have to make identical updates to each layout.

                        Here's the layout text method:

                        Type this text somewhere off to the side on your layout where it will not be in the way of your layout design:

                        I declare variables

                        The exact text is not important, you just want to be able to see it when you are in layout mode so that you know it's there.

                        Give it this conditional format expression:

                        Let ( [ Dte = Yourtable::Datefield ;
                                   d = Day ( Dte ) ;
                                   $$FormattedDate = If ( d < 4 ; Choose ( d ; "" ; "1st" ; "2nd" ; "3rd" ) ; d & "th" ) & " " & MonthName ( dte ) & " " Year ( dte )
                                  ] ;
                                   True )

                        You can update this to use the modified choose function we discussed if you want.

                        Then click the More Formatting... button and select a font size of 500. This expression always evaluates as True and then makes the text disappear when you are not in layout mode. It assigns the value of the calculation we have been discussing to the global variable $$FormattedDate as a "side effect".

                        Now put the layout text: <<$$FormattedDate>> on your layout where you want to see the formatted date text and this should work. (And a thanks to LaRetta for teaching me this trick.)

                        • 9. Re: Add the 'th' or 'st' to 1 December or 4 December etc

                          Thank you very much for such a thorough explanation. I shall look through it all and experiment...

                          • 10. Re: Add the 'th' or 'st' to 1 December or 4 December etc

                            Works perfectly - although I have a small problem as I want to use this on other dates...Eg I have an orders table with order date, as well as enquiry table with enqiury date and the same again for invoices..How would I adapt this to work with these? I have altered the DTE Let part but I assume this wont work due to using the merge field being the same. 

                            • 11. Re: Add the 'th' or 'st' to 1 December or 4 December etc
                              philmodjunk

                              You need to change the date field referenced and then use a different name for your variable.

                              • 12. Re: Add the 'th' or 'st' to 1 December or 4 December etc

                                have tried this but it doesnt work any ideas?

                                Enquiry Date posted is the name of my field. (Cutomers::Enquiry Date posted)

                                 

                                I renamed the variable to $$EnquiryDate. IS this ok? Can I make it anything I want? What is odd, is that nothing is appearing at all on my layout - not even a ?

                                Let ( [ Dte = Enquiry Date posted;
                                           d = Day ( Dte ) ;
                                
                                           $$EnquiryDate = If ( d < 4 ; Choose ( d ; "" ; "1st" ; "2nd" ; "3rd" ) ; d & "th" ) & " " & MonthName ( dte ) & " " & Year ( dte )
                                          ] ;
                                           True )
                                • 13. Re: Add the 'th' or 'st' to 1 December or 4 December etc

                                  Actually, it jsut shows the variable field as I typed it out in layout mode ie <<$$EnquiryDate>>.

                                  I assume that the orginal field shoudl be formatted as a Date as opposed to text. Will it work if the date is auto entered into the original field (the Enquiry date posted field) by a set field script for todays date?

                                   

                                  Could this work?

                                  Let ( [ Dte = Get(CurrentDate);
                                             d = Day ( Dte ) ;
                                  
                                             $$EnquiryDate = If ( d < 4 ; Choose ( d ; "" ; "1st" ; "2nd" ; "3rd" ) ; d & "th" ) & " " & MonthName ( dte ) & " " & Year ( dte )
                                            ] ;
                                             True )
                                   
                                  ??
                                  Also..
                                  If the user would want to reprint this layout, as it is a print layout, what would be the best way to go about providing the user with an option to reprint with
                                   the orginal date the layout was printed or with the current date? I know first things first but may forget to ask later as hope it it a simple answer...
                                  • 14. Re: Add the 'th' or 'st' to 1 December or 4 December etc
                                    philmodjunk

                                    If there is no value in the variable, you'll see <<$$EnquiryDate>>. You should use

                                    Let ( [ Dte = Customers::Enquiry Date posted;

                                    and make sure th name of the global variable is letter for letter exactly the same.
                                    get ( CurrentDate ) should work just fine if you want today's date for this.

                                    If the user needs to be able to specify the date, I'd use a global date field with controls that let the user select the desired date.
                                    1 2 Previous Next