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>")
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
Xavier Fan, Champion
 410 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.
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
Variance Original = Variance Color
Xavier Fan, Champion
 410 Points
I didn't see any new screen shots? Maybe edit your original question to attach them.
Xavier Fan, Champion
 410 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].
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].
Xavier Fan, Champion
 410 Points
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.
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.
 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>")
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
 410 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
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
 410 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>")
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>")
QuickBaseCoach App Dev./Training, Champion
 65,542 Points
Xavier Fan, Champion
 410 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>")
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>")
Related Categories

Formulas & functions
 2923 Conversations
 74 Followers

Tables & fields
 7209 Conversations
 182 Followers