Help

Re: And() returning true for formula field even if not all are met

Solved
Jump to Solution
766 0
cancel
Showing results for 
Search instead for 
Did you mean: 

Said formula:

AND({FORMULA1}!="", {FORMULA 2}!="", {FORMULA 3}!="")
no matter what combination of those 3 fields are empty or populated it always returns 1. i only want it to return 1 once all those fields have returned a value.

conditional automations using ‘and’ also return true even if not all of the options are true when using formula fields. are we seriously not able to check if several formulas did or didnt populate? am i missing something?

seems to me that AND() is behaving more like OR() but i have no idea what im talking about

1 Solution

Accepted Solutions
kuovonne
18 - Pluto
18 - Pluto

I agree that we need more information about the formula / field values to troubleshoot this better for you. Are {FORMULA1}, {FORMULA2], and {FORMULA3} individual fields or are they formulas?

If your formulas will be return text strings or dates, you can simplify this with the following formula: Strings with at least one character and dates are “truthy” values, so you do not need to compare them to an empty string.

AND( {FORMULA1}, {FORMULA2}, {FORMULA3} )

If you are comparing numeric values and you want to include zero as an acceptable value, you should use something like this

AND(
  {FORMULA1} & "",
  {FORMULA2} & "",
  {FORMULA3} & ""
)

You should also check the individual formulas. One of them may be returning a value (such as the string " "), even if you don’t see anything.

See Solution in Thread

4 Replies 4

What types of results do your formulas 1, 2, and 3 give you? Numbers, dates, text, etc.?

For example: If they’re resulting in Dates, you need to check if they’re not equal to BLANK() instead of not equal to "". It might be the same for numbers too, but I’m not sure.

kuovonne
18 - Pluto
18 - Pluto

I agree that we need more information about the formula / field values to troubleshoot this better for you. Are {FORMULA1}, {FORMULA2], and {FORMULA3} individual fields or are they formulas?

If your formulas will be return text strings or dates, you can simplify this with the following formula: Strings with at least one character and dates are “truthy” values, so you do not need to compare them to an empty string.

AND( {FORMULA1}, {FORMULA2}, {FORMULA3} )

If you are comparing numeric values and you want to include zero as an acceptable value, you should use something like this

AND(
  {FORMULA1} & "",
  {FORMULA2} & "",
  {FORMULA3} & ""
)

You should also check the individual formulas. One of them may be returning a value (such as the string " "), even if you don’t see anything.

theyre address labels so they just concatenate 3 separate fields into 1 field to make a properly formatted address. if all three addresses populate the record needs to be marked complete

if a label doesnt populate it means i need to ask that sales person for the info
what i need is for the output to return true if all 3 addresses are populated and nothing if any of the 3 are missing. right now they all say theyre ready even when theres nothing in any of the fields

@kuovonne they have line breaks in them i didnt even remotely consider that