Complex Queries in OOB SharePoint List View
Many a times during SharePoint development, we come across the problem of a SharePoint list view with a very complex query. This is a problem, as the OOB SharePoint view settings page, does not allow the flexibility of applying brackets etc to our view ( or even specifying the CAML directly! )
To sum up the problem, I want a view of SharePoint items where in columns
( A > [Today] AND B is empty ) OR
( C > [Today] AND D is empty ) OR
( E > [Today] AND F is empty ) etc etc
Now there are couple of ways to do so....
Create three calculated columns which will essentially store the result of each of the lines above, ie if ( A > [Today] AND B is empty ) then my calculated column ABColCalCulation = true.
Post that, create a new view with the values of at least one of these column to be true.
Postivies: OOB, not much work
Negatvies: didn't really like the idea of creating 3 new fields and that too calculated ones.
2. Custom view C# or JS
Write your own custom view/ page to show the content. Easy in C# and even easier with Angular and Rest
Positives: Straight Forward and easy to write, sort of first instinct of a developer.
Negatvies: Custom Development, Plus would have to implement the ECB functionality also. That's a bit of a problem
3. Make use of Existing view
This is the approach i went with. Created a new view on the list by copying existing view, which looked and felt like my new view, with all columns and groupings etc.
With the help of CAML Query Builder and some RnD wrote the actually CAML Query for this filtration. You can copy paste this query into CAML Query Builder and test, if the results are as accepted.
Post that, via powershell, opened the view and set the query parameter and viola it works...
$web = Get-Spweb https://akki.test/sites/test/
$myList = $web.Lists["My List"]
$viewName = "MyNewView"
$query = "<Where><Or><Or><And><Gt><FieldRef Name='Due_x0020_date_1' /><Value Type='DateTime'><Today /></Value></Gt><IsNull><FieldRef Name='Checked_x0020_by_1' /></IsNull></And><And><Gt><FieldRef Name='Due_x0020_date_2' /><Value Type='DateTime'><Today /></Value></Gt><IsNull><FieldRef Name='Checked_x0020_by_2' /></IsNull></And></Or><And><Gt><FieldRef Name='Due_x0020_date_3' /><Value Type='DateTime'><Today /></Value></Gt><IsNull><FieldRef Name='Checked_x0020_by_3' /></IsNull></And></Or></Where>"
$myListView = $myList.Views[$viewName]
$myListView.Query = $query
$myListView.DefaultView = $False
$myList.OnQuickLaunch = $True
Positives: No custom coding, no new fields etc etc In nutshell, if you can express it in CAML, you can use any existing view and modify its query parameter to show the required data .
Negatives: You cant make any changes to the View Settings page post this. This is because, every time you make any changes ( irrespective of what change you make,like adding a new column to the view), the Query parameter is overridden by SharePoint.
I feel there should be a section in the view page wherein the admins can specify the bare CAML Query, hope MS is listening ...