Formula text field slightly changing a numeric value?

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered
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>")
Photo of Ian

Ian

  • 0 Points

Posted 3 years ago

  • 0
  • 1
Photo of Xavier Fan

Xavier Fan, Champion

  • 800 Points 500 badge 2x thumb
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.
Photo of Ian

Ian

  • 0 Points
See attached screenshots. [Materials Budget] - [Purchase Orders Amount] = Variance Original
Variance Original = Variance Color
Photo of Xavier Fan

Xavier Fan, Champion

  • 800 Points 500 badge 2x thumb
I didn't see any new screen shots?  Maybe edit your original question to attach them.
Photo of Ian

Ian

  • 0 Points
I thought I had. Look now please.
Photo of Xavier Fan

Xavier Fan, Champion

  • 800 Points 500 badge 2x thumb
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].
Photo of Ian

Ian

  • 0 Points
Total Materials Cost
$180,583.6239
Photo of Xavier Fan

Xavier Fan, Champion

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

Use this formula instead:

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.
Photo of Ian

Ian

  • 0 Points
That did the trick. Thank you!
Photo of Xavier Fan

Xavier Fan, Champion

  • 800 Points 500 badge 2x thumb
Great - you're welcome!
Photo of Ian

Ian

  • 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>")
Photo of Xavier Fan

Xavier Fan, Champion

  • 800 Points 500 badge 2x thumb
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
Photo of Ian

Ian

  • 0 Points
You're right, when I add cents, it reflects. Any idea how to update the formula?
Photo of Xavier Fan

Xavier Fan, Champion

  • 800 Points 500 badge 2x thumb
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>")
Photo of Ian

Ian

  • 0 Points
Nice work!
Photo of Xavier Fan

Xavier Fan, Champion

  • 800 Points 500 badge 2x thumb
Hope it's working!
Xavier. What if the number ends in .10, which means actually .1
Photo of Xavier Fan

Xavier Fan, Champion

  • 800 Points 500 badge 2x thumb
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>")