Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook



The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic

Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.

If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.

Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.

Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

Phone number formatting in report.

  • Filter
  • Time
  • Show
Clear All
new posts

  • Phone number formatting in report.

    I'm wanting to take a phone number that's been entered as ########## in the table, and format it in the report to appear as (###) ####-####.

    I have the PHONE field listed as Character, 14 width, and have it setup with a mask of (000) 000-0000.

    The problem is, the report shows my phone numbers with no formatting at all.

    I went back to the Report Designer and under the FORMAT STYLE properties for my PHONE field, the only options it gave me were: uppercase, lowercase, first word, etc. type of formatting options.

    What am I doing wrong?

  • #2
    Re: Phone number formatting in report.

    You are not doing anything worng.
    Create a calc field to replace your phone field and use transform()


    • #3
      Re: Phone number formatting in report.

      What am I doing wrong here? I created a new calculated field, and for the expression entered in
      transform(PHONE,"(###) ###-####")

      If my original phone number was 1234567890, it should read as (123) 456-78920, but with the above transform command, I get (234) 567- and nothing else. It didn't include either the first number or the last 3.


      • #4
        Re: Phone number formatting in report.

        Use mask in the calculated field.

        ? mask("1234567890","(   )   -    ")
        = "(123)-456-7890"
        Three spaces between ( and ), three spaces between ) and -, four spaces at the end.

        If you want to use transform() there is an example in the help file suited to what you need.

        TRANSFORM(PHONE,"@R (999) 999-9999")
        Last edited by Stan Mathews; 02-17-2010, 03:23 PM.
        There can be only one.


        • #5
          Re: Phone number formatting in report.


          TRANSFORM(PHONE,"@R (999) 999-9999"


          • #6
            Re: Phone number formatting in report.

            The TRANSFORM function worked great. Now I've run into another problem.

            My report looks something like this:

            name1 , (123) 456-7890, ( ) - , (123) 456-7890
            name2 , ( ) - , ( ) - , (123) 456-7890

            How can I exclude the formatting from blank fields?


            • #7
              Re: Phone number formatting in report.

              Originally posted by Parish Mills View Post
              The TRANSFORM function worked great. Now I've run into another problem.

              My report looks something like this:

              EMPLOYEE, CELL 1, CELL 2, HOME PHONE
              name1 , (123) 456-7890, ( ) - , (123) 456-7890
              name2 , ( ) - , ( ) - , (123) 456-7890

              How can I exclude the formatting from blank fields?
              Al Buchholz
              Bookwood Systems, LTD
              Weekly QReportBuilder Webinars Thursday 1 pm CST

              Occam's Razor - KISS
              Normalize till it hurts - De-normalize till it works.
              Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
              When we triage a problem it is much easier to read sample systems than to read a mind.
              "Make it as simple as possible, but not simpler."
              Albert Einstein



              • #8
                Re: Phone number formatting in report.

                No if()'s and's or but's:
                ?TRANSFORM(x,"@R (aaa)aaa-aaaa")
                = "(123)456-7890"
                ?TRANSFORM(x,"@R (aaa)aaa-aaaa")
                = ""
                This worked once.. now not.

                ?? now it's working again. I am not sure what I did in the IW to P it O
                Last edited by G Gabriel; 02-17-2010, 04:59 PM.


                • #9
                  Re: Phone number formatting in report.

                  How would I implement the IF() statement with the following?

                  transform(Emp_Cellph,"@R (999) 999-9999")


                  • #10
                    Re: Phone number formatting in report.

                    if(alltrim(Emp_Cellph)>"",transform(Emp_Cellph,"@R (999) 999-9999"),"")
                    There can be only one.


                    • #11
                      Re: Phone number formatting in report.

                      Report is done. Thanks to all of you for your help. Too bad this is one of the easier reports I'll have to do. This multi-tasking I'm having to do is making it difficult to learn this program. If I can ever take advantage of the program, I know it will in turn help me with the multi-tasking.


                      • #12
                        Re: Phone number formatting in report.

                        Don't feel bad. Most of us started out doing a lot of multi-tasking - then finding out later on how to avoid it. As you get more experience you find out which methods/structures work well for you and which don't. And, I'm not talking about "which A5 features work and which don't" -- I'm talking about "which of my techniques as a developer work well for me and which don't." I don't think there is any real shortcut to it other than experience.

                        My method of handling dates is different than most but it works very well for me. In the CanWrite event of every date field I call my Phone_dash() function. This function reformats any US phone number regardless of how the user enters it or what format it was pasted in with. It removes any leading "1" if it appears to be the US country code then saves it as either xxx-xxxx or xxx-xxx-xxxx. And, if it begins with a zero it assumes it's a foreign country code and does not change the format. This method has a lot of advantages as far as I'm concerned. (Otherwise I wouldn't be using it!)
                        - It handles numbers with and without an area code
                        - It forces consistency
                        - No bother when displaying in reports and forms
                        - Custom lookouts can also use the Phone_dash() function so the users can enter the number any way they want to when running my custom searches. (A simple Ctrl-K find still requires them to enter it in the correct format but I often end up creating custom searches in order to make things easier for the user.)

                        Here are some results from the Interactive Window.

                        ?Phone_dash( "123456" )
                        = "123456"
                        ?Phone_dash( "1234567" )
                        = "123-4567"
                        ?Phone_dash( "12345678" )
                        = "123-456-78"
                        ?Phone_dash( "123456789" )
                        = "123-456-789"
                        ?Phone_dash( "1234567891" )
                        = "123-456-7891"
                        ?Phone_dash( "(123) 456-7891")
                        = "123-456-7891"
                        'Note that this removes the leading 1 because there
                        'are now 11 characters and the "1" seems to be the US
                        'country code. It did not remove it above because of
                        'some special demo situations I've had where there
                        'actually was a "1" at the beginning of the demo data.
                        ?Phone_dash( "12345678911" )
                        = "234-567-8911"
                        'Here it's long enough that it shouldn't be a US country
                        'code so the leading "1" is not removed.
                        ?Phone_dash( "123456789012" )
                        = "123-456-789012"
                        'If it begins with "0" it should be an international
                        'number so don't change it.
                        ?Phone_dash( "0123-45(67)8-911-1234" )
                        = "0123-45(67)8-911-1234"

                        Here's a video of it in action with a custom Find operation.

                        Here's the code. Obviously it can be altered to change the resulting format - if you really like to see parentheses around your area code, change the script as required.

                        'Date Created: 03-Feb-2003 08:47:44 PM
                        'Last Updated: 05-Jan-2009 02:53:13 PM
                        'Created By  : Cal
                        'Updated By  : Cal Locklin
                        FUNCTION Phone_dash AS C (Phone_in AS C )
                        'Description:Adds dashes to phone numbers as necessary and removes leading "1"s.  Assumes 333-444-1234 output format will always be valid.
                            DIM phone_w as C
                            phone_w = remspecial(phone_in)    'Define the 'working' number.
                            '2009-01-05 Since any normal phone number should have at least 7 digits, leave anything with fewer digits as-is.
                            IF len(trim( Phone_in )) < 7
                                Phone_dash = phone_w
                                EXIT FUNCTION 
                            END IF 
                            'Assume any number beginning with 01 is international and leave it as-is.
                            IF left(phone_w,2)="01"
                                Phone_dash = Phone_in
                                EXIT FUNCTION
                            END IF
                            'Remove any leading 1 that might be for long distance.
                            'Left the ability to start an area code or exchange with "1" because of a
                            '   conversion routine I use to "encrypt" phone numbers. (Although, it would be
                            '   better to change the "encryption" too so all beginning 1s becomes 2s...)
                            IF left(phone_w,1)="1" .and. len(phone_w)=11
                                phone_w = substr(phone_w,2)
                            END IF
                            phone_w = stuff( phone_w, 4, 0, "-" )
                            IF len(trim(phone_w))>8
                                phone_w = stuff( phone_w, 8, 0, "-" )
                            END IF
                            Phone_dash = phone_w
                        END FUNCTION


                        • #13
                          Re: Phone number formatting in report.

                          Don't like mask() or transform()?
                          Do your own:
                          if(phone="","","("+left(phone,3)+") "+substr(phone,4,3)+"-"+right(phone,4))


                          • #14
                            Re: Phone number formatting in report.

                            Oops! Don't know what I was thinking. Someone pointed out to me that this:

                            My method of handling dates is different than most but it works very well for me. In the CanWrite event of every date field I call...:o

                            should be this:

                            My method of handling phone numbers is different than most but it works very well for me. In the CanWrite event of every phone number field I call...:D

                            On the other hand, I suspect anyone who couldn't figure that out for themselves wouldn't be posting on this board.