Help in using "<>" in a formula.

  • 0
  • 1
  • Question
  • Updated 7 months ago
  • Answered
I am trying to find mismatches when comparing two fields. The [TO_iM_StaticPressureCFM] is numeric field while the [TO_CalculatedCFM] field is a formula - numeric field. The below script is not working correctly. I guess I am not using the "<>" correctly.

If([Customer - Related Client]=3 and [AirFlowMethodID]=2 and [TO_iM_StaticPressureCFM]<>[TO_CalculatedCFM],"Does not match"&"\n")&

Thanks
Photo of Stephen Anguiano

Stephen Anguiano

  • 394 Points 250 badge 2x thumb

Posted 7 months ago

  • 0
  • 1
Photo of Chris

Chris, Champion

  • 4,390 Points 4k badge 2x thumb
That last ampersand, if you really do have it, should go.
Photo of Evan Martinez

Evan Martinez, Community Manager

  • 8,774 Points 5k badge 2x thumb
HI Stephen,

When that formula is not working correctly are you getting a specific error message back when trying to save or are you getting a wrong output?
Photo of Evan Martinez

Evan Martinez, Community Manager

  • 8,774 Points 5k badge 2x thumb
What type of fields are [TO_iM_StaticPressureCFM] and [TO_CalculatedCFM]
Photo of Stephen Anguiano

Stephen Anguiano

  • 394 Points 250 badge 2x thumb
 [TO_iM_StaticPressureCFM] is a numeric field and [TO_CalculatedCFM] is a formula - numeric field.
Photo of Evan Martinez

Evan Martinez, Community Manager

  • 8,774 Points 5k badge 2x thumb
Hi Stephen,

Does that formula numeric use any rounding? If you go into that field and set the decimal place to 14 do you see any trailing decimal points for that calculation? Sometimes due to rounding numbers can appear the same but are stored as different, essentially one might be 2.5 and the other might be 2.50000134 and that 134 is still logged by the formula but not displayed due to the field settings. Expanding the decimal place manually can help to assure that isn't happening and causing inconsistent results. 
Photo of Stephen Anguiano

Stephen Anguiano

  • 394 Points 250 badge 2x thumb
Evan,

Thanks for the information and I was able to prove your theory. I did not see everything after the decimal point because of the field settings.
Thanks.
Photo of Evan Martinez

Evan Martinez, Community Manager

  • 8,774 Points 5k badge 2x thumb
Hi Stephen,

I am glad I was able to help. I have ran into that myself a number of times with numeric formulas, Quick Base at times will hide those small decimal places and it can be confusing to figure out where the mismatch is. If you are looking to still use the comparison for your formula you can apply a rounding to your formula field via the Round function to force it to round the results to the decimal place you would like. Thank you and have a great day!
(Edited)