x Want to participate? Join the group to interact
  • State Verified Answer
  • Replies 3 replies
  • Subscribers 137 subscribers
  • Views 1455 views
  • Users 0 members are here
Browse Forums
Announcements
237
topics
29
replies
General Discussion
5k
topics
13k
replies
Sage Data & Analytics
56
topics
95
replies
Sage Enterprise Intelligence
210
topics
416
replies
Sage Intelligence Reporting
121
topics
322
replies
Fabian Mueller

Phone formatting in Crystal Reports

SOLVED
Posted By

Does anyone have a suggestion on how to format the phone numbers based on the country's phone number format?

Since the table doesn't show the formatted entry (which really makes no sense), I need to create a formula in Crystal, but isn't there an easier way instead of including each country in the formula? This is where I don't understand Sage and why they don't put the format where the phone fields are.

Does anyone else have this problem?

Thanks

Fabian

Parents
  • +1
    verified answer

    I had the same issue- the problem is that the screen will display the proper format based on the setup in the country code however the table just retains the numbers with no format.  Below is formula I created in crystal so the forms/reports will display the phone number according to the format of the country.  I didn't know of any other way but you can copy this if it helps.

    if {PORDER.BPOCRY_0} = "AR" THEN "(+54)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "AT" THEN "(+43)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "AU" THEN "+61"+ " "+"("+LEFT({CONTACT.TEL_0},1)+ ")"+ MID({CONTACT.TEL_0},2,1)+ " "+ MID({CONTACT.TEL_0},3,4) + " " + RIGHT({CONTACT.TEL_0},4) ELSE
    if {PORDER.BPOCRY_0} = "BE" THEN "(+32)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "BR" THEN "+55"+"("+LEFT({CONTACT.TEL_0},2)+ ")"+ RIGHT({CONTACT.TEL_0},9) ELSE
    if {PORDER.BPOCRY_0}= "CA" THEN "(+1)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "CH" THEN "(+41)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "CN" THEN "(+86)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0}= "CZ" THEN "(+420)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "DE" THEN {CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "DK" THEN "(+45)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "ES" THEN "(+34)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "FI" THEN "(+358)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "FR" THEN "(+33)"+LEFT({CONTACT.TEL_0},1)+","+MID({CONTACT.TEL_0},2,2)+","+MID({CONTACT.TEL_0},4,2)+","+MID({CONTACT.TEL_0},6,2)+ ","+RIGHT({CONTACT.TEL_0},2) ELSE
    if {PORDER.BPOCRY_0} = "GB" THEN "+44"+ " "+"("+LEFT({CONTACT.TEL_0},1)+ ")"+ MID({CONTACT.TEL_0},2,4) + " "+ RIGHT({CONTACT.TEL_0},6) ELSE
    if {PORDER.BPOCRY_0} = "GR" THEN "(+30)"+{CONTACT.TEL_0}ELSE
    if {PORDER.BPOCRY_0} = "HU" THEN "(+36)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "IE" THEN "(+353)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "IL" THEN "(+972)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "IN" THEN "(+91)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "IT" THEN "(+39)"+{CONTACT.TEL_0} else
    if {PORDER.BPOCRY_0} = "JP" THEN "(+81)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "KR" THEN "(+82)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "LV" THEN "(+371)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "MX" THEN "(+52)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "MY" THEN "(+60)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "NL" THEN "(+31)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "NO" THEN "(+47)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "RU" THEN "(+7)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "MX" THEN "(+52)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "SE" THEN "(+46)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "SK" THEN "(+421)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "TH" THEN "(+66)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "UA" THEN "(+380)"+{CONTACT.TEL_0} ELSE
    IF {PORDER.BPOCRY_0} = "US" THEN Left ({CONTACT.TEL_0},3 )+"-"+Mid ({CONTACT.TEL_0},4 ,3 )+"-"+Right ({CONTACT.TEL_0},4 ) ELSE

    {CONTACT.TEL_0}

Reply
  • +1
    verified answer

    I had the same issue- the problem is that the screen will display the proper format based on the setup in the country code however the table just retains the numbers with no format.  Below is formula I created in crystal so the forms/reports will display the phone number according to the format of the country.  I didn't know of any other way but you can copy this if it helps.

    if {PORDER.BPOCRY_0} = "AR" THEN "(+54)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "AT" THEN "(+43)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "AU" THEN "+61"+ " "+"("+LEFT({CONTACT.TEL_0},1)+ ")"+ MID({CONTACT.TEL_0},2,1)+ " "+ MID({CONTACT.TEL_0},3,4) + " " + RIGHT({CONTACT.TEL_0},4) ELSE
    if {PORDER.BPOCRY_0} = "BE" THEN "(+32)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "BR" THEN "+55"+"("+LEFT({CONTACT.TEL_0},2)+ ")"+ RIGHT({CONTACT.TEL_0},9) ELSE
    if {PORDER.BPOCRY_0}= "CA" THEN "(+1)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "CH" THEN "(+41)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "CN" THEN "(+86)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0}= "CZ" THEN "(+420)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "DE" THEN {CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "DK" THEN "(+45)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "ES" THEN "(+34)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "FI" THEN "(+358)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "FR" THEN "(+33)"+LEFT({CONTACT.TEL_0},1)+","+MID({CONTACT.TEL_0},2,2)+","+MID({CONTACT.TEL_0},4,2)+","+MID({CONTACT.TEL_0},6,2)+ ","+RIGHT({CONTACT.TEL_0},2) ELSE
    if {PORDER.BPOCRY_0} = "GB" THEN "+44"+ " "+"("+LEFT({CONTACT.TEL_0},1)+ ")"+ MID({CONTACT.TEL_0},2,4) + " "+ RIGHT({CONTACT.TEL_0},6) ELSE
    if {PORDER.BPOCRY_0} = "GR" THEN "(+30)"+{CONTACT.TEL_0}ELSE
    if {PORDER.BPOCRY_0} = "HU" THEN "(+36)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "IE" THEN "(+353)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "IL" THEN "(+972)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "IN" THEN "(+91)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "IT" THEN "(+39)"+{CONTACT.TEL_0} else
    if {PORDER.BPOCRY_0} = "JP" THEN "(+81)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "KR" THEN "(+82)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "LV" THEN "(+371)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "MX" THEN "(+52)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "MY" THEN "(+60)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "NL" THEN "(+31)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "NO" THEN "(+47)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "RU" THEN "(+7)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "MX" THEN "(+52)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "SE" THEN "(+46)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "SK" THEN "(+421)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "TH" THEN "(+66)"+{CONTACT.TEL_0} ELSE
    if {PORDER.BPOCRY_0} = "UA" THEN "(+380)"+{CONTACT.TEL_0} ELSE
    IF {PORDER.BPOCRY_0} = "US" THEN Left ({CONTACT.TEL_0},3 )+"-"+Mid ({CONTACT.TEL_0},4 ,3 )+"-"+Right ({CONTACT.TEL_0},4 ) ELSE

    {CONTACT.TEL_0}

Children

*Community Hub is the new name for Sage City