Pages

Friday, November 25, 2011

Crystals Length Function

I have seen the Length function that comes with Business Objects Crystal Reports used a lot in the standard Sage SalesLogix reports.  Mostly you see this function used in the formula fields created for displaying phone numbers in true false statements.
The first rule for this function is it works only with string fields so be prepared to convert if neccessary.  The syntax for this function is simple:
If Length {field} = number  then formula.
Here is an example of how the function is used from one of the many Customer FX Improved SalesLogix Reports.
If  Length ({ACCOUNT.MAINPHONE}) = 10 and {@Telephone_Country} = "Standard"
    Then
    "(" + {ACCOUNT.MAINPHONE}[1 to 3] + ") " + {ACCOUNT.MAINPHONE}[4 to 6] + "-" + {ACCOUNT.MAINPHONE}[7 to 10]
    Else
    If  Length ({ACCOUNT.MAINPHONE}) > 14 or Length ({ACCOUNT.MAINPHONE}) < 10
    Then
    {ACCOUNT.MAINPHONE}
    Else
    if Length ({ACCOUNT.MAINPHONE}) = 14 and "(" in {ACCOUNT.MAINPHONE}
    then
    "(" + {ACCOUNT.MAINPHONE}[2 to 4] + ") " + {ACCOUNT.MAINPHONE}[7 to 9] + "-" + {ACCOUNT.MAINPHONE}[11 to 14]
    else
    if Length ({ACCOUNT.MAINPHONE}) = 14
    then
    "(" + {ACCOUNT.MAINPHONE}[1 to 3] + ") " + {ACCOUNT.MAINPHONE}[4 to 6] + "-" + {ACCOUNT.MAINPHONE}[7 to 10] + " " + {ACCOUNT.MAINPHONE}[11 to 14]
    else
    {ACCOUNT.MAINPHONE}
FYI, this formula fields requires the use of another formula field called Telephone_Country which simply is looking at the Address.Country field to help determine the formatting for the phone number.
I hope everyone has great weekend.

No comments:

Post a Comment