Currency type field is rounding off its value | Community
Skip to main content
KCS_Integration
KCS_Integration
Level 2
May 9, 2018

Currency type field is rounding off its value

  • May 9, 2018
  • 2 replies
  • 2306 views

Issue Description

Currency type fields are changing unexpectedly. It may appear that they are being rounded off at first glance, but a better description is that they are being approximated imprecisely. This is a feature of all floating point values.

For example: if you enter the value as 8092384905, Marketo will truncate it to 8092390000.

 

Issue Resolution

This is an expected behavior. Currency type fields are stored as single precision float values which have 6-7 digits of significance. Since standard fields type cannot be changed, we suggest that you create a new custom field to hold the currency value.

Human-readable Values: Strings

Often the easiest work-around for larger currency values is to use a field of type "String". String fields are adequate for storing a value, but do not offer the capability to compare (between, greater than, less than, at least, at most) values.

 

Comparable Values: Integers

To preserve comparison capability, store the value as an integer which can take on values from -2147483647 to 2147483647. For US Dollars, this integer can represent the number of cents (pennies) and therefore currency values from ($21,474,836.47) to $21,474,836.47. Using this technique, the operators between, greater than, less than, at least, and at most will work, just remember you're what you're comparing: cents in this example.

Even Larger Comparable Values: Compound Integers

If even larger values are required, two integer fields (large and small) can be used to represent currencies (again, assuming cents) from ($46,116,860,141,324,206.00) to $46,116,860,141,324,206.00. To break up a currency value into two fields, divide the number of cents by 2147483647 and store in the large value field, then store the remainder (modulus) in the small value field. To compare a compound (two fields) value, compare the large values first and if they are equal, then compare the small values. Since compound fields will be hard for a human to read, it may be prudent to also store the value as a string depending on your requirements.

Is this article helpful ?

YesNo

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

2 replies

SanfordWhiteman
SanfordWhiteman
Level 10
May 10, 2018

For example, if you enter the value as 8092384905, Marketo is automatically rounding it to 809239000

Actually, if you enter 8,092,384,905 the stored value would be 8,092,380,200.

The problem with this doc is it makes it seem like the "rounding" is like what humans do with (decimal) dollar values. But it's not, it's float approximation, which does not abide by any familiar human rules. So seemingly already-rounded numbers are not stored in their original form, but may be changed to something that can look completely out of whack.

For example, if you enter

     8,092,380,000

then the stored value is

     8,092,380,200

That's same value stored when you enter 8,092,384,905, only in this case 200 dollars are materializing out of nowhere. It makes the value seem more "precise" and yet clearly more wrong.

There's also a difference between the value as displayed in the Marketo UI and the value in the database (i.e. the value that would be output in an email).

It's really quite a messy situation. I think the recommendation should be don't use Currency fields, ever, as they are implemented incorrectly. Currency should've used an exact datatype, not a float.

SanfordWhiteman
SanfordWhiteman
Level 10
November 20, 2019

Lower bound of Int32 is -2147483648.

Terms & ConditionsAccessibility statement

Loading footer...