Discussions

Expand all | Collapse all

If formula on a field in a relation table not working.

  • 1.  If formula on a field in a relation table not working.

    Posted 08-04-2017 17:37

    I have a field on the main table that is a lookup field.  I tried to add a formula text field with this formula.

     If (IsNull([Target Region]),"Non-Target","Target")

    It only brings back Target regardless.




  • 2.  RE: If formula on a field in a relation table not working.

    Posted 08-04-2017 17:46
    Yep, the IsNull is documented not to work on text fields,. Not intuitive, I know and many before you have fallen in that same pothole.  So you need to compare it to empty quotes.  I like to Trim it just in case it has some invisible spaces in the data

    If (Trim([Target Region])="","Non-Target","Target")


  • 3.  RE: If formula on a field in a relation table not working.

    Posted 08-04-2017 17:52
    Thank you so much, that worked perfectly.  I'll try and remember this in the future.