Discussions

 View Only
Expand all | Collapse all

Nested If Formula Error: Expecting Boolean but found number

  • 1.  Nested If Formula Error: Expecting Boolean but found number

    Posted 05-04-2018 17:59
    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!!


  • 2.  RE: Nested If Formula Error: Expecting Boolean but found number

    Posted 05-04-2018 21:52
    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.





  • 3.  RE: Nested If Formula Error: Expecting Boolean but found number

    Posted 05-06-2018 17:06
    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,


  • 4.  RE: Nested If Formula Error: Expecting Boolean but found number

    Posted 05-24-2018 20:49
    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?


  • 5.  RE: Nested If Formula Error: Expecting Boolean but found number

    Posted 05-24-2018 20:55
    Aside from the syntax error, it is an odd looking formula, can you explain in words what the logic is trying to do?


  • 6.  RE: Nested If Formula Error: Expecting Boolean but found number

    Posted 05-24-2018 21:15
    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.


  • 7.  RE: Nested If Formula Error: Expecting Boolean but found number

    Posted 05-24-2018 21:33
    Is the result to be s number or a true /false result?

    Can you explain the he logic for when it should �count�?


  • 8.  RE: Nested If Formula Error: Expecting Boolean but found number

    Posted 05-24-2018 21:59
    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. 


  • 9.  RE: Nested If Formula Error: Expecting Boolean but found number

    Posted 05-24-2018 22:02
    Ok. Now I understand

    Contains([Activity Type], �PDFA")
    OR
    Contains([Activity Type],�PDAFA")


  • 10.  RE: Nested If Formula Error: Expecting Boolean but found number

    Posted 05-24-2018 22:28
    Yep but when I put the OR in instead of the comma it give me another error


  • 11.  RE: Nested If Formula Error: Expecting Boolean but found number

    Posted 05-25-2018 02:37
    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.


  • 12.  RE: Nested If Formula Error: Expecting Boolean but found number

    Posted 07-02-2018 17:44
    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.