Referencing multiple field for a formula

  • 0
  • 1
  • Question
  • Updated 8 months ago
  • Answered

I’m trying to get a formula checkbox or text field with a message message to display when a condition isn’t met. I don’t know if what I want is not possible of I just can’t figure out how to input the formula correctly? Basically I’m looking for either the check box to be selected or a message to display to signify if I need to start another step in a process.

 

I have multiple Text-Multiple Choice fields, 1 Text field, and 1 Formula-Currency I want to reference. I know that Excel would be able to do this with “AND”/”OR” formulas but I cant figure this one out in QuickBase. Any help you can provide would be appreciated.

 

·         Text-Multiple Choice Field

o   Grievance Type

o   Level 1 Company Decision

o   Level 1 IAM Decision

o   Level 1 TWU Decision

o   Level 2 Company Decision

o   Level 2 TWU Decision

·         Text Field

o   Labor Summary or Settlement Note

·         Formula Currency Field

o   Amount Paid Out

 

This is what is was thinking.....   If “grievance type” is equal to “Overtime Bypass” or “Mandatory Overtime” or “Payroll” or “Termination” and “level 1 Company Decision” is equal to “Accept” and “Level 1 IAM Decision” is equal to “Accept” or “level 1 Company Decision” is equal to “Accept” and “Level 1 TWU Decision” is equal to “Accept” or “level 2 Company Decision” is equal to “Accept” and “Level 2 TWU Decision” is equal to “Accept” or “Labor Summary or Settlement Note” contains the word “Pay” and  “Amount Paid Out” is equal to zero, then display the message of check then box.
Photo of Brian

Brian

  • 200 Points 100 badge 2x thumb

Posted 8 months ago

  • 0
  • 1
I will do some of your formula for you. This would be a formula check box field type.  You would need to create a separate field to calculate a message based on this checkbox.

 It's a lot of typing and I'm not that fast :)

Note that Quick Base quote always look like this  "  and not the curly kind in your post. Just in case I missed any

Your logic statement was basically just a RunOnSentenceWithVery LittlePunctuation, so I'm kinda guessing how the ANDs and ORs and brackets should go. 


 If(
([grievance type] = "Overtime Bypass"
or
 [grievance type]="Mandatory Overtime"
or
 [grievance type]="Payroll"
or
 [grievance type] = "Termination")

and
(
([level 1 Company Decision] = "Accept"
and
[Level 1 IAM Decision] = "Accept") 

or
([level 1 Company Decision] = "Accept"
and [Level 1 TWU Decision] = "Accept") 

or
([level 2 Company Decision] = "Accept”
and
([Level 2 TWU Decision] = "Accept" 
 or
[Level 2 TWU Decision]  = "Labor Summary")
)

and

Contains([Settlement Note", "Pay") 
and 
[Amount Paid Out] =0,
true)



if there is a syntax error in the above that you cannot figure out, be sure to copy and paste post your exact formula and the complete error message (ie not a screen shot, but a copy and paste).
Photo of Brian

Brian

  • 200 Points 100 badge 2x thumb
Thanks for your help. I am receiving a Formula syntax error that i have below.  

A closing parenthesis is missing.


If(
([grievance type]="Overtime Bypass" or [grievance type]="Mandatory Overtime" or [grievance type]="Payroll" or [grievance type]="Termination") 
and (
([level 1 Company Decision]="Accept" and [Level 1 IAM Decision] ="Accept") or ([level 1 Company Decision]="Accept" and [Level 1 TWU Decision]="Accept") or ([level 2 Company Decision]="Accept” and [Level 2 TWU decision] ="  Accept")) 
and Contains([Labor Summary or Settlement Note], "Pay") and [Amount Paid Out]=0, true)

It was actually one of the curly quotes acting up, just as Mark predicted

This should work

If(
([grievance type]="Overtime Bypass" or [grievance type]="Mandatory Overtime" or [grievance type]="Payroll" or [grievance type]="Termination") 
and (
([level 1 Company Decision]="Accept" and [Level 1 IAM Decision] ="Accept") or 
([level 1 Company Decision]="Accept" and [Level 1 TWU Decision]="Accept") or 
([level 2 Company Decision]="Accept" and [Level 2 TWU decision] ="Accept")
)
and Contains([Labor Summary or Settlement Note], "Pay") and [Amount Paid Out]=0, true)
(Edited)
Photo of Brian

Brian

  • 200 Points 100 badge 2x thumb
Thanks for your help. Its working now