Skip to main content
Question

Unexpected behavior of AND() in a formula field

  • September 24, 2025
  • 2 replies
  • 41 views
K
Forum|alt.badge.img+5
  1. Formula field {Allocation Form Recipients} = IF({Send Allocation Form},{Conditional Recipients Rollup (from Payment Schedule)})
    where {Conditional Recipients ...} is a rollup field and {Send Allocation Form} is a checkbox field.
  2. Formula field {Approval Email Subject Tag} = IF( AND({Term Deal},{Allocation Form Recipients}), 'Recoupment Allocation', 'Payment' )
    where {Term Deal} field is a rollup and {Allocation Form Recipients} is a formula field which I explained in #1.
  3. When the {Term Deal} rollup is empty and the {Allocation Form Recipients} formula field is also empty, the {Approval Email Subject Tag} should show 'Payment' but it shows 'Recoupment Allocation'.
  4. I tried to troubleshoot this by creating 2 separate formula fields stated below:
    a. _check = IF({Term Deal},1,0)
    b. _check copy = IF({Allocation Form Recipients},1,0)
    Surprisingly, these 2 formula fields work as expected. Meaning where Term Deal is blank _check is 0. When {Allocation Form Recipients} is blank, _check copy is 0.
    However, for the same row, the {Approval Email Subject Tag} field o/p is 'Recoupment Allocation'.
    I'm unable to understand why is there such discrepancy when individual fields are returning 0.

2 replies

K
Forum|alt.badge.img+5
  • kschhajed
  • Author
  • Participating Frequently
  • September 25, 2025

I have an interesting observation to the above issue. The {Term Deal} field being mentioned in #2 and #3 above is configured as below.

If I remove the highlighted condition, the o/p is as expected for me. However, this additional filter was added in place purposefully since previously when creating a similar rollup, blank/null values were being included and the rollup would look blank but not actually be blank. 
Surprisingly, the formula works when ideally it shouldn’t, after removing this filter; and should work after applying this filter. 

Did Airtable change their handling of nulls for lookups/rollups recently?

TheTimeSavingCo
Forum|alt.badge.img+31

Hm, extremely long shot, what happens if you add a ‘& “”’ to ‘ARRAYUNIQUE(values)’ there?  Wondering whether it’s to do with how formulas perceive data types, and whether ARRAYUNIQUE’s output is an array or something and so it’s not considered empty

Then again, if that’s true then your subsequent experiment should have worked differently...
---

As a terrible workaround, what if you replaced that ‘AND’ with those two working ‘IF’s instead?

Adam, Airtable Consultant - thetimesaving.company
Terms & ConditionsAccessibility statement