How to fix 5000 Limit for Views but without breaking Alerts

Question

So recently I saw the infamous message about getting close to the 5,000 item limit. After spending a couple of days doing some testing, it seemed like the best option would be to simply Index the Modified date and add an additional filter to all my views of "Modified >= [Today]-180"

Basically it takes a bit over 6 months to add 5,000 records. This seemed to do the trick until another Microsoft 'feature' bit me yet again.

Apparently, although [Today] is supported as a valid filter for Views, it makes the view ineligible for Alerts the moment you try to do any math on it.

By ineligible, I mean that when a user tries to sign up for specific view, there is a drop down that shows the views associated with the particular list...

Modified (Indexed) <= [Today]      --- This will show up in the Drop Down
Modified (Indexed) <= [Today]-90   --- This will NOT

At this point all I can think of is to fall back on the same method I have done for all of the SharePoint shortfalls I have come across... running scheduled PowerShell scripts.

All I can think of is to create a Hidden Yes\No field for all my list called "ExceedsViewThreshHold". Then Index on that column and change all my view so that the 1st filtered field is ExceedsViewThreshHold = No.

Then Daily I would have to run a PowerShell script that would go through every item in every list and look at it's last modified time. If it exceeds over 6 months.. set the value of that field to "Yes".

I believe it shouldn't be too bad only because I can create a CAML query in PowerShell to pull only those records who's value is currently set to "NO" and who'd Modified Date is Older than 6 months.... I shouldn't get to many results especially after the initial run.

Anyone have any better ideas? Options?

Answer 1

Man I thought I was onto something big.... my comment above mentions that the Expired date for an Announcement Library would work except for the fact that it only gets set when the item is first created.

However, if I follow that same logic but as a Calculated Column (not a Calculated Default Value) I can set it to return a "Date and Time" Value. Then I can set the formula to =Modified+180

Anytime a record is then updated, it 'resets' this field. The view then simple checks on that field being > [Today]

The Good News: By doing the "math" in the record and not in the filter the view still shows up when I try to sign up for alerts.

The Bad News: Again a freaking dead end. Calculated columns can not be Indexed on. Without the Index, it defeats the entire purpose and now I am back to my views breaking at 5,000 items!!

Answer 2

Ok all... after going down all sorts of paths yesterday, I have a solution that I'd thought I'd add in case it helps anyone else.

Actually I think this is pretty simple. I use Powershell scripts that run via the Windows task Schedule to overcome and do many things in SharePoint. So for me, the TRUE solution to never running into the 5,000 limit on any list is to simply make sure that you will never have more than 5,000.

The steps are easy...

• Create a Yes/No Field in each list called "ExceedsViewThreshold"
• Have it's Default value = 'No'
• Add it to the Indexed Columns
• Have all views set so that the 1st Filtered Column is "ExceedsViewThreshold NOT equal Yes" -- This is to account for the No and NULL values
• Hide the field from the Add\Edit forms so user's can't change it (this is optional but nice)

Then this is the PowerShell script.

    # ****************************************************************************************************************
#   This script goes through the specified List(s) and updates the Threshold flag field
#   for records that exceed the limit. 
#
#   The Records are determined via the Last Modified\Update date (or any date field you pass). 
#   The Script starts with the oldest Records and updates the Flag field until the remaining 
#   count is less than the Threshold limit (with some extra padding).
#
#   Requirements: 
#                  1) The list must have a "yes/no" Field called "ExceedsViewThreshold"
#
#                  2) The "ExceedsViewThreshold" default must be set to 'NO'
#
#                  3) The "ExceedsViewThreshold" must be INDEXED
#
#                  4) All views must have the 1st Filtered Field set to 'ExceedsViewThreshold NOT equal to YES' (to account for NULL values)
#
#                  5) The "ExceedsViewThreshold" field should be hidden from the users (at least on the New\Edit forms) so they can't change it    
#
# ****************************************************************************************************************

# Add the SharePoint Assembly
Add-PSSnapin Microsoft.SharePoint.Powershell
Clear-Host

#Get our Web Site
$global:myWebSite = Get-SPWeb "http://SharePointServer/sites/Demo/"
$global:myPaddedThreshold = 4500


#----------------------------------------------------------------------------
# The MAIN Function for each list that needs the Threshold Flag enforced
function UpdateList([string]$myListName, [string]$myDateFieldName)
{
    # Concept is Simple, we pull a list of ALL items that do not already have this flag set
    # in the order of Oldest to Newest. Then we simply update each until we have less than the Threshold
    Write-Host ("Using Field [" + $myDateFieldName + "] to update the Threshold flag for List [" + $myListName + "]")
    $myList = $global:myWebSite.Lists[$myListName]
    Write-Host ("`t List has [" + $myList.ItemCount + "] TOTAL items")


    # Now pull just the Items that are NOT already Flagged. 
    # NOTE: We MUST pull them in the order of Oldest to Newest
    Write-Host ("`t Determining Threshold Count Status...")
    $myQuery = New-Object Microsoft.SharePoint.SPQuery
    $myQuery.Query = "<Where>
            <Neq> 
              <FieldRef Name='ExceedsViewThreshold' />
              <Value Type='Integer'>1</Value> 
            </Neq> 
              </Where>
              <OrderBy>
             <FieldRef Name='$myDateFieldName' Ascending='True' />
              </OrderBy>
              "    
    $myPotentialItems = $myList.GetItems($myQuery)

    #Do some math and report our findings
    $myLimitExceededBy = $myPotentialItems.Count - $global:myPaddedThreshold
    if ($myLimitExceededBy -le 0)
    {
    Write-Host ("`t Padded Threshold Limit of [" + $global:myPaddedThreshold + "] under by [" + $myLimitExceededBy + "] items... ** No Changes Will Be Made **")
    return
    }
    else
    {
    Write-Host ("`t Padded Threshold Limit of [" + $global:myPaddedThreshold + "] exceeded by [" + $myLimitExceededBy + "] items!")
    }

    #Now this part is easy
    foreach ($myItem in $myPotentialItems)
    {
    #See if we are done before we run out of items
    if ($myLimitExceededBy -le 0)
    {
        break
    }

    Write-Host ("`t `t Updating: " + $myItem["Title"])
    $myItem["ExceedsViewThreshold"] = $true
    $myItem.SystemUpdate($false)      # This is a special update that does not trigger Alerts
    $myLimitExceededBy--
    }

    #And finally tell the list to update
    $myList.Update()

    return

}


#----------------------------------------------------------------------------
# Powershell actuall starts running here
UpdateList -myListName "myViewLimitTest" -myDateFieldName "Expires"




#Dispose of our WebSite Object to clean up
$global:myWebSite.Dispose()