Nested If Formula Error: Expecting Boolean but found number

  • 0
  • 1
  • Question
  • Updated 4 months ago
  • Answered
  • (Edited)
Hey everybody!  Can someone help me with the below formula?  I get the "expecting Boolean but found number" error at the end of the FIRST If statement.  If i change it to NULL, then the 2nd IF statement turns yellow saying it expects a different outcome as well.  :(  

What this formula says is: 

  • TO Service Mgmt is the only Cost Center who can check the checkbox to override a field called "REG" (where the formula below lives). 
  • The REG formula defines if the employee worked 8 hours that day or not. 
  • The TO Service Mgmt Cost Center can work Sat/Sun and the rest of the CC's are Mon - Fri shifts, therefore, Sat & Sun should = 0. 


If(

[Cost Center Name] = "TO Service Mgmt" and [REG Override] = true, 8 - Sum([FHL Form],[PTO Form],[JRY Form],[SCK Form],[Time without Pay Form],[Unscheduled PTO Form],[FNL Form],[FMLA Form],[HOL Hours],[Other Form]), 0,

[Cost Center Name] <> "TO Service Mgmt" and [REG Override] = false and ([Day of Week] = "Sat" or [Day of Week] = "Sun"), 0,

Sum([FHL Form],[PTO Form],[JRY Form],[SCK Form],[Time without Pay Form],[Unscheduled PTO Form],[FNL Form],[FMLA Form],[HOL Hours],[Other Form]) > = 8, 0, 8 - Sum([FHL Form],[PTO Form],[JRY Form],[SCK Form],[Time without Pay Form],[Unscheduled PTO Form],[FNL Form],[FMLA Form],[HOL Hours],[Other Form])

)


When i comment out the 1st IF statement, the formula works, but i think i STILL  have something wrong with the formula b/c it automatically checks the REG Override box for all Days of Week for Mon - Fri for TO Service Mgmt Cost Center and none are checked for NON TO Service Mgmt Cost Center (which is an expected behavior).  

The expected behavior is for the person to check the box to override the REG value if they work random days throughout the week.  With that said, the box should always be unchecked unless specifically checked.

Hopefully that makes sense.

Thank you in advance!!
Photo of Jana

Jana

  • 324 Points 250 badge 2x thumb

Posted 6 months ago

  • 0
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
I'll try to help with the syntax, but the logic (what you need) is not crystal clear to me.

I think the IF statement logic is just a little mixed up.

When you have multiple if's together you don't need to provide the 'else' value in every line.

If(
question 1, true answer 1,
question 2, true answer 2, 
question 3, true answer 3,
answer if all are false)

currently it looks like you have an 'else' answer for every  question.

Basically you don't need the ", 0," after every line, but only at the end.

 Secondly, you need to write the statement from top to bottom.  i.e. Once it finds one correct answer it will use that, and not even check the rest.



Matthew Neil - Product Specialist
Photo of Jana

Jana

  • 324 Points 250 badge 2x thumb
Thanks Matt!  Basically the only change needed was to remove this part.  it wasn't needed.  It allows the user to toggle the Day of Week to 0 or 8 thus creating a virtual "weekend" for shift workers.  

[Cost Center Name] = "TO Service Mgmt" and [REG Override] = true, 8 - Sum([FHL Form],[PTO Form],[JRY Form],[SCK Form],[Time without Pay Form],[Unscheduled PTO Form],[FNL Form],[FMLA Form],[HOL Hours],[Other Form]), 0,
Photo of Ann Swelgin

Ann Swelgin

  • 382 Points 250 badge 2x thumb
I am also getting this error.. The 1 in the formula is highlighted and it says "expecting boolean but found number":

If (Contains([Activity Type], "PDFA"), Contains([Activity Type], "PDAFA"), 1, null)

and based on the comments above I assume my null should be a 0?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,006 Points 50k badge 2x thumb
Aside from the syntax error, it is an odd looking formula, can you explain in words what the logic is trying to do?
Photo of Ann Swelgin

Ann Swelgin

  • 382 Points 250 badge 2x thumb
I believe it would be true/false, so whether it has either of these activity types or not.

This is the original formula:
If(Contains([Activity Type],
"PDFA",1,
"PDAFA",1,
null)

I found this report someone else had created a few years ago and I am still learning what this table actually does.. I'll do some deeper digging and see if I can fix on my own or ask again. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,006 Points 50k badge 2x thumb
Ok. Now I understand

Contains([Activity Type], “PDFA")
OR
Contains([Activity Type],”PDAFA")
Photo of Ann Swelgin

Ann Swelgin

  • 382 Points 250 badge 2x thumb
Yep but when I put the OR in instead of the comma it give me another error
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,006 Points 50k badge 2x thumb
Can you post your exact formula and the error message. I’m not able to see any syntax errors in my formula.

Note that my formula was meant to replace yours.
Photo of Adam Keever

Adam Keever

  • 996 Points 500 badge 2x thumb
I had a similar case where after adding an 'OR' condition the formula error checker told me it was expecting a comma, but I saved the field and when I went back in to evaluate it the error was gone and the formula was working fine.

After starting to write this response I thought I would check again and sure enough by going back into the same formula and overwriting 'or' with 'OR' i get the same error stating it is expecting a comma. When I saved it the first time it changed the CAPS version of 'OR' to lowercase 'or' and worked fine. Seems like a bug.