How are stored percentage fields ? They don't always return the same value

Question

I have a field of type Percent(3,2) named Sector__c.VatRate__c. I initialized it with anonymous apex withe values like 5.5, 10, 20, etc. The code looks like that :

sec.VatRate__c = 10;
update sec;

Now I have a formula field on Opportunity named AmountWithTax__c, the formula is :

AmountWithoutTax__c * ( 1 + VatRate__c /100 )

I expected, for a record having AmountWithoutTax__c set to 200, a result of 220, but I get 200.2.

Changing the formula to

AmountWithoutTax__c * ( 1 + VatRate__c )

gives me the expected result.

Trailhead states this :

A Percent is stored as a number divided by 100, meaning that 100% is represented as 1, 90% as 0.9, and so on.

So everything so far is ok.

But if I query the Sector object, the value returned for VatRate__c is 10, not 0.1. Worse, if I execute this code :

Sector__c[] sec = [Select VatRate__c from Sector__c where VatRate__c = 10];
Double d = 200.00;
System.debug(sec[0].VatRate__c);
System.debug(d * (1 + sec[0].VatRate__c) );

I get 10.00 and 2200.

That looks not consistent to me, I expect an Apex code to return the same value as a matching formula...

Also the trailhead statement looks inaccurate, the percentage value seems to be stored as 90 for 90%, not as 0.9.

I tried to re-set the percentage value from the interface rather than from apex, but that changed nothing.

So am I missing something or is the trailhead doc wrong. And why does the formula return a number divided by 100 while Apex doesn't ?

Thanks

Answer 1

Formulas behave like Excel formulas, and if you format a cell as a Percent, Excel automatically handles conversion of percentages so your formulas "just work." Give it a try sometime in Excel (or Open Office Calc, or Google Spreadsheets, etc):

     A    B    C
1    200  10%  =A1*B1

In fact, do this in any reasonable spreadsheet program, and this should work. The reason why formulas work that way is to provide a consistent experience with inexperienced administrators who may have used a spreadsheet before.

Originally, the SOAP API was the only real API available, with others not available until years later (including Apex). In the SOAP API, a percent is expressed as written on a screen. I'm not entirely sure why, but it may have to do with the fact that there is no xsd:percent data type in the WSDL language. Later, the Apex language was invented and used the same query engine as the SOAP version, and yet later, REST was added, so it had to be consistent with the previous APIs.

The difference appears to be grounded in how the product was meant to be used, and technical restraints for the API options available from the earliest days of the API. All you really need to remember is that Formulas and APIs are different because of different product requirements for each, and that formulas automatically do the "divide by 100" for you.

Answer 2

Please, refer to this article: https://help.salesforce.com/s/articleView?id=000351429&type=1 Even though it is a bit counterintuitive, it looks understandable.

Answer 3

This is pretty annoying and confusing. Either way, manually changing the API version to 53 (from 54) in my case made Flow evaluate 90% as 0.9 instead of 90 which is what I was looking for/expecting.

I know this isn't a great solution, but got the job done for now.

More details here: https://help.salesforce.com/s/articleView?id=000351429&type=1