Forum Discussion

JanaBaker's avatar
JanaBaker
Qrew Cadet
7 years ago

Nested If Formula Error: Expecting Boolean but found number

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

11 Replies

  • MCFNeil's avatar
    MCFNeil
    Qrew Captain
    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
    • JanaBaker's avatar
      JanaBaker
      Qrew Cadet
      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?
    • AnnSwelgin's avatar
      AnnSwelgin
      Qrew Trainee
      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's avatar
      QuickBaseCoachD
      Qrew Captain
      Is the result to be s number or a true /false result?

      Can you explain the he logic for when it should �count�?
    • AnnSwelgin's avatar
      AnnSwelgin
      Qrew Trainee
      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.