0

I am trying to create a validation rule for currency field which will take the maximum 2 decimal places only if you put the third decimal value it will should give you an error.

My currency field is having 16 numbers and 2 decimal places.

For e.g

Valid number - 12, 112.23,14.01,15.00 Invalid Number - 12.23455

Please find the below regular Expression which i am using in the validation rule

NOT(REGEX(TEXT(Amount__c), "[0-9]+[.][0-9][0-9]?"))

Thanks,

2 Answers 2

2

Just do this:

Amount__c <> ROUND(Amount__c, 2)

If there's a difference, this formula returns an error.

9
  • Is this even needed at all? Doesn't the field definition already constrain the values to 2 decimal places (I don't think rounding takes place, I think any extra decimal places given by a user are just straight trimmed out, even when updating from Apex)?
    – Derek F
    Jul 7, 2020 at 18:16
  • @DerekF Hilariously enough, no. The display is truncated to 2 digits, but you can still store many more than 2. I ran in to this problem once when calculating discount percentages in a custom app we'd build for the AppExchange (in a previous life). It's important that you either restrict the input via a validation rule, or fix it in a trigger/process/etc.
    – sfdcfox
    Jul 7, 2020 at 18:22
  • There are currencies that require 3 or even 4 decimal places.
    – Phil W
    Jul 7, 2020 at 18:41
  • @PhilW It's not even that, all number fields can store more than their designated decimal units. Never assume that what you see on the screen is what is actually stored in the database.
    – sfdcfox
    Jul 7, 2020 at 18:56
  • Ah, OK, I didn't realize that was what you were getting at. I hadn't spotted that issue myself, TBH, and will have a look see. Still, there are currencies that use more than 2 decimals :)
    – Phil W
    Jul 7, 2020 at 19:22
0

You can try the below regular expression.

NOT(REGEX(TEXT(Amount__c), "[0-9]+\.[0-9][0-9]?"))

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .