Syntax for IF formula when testing for several conditions

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

I am having issues in understanding IF formula syntax.  Coming from MS Access VB background,  I need to understand how AND or OR work in QB.  I have a simple situation where I am testing for a multiple conditions. 

If(

[Var Category] = "Variation Claim By Builder", 

If(

[Variation Status] = "Approved", "BVR Approved", "BVR Pending"))

But I am not getting the right answer.  What do you use for OR operator in formula? 

Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb

Posted 3 years ago

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

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
Can you write in engish words the logic that you would like to have?  Then I can provide the syntax.
Photo of Matthew Neil

Matthew Neil

  • 31,678 Points 20k badge 2x thumb
If( [Field 1]="Condition 1" AND [Field 2]="Condition 2", "If both true Response" , "If either or both false Rseponse" )

If( [Field 1]="Condition 1" OR [Field 1]="Condition 2", "If either condition is true" , "If neither are true" )
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
As this is not an english sentence, I still am not understanding what you are trying to say.

But to try to answer in a general sense, when an IF calculates, it just keeps evaluating the listed conditions until it either finds one that is true or it runs out of tests to try and a default "else" is provided. be sure to list your conditions in the order that they should be tested as the formula will take the first condition it finds as true.


For example
IF(
[The sky is polkadot]="yes" or [The ocean is red] = "yes", "we are not on earth",
[The sky is blue] = "yes" and [The ocean is dry] = "yes", We are not on earth",
[The sky is blue] = "yes" and [The ocean is wet] = "no", "We are not on earth",
[The sky is blue] = "yes" and [The ocean is wet] = "yes, "We are on earth", "we are not sure where we are")

The above would calculate to "we are on earth".

here is some help on IF.
https://www.quickbase.com/db/6ewwzuuj?a=dr&r=cm&rl=cqd

Feel free to post back if you are stuck.  Perhaps and example would help me understand your desired logic tests.
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Thanks Matt.  I two fields and I am testing both for various conditions which is as under:

If var category is equal to statement 1 and var status is equal to approved, then categorise the record as "BVR Approved".  If the variation status is still not approved, then call it "BVR Pending". Then I have a second set of condition using the same var category testing for statement 2 and whether or not var status is approved and these are called DVR Approved and DVR Pending.  I worked out the following formula which seems to be working but might run past and Mark.  

If(
[Var Category] = "Variation Claim By Builder" and [Variation Status] = "Approved", "BVR Approved",
If(
[Var Category] = "Variation Claim By Builder" and [Variation Status] <> "Approved", "BVR Pending",
If(
[Var Category] = "Change to Issued Drawing" and  [Variation Status] = "Approved By PCCG", "DVR Approved",
If(
[Var Category] = "Change to Issued Drawing" and  [Variation Status] <> "Approved By PCCG", "DVR Pending", ""))))
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
Arshad, sorry I got confused between your post and Matt chiming in.

Do not nest your IFs, that will just confuse you with the extra brackets.

IF(
[Var Category] = "Variation Claim By Builder" and [Variation Status] = "Approved", "BVR Approved",

[Var Category] = "Variation Claim By Builder" and [Variation Status] <> "Approved", "BVR Pending",

[Var Category] = "Change to Issued Drawing" and  [Variation Status] = "Approved By PCCG", "DVR Approved",

[Var Category] = "Change to Issued Drawing" and  [Variation Status] <> "Approved By PCCG", "DVR Pending")

Also you do not need to say that the alternative is null ie empty quotes as if your are silent about the else result,  it will be null anyways.
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Thanks a lot for your help.  I have amended as advised. Works fine.  Is there a good resource on IF formulae I can read?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,542 Points 50k badge 2x thumb
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Thanks a lot.