2 Replies Latest reply on Sep 11, 2013 10:52 AM by shae1725

    format field help  123123123  to 123_123_123



      format field help  123123123  to 123_123_123


           how do I format data already in a field

           123123 becomes 123_123_

           123123123 becomes 123_123_123

           12312 becomes 123_12_

        • 1. Re: format field help  123123123  to 123_123_123

                              IF IT WAS A PHONE NUMBER

                              Try the following as an auto-entered calculated value with the "do not replace existing value..." option cleared:
                              Let (ph = GetasNumber(Phonenumber);

                                     TextFormatRemove(Case (Length(ph) = 7; Left (ph; 3) & "-" & right(ph;4);

                                                                        Length(ph) = 10; left(ph; 3) & "-" & middle (ph; 4; 3) & "-" Right (ph; 4);


                              PhoneNumber is the name of your text field (If you are using a recent version of FMP, you can replace it with "Self") to which you've set up this auto-enter option.
                              GetasNumber strips out any non numeric characters that get entered.
                              TextFormatRemove eliminates any undesirable text styles that might otherwise get pasted into the text field along with the number.
                              If the number of digits in your phone number isn't 7 or 10, the expression will simply return the original text, but with any pasted text styles removed.

                              MORE STEP BY STEP

                              Open manage | Database | fields from the File menu.

                              Find your text field
                              Double click it to open the Field Options Dialog.
                              Click the auto-enter tab in this dialog.
                              Click the "by calculation" option to open the specify calculation dialog.
                              Enter: TextFormatRemove ( Self )

                              Let(xyz =  left(xyz;3) & "_" & middle(xyz;4;3) & "-" & middle(xyz;7;3))

                              [Example: If xyz contains 408555121 this calculation returns 408_555_121]

                              And click OK. ("Self" is a special name you can use here so that the expression automatically refers to your field without needing to enter the actual field name in place of self. This makes it possible to set up utility expressions like this one and copy and paste them into multiple fields without having to edit them to add the specific field name.)

                              On the auto-enter tab, you'll find a check box for "do not replace existing value..." clear this check box. Since this calculation replaces the data entered by the user with the modified (format removed) data, it won't do anything unless this option is cleared.


          • 2. Re: format field help  123123123  to 123_123_123

                 Thanks I can see how this works but as im a novice

                 can you show me how to compile your instructions?

                 Im assuming xyz is the field containing the original data

                 TextFormatRemove ( Self )

                 Let(xyz = left(xyz;3) & "_" & middle(xyz;4;3) & "-" & middle(xyz;7;3))