Formula text field isn't working with IfNull()

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered

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.

Photo of Brandon

Brandon

  • 120 Points 100 badge 2x thumb

Posted 3 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,310 Points 50k badge 2x thumb
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)]

Photo of Brandon

Brandon

  • 120 Points 100 badge 2x thumb
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!
Photo of Ken

Ken

  • 292 Points 250 badge 2x thumb
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.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,310 Points 50k badge 2x thumb
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#]))
Photo of Ken

Ken

  • 292 Points 250 badge 2x thumb
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.
Photo of Ken

Ken

  • 292 Points 250 badge 2x thumb
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. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,310 Points 50k badge 2x thumb
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]?
Photo of Ken

Ken

  • 292 Points 250 badge 2x thumb
I've entered the value TVP123546 in [invoiceNumberOverride]. A 0 (zero) appears in the Invoice Number field (the field that contains this formula). Thanks.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,310 Points 50k badge 2x thumb
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.
(Edited)
Photo of Ken

Ken

  • 292 Points 250 badge 2x thumb
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!