0

I've got a 2010 SP list that tracks tickets about an application I own. It tracks various ticket attributes including'status' and 'type'. I want to create a view of 'open' tickets for my developers based on the value of 'status' and/or 'type'. There are enough options for these fields that I've run out of filter options on the view. So, I've added a calculated-column, boolean field, call it 'DevOpen', to the InfoPath form. I've written a formula to determine this fields value as follows;

not((Status = "A") or (Status = "B") or (Type = "1"))

where the bold text are inserted fields and status A and B and type 1 are associated with 'closed' tickets.

When I open the form, the value of the Boolean field value is correct...either true or false...according to the formula.

My problems are two-fold. First when I add this new field to a view, the values are wrong. There are several variants; the InfoPath form properly shows 'true' but the view shows 'No', the InfoPath form rightly shows 'false' and the view shows nothing/blank, or the InfoPath form shows 'true' and view shows nothing/blank.

Secondly and likely related to the first point, when I try to use this new field to filter a view it doesn't work. Again, I'm trying to get a view of open tickets. I've tried all variants of the filter criteria (Yes/yes/true/True/etc. and No/no/False/false/etc.). Nothing seems to work properly although if I set the 'DevOpen' field filter to 'No' I do get tickets shown but they're not the ones I want. I want DevOpen=true/yes tickets to be shown.

What am I doing wrong?

Improve this question

1 Answer 1

Reset to default
0

SharePoint Formulas can not do operators like A OR B

SharePoint has logical functions

So the syntax for a Calculated Column Formula is:

NOT( OR(  OR([Status]="A",[Status]="B")  ,  [Type]="1"  ) )
Improve this answer
3
  • Thanks for the reply, Danny, but I'm confused. The simplified version of my formula is below and shows as valid when I use the 'Verify formula' button... not((Status = "A") or (Status = "B") or (Status = "C") or (Status = "D") or (Status = "E") or (Status = "F") or (Status = "G") or (Status = "H") or (Status = "J") or (Issue Type = "1") or (Status = "K") or (Issue Type = "2") or (Issue Type = "3")) I guess I'd expect an error to be thrown if this isn't valid. Also, when I look at the field at the bottom of InfoPath form, the calculated value is correct given the status/issue type.
    – BruinsFanVA
    Sep 8, 2015 at 18:38
  • I think you are mixing InfoPath notation with SharePoint notation. In SharePoint OR( ) is a function (with maximum of 30 parameters). In InfoPath your notation with the operator OR works fine.
    – Danny '365CSI' Engelman
    Sep 9, 2015 at 19:58
  • OK, that explains why my InfoPath notation works. Unfortunately, I'm still lost as to why this new calculated field carries the proper value when viewing it from the InfoPath form but when I look at it in the SP view it sometimes has the right value, sometimes the wrong value and sometimes no value at all. Further, every time a value is shown in the view, the value is 'No' regardless. On the InfoPath form, the values are either 'true' or 'false'. What's going on with the view? I can maybe understand the 'false' value showing in the view as 'No' but not why the 'true' values aren't shown.
    – BruinsFanVA
    Sep 15, 2015 at 19:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.