Highlight row with different colors when multiple conditions are met.

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

I am trying to highlight rows in a report to indicate whether a record is complete (green) or not (yellow). This is based on multiple check boxes and a drop-down menu. I am either getting "need closing parenthesis" of "Syntax Error". Below is where I am at, right now. Any help would be appreciated.



 If(
[Type]="Both" and not IsNull ([BAM Approved]) and not IsNull ([Engineering Approved]) and not IsNull ([Manufacturing Approved]) and not IsNull ([Purchasing Approved]) and not IsNull ([Quality Approved])), "#8FBC8F",

([Type]="Both" and IsNull ([BAM Approved]) or IsNull ([Engineering Approved]) or IsNull ([Manufacturing Approved]) and IsNull ([Purchasing Approved]) or IsNull ([Quality Approved])),"#FFD700",

([Type]="Change of Work Instructions" and not IsNull ([BAM Approved]) and not IsNull ([Engineering Approved]) and not IsNull ([Manufacturing Approved]) and not IsNull ([Purchasing Approved]) and not IsNull ([Quality Approved])), "#8FBC8F",

([Type]="Change of Work Instructions" and IsNull ([BAM Approved]) and IsNull ([Engineering Approved]) and IsNull ([Manufacturing Approved]) and IsNull ([Purchasing Approved]) and IsNull ([Quality Approved])),"#FFD700")


Photo of Dan

Dan

  • 0 Points

Posted 4 years ago

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

QuickBaseCoach App Dev./Training, Champion

  • 65,310 Points 50k badge 2x thumb
try this version


If(
[Type]="Both" and not IsNull ([BAM Approved]) and not IsNull ([Engineering Approved]) and not IsNull ([Manufacturing Approved]) and not IsNull ([Purchasing Approved]) and not IsNull ([Quality Approved]), "#8FBC8F",

[Type]="Both" and IsNull ([BAM Approved]) or IsNull ([Engineering Approved]) or IsNull ([Manufacturing Approved]) and IsNull ([Purchasing Approved]) or IsNull ([Quality Approved]),"#FFD700",

[Type]="Change of Work Instructions" and not IsNull ([BAM Approved]) and not IsNull ([Engineering Approved]) and not IsNull ([Manufacturing Approved]) and not IsNull ([Purchasing Approved]) and not IsNull ([Quality Approved]), "#8FBC8F",

[Type]="Change of Work Instructions" and IsNull ([BAM Approved]) and IsNull ([Engineering Approved]) and IsNull ([Manufacturing Approved]) and IsNull ([Purchasing Approved]) and IsNull ([Quality Approved])),"#FFD700")
Photo of Dan

Dan

  • 0 Points
Thank you for the fast reply. I did try your version. The green showed up but didn't change when boxes were unchecked.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,310 Points 50k badge 2x thumb
What field types are your approval fields?  Are they text or checkboxes?  If they are text fields, then you cannot test them for being blank with IsNull.  You need to test them being blank by testing like [BAM Approved]=""
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,310 Points 50k badge 2x thumb
.. or [BAM Approved]<>""   ( for not equal to blank)
Photo of Dan

Dan

  • 0 Points
The [BAM Approved], [Engineering Approved], etc. and are checkboxes and "Type" is multiple choice text.
Photo of Dan

Dan

  • 0 Points
I think I found my mistake.
Photo of Dan

Dan

  • 0 Points
Nope I was wrong.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,310 Points 50k badge 2x thumb
Do not test checkboxes with isNull.  They are inherently already true or false.

If(
[Type]="Both" and [BAM Approved] and [Engineering Approved] and [Manufacturing Approved] and  [Purchasing Approved] and [Quality Approved], "#8FBC8F",

[Type]="Both" and not [BAM Approved] or not [Engineering Approved] or not [Manufacturing Approved] and not [Purchasing Approved]) or not [Quality Approved],"#FFD700",

[Type]="Change of Work Instructions" and [BAM Approved] and [Engineering Approved] and [Manufacturing Approved] and [Purchasing Approved] and [Quality Approved], "#8FBC8F",

[Type]="Change of Work Instructions" and not [BAM Approved] and not [Engineering Approved] and not [Manufacturing Approved] and not[Purchasing Approved] and not [Quality Approved],"#FFD700")
Photo of Dan

Dan

  • 0 Points
That makes sense. I do appreciate all the help.
I entered the formula you provided and it shows:

"There are extra characters beyond the end of the formula."
It shows the error at the first   ,"#FFD700",
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,310 Points 50k badge 2x thumb
There is an extra closing bracket in my post after
and not [Purchasing Approved])

should just be
and not [Purchasing Approved]
Photo of Dan

Dan

  • 0 Points
Mark, you are awesome!! Thank you very much!!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,310 Points 50k badge 2x thumb
Great, thx for letting me know.