Numeric formula isn't calculating

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

This is what I'm trying to say

If Loaded text box is checked AND Product text = "this" OR "that", then Qty Loaded, otherwise 0 

I'm not getting any error codes, it just isn't giving Qty Loaded when it should, I just get 0

If(

([Loaded]=true)

and

([Product] = "Wattle 9"

or [Product] = "Sand bags - filled"),

[Qty Loaded],0

)

Photo of Suzanne

Suzanne

  • 10 Points

Posted 3 years ago

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

QuickBaseCoach App Dev./Training, Champion

  • 67,760 Points 50k badge 2x thumb
your formula looks correct.  Can you tell me for a record that you think is incorrect, what is in the field for [Product] and what is in the field for [Qty Loaded]. Also, is there any chance that the Product has any leading or trailing spaces in it.
Photo of Suzanne

Suzanne

  • 10 Points
The field for [Product] is Wattle 9"x20' - Yellow (9"x20' Certified Weed Free, Biodegradeable Wattle - Yellow)
The field for [Qty Loaded] is 2000
I don't think the Product has any leading spaces. I don't know about trailing.  Do I need to put the entire field in the formula?
Photo of QuickBasePros_IDS

QuickBasePros_IDS, Champion

  • 4,208 Points 4k badge 2x thumb
Try this:

If(

[Loaded]=true

and

(Contains([Product],"Wattle 9")

or 

Contains([Product],"Sand bags - filled")),

[Qty Loaded],0

)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,760 Points 50k badge 2x thumb
Laura answer is correct.  Computer are not like people where you can just shout across the room and ask if we got any Waddle 9" laying around.

In QuickBase, like Excel, = means exact'y equals.  So you either need the full description or use either "Contains" as Laura has shown or else similar to "Contains" is the "Begins" function.
Photo of Suzanne

Suzanne

  • 10 Points
I get a "closed parenthesis is missing" error:
If(
[Loaded]=true
and
(contains([Product], "Wattle 9")
or
contains([Product], "Sand bags - filled")
or
contains([Product], "Gravel bags - filled")
or
contains([Product], "Dewatering")
or
contains([Product], "DI Bag")
or
contains([Product], "Syman Washout")
or
contains([Product], "Concrete Washout Sign")
or
contains([Product], "SWPPP Sign Rental")
or
contains([Product], "Spill Kit 25 Gal")
or
contains([Product], "Spill Kit 5 Gal Bucket"),
[Qty Taken],0
)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,760 Points 50k badge 2x thumb
you need one more closing ) at the very end.
Photo of Suzanne

Suzanne

  • 10 Points
already tried that, no worky...
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,760 Points 50k badge 2x thumb
right, sorry

If(
[Loaded]=true
and
(contains([Product], "Wattle 9")
or
contains([Product], "Sand bags - filled")
or
contains([Product], "Gravel bags - filled")
or
contains([Product], "Dewatering")
or
contains([Product], "DI Bag")
or
contains([Product], "Syman Washout")
or
contains([Product], "Concrete Washout Sign")
or
contains([Product], "SWPPP Sign Rental")
or
contains([Product], "Spill Kit 25 Gal")
or
contains([Product], "Spill Kit 5 Gal Bucket")),
[Qty Taken],0
)
Photo of Suzanne

Suzanne

  • 10 Points
Yay, that works, thank you soooo much!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,760 Points 50k badge 2x thumb
:)
Photo of QuickBasePros_IDS

QuickBasePros_IDS, Champion

  • 4,208 Points 4k badge 2x thumb
I had added in the missing parenthesis in my "updated" response - but that probably didn't fire an email.  In this case, since you have so many "if's".. I would consider either making a variable response or creating a formula checkbox field which determines whether the If (Contains..) are true or false; and then make your formula shorter and simpler.