# Formula text field slightly changing a numeric value?

• 0
• Question
• Updated 3 years ago
Why would this (Formula Numeric field):

Materials Variance Original

\$30,955.65

Go to this (formula text field):

Materials Variance
\$30,955.52

See screenshot. Here is the formula:
var text tnum = ToFormattedText(Int([Materials Variance Original]),"comma_dot") & "." &Right(ToFormattedText(Frac([Materials Variance Original])*100+100,"comma_dot"),2);
If([Materials Variance Original]<0,"<div style=\"color:red;\">"&"\$"&\$tnum&"</div>",
[Materials Variance Original]=0,"<div style=\"color:#black;\">"&"\$"&\$tnum&"</div>",
[Materials Variance Original]>0,"<div style=\"color:#0AD823;\">"&"\$"&\$tnum&"</div>")     • 0 Points

Posted 3 years ago

• 0

Xavier Fan, Champion

• 800 Points I'm having trouble reproducing this - I'm seeing the same numeric value in [Materials Variance Original] and [Materials Variance].

I created a test app, with just 4 fields in the single table:
- Materials Budget (Numeric - Currency) = 211000
- Total Materials Cost (Numeric - Currency) = 180500.42
- Materials Variance Original (Formula - Numeric), Formula = [Materials Budget] - [Total Materials Cost] = 30499.58
- Materials Variance (Formula - Text), Formula copied from what you had = 30499.58 (in green)

Can you give some more details as to how to reproduce this?

My guess is that your [Materials Budget] and [Total Materials Cost] are also Formula Numeric or Summary fields which are aggregating a bunch of other numbers, and that this is some kind of rounding issue when converting from Number to Text and using the Int / Frac functions etc.  But it would be easier to test if we can find a simple case to reproduce.
• 0 Points
See attached screenshots. [Materials Budget] - [Purchase Orders Amount] = Variance Original
Variance Original = Variance Color

Xavier Fan, Champion

• 800 Points I didn't see any new screen shots?  Maybe edit your original question to attach them.
• 0 Points

Xavier Fan, Champion

• 800 Points Can you tell me what [Total Materials Cost] is when you show 4 decimal places?

In my test app - the only difference is that my [Total Materials Cost] is a directly entered number (I entered \$180,500.42).  With that number, I don't see a difference between [Materials Variance Original] and [Materials Variance].
• 0 Points
Total Materials Cost
\$180,583.6239

Xavier Fan, Champion

• 800 Points The issue was because the way the formula with Right and Frac retrieved the decimal values after "cents".

var Number originalvalue = Round([Materials Variance Original], 0.01);

var Text dollars = Part ( ToFormattedText( \$originalvalue, "comma_dot"), 1, ".");

var Text cents = Right ( ToText( \$originalvalue * 100 ), 2);

var Text tnum = \$dollars & "." & \$cents;

If(\$originalvalue < 0,"<div style=\"color:red;\">" & "\$" & \$tnum & "</div>",

\$originalvalue = 0,"<div style=\"color:#black;\">" & "\$" & \$tnum & "</div>",

\$originalvalue > 0,"<div style=\"color:#0AD823;\">" & "\$" & \$tnum &"</div>")

tnum is now calculated by first using the Round function to round [Materials Variance Original] to the nearest cent (0.01), and then you use ToFormattedText to output the text value, as you had it before.

EDIT:  Now updated as follows:

originalvalue stores the original value, rounded to the nearest cent.  Using this variable means you can use the same formula in other fields, just switch which field it's using in a single place.

dollar gets the dollar part of originalvalue - using the Part function to get whatever's to the left of the decimal place.

cents gets the cents part of originalvalue - multiplying originalvalue by 100 (to make sure we get any trailing zeroes), and then using Right to get the last 2 digits for the cents.

tnum just puts the dollars and cents together, separate by a decimal place.

The final If statement now uses originalvalue (which is rounded to the nearest cent) to select which color to display tnum in.
• 0 Points
That did the trick. Thank you!

Xavier Fan, Champion

• 800 Points Great - you're welcome!
• 0 Points
Actually, it's not working correctly in another field, and I have NO idea why, since it's copied exactly, except for the different field names. The formula text field is truncating the decimal spaces to 0 (the Original field it's mimicking is set to 2 decimal spaces).

var text tnum = ToFormattedText( Round([Design & Permitting Variance Original], 0.01), "comma_dot");

If([Design & Permitting Variance Original]<0,"<div style=\"color:red;\">"&"\$"&\$tnum&"</div>",

[Design & Permitting Variance Original]=0,"<div style=\"color:#black;\">"&"\$"&\$tnum&"</div>",

[Design & Permitting Variance Original]>0,"<div style=\"color:#0AD823;\">"&"\$"&\$tnum&"</div>")

Xavier Fan, Champion

• 800 Points I'm guessing it's because [Design & Permitting Variance Original] is ending with a whole dollar value (no cents).

Can you give the numeric values of [Design & Permitting Variance Original] (to 4 decimal places), and the resulting displayed value?  e.g. \$180,732.00 and \$180,732
• 0 Points
You're right, when I add cents, it reflects. Any idea how to update the formula?

Xavier Fan, Champion

• 800 Points Try this formula - the last part of the tnum calculation adds ".00" to the string if there are cents.  Don't forget to update your original formula for Materials Variance as well (I'll edit my answer above to update).

var text tnum = ToFormattedText( Round([Design & Permitting Varaince Original], 0.01), "comma_dot") & If ( Frac([Design & Permitting Varaince Original]) = 0, ".00");

If([Design & Permitting Varaince Original]<0,"<div style=\"color:red;\">"&"\$"&\$tnum&"</div>",

[Design & Permitting Varaince Original]=0,"<div style=\"color:#black;\">"&"\$"&\$tnum&"</div>",

[Design & Permitting Varaince Original]>0,"<div style=\"color:#0AD823;\">"&"\$"&\$tnum&"</div>")
• 0 Points
Nice work!

Xavier Fan, Champion

• 800 Points Hope it's working!
• 72,488 Points Xavier. What if the number ends in .10, which means actually .1

Xavier Fan, Champion

• 800 Points Use the following formula instead.  I've also updated the explanation above with the details of what's going on.

var Number originalvalue = Round([Design & Permitting Varaince Original], 0.01);

var Text dollars = Part ( ToFormattedText( \$originalvalue, "comma_dot"), 1, ".");

var Text cents = Right ( ToText( \$originalvalue * 100 ), 2);

var Text tnum = \$dollars & "." & \$cents;

If(\$originalvalue < 0,"<div style=\"color:red;\">" & "\$" & \$tnum & "</div>",

\$originalvalue = 0,"<div style=\"color:#black;\">" & "\$" & \$tnum & "</div>",

\$originalvalue > 0,"<div style=\"color:#0AD823;\">" & "\$" & \$tnum &"</div>")