Discussions

Expand all | Collapse all

Using 'not ifnull' formula in a 'formula text field'

  • 1.  Using 'not ifnull' formula in a 'formula text field'

    Posted 08-09-2017 19:05
    I need some help with a formula. I am trying display text if a field is not null. This is my current formula:

    If(not IsNull([AP Supplier Type]), "Oracle Hold",
    If(not IsNull([CURRENTWORKNODE]), "Rapid Hold",
    If(not IsNull([Amt]), "Payment Hold")))

    Currently, everything is being listed as an "Oracle Hold". I am not sure what I am doing wrong. Your help is much appreciated!!


  • 2.  RE: Using 'not ifnull' formula in a 'formula text field'

    Posted 08-09-2017 20:28
    The IsNull function is documented not to work with Text Realty. To test of a text field is blank then use the syntax here

    If(Trim([my text field])="", This field is blank")

    Ie you have to test against = empty quotes.


  • 3.  RE: Using 'not ifnull' formula in a 'formula text field'

    Posted 08-09-2017 20:38
    I do not want to test if the field is blank. I want to label a record as either, Oracle, Payment, or Rapid hold in a summary report if a certain field has text in it. Does that make sense?


  • 4.  RE: Using 'not ifnull' formula in a 'formula text field'

    Posted 08-09-2017 20:57
    In other words uf the field is not blank, then you want to do something. So you do, in fact, need a way to test if a field is blank.


  • 5.  RE: Using 'not ifnull' formula in a 'formula text field'

    Posted 08-09-2017 21:00
    Try this

    If(
    [AP Supplier Type])<>"", "Oracle Hold",
    [CURRENTWORKNODE]<>"", "Rapid Hold",
    [Amt])<>"", "Payment Hold")

    Note that unlike Excel, you do not need to nest your IF statements.


  • 6.  RE: Using 'not ifnull' formula in a 'formula text field'

    Posted 08-09-2017 21:04
    Thank you for your help! It is coming back with a syntax error.


  • 7.  RE: Using 'not ifnull' formula in a 'formula text field'

    Posted 08-09-2017 21:08
    There was an extra bracket after AMT

    Try this

    If(
    [AP Supplier Type])<>"", "Oracle Hold",
    [CURRENTWORKNODE]<>"", "Rapid Hold",
    [Amt]<>"", "Payment Hold


  • 8.  RE: Using 'not ifnull' formula in a 'formula text field'

    Posted 08-09-2017 21:11
    It is coming up with the same error message :(


  • 9.  RE: Using 'not ifnull' formula in a 'formula text field'

    Posted 08-09-2017 21:14
    Got it! The error was coming from [AP Supplier Type]) <-----


  • 10.  RE: Using 'not ifnull' formula in a 'formula text field'

    Posted 08-09-2017 21:14
    It worked beautifully. Thanks so much!


  • 11.  RE: Using 'not ifnull' formula in a 'formula text field'

    Posted 08-09-2017 21:14
    Sorry, there were more extra brackets.

    There was an extra bracket after AMT

    Try this

    If(
    [AP Supplier Type]<>"", "Oracle Hold",
    [CURRENTWORKNODE]<>"", "Rapid Hold",
    [Amt]<>"", "Payment Hold")

    I'm actually sitting in a tent now on vacation and it's hard to see on my iPad :)