Currency data type

[Clayton Chow]

OOB the mandatory precision and scale for Currency type [which we persist as decimal] is (19,4).

Using en.locale, shortCurrency formatter we display scale of 2 even though the database scale is 4. [In most cases this rounded off in UI]

 

In the advance filter when filtering with these attributes using equal operator the values returned will not be correct or it will not return value in most cases.

For example the Currency field might display ‘1010.32’ in UI whereas the value in database would be ‘1010.3196’.

 

The solution I suggested is to reduce the scale of these Currency type attributes at the db level if scale of 4 is not necessary. But unfortunately framework has a validation which makes the currency type to have a minimum precision and scale of (19,4).

Trying to reduce the scale fails the upgrade process (error below):

(expected minimal precision of 19 and scale of 4, or unspecified values). (err.meta.invalidCurrencyMapping)

 

My questions are as bellow,

a.       Is there a specific reason for having this validation of scale 4 on Currency type attributes

b.      If so, what would be the recommended work around for searching by these attributes in advanced filters or UDF ?

 

The one work around I can think of is to capture in the where clause whenever it contains these attributes and manipulate them.

(in the “processWhere” event, convert the clause to compare the input, which has 2 decimal places, to the rounded value in the db column)

 

0

[Clayton Chow]

Internal response below:

 

a.       Is there a specific reason for having this validation of scale 4 on Currency type attributes

“That was determined to be a best practice based on previous project implementations.  For display we may want to show only 2 decimal places, but to minimize round-off errors from calculations or conversions we want to store 4.”

 

b.      If so, what would be the recommended work around for searching by these attributes in advanced filters or UDF ?

“I would not change server-side code to accommodate UI behavior though.  Can we change the UI to query for a range instead of an exact value when dealing with number values?  I.e. trying to find a value that equals to 1.99 would be >= 1.985 and < 1.995.

However, Assuming it is a valid use-case and that the UI framework does not support converting the equality operator to a range, it would be possible to create an aspect that will perform rounded filtering on all decimal and floating type attributes of a class. “

 

0

[Author]

Posts