Skip to content

Format Strings in CRM and fn_GetNumberFormatString surrogate

October 19, 2011

CurrenciesIf you need to format numbers in plugins in the same way that CRM does, you will likely benefit of the code below. This is especially useful for Currencies, since although CRM calculates the format of currency fields based on the user or system settings, there is no direct method to get the format from the API.

What you will find below is a direct re-implementation of the fn_GetNumberFormatString SQL function that CRM uses to render the number format in Filtered Views. The input parameters are quite self explanatory, in your code you can retrieve them from the user settings or the organization settings, depending on whether you are displaying user or system wide information.

public static string GetNumberFormatString(int precision, string numberGroupFormat,
    int negativeFormatCode, bool isCurrency, string currencySymbol = "",
    int currencyformatcode = 0)
{
    string precisionString = string.Empty;
    switch (precision)
    {
        case 1:
            precisionString = ".0";
            break;
        case 2:
            precisionString = ".00";
            break;
        case 3:
            precisionString = ".000";
            break;
        case 4:
            precisionString = ".0000";
            break;
        case 5:
            precisionString = ".00000";
            break;
    }

    string positiveNumberFormat = null;
    switch (numberGroupFormat)
    {
        case "3,0":
            positiveNumberFormat = "##########,##0" + precisionString;
            break;
        case "3,2":
            positiveNumberFormat = "##,##,##,##,##,##0" + precisionString;
            break;
        default:
            positiveNumberFormat = "###,###,###,##0" + precisionString;
            break;
    }

    string zeroNumberFormat = "0" + precisionString;

    string negativeNumberFormat = null;
    if (isCurrency)
    {
        switch (negativeFormatCode)
        {
            case 0:
                negativeNumberFormat = "(\"" + currencySymbol + "\"" + (char)8203 + positiveNumberFormat + ")";
                break;
            case 1:
                negativeNumberFormat = "-\"" + currencySymbol + "\"" + (char)8203 + positiveNumberFormat;
                break;
            case 2:
                negativeNumberFormat = "\"" + currencySymbol + "\"-" + positiveNumberFormat;
                break;
            case 3:
                negativeNumberFormat = "\"" + currencySymbol + "\"" + (char)8203 + positiveNumberFormat + "-";
                break;
            case 4:
                negativeNumberFormat = "(" + positiveNumberFormat + (char)8203 + "\"" + currencySymbol + "\")";
                break;
            case 5:
                negativeNumberFormat = "-" + positiveNumberFormat + (char)8203 + "\"" + currencySymbol + "\"";
                break;
            case 6:
                negativeNumberFormat = positiveNumberFormat + "-\"" + currencySymbol + "\"";
                break;
            case 7:
                negativeNumberFormat = positiveNumberFormat + (char)8203 + "\"" + currencySymbol + "\"-";
                break;
            case 8:
                negativeNumberFormat = "-" + positiveNumberFormat + (char)160 + "\"" + currencySymbol + "\"";
                break;
            case 9:
                negativeNumberFormat = "-\"" + currencySymbol + "\"" + (char)160 + positiveNumberFormat;
                break;
            case 10:
                negativeNumberFormat = positiveNumberFormat + (char)160 + "\"" + currencySymbol + "\"-";
                break;
            case 11:
                negativeNumberFormat = "\"" + currencySymbol + "\"" + (char)16 + positiveNumberFormat + "-";
                break;
            case 12:
                negativeNumberFormat = "\"" + currencySymbol + "\" -" + positiveNumberFormat;
                break;
            case 13:
                negativeNumberFormat = positiveNumberFormat + "- \"" + currencySymbol + "\"";
                break;
            case 14:
                negativeNumberFormat = "(\"" + currencySymbol + "\"" + (char)160 + positiveNumberFormat + ")";
                break;
            case 15:
                negativeNumberFormat = "(" + positiveNumberFormat + (char)160 + "\"" + currencySymbol + "\")";
                break;
            default:
                negativeNumberFormat = "(\"" + currencySymbol + "\"" + (char)8203 + positiveNumberFormat + ")";
                break;
        }

        switch (currencyformatcode)
        {
            case 0:
                positiveNumberFormat = "\"" + currencySymbol + "\"" + (char)8203 + positiveNumberFormat;
                zeroNumberFormat = "\"" + currencySymbol + "\"" + (char)8203 + zeroNumberFormat;
                break;
            case 1:
                positiveNumberFormat = positiveNumberFormat + (char)8203 + "\"" + currencySymbol + "\"";
                zeroNumberFormat = zeroNumberFormat + (char)8203 + "\"" + currencySymbol + "\"";
                break;
            case 2:
                positiveNumberFormat = "\"" + currencySymbol + "\"" + (char)160 + positiveNumberFormat;
                zeroNumberFormat = "\"" + currencySymbol + "\"" + (char)160 + zeroNumberFormat;
                break;
            case 3:
                positiveNumberFormat = positiveNumberFormat + (char)160 + "\"" + currencySymbol + "\"";
                zeroNumberFormat = zeroNumberFormat + (char)160 + "\"" + currencySymbol + "\"";
                break;
            default:
                positiveNumberFormat = "\"" + currencySymbol + "\"" + positiveNumberFormat;
                zeroNumberFormat = "\"" + currencySymbol + "\"" + zeroNumberFormat;
                break;
        }
    }
    else
    {
        switch (negativeFormatCode)
        {
            case 0:
                negativeNumberFormat = "(" + positiveNumberFormat + ")";
                break;
            case 1:
                negativeNumberFormat = "-" + positiveNumberFormat;
                break;
            case 2:
                negativeNumberFormat = "-" + (char)160 + positiveNumberFormat;
                break;
            case 3:
                negativeNumberFormat = positiveNumberFormat + "-";
                break;
            case 4:
                negativeNumberFormat = positiveNumberFormat + (char)160 + "-";
                break;
            default:
                negativeNumberFormat = "(" + positiveNumberFormat + ")";
                break;
        }
    }

    return positiveNumberFormat + ";" + negativeNumberFormat + ";" + zeroNumberFormat;
}

Happy coding!

Alberto “Form-over-function” Gemin

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: