14 Replies Latest reply on Mar 16, 2017 5:26 PM by keywords

    How to force initial caps on data entry

    jeffsb

      When entering, for instance, first and last names, how can you force initial character to be upper case. How when entering state abbreviations (NY, CO, TX, NM), can you force all caps.

       

      Thanks for any help with this.

        • 1. Re: How to force initial caps on data entry
          coherentkris

          Upper() or Proper()

          1 of 1 people found this helpful
          • 2. Re: How to force initial caps on data entry
            philmodjunk

            Use those functions as auto-enter calculations.

            • 3. Re: How to force initial caps on data entry
              beverly

              Upper() converts all characters to uppercase.

              Proper() converts first character of all words to uppercase.

               

              For State abbreviations, Upper() is correct.

               

              beverly

              1 of 1 people found this helpful
              • 4. Re: How to force initial caps on data entry
                jeffsb

                I marked this as the correct answer because you mentioned auto-enter, which i didn't even know what that was -- had to look it up, an was necessary to know before I could get these functions to work!

                 

                Thank you for solving this problem for me!

                 

                And thank you Beverly and CoherentKris for your helpful answers!

                 

                Is the fact that the field is text that makes it auto-enter calculation and if it was a number field, then it would be a "regular" calculation?  What is the difference between an auto-enter calculation and a "regular" calculation?

                • 5. Re: How to force initial caps on data entry
                  philmodjunk

                  This was what I call a classic "wedge" response. The first response was the "tip". If all you needed was the function names, your question ias answered. Each following response added a bigger "slice" of information.

                   

                  Auto-enter calculations are set up on the auto-enter tab. The field type can be any type that permits directly editing the field. This can be a number, text, date--pretty much any type except the calculation field type. Fields of type calculation cannot be edited and must have a result data type selected.

                   

                  Auto-enter calculations on a data field and a calculation field can be functionally identical. But there are differences in indexing, storage, and how they update.

                  • 6. Re: How to force initial caps on data entry
                    keywords

                    Be aware that your auto-enter calc may need to be able to take into account spelling variations other than just basic Proper ( ). For, example: McCarthy, MacLennan, Macadam, O'Donnell, Everyman-Jones, and a string of other possibilities. You may be able to anticipate these and build them into the calc, or you may need a method for accepting variants that the calc would otherwise alter.

                    • 7. Re: How to force initial caps on data entry
                      jeffsb

                      Well, Proper() saves the person keying in the data from having to capitalize the first letter of the last name but they will still have to capitalize the other letters in cases such as you give, like McCarthy etc. We are not going to try to automate the capitalization of Scottish etc names like those -- just the first letter of the first and last names.

                       

                      Thanks for your suggestion.

                      • 8. Re: How to force initial caps on data entry
                        philmodjunk

                        I figured out this calculation years ago to combat setting the caps lock and entering all caps:

                         

                        Case ( Exact ( self ; Upper ( self ) ) Or

                                    Exact ( self ; Lower ( self ) ) ; Proper ( self );

                                     Self )

                         

                        It doesn't guarantee perfect capitalization, but does keep the calculation from removing capitalized letters entered for other than first letters.

                        1 of 1 people found this helpful
                        • 9. Re: How to force initial caps on data entry
                          keywords

                          That is very neat, Phil. I use a rather more complicated system involving a script trigger to allow subsequent change to the field content if required. Basically, my script checks to see if the field already contains data. If it is empty, the script exits and the auto-enter calc does its job; if data exists, the script checks to see if you want to alter it or replace it and then responds accordingly. If you choose alter, it allows whatever you then enter to stand.

                          The biggest limitation is that if the user entered what they want in the first place they have to do it all over again. Your calc allows what the user enters to stand if it is non-standard capitalisation. In other words: if you enter iphone it will be converted to Iphone, but if you enter iPhone it will be allowed to stand.

                          There are obviously situations where subsequent alteration may still be required (e.g. fixing typos and other errors) but I will be looking at how to incorporate your calc into my system, and when I do I will acknowledge you and this forum as the source in my calculation comments. Thank you.

                          • 10. Re: How to force initial caps on data entry
                            jeffsb

                            philmodjunk wrote:

                             

                            I figured out this calculation years ago to combat setting the caps lock and entering all caps:

                             

                            Case ( Exact ( self ; Upper ( self ) ) Or

                            Exact ( self ; Lower ( self ) ) ; Proper ( self );

                            Self )

                             

                            It doesn't guarantee perfect capitalization, but does keep the calculation from removing capitalized letters entered for other than first letters.

                            I didn't realize that the problem with proper() is that it does not allow, for instance, McDonald. I inserted your code, philmodjunk, which helps, but you sometimes lose the first letter being automatically capitalized so you have to go back and correct it. But at least you CAN correct it. Why isn't there a code that ONLY automatically makes the first letter Capitalized but allows subsequent letters being in either upper or lower case? Is there a way to script this?

                            • 11. Re: How to force initial caps on data entry
                              philmodjunk

                              My calculation does allow you to capitalize subsequent letters but it doesn't force capitalization of the first letter if subsequent letters are capitalized.

                               

                              This is is by design not a limitation of FileMaker.

                               

                              Upper ( Left ( Self ; 1 ) ) & right ( self ; length ( self ) - 1 )

                               

                              Will capitalize the first letter leaving the rest "as entered".

                              1 of 1 people found this helpful
                              • 12. Re: How to force initial caps on data entry
                                keywords

                                As noted in my previous post, I have a method that uses script triggers to allow non-standard capitalisation. This method now incorporates the Case ( ) calc posted by Phil, as follows:

                                1.     The Auto-Enter Calculation

                                // PURPOSE: format Name data in the standard format required

                                // NOTE:

                                // 1. the above calculation applies standard formatting to any data entered

                                // 2. result must (1) contain no extraneous spaces, (2) contain no carriage returns, and (3) be in Title case—unless intentional: see below

                                // 3. existing contents if any can be passed to global variable ($$thisField) set by an onEnter script and cleared by an onExit script—this allows for subsequent intentional changes to data if they are outside the Title Case rules

                                // 4. variation for deployment in a data-separation solution—existing contents if any must be passed to a global FIELD as global variables are file specific

                                // 5. this version incorporates a Case function ("formatted" variable) to allow any non-standard capitalisation initially entered by the user. This calc was submitted to the FM Technet Forum by Phil Caulkins, aka PhilModJunk, aka Caulkins Consulting

                                 

                                Let (

                                [

                                    existing = tableName::temp

                                  ; rawData = Self

                                  ; stripped = TrimAll ( Substitute ( rawData ; "¶" ; " " ) ; 2 ; 2 )

                                  ; formatted = Case (

                                  Exact ( rawData ; Upper ( rawData ) )

                                  or

                                  Exact ( rawData ; Lower ( rawData ) ) ;

                                  Proper ( rawData ) ;

                                  rawData

                                  )

                                  ; result = If ( IsEmpty ( existing ) ; formatted ; rawData )

                                ] ;

                                result

                                )

                                 

                                2.     Script activated on field entry

                                # activated OnObjectEnter to captures existing contents of a field for use in an internal autoenter formatting calculation

                                If [ IsEmpty ( Get ( ActiveFieldContents ) ) ]

                                  Exit Script [ ]

                                End If

                                Set Variable [ $thisField; Value:Get ( ActiveFieldTableName ) & "::" & Get ( ActiveFieldName ) ]

                                Show Custom Dialog [ Title: "Just checking ..."; Message: "This field already contains data. Do you wish to alter it or replace it?¶ —click CANCEL to leave unchanged"; Default Button: “Alter”, Commit: “No”; Button 2: “Replace”, Commit: “No”; Button 3: “CANCEL”, Commit: “No” ]

                                If [ defaultButton // choose to ALTER data ]
                                       Set Field [ tableName::temp; Get ( ActiveFieldContents ) ]

                                  Else If [ secondButton // choose to REPLACE data ]

                                       Set Field By Name [ $thisField; "" ]

                                  Else If [ thirdButton // choose to CANCEL action ]

                                       Set Field [ tableName::temp ; "" ]

                                  GotoField[ ]

                                End If
                                Exit Script
                                [ ]

                                3.     Script activated on field exit

                                # activated OnObjectExit to clear the global variable set by the set thisField script when the field #—
                                Set Field [ tableName::temp ; "" ]
                                GotoField[ ]

                                Exit Script [ ]

                                1 of 1 people found this helpful
                                • 13. Re: How to force initial caps on data entry
                                  jeffsb

                                  philmodjunk wrote:

                                   

                                  My calculation does allow you to capitalize subsequent letters but it doesn't force capitalization of the first letter if subsequent letters are capitalized.

                                   

                                  This is is by design not a limitation of FileMaker.

                                   

                                  Upper ( Left ( Self ; 1 ) ) & right ( self ; length ( self ) - 1 )

                                   

                                  Will capitalize the first letter leaving the rest "as entered".

                                  Thank you! Just what the doctor ordered! I inserted that in first name, last name and city -- works great! I'm willing to accept the limitation that with this code you can't put the first letter in lower case  --  prefer that than allowing lower case first letter.

                                  • 14. Re: How to force initial caps on data entry
                                    keywords

                                    Further to my post above. I have now realised that Phil's Case ( ) function obviates the need for the scripting above, since it allows non-standard capitalisation, which is what my scripted approach was designed to achieve. Nevertheless, I still use that function within my calc because I still want to strip out carriage returns and extra spaces. In the process of testing it I also fixed a couple of other errors.

                                    For the benefit of anyone who comes across this post in future I thought I'd post my corrected self-replacing calc, which I deploy in relevant data entry fields—

                                     

                                    // PURPOSE: to format Name data in the standard format required

                                    // NOTE:

                                    // 1. result must (1) contain no extraneous spaces, (2) contain no carriage returns, and (3) be in Title case—unless intentional: see below

                                    // 2. this version incorporates a Case function ("formatted" variable) to allow any non-standard capitalisation initially or subsequently entered by the user. This calc was submitted to the FM Technet Forum by Phil Caulkins, aka PhilModJunk, aka Caulkins Consulting

                                    // 3. the result will still remove extra spaces and carriage returns but will accept any non-standard capitalisation

                                     

                                    Let (

                                    [

                                       rawData = Self

                                      ; stripped = TrimAll ( Substitute ( rawData ; "¶" ; " " ) ; 2 ; 2 )

                                      ; formatted = Case (

                                      Exact ( stripped ; Upper ( stripped ) )

                                      or

                                      Exact ( stripped ; Lower ( stripped ) ) ;

                                      Proper ( stripped ) ;

                                      stripped

                                      )

                                      ; result = formatted

                                    ] ;

                                    result

                                    )