[SalesForce] Number Type, Decimal Points Confusion

We have a number typed field say Min_Distance__c where we have defined, Length=1 and Decimal places=2. But we experienced that salesforce allows to save numbers with more than 2 decimal places.

In pages, it shows the correct rounded values while queries or remote calls give the actual value(all the decimal places). Can someone confirm what is the behavior we should expect?

My thought is even in setup we define the decimal places, that is only for showing in visualforce pages. It saves whatever the decimal points it receives.

Best Answer

Only enforced in the UI (to maintain backward compatability). Apex can store up to 24 decimal places:

http://help.salesforce.com/HTViewSolution?id=000212661

Define a custom number field, say, "Number." Give it length = 3, and decimal places = 1. It might seem that this is done to restrict the precision of the field to two decimal places. However, on the UI level (on a standard edit page), if you try to type in, say, 237.631, it'll round off 237.631 when you click "Save" and store it in the database as 237.6 But if you then set a value via API, say 237.631, it'll be stored in the database - and returned via API (e.g. SOQL query) - as 237.631. In fact , SFDC will let you store up to 24 decimal points via API, regardless of the decimal places provided in the field definition. So you could save 237.123456789012345678901234.

This is completely working as designed and we kept the behavior so that it can be backward compatible.

Related Solutions

[SalesForce] Apex – format number with more than 3 digits after decimal point

I've logged an Idea for this issue to ask Salesforce for solving this.

Please also find the following 'work-a-round' code that I've written for my current project to make this failure work either way.

/**
 * Function to print a number String or Decimal into the local Currency
 * Format() only returns up till an accuracy of 3 digits, causing a potential issue
 */
public static String formatCurrency( Object o, Integer numberOfDecimals ) {
    // Construct separators based on UserLocale (Decimal.format() is locale sensitive)
    Decimal value           = 1000.10;
    String formattedValue   = value.format();
    String thousandChar     = formattedValue.substring(1,2);
    String decimalChar      = formattedValue.substring(5,6);

    // fetch input number
    Decimal decVal = 0;
    if      ( o == null )            decVal = 0;
    else if ( o instanceof Decimal ) decVal = ( Decimal ) o;
    else if ( o instanceof String )  decVal = Decimal.valueOf( String.valueOf( o ) );

    // apply rounding or add additional 0 decimals
    decVal = decVal.setScale( numberOfDecimals, RoundingMode.CEILING );

    // use format() to print the Integer including thousand separators in user locale
    // Note: only prints up to an accuracy of 3 and does not print 'ending zeros'
    String formattedString = decVal.format();

    // fetch full decimals string
    String originalNumber = decVal.ToPlainString(); // prints with System separators 
    String decimalString  = originalNumber.substringAfter('.');

    // fetch the integer string (before decimal separator), containing thousand separators
    String integerString  = formattedString.substringBefore( decimalChar );

    // concatenate those together
    return integerString + decimalChar + decimalString;
}

Hope this can help you and please upvote the Idea :)

[SalesForce] Format decimal number with thousand separator with a dot (.)

Try using apex:outputField, it would do the formatting for you based on your locale.

Unfortunately its not possible with apex:outputText and apex:param. Here is a similar idea.

There was a comment in the idea which could be helpful but would need bit of modification as per requirement:

Option 1: Standard

<apex:outputText value="{0, number, ###,##0.00}">
    <apex:param value="{!Opportunity.Amount}"/>
</apex:outputText> </span>

result : 100,000.00

Option 2: Custom formulas

<span>
    {!IF(Opportunity.Amount < 0, 
        "-", 
        "") & 
        IF(ABS(Opportunity.Amount) >= 1000000, 
            TEXT(FLOOR(ABS(Opportunity.Amount) / 1000000)) & ".", 
            "") & 
        IF(ABS(Opportunity.Amount) >= 1000, 
            RIGHT(TEXT(FLOOR(ABS(Opportunity.Amount) / 1000)), 3) & ".", 
            "") & 
        RIGHT(TEXT(FLOOR(ABS(Opportunity.Amount))), 3) & "," & 
        IF(MOD(ABS(Opportunity.Amount) , 1) * 100 < 10, 
            "0" & TEXT(ROUND(MOD(ABS(Opportunity.Amount), 1), 2) * 100), 
            TEXT(MIN(ROUND(MOD(ABS(Opportunity.Amount) , 1), 2) * 100, 99))
        )}
</span>

Result: 100.000,00

Best Answer

Related Solutions

[SalesForce] Apex – format number with more than 3 digits after decimal point

[SalesForce] Format decimal number with thousand separator with a dot (.)

Related Topic