Skip to main content
Question

Trouble comparing number values in formula field

  • August 30, 2018
  • 0 replies
  • 174 views
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? 







    This topic has been closed for replies.

    • KevinSlider
    • Registered
    • August 30, 2018
    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.
    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. 


    • KevinSlider
    • Registered
    • August 30, 2018
    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.
    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!
    Terms & ConditionsAccessibility statement