2016-10-08

SharePoint lists & complex views

How to make SharePoint bugs work to your advantage.

I do not like to trick software to do what I want or create complex solutions, but sometimes there is no other way. This time I had to both trick and be somewhat complex. We hit one SharePoint pain point, the dreaded 5000 limit, no matter what the SP sales guys say you have a situation when your list goes above 5000 items.

We have a list with historical, current and future items. The list have a ValidFrom and a ValidTo date items. Each change in an item closes the current item and creates a new one with a blank ValidTo. Not only does this in itself create many new items, it makes it super complex to create SP “time slot views” on the list, as you must add an OR condition; ValidTo equals space ([ValidTo]=""). We now have +5000 current items, we need to divide them into smaller views so we can access them. In a programming language you express this with a boolean condition like:
FieldA=’X’ and ValidFrom >= [Today] and (ValidTo<[Today] or ValidTo=” “)

Note the parenthesis they determine the order of evaluation, so you can with great precision tell the result of an expression.
In user friendly sharePoint you point and click your view filter like:


After many mature words, trials and errors I came to the conclusion SP view filter evaluates from top with next expression below, one expression at the time. The filter above which was used in production is almost correct. This is how it should look like:


First thing we realised when we passed 5000 items, you cannot index a column since the list is too big and filter fields must be indexed if you have more than 5000 items in a list. I bet your SP sales representative didn’t tell you that. We deleted items and indexed the fields and then started to apply filters, but how much we tried we could not make any filter work 100% correct, I suspect there is a bug in SP showing when you have supermany items in a list (+5000), but I’m not sure, I can have missed something when I tested the filter. Anyway the needed filters are beyond the obvious, during my failed attempts to create a valid filter I stumbled upon a post describing a clever trick. I had already tried to create a computed boolean field with this formula:
=OR([ValidTo]>=[Today],[ValidTo]="")  


But SP told me I could not use [Today] in a computed field! Why, why in Gods name do anyone implement such a stupid limitation? At this point I started to feel it was impossible to to create a working filter on the list. But then I saw this trick, first create a field named Today, then create the computed field with the formula, now referencing a field named Today, and now comes the stunning final remove the field Today. I said to myself this can’t possibly work, first Today should be a reserved keyword, second SP should say ‘Today is ambiguous’ when I try to reference it and finally SP should refuse to remove the field Today as it is used in computed formulas.
But lo and behold, it was possible to create the field Today. However SP refused to create a computed field in a list with more than 5000 items. #¤%&@ SharePoint! We had to remove the excess items again then define the computed field:


Here you see the calculated ValidToOK field, based on other columns. Finally I removed the Today field. This worked and SharePoint still says it is based on other columns (i.e. SP field, column and field is used interchangeably in SP litterature, I suppose nobody cares). SP still thinks ValidToOK is based on other columns, but Today  now evaluates to  current date! This view filter not only works correct, it is clear and simple to understand:


It works on super large lists +5000 items too, Big Data here we come:)
I have lost the link to the post showing the 'Today trick' so I cannot credit the inventor.

The bad thing; I do not know how many bugs in SP exposed and used in this post, but there are quite a few. The really bad thing; this trick can cease to work with next SP update. I will cross that bridge when I come to it.

2016-10-03

The dreaded 5000 limit

Today I hit the dreaded 5000 limit in SharePoint.
This view cannot be displayed because it exceeds the list view threshold (5000 items) enforced by the administrator.

To view items, try selecting another view or creating a new view. If you do not have sufficient permissions to create views for this list, ask your administrator to modify the view so that it conforms to the list view threshold.

Learn about creating views for large lists.


This means you can not access the list FULLSTOP.

Do not believe the experts when they tell you 5000 items is not a problem, that you can have 32 million
items in a list, that you just have to add a view filtering out less than 5000 items. They do not tell you upfront, you need to have an index supporting the filter in place before you hit the 5000 limit. That they tell you when you have hit the ceiling. Filters can only be latched on indexed fields when the list is larger than 5000 items. And you cannot create an index on a list with more than 5000 items. Catch 22. A black hole storage. If I can't access the items I do not care if can store 32 zillon items in a list.
How can anyone the year 2016 have a product out on the market that cannot handle more than 5000 items. SharePoint is a work in progress and that is very diplomatic.
I will certainly publish a comment from a (proud) SharePoint developer designer defending the 5000 limit. Come on; tell us why 5000 items is a good limit for SharePoint in this day and age of IoT. Right now I can use some soothing words to lower my blood pressure down.

I Found the answer myself, this is reason for the 5000 limit


If you do not care or understand what you are writing this is probably a good answer.