# Trouble comparing number values in formula field

• 0
• Question
• Updated 11 months ago
Alright, so I have two tables, say Order Sheets and Order Items

Each Order Sheet can have multiple Order items.
Each Order Sheet has a field labeled Order Amount
Each Order Item has a Quantity field(numeric) and a Price(currency) field.

I have created a formula field Line Total that totals each Order Items record by doing [Quantity] * [Price]

I also have a summary field on Order Sheets called Total Items that sums all the Line Totals

I then created a formula field to check if the Order Amount is equal to Total Items and if not displays a value let's say

If ([Order Amount] <> [Total Items], "Not Equal")

No matter what I do, I cannot get the values to check as equal.
I know the values are the same because the Order Amount is user input as 29.90, which QB stores as 29.9. I also know the two items are 5x\$2.99 and the sum of those two is 29.9

I have tried every variation of options in QB to display each field as a number, currency, decimal points, etc.
I have tried using conversions such as ToNumber on the values as well.
I have viewed the number values on each and know they both are 29.9
I have tried rounding function on values.

I am stumped here. I even tried setting all currency fields to numeric assuming it was a type mismatch issue.

But for whatever reason, QB still fails to say they are equal.

Any ideas?

• 1,222 Points Posted 11 months ago

• 0
• 1,386 Points It sounds like you have tried setting the fields to currency with matching decimal places. If Quick Base shows 29.90 in both fields and the formula still evaluates Not Equal, then I would take your formula and adjust it with the round function to round to the nearest cent.

If (Round([Order Amount], .01) <> Round([Total Items], .01), "Not Equal")

Let me know if that helps.
• 192 Points Yes, I currently have that exact formula in place and occasionally get a matching value but 99% of the time even when the values are the same, it still checks are not equal.

• 1,386 Points This is where I would try converting to text.
If (totext(Round([Order Amount], .01)) <> totext(Round([Total Items], .01)), "Not Equal")

if this didnt work, I would be setting up a test formula to evaluate what the value is for
totext(Round([Order Amount], .01)) and  totext(Round([Total Items], .01))

• 192 Points 