8 Replies Latest reply on Jan 17, 2010 8:22 PM by TimG

    Newbie Having difficulty following instructions

    TimG

      Title

      Newbie Having difficulty following instructions

      Post

      Hi everyone:

       

      I am trying to format my phone numbers so that is looks like this. (###) ###-####

       

      Here are the directions I am trying to follow. Answer #3516

      Here is the url. 

      http://filemaker.custhelp.com/cgi-bin/filemaker.cfg/php/enduser/std_adp.php?p_faqid=3516&p_created=1115172726&p_sid=fa7DaeSj&p_accessibility=0&p_redirect=&p_lva=&p_sp=cF9zcmNoPTEmcF9zb3J0X2J5PSZwX2dyaWRzb3J0PSZwX3Jvd19jbnQ9MTgsMTgmcF9wcm9kcz04NjQmcF9jYXRzPSZwX3B2PTEuODY0JnBfY3Y9JnBfcHNfYW5zX3VwZGF0ZWQ9JnBfcGFnZT0xJnBfc2VhcmNoX3RleHQ9Zm9ybWF0IHBob25l&p_li=&p_topview=1

       

      Here are the instructions.

       

      Data entry into certain types of fields can be significantly automated by using field filters to supply standardized formatting automatically. Two examples of such fields are phone number and social security fields. With a phone number entry, the parenthesis around the area code and the hyphen between the first three and last  four digits of the phone number can be automatically supplied. In the case of a social security number, hyphens are typically used in the following standard format: 123-45-6789.

      Create a calculation field that supplies the formatting, place the calculation field directly over the data entry field, and set the calculation field so that entry is not allowed using the Field Format dialog. This combination of fields allows data entry either by tabbing or clicking into the field, and shows the formatting as soon as the next data entry field is tabbed to.

      For example, let's create a phone number field filter.  (A ^ in any of the following formulae denotes a space.)

      1. Create two fields:
      PhoneNumber, a text field.
      PhoneNumberFilter, a calculation field resulting in text with the formula, "(" & left(PhoneNumber,3) & ")^ " & middle(PhoneNumber,4,3) & "-" & right(PhoneNumber,4)

      2. Place the PhoneNumber field on a data entry layout. Make sure the option to allow entry into field in the Field Format dialog is checked.

      3. Place the PhoneNumberFilter field on the layout. Make sure the option to allow entry into field in the Field Format dialog is NOT checked and that the fill color is set to white. Move the field so that is exactly on top of the PhoneNumber field.

      4. Return to Browse mode, try creating entering or changing some phone numbers. Note that clicking into the field is just like clicking into a standard data entry field. Note that the formatting is automatically supplied as soon as the field is exited.

      5. Remember that any report layouts that use the phone number field should actually use the PhoneNumberFilter field.

      In all current versions of FileMaker, the filtered text will not appear until after clicking out of the text entry field to commit the data entry

       

      Problems I am encountering.

      When I follow these instructions I get a blank box that does not mask the phone numbers. Instead the phone numbers are now hidde3n and they will quickly show for a second when cycling thru records, however then it is covered by the PhoneNumberFilter field which I created and placed over the PhoneNumber fiel.

       

      I think my problem is I am not following these directions accurately. Here are the issues.

      2.  Place the PhoneNumber field on a data entry layout. Make sure the option to allow entry into field in the Field Format dialog is checked. I amassuming this is in the Manage > Database options for PhoneNumber and I would check "Allow user to override data entry. I think I have this wrong.

      3.  Place the PhoneNumberFilter field on the layout. Make sure the option to allow entry into field in the Field Format dialog is NOT checked and that the fill color is set to white. Move the field so that is exactly on top of the PhoneNumber field.

       

      Again I am assuming this is in Manage > Database options for the PhoneNumberFilter field.

       

      Everything else I seem to be okay on. I am new to this and have read "The Missing Manual" as well as the real manual and watched most of the videos but I am still having trouble with this. Can anyone help please.

       

      Thank you.

       

      Tim G.

        • 1. Re: Newbie Having difficulty following instructions
          comment_1
            

          TimG wrote:
          Make sure the option to allow entry into field in the Field Format

          dialog is checked. I amassuming this is in the Manage > Database options for PhoneNumber and I would check "Allow user to override data entry. I think I have this wrong.


          Yes, you are right - you are wrong. Allowing entry is done on the layout level: in Layout mode, select Format > Field/Control > Behavior…

           

          However, the advice you are following has been obsolete since version 7. A single field, with auto-entered calculation (replacing existing value) will work just fine.


          • 2. Re: Newbie Having difficulty following instructions
            RickWhitelaw
              

            Here's a function (custom) called Phone Format. The variable is "input" and should be "Self" in your field definition. So, if you're saving it as a custom function, your auto-enter by calculation would be Phone Format(self). So the field is a text field with auto-enter by calculation, and allows the replacement of existing data.

             

            Case(  Length ( Filter (input ; "0123456789" ) )   < 10  and Left (input; 1 ) ≠ "+"; "Enter 10 digits!";Length ( Filter (input; "0123456789" ) )   = 10; "(" &  Left ( Filter ( input; "0123456789" ) ; 3 ) & ") " & Middle ( Filter ( input; "0123456789" ) ; 4 ; 3 ) &"-" & Middle ( Filter ( input;"0123456789" ) ; 7 ; 4 );input )

             

            This function won't impose the format on international (beginning with "+" because almost any number of digits is common) but will prompt the user to enter 10 digits if it's not an international "call". 

             

             

            • 3. Re: Newbie Having difficulty following instructions
              TimG
                

              Am I correct that I cannot create a custom function unless I have FileMakerPro advanced.

               

              If not, can you give me any hints as to how to start creating a custom function in FileMaker Pro 10.

               

              Thank you, I am continuing to research functions right now.

               

              • 4. Re: Newbie Having difficulty following instructions
                RickWhitelaw
                  

                You can't define custom functions without Advanced. You can still use the calculation however. Just assign it to your phone number field, and wherever you see "input" put "Self" instead. All this means is that you'll have

                to enter the calculation for each field using it. The custom Function is kind of a signpost or shortcut, but you don't have to do it that way. Oh . . . the field type should be text and this should be an auto-enter by calculation with Do not replace existing values . . . should be un-checked. 

                • 5. Re: Newbie Having difficulty following instructions
                  TimG
                    

                  Okay I think I am geting this.

                   

                  I did have one hang up. Here is the modified string.

                   

                  Case(  Length ( Filter (Self ; "0123456789" ) )   < 10  and Left (Self; 1 ) ? "+"; "Enter 10 digits!";Length ( Filter (Self; "0123456789" ) )   = 10; "(" &  Left ( Filter ( Self; "0123456789" ) ; 3 ) & ") " & Middle ( Filter ( Self; "0123456789" ) ; 4 ; 3 ) &"-" & Middle ( Filter ( Self;"0123456789" ) ; 7 ; 4 );Self )

                   

                  It is choking on the ?. It is telling me the specified field cannot be found.

                   

                  Should the ? be there?

                   

                  Thanks a bunch Rick. This is another big step for me.

                  • 6. Re: Newbie Having difficulty following instructions
                    TimG
                      

                    Is there a way to get the database to re format the existing records that has the wrong format for the date now. I got another simplier routine to work (although I am going to get this one to work too). And if I go back into the field and change anything, the field will update, but only if I change something. Is there a way to get the field to update automatically.

                     

                    I unchecked "Do not replace vale for dield (if any.

                     

                    Thanks.

                    • 7. Re: Newbie Having difficulty following instructions
                      RickWhitelaw
                        

                      You have a question mark in the formula. It should be the symbol for "not equal to" as I originally posted. If it's not copying properly go into the field definition, highlight the "?" and click on the proper symbol. This will replace it.

                       

                      Case(  Length ( Filter (Self ; "0123456789" ) )   < 10  and Left (Self; 1 ) ? "+"; "Enter 10 digits!";Length ( Filter (Self; "0123456789" ) )   = 10; "(" &  Left ( Filter ( Self; "0123456789" ) ; 3 ) & ") " & Middle ( Filter ( Self; "0123456789" ) ; 4 ; 3 ) &"-" & Middle ( Filter ( Self;"0123456789" ) ; 7 ; 4 );Self )

                        

                      • 8. Re: Newbie Having difficulty following instructions
                        TimG
                          

                        That worked great. I guess I did not look close enough when I copied it. Everything is working fine now except getting the phone field to update all the records.

                         

                        Thanks again. That was fun.