# Discussions

View Only

## Trouble comparing number values in formula field

• #### 1.  Trouble comparing number values in formula field

Posted 08-30-2018 00:26
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?

• #### 2.  RE: Trouble comparing number values in formula field

Posted 08-30-2018 04:22
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.

• #### 3.  RE: Trouble comparing number values in formula field

Posted 08-30-2018 13:28
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.

• #### 4.  RE: Trouble comparing number values in formula field

Posted 08-30-2018 13:42
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))