I need formula help please:

  • 0
  • 1
  • Question
  • Updated 8 months ago
  • Answered
  • (Edited)
How do I say "if the value in this field is not blank show me this...? I've been trying to use the not isnull function, but no luck. I'm not sure what I'm doing wrong. This is the formula so far: Case([Inventory Item Name], "", [Edit Price], [Inventory Item Name], IS NOT BLANK/EMPTY [Quantity Proposed]*[Inventory Item - Rental Price Per Unit])

Thank you!!
Photo of Elena Larrabee

Elena Larrabee

  • 736 Points 500 badge 2x thumb

Posted 8 months ago

  • 0
  • 1
Photo of Chris

Chris, Champion

  • 4,390 Points 4k badge 2x thumb

will this work?

case([inventory item name],"",[Edit Price],if(not isnull([inventory item name])),[Quantity proposed]*...)

The good ol' IF() is usually the best practice.


Photo of Elena Larrabee

Elena Larrabee

  • 736 Points 500 badge 2x thumb
I'll give it a try! I didn't know I could use the If function within a Case function. 
Photo of Chris

Chris, Champion

  • 4,380 Points 4k badge 2x thumb
QB will always surprise you one way or the other. I never cease to be.
Photo of Elena Larrabee

Elena Larrabee

  • 736 Points 500 badge 2x thumb
Nope, it's throwing me back an error message. 

Formula error -- Bad or missing arguments in function call

The types of the arguments or the number of arguments supplied do not meet the requirements of the function If.

The function is defined to be If (Boolean condition1, result1, ..., else-result).

You may need to do one of the following to fix the problem:
  • Choose the right number and type of arguments.
  • Use a type conversion function to convert the arguments you are using to the correct type.
  • Choose a different function.
Photo of Chris

Chris, Champion

  • 4,390 Points 4k badge 2x thumb

So, let's go with a full on IF()

If(

[Inventory Item Name]="", [Edit Price],

not isnull([Inventory Item Name]),[Quantity Proposed]*[Inventory Item - Rental Price Per Unit],null

)


Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,036 Points 50k badge 2x thumb
you cannot check for blank like this

isnull([Inventory Item Name])

IsNull does not work for text fields.

You need to use

IF(Trim([my text field])="", ......, .... )
Photo of Elena Larrabee

Elena Larrabee

  • 736 Points 500 badge 2x thumb
Hey, seems like it worked! Thank you so much! So I understand that this formula says basically if item name is blank, show edit price, and if it isn't blank, show the result of this math function, what is the final "null" for? Is it just to say that if neither of these options is true then show nothing?