Formula Change from Decimal to Percentage
Hey Everyone, This is a Rich Text field. I'm working on a formula that takes a field and adds some styling to try and make the field larger while being highlighted based on its value. I've been ...
Forum Discussion
Any suggestions on the percent sign? I changed the code to:
ToNumber(Left(ToText([Total Job Gross Margin F]),6))
So now my result is 28.13
------------------------------
Alex Bennett
------------------------------
ToNumber(Left(ToText([Total Job Gross Margin F]),6))
So now my result is 28.13
------------------------------
Alex Bennett
------------------------------
Add the percentage sign to your formula in the first post for the rich text field. Put it directly after where you are placing $margin.
You might be able to just add a new variable up top for this as well. This may or may not work as I typed it up. This is just a quick example. It still uses marginOne for the calculation(as that is a number) and then inserts marginTwo for the text. If you didn;t want to do multiple variables here you could also just put "%" in the formula below like I first mentioned. I just found this easier.
var number marginOne = [Total Job Gross Margin F];
var text marginTwo = $marginOne + "%";
If( $marginOne >= 0 , "<span style=\"background-color:#fbff00; font-size:16px; font-weight:bold\">"&$marginTwo&"</span>", "<span style=\"background-color:#ff0000; font-weight:bold\">"&$marginTwo&"</span>" )
You might be able to just add a new variable up top for this as well. This may or may not work as I typed it up. This is just a quick example. It still uses marginOne for the calculation(as that is a number) and then inserts marginTwo for the text. If you didn;t want to do multiple variables here you could also just put "%" in the formula below like I first mentioned. I just found this easier.
var number marginOne = [Total Job Gross Margin F];
var text marginTwo = $marginOne + "%";
If( $marginOne >= 0 , "<span style=\"background-color:#fbff00; font-size:16px; font-weight:bold\">"&$marginTwo&"</span>", "<span style=\"background-color:#ff0000; font-weight:bold\">"&$marginTwo&"</span>" )
- Rather than having to convert the number back and forth between Numeric and Text, you can also use the Round function:
var number margin = Round([Total Job Gross Margin F]*100,.01); If( $margin >= 0 , "<span style='background-color:#fbff00; font-size:16px; font-weight:bold'>" & $margin &"%</span>", "<span style='background-color:#ff0000; font-weight:bold'>"& $margin & "%</span>" )
------------------------------
Blake Harrison
bharrison@datablender.io
DataBlender - Quickbase Solution Provider
Atlanta GA
404.800.1702 / http://datablender.io/
------------------------------ - It won't let me add it that way as I'm getting an error of The operator '+' can't be applied on types number, text
------------------------------
Alex Bennett
------------------------------ - var number margin = ToNumber([Total Gross Margin])*100;
If( $margin >= 0 , "<span style=\"background-color:#fbff00; font-size:16px; font-weight:bold\">"&$margin&"</span>", "<span style=\"background-color:#ff0000; font-weight:bold\">"&$margin&"</span>" )
------------------------------
Alex Bennett
------------------------------ - Sorry it would be an & in that case. I have been doing too much Python lately and not enough formulas.
The other way you showed would be the best method. I just was not sure what fields you were working with exactly and was trying to do it through what was already set up. - Switching to & worked. I had to keep the ToNumber as I had to use the Left()ToText() to only get XX.XX digits before adding the "%".
Thanks for all your help Austin! I now have the Accurate 28.13% allowed to be highlighted to show importance as well!
Thanks again!
------------------------------
Alex Bennett
------------------------------