Discussions

View Only

Trouble comparing number values in formula field David Brogdon08-30-2018 00:26 Slider Slider08-30-2018 04:22 David Brogdon08-30-2018 13:28 Slider Slider08-30-2018 13:42 • 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!