Why do I see exponential values for custom number field?
|Knowledge Article Number||000221617|
|Description||When querying large value custom number fields through the API, you will notice the values are returned with scientific notation. This is not just through SOAP API, but Rest API or SOQL as well.
For example if you see value 1234567 through the UI, when querying through API - you will see 1234567.0
However if you see value 12345678 through the UI, when querying through the API - you will see 1.2345678E7
When the custom number field has values up to 7 digits (such as 1234567), the API values returned are normal(1234567.0). But for custom number field with digits 8 - 14 (such as 12345678), API values returned will be exponential values (1.2345678E7).
This is actually Working as Design.
Custom Number fields are stored as Double values in our database. Large values are stored in scientific notation as that stated in XML standards. Due to this, our queries return scientific values rather than plain numbers.
|Resolution||If you review the SOAP API documentation, page 31 states the following
Double values: Fields of this type can contain fractional portions (digits to the right of the decimal place),such as ConversionRate in CurrencyType. In the API, all non-integer values (such as Currency Field double Type and Percent Field Type) contain values of type double. Some restrictions may be applied to double
• scale: Maximum number of digits to the right of the decimal place.
• precision: Total number of digits, including those to the left and the right of the decimal place
The maximum number of digits to the left of the decimal place is equal to precision minus scale. In the online application, precision is defined differently—it is the maximum number of digits allowed to the left of the decimal place.
Values can be stored in scientific notation if the number is large enough (or, for negative numbers, small enough), as indicated by the W3C XML Schema Part 2: Datatypes Second Edition specification.
http://www.w3.org/TR/xmlschema-2/#double - XML Standards, Primitive Datatypes, Double-
22.214.171.124 Lexical representation
Double values have a lexical representation consisting of a mantissa followed, optionally, by the character "E" or "e", followed by an exponent. The exponent ·must· be an integer. The mantissa must be a decimal number. The representations for exponent and mantissa must follow the lexical rules for integer and decimal. If the "E" or "e" and the following exponent are omitted, an exponent value of 0 is assumed. The special values positive and negative infinity and not-a-number have lexical representations INF, -INF and NaN, respectively. Lexical representations for zero may take a positive or negative sign. For example, -1E4, 1267.43233E12, 12.78e-2, 12 , -0, 0 and INF are all legal literals for double.
If you would like to change this behavior, there is an existing Salesforce Idea to have this implementation changed:
Voting for this idea will help to change the current behavior and not follow the XML standard, but to follow the customer expectation of the API returned value reflecting the value displayed in the UI.