Skip to main content
Question

Formula text field isn't working with IfNull()

  • November 5, 2015
  • 0 replies
  • 222 views

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.

This topic has been closed for replies.

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)]

  • ArchiveUser
  • Author
  • Registered
  • November 5, 2015
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!
  • KenKen
  • Registered
  • January 8, 2018
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.
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#]))
  • KenKen
  • Registered
  • January 8, 2018
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.
  • KenKen
  • Registered
  • January 8, 2018
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. 
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]?
  • KenKen
  • Registered
  • January 8, 2018
I've entered the value TVP123546 in [invoiceNumberOverride]. A 0 (zero) appears in the Invoice Number field (the field that contains this formula). Thanks.
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.
  • KenKen
  • Registered
  • January 9, 2018
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!
Terms & ConditionsAccessibility statement