Discussions

Expand all | Collapse all

Formula text field isn't working with IfNull()

  • 1.  Formula text field isn't working with IfNull()

    Top
    Contributor
    Posted 11-04-2015 19:14

    I have a formula text field with the following formula: If(IsNull([Preferred Vendor(Form)]),[Item List - Preferred Vendor],[Preferred Vendor(Form)]). The [Item List - Preferred Vendor] field is a lookup field. I want to be able to change the preferred vendor if need be, so I tried creating a field that would look to see if I entered a different vendor in the [Preferred Vendor(Form)] field, and if I had not then it would default to the preferred vendor that's already listed. It's not working though. It will fill in the field if I have entered a value into the [Preferred Vendor(Form)] field, but if there's nothing in that field then it just shows up blank. Am I doing something wrong with the formula? 

    Thanks for the help.



  • 2.  RE: Formula text field isn't working with IfNull()

    Posted 11-04-2015 19:18
    Not obvious but IsNull() is documented not to work with text fields.

    instead do


    If([Preferred Vendor(Form)]="",[Item List - Preferred Vendor],[Preferred Vendor(Form)]) // ie test against empty quotes.


    But I usually do

    If(Trim([Preferred Vendor(Form)])<>"",[Item List - Preferred Vendor],[Preferred Vendor(Form)])

    Just in case someone entered spaces in [Preferred Vendor(Form)]



  • 3.  RE: Formula text field isn't working with IfNull()

    Top
    Contributor
    Posted 11-04-2015 19:20
    Oh my goodness. I don't know how I've gone this long without knowing that IsNull() doesn't work with text fields. Thank you!


  • 4.  RE: Formula text field isn't working with IfNull()

     
    Posted 01-08-2018 17:06
    I've found this discussion very helpful because I'm having the same issue. I tried the solution above in my app but I get a 0 instead of the expected text field. The complete formula is below:

    If(Trim([invoiceNumberOverride]) <> "",[invoiceNumberOverride],ToText(5000 + [Record ID#]))

    Any ideas what I am doing wrong? Any help would be greatly appreciated.


  • 5.  RE: Formula text field isn't working with IfNull()

    Posted 01-08-2018 17:09
    It looks to me that your [invoiceNumberOverride] field is actually a numeric field type.

    If so, you would do

    If([invoiceNumberOverride] > 0, ToText([invoiceNumberOverride]),ToText(5000 + [Record ID#]))


  • 6.  RE: Formula text field isn't working with IfNull()

     
    Posted 01-08-2018 17:16
    Thanks for your quick response! I verified that the invoiceNumberOverride field is a text field. Should I use a ToText() function to force it to text? Thanks again.


  • 7.  RE: Formula text field isn't working with IfNull()

     
    Posted 01-08-2018 17:19
    OK, the problem only seems to exist when I use letters in the invoice number (i.e.,12345 works fine, PTY12345 does not). Thanks again. 


  • 8.  RE: Formula text field isn't working with IfNull()

    Posted 01-08-2018 17:45
    OK, let's go back to your original formula

    If(Trim([invoiceNumberOverride]) <> "",[invoiceNumberOverride],ToText(5000 + [Record ID#]))

    Can you choose a record which is not working and tell me what is in the field for [invoiceNumberOverride]?


  • 9.  RE: Formula text field isn't working with IfNull()

     
    Posted 01-08-2018 18:25
    I've entered the value TVP123546 in [invoiceNumberOverride]. A 0 (zero) appears in the Invoice Number field (the field that contains this formula). Thanks.


  • 10.  RE: Formula text field isn't working with IfNull()

    Posted 01-08-2018 18:38
    What field type is your formula field?  It needs to be a formula text field type.  I think it may be a formula numeric field type.


  • 11.  RE: Formula text field isn't working with IfNull()

     
    Posted 01-08-2018 19:00
    You're right. It was formula - numeric. I changed it to formula - text and it works great now. Thank you so much for your help!