function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Steve_EarlySteve_Early 

Problems getting 2 decimal points to display on Text formula field

We are using this set of formulas to test to see if the quantity price is null. If it isn't it should be output, otherwise it is skipped.

IF(ISNULL(Product2.Qty_1_9__c),"","$"&TEXT(Product2.Qty_1_9__c)&BR())
&IF(ISNULL(Product2.Qty_10_49__c),"","$"&TEXT(Product2.Qty_10_49__c)&BR())
&IF(ISNULL(Product2.Qty_50_199__c),"","$"&TEXT(Product2.Qty_50_199__c)&BR())
&IF(ISNULL(Product2.Qty_200_499__c),"","$"&TEXT(Product2.Qty_200_499__c)&BR())
&IF(ISNULL(Product2.Qty_500_999__c),"","$"&TEXT(Product2.Qty_500_999__c)&BR())
&IF(ISNULL(Product2.Qty_1000__c),"","$"&TEXT(Product2.Qty_1000__c)&BR())),"",

The goal is to have output on a Quote:
Qty 1-9  $15
Qty 10-19  $14.45
Qty 20-39  $13.95

It works fine with one exeption: If the price ends in zeroes ($15.00) on the PDF output we are seeing is "$15.0" - the second zero is being dropped. The field involved is set up with 2 decimal places.

Is there anything I can put in this formula to force the second 0 to print on PDF output? 
Best Answer chosen by Steve_Early
Stephen WillcockStephen Willcock
Steve, you could try this kind of expression in your formula:

"$" + text( floor( My_Number__c ) ) + "." + right( text( ( 1 + My_Number__c - floor( My_Number__c ) ) * 100 ), 2 )

We treat the integer and decimal parts separately, and reassemble them.
First, separate out the integer and decimal parts using the floor() function, which always rounds down.
Then add 1 to the decimal part, and multiply by a hundred, so for zero decimal, we get "100" in text.
Finally trim the "1" from the decimal part by using the right() function.

All Answers

Virendra ChouhanVirendra Chouhan
Hi Steve,

As you said your field involved is set up with 2 decimal places.
so which field Qty_1_9__c. If yes this one 

then what about Formula field it also have Options for Decimal Places.
Ley me know if it helped.

Regards
Virendra
Steve_EarlySteve_Early
Hi Virenda - thank you for taking the time to answer and your idea.

Yes the Qty_1_9__c field is set up as a currency field with 2 decimal places. The formula field here (Quantity_Pricing__c) is set to TEXT as the output so that we can display "$" on a quote instead of "USD". There is no option for decimal plasces.
Virendra ChouhanVirendra Chouhan
Hi , Yeah you are right, If you select Text in formula field then there is no option for decimal. So, change formula return type with Currency. And set 2 decimal value ( this option apper where you select return type for formula ) And the $ sign is automatically shown in currency field based on your profile . Let me know it it helped . Regards Virendra --- Original Message ---
Virendra ChouhanVirendra Chouhan
--- Original Message ---
Stephen WillcockStephen Willcock
Steve, you could try this kind of expression in your formula:

"$" + text( floor( My_Number__c ) ) + "." + right( text( ( 1 + My_Number__c - floor( My_Number__c ) ) * 100 ), 2 )

We treat the integer and decimal parts separately, and reassemble them.
First, separate out the integer and decimal parts using the floor() function, which always rounds down.
Then add 1 to the decimal part, and multiply by a hundred, so for zero decimal, we get "100" in text.
Finally trim the "1" from the decimal part by using the right() function.
This was selected as the best answer
Steve_EarlySteve_Early
@FooBarForce - I shall try that today and let you know how I make out. Looks rather promising - thanks!!
Virendra ChouhanVirendra Chouhan
That's cool Steve I never try this ! Thanks for teach me one new thing --- Original Message ---
Steve_EarlySteve_Early
@FooBarForce - Brilliant!  Success!  Many thanks. Our sales and customer ops people will be ecstatic.  :D
Stephen WillcockStephen Willcock
Excellent! Pleased to have been of help :-)
Ramesh Reddy KasireddyRamesh Reddy Kasireddy
In my case, I used the following formula which works for both positive and negative values. Posting this incase if someone needs it.

"$" & text(FLOOR(ROUND(Your_Field,2))) & "." &
LPAD(TEXT(ABS((ROUND(Your_Field,2) - FLOOR(ROUND(Your_Field,2)))*100)), 2 , "0")