Skip to main content
Solved

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

  • January 27, 2021
  • 4 replies
  • 30 views
R
Forum|alt.badge.img+4

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

Best answer by kuovonne

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.

    4 replies

    ScottWorld
    Forum|alt.badge.img+35
    • ScottWorld
    • Genius
    • January 27, 2021

    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
    Forum|alt.badge.img+29
    • kuovonne
    • Brainy
    • Answer
    • January 27, 2021

    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.

    R
    Forum|alt.badge.img+4
    • Rebecca_Elam
    • Author
    • Inspiring
    • January 27, 2021

    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

    R
    Forum|alt.badge.img+4
    • Rebecca_Elam
    • Author
    • Inspiring
    • January 27, 2021

    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

    Terms & ConditionsAccessibility statement