Trouble comparing number values in formula field

  • 0
  • 1
  • Question
  • Updated 3 months ago
  • Answered
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? 







Photo of David Brogdon

David Brogdon

  • 366 Points 250 badge 2x thumb

Posted 3 months ago

  • 0
  • 1
Photo of Slider

Slider

  • 1,326 Points 1k badge 2x thumb
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.
Photo of David Brogdon

David Brogdon

  • 192 Points 100 badge 2x thumb
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. 


Photo of Slider

Slider

  • 1,326 Points 1k badge 2x thumb
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))

Let us know your progress.
Photo of David Brogdon

David Brogdon

  • 192 Points 100 badge 2x thumb
Okay, so at the moment I think it seems to be working. Not 100% sure what the problem was but I deleted all formulas and re-wrote everything. The only thing that might have thrown it last time is I'm not sure if I was rounding the Quantity field to .01 which I am doing now so that its not 5x$2.99, it is 5.00 x $2.99. Don't know why this would matter since I was already rounding both numbers in the final check?? 

If it throws any more errors I will try converting to text. Thanks for your help!