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:

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.

No comments:

Post a Comment