1

I hope someone can provide some insight as I've been struggling with a View. No previous question/answer on StackExchange seems to quite get at what I need. I've combed the web and reviewed the blog at WSS Essentials which helped, but I'm still struggling.

The blog made me realize I needed my OR fields first, followed by AND. But I have an additional OR within the AND statement which I cannot make work properly.

List1 that contains two Location fields fed from a lookup in List2.
When these locations are selected, the lookup also imports Email field from List2.
Additionally in List1 I have a Date field and a Flag field in List1 calculated off multiple List1 columns.

I want the View to show List1 records that meet this criteria:
(Location1 = x OR Location2 = x) AND (Date >= [TODAY] OR Flag = "Yes")

I can make the View work with a single AND field, but not both. See pic:
enter image description here

I unfortunately cannot make modifications with SharePoint Designer directly to the code/CAML which might solve my problem. I may have to change the location lookup fields to Choice and then just calculate a new Flag instead, but then I have to find a way to import the List2 Email field I need without users choosing it.

So, can I make this View work simply by entering field values into the SharePoint interface?

Thanks for any help you can provide.

1 Answer 1

1

Original answer below, but here is an option that doesn't require any custom CAML, but does require adding a Calculated column to your list, and then including that column in your filter.

Add a boolean (yes/no) Calculated column named something like IsXLocation with a formula of =OR([LocationField1]="x", [LocationField2]="x").

Now unfortunately, the [Today] value cannot be used in Calculated columns, otherwise we could solve your problem very easily by just creating a column that calculates your other OR condition, then you could AND the two fields in a filter -- in fact you could probably do the whole thing in a single Calculated column to then make a simple filter against that one column -- except for that comparison against the [Today] value.

However, as you mentioned in your question, if you build a filter in the User Interface with an OR between your first two criteria, and then an AND with the next, it has the affect of applying the AND to the grouped logical result of your OR. So, if you configure your view to say Show the items when the column FlagField1 is equal to TRUE OR DateField1 is greater than or equal to [TODAY] AND IsXLocation = TRUE, this has the effect of performing the calculation just through the UI-configured filter of doing (FlagField1=TRUE OR DateField1>=[TODAY]) AND IsXLocation = TRUE. By moving just one of your OR conditions into a pre-calculated field, you should be able to get the view that you want.


Original Answer:

While SharePoint view filters do support complex logical groupings, the user interface for configuring views does Not. The only way to AND together two groups of OR's is by setting the CAML directly.

If you cannot use SharePoint designer, you can also set the query CAML via PnP-PowerShell (or through one of the other SharePoint remote API's). In PnP-Powershell, you would set it like this:

$viewQuery = "<Where><And><Or><Eq><FieldRef Name='LocationField1'/><Value Type="Text">x</Value></Eq><Eq><FieldRef Name='LocationField2'/><Value Type="Text">x</Value></Eq></Or><Or><Geq><FieldRef Name="DateField1" /><Value Type="DateTime"><Today /></Value></Geq><Eq><FieldRef Name='FlagField1'/><Value Type="Boolean">1</Value></Eq></Or></And></Where>"

Add-PnPView -list 'YourListName' -Title "YourViewName" -Query $viewQuery

Make sure you replace my fake FieldNames with the actual internal names of your fields. You may want to start by calling Get-PnPView to see internal field names.

One big caveat, if you set the view's query CAML to something not capable of being rendered in the UI, then any user with rights to edit the list view structure can attempt to edit your view, and if they make any changes it will replace your CAML with what seems to appear in the UI.

3
  • Thanks @willman, that unfortunately confirms my research yet again.I'll have to consider other options.
    – fmcgraw
    Jan 26, 2020 at 19:16
  • I edited the answer with an alternative that doesn't use custom CAML. See if that helps.
    – willman
    Jan 26, 2020 at 21:13
  • Thanks @willman, I ended up doing what you suggested and then saw your edited response. Great minds think alike. ;-) The filter still isn't 100% the way I'd like to see it but getting location into a calculated column got me 80% there. - David
    – fmcgraw
    Feb 1, 2020 at 19:10

Your Answer

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

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