Discussions

Expand all | Collapse all

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))

    Let us know your progress.


  • 5.  RE: Trouble comparing number values in formula field

    Posted 08-30-2018 13:49
    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!