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!!

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.

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,

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?

Aside from the syntax error, it is an odd looking formula, can you explain in words what the logic is trying to do?

It is within a bar chart report. basically if the activity type is either of those it would count 1 and if not those type then it doesn't count it.

Nested If Formula Error: Expecting Boolean but found number

11 Replies

MCFNeil
Qrew Captain
8 years ago
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.
- JanaBaker
  Qrew Cadet
  8 years ago
  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,
AnnSwelgin
Qrew Trainee
8 years ago
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?
QuickBaseCoachD
Qrew Captain
8 years ago
Aside from the syntax error, it is an odd looking formula, can you explain in words what the logic is trying to do?
- AnnSwelgin
  Qrew Trainee
  8 years ago
  It is within a bar chart report. basically if the activity type is either of those it would count 1 and if not those type then it doesn't count it.
- QuickBaseCoachD
  Qrew Captain
  8 years ago
  Is the result to be s number or a true /false result?
  
  Can you explain the he logic for when it should �count�?
- AnnSwelgin
  Qrew Trainee
  8 years ago
  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.

Forum Discussion

Nested If Formula Error: Expecting Boolean but found number

11 Replies

Related Content

Empower exceeded all expectations!

Formula Not Functioning as Expected

syntax help with formula

Formula Date Field

Formula Url Buttons