Filtering and Sorting SharePoint List Views

Built-in URL Filtering

SharePoint’s list view controls (XsltListViewWebPart, ListViewWebPart, etc) recognize a number of parameters from the query string that can be used to filter and sort automatically a list view, without requiring any additional effort.

Filter by single value:

http://server/Lists/Tasks/AllItems.aspx?FilterField1=PercentComplete&FilterValue1=.5

FilterField must be an internal field’s name and FilterValue is case insensitive.

Filter by single value and an operator:

http://server/Lists/Tasks/AllItems.aspx?FilterField1=PercentComplete&FilterValue1=.5&FilterOp1=Neq

The list of possible operators (FilterOp) is:

• Eq: equal (default);
• Neq: not equal;
• Gt: greater than;
• Lt: less than;
• Geq: greater or equal to;
• Leq: less or equal to;
• BeginsWith: the start of a string;
• Contains: part of a string.

You can add up to 10 FilterField/FilterValue/FilterOp entries, the items must match all conditions (AND operator). If you wish to filter on an additional field of a lookup field, use this syntax:

http://server/Lists/Tasks/AllItems.aspx?FilterField1=LookupField_x003a_AdditionalField&FilterValue1=1

Filter by one of multiple exact values:

http://server/Lists/Tasks/AllItems.aspx?FilterName=PercentComplete&FilterMultiValue=.5;1

FilterName is also the internal name of the field and the values in FilterMultiValue are treated as case-insensitive. Separate then by ;.

Filter by one of multiple partial values:

http://server/Lists/Tasks/AllItems.aspx?FilterName=Author&FilterMultiValue=*Peres*;*Ricardo*

http://server/Lists/Tasks/AllItems.aspx?FilterName=Author&FilterMultiValue=Ricardo*;*Peres

The * character acts as a wildcard, matching anything before or after. Placing a term between * is the same as using FilterField/FilterValue with the Contains operator in FilterOp.

Filter by a taxonomy field:

If you want to filter by a managed metadata field, you can either search by the display name using FilterField and FilterValue, or you can use the ids of the terms. A little known fact is that all usages of managed metadata values – values of fields of managed metadata type – are stored in the TaxonomyHiddenList hidden list. If you want to filter by an id of a term, you need to use the following syntax (search by one of two values with FilterOp=In, values separated by commas):

http://server/Lists/MyListWithTaxonomyField/AllItems.aspx?FilterField1=MetadataField&FilterLookupId=1&FilterValue1=1,2&FilterOp1=In

Or by a single value (no need for FilterOp):

http://server/Lists/MyListWithTaxonomyField/AllItems.aspx?FilterField1=MetadataField&FilterLookupId=1&FilterValue1=1

The FilterLookupId=1 is only there to tell SharePoint that it needs to search not by value (FilterValue) passed in the query string, but by term pointed by it. You can find the ids for FilterValue in the TaxonomyHiddenList list.

Sorting:

http://server/Lists/Tasks/AllItems.aspx?SortField=PercentComplete&SortDir=Desc

Possible values for SortDir are pretty obvious:

• Asc: ascending (default);
• Desc: descending.

You can only sort by a single field.

Column Names in UserInformationList

{
    //etc..
    "Title": "Sanjay Chauhan",
    "Account": "dm\\sanjayc",
    "WorkEMail": "sanjay.chauhan@domain.com",
    "MobilePhone": null,
    "AboutMe": null,
    "SIPAddress": "sanjay.chauhan@domain.com",    "IsSiteAdmin": true,
    "Deleted": false,
    "Picture": null,
    "Department": "3.14",
    "Title0": "Programmer Analyst",
    "FirstName": null,
    "LastName": null,
    "WorkPhone": null,
    "UserName": "rkauche",
    "WebSite": null,
    "AskMeAbout": "SQL Server, Windows Server, Silverlight, C#, PowerShell",
    "Office": null,
    "Id": 1,
    "ContentType": "Person",
    //etc...
}

SharePoint Web Serivces

WSS Web Services	Web Reference
Administration Service	http://<server-url:port-number>/_vti_adm/admin.asmx
Alerts Service	http://<server-url>/_vti_bin/alerts.asmx
Document Workspace Service	http://<server-url>/_vti_bin/dws.asmx
Forms Service	http://<server-url>/_vti_bin/forms.asmx
Imaging Service	http://<server-url>/_vti_bin/imaging.asmx
List Data Retrieval Service	http://<server-url>/_vti_bin/dspsts.asmx
Lists Service	http://<server-url>/_vti_bin/lists.asmx
Meetings Service	http://<server-url>/_vti_bin/meetings.asmx
Permissions Service	http://<server-url>/_vti_bin/permissions.asmx
Site Data Service	http://<server-url>/_vti_bin/sitedata.asmx
Site Service	http://<server-url>/_vti_bin/sites.asmx
Users and Groups Service	http://<server-url>/_vti_bin/usergroup.asmx
Versions Service	http://<server-url>/_vti_bin/versions.asmx
Views Service	http://<server-url>/_vti_bin/views.asmx
Web Part Pages Service	http://<server-url>/_vti_bin/webpartpages.asmx
Webs Service	http://<server-url>/_vti_bin/webs.asmx
Area Service	http://<server-url>/_vti_bin/areaservice.asmx
Query Service	http://<server-url>/_vti_bin/search.asmx
User Profile Service	http://<server-url>/_vti_bin/userprofileservice.asmx
SPS Crawl Service	http://<server-url>/_vti_bin/spscrawl.asmx
Outlook Adapter Service	http://<server-url>/_vti_bin/outlookadapter.asmx

WSS Web Services	Description
Administration Service	This Web service provides administrative capabilities like creating a new top-level site, deleting a top-level site and getting the list of available languages.
Alerts Service	Provides access to the list of active alerts and allows to delete active alerts.
Document Workspace Service	This Web service is used to manage Document Workspace sites. It allows you to create new document workspaces, delete document workspaces, create new sub-folders, delete sub-folders, and so forth.
Forms Service	Each list has forms associated which are used to display list items, create new list items, and update or delete existing list items. This Web service allows to get the collection of forms associated with a list and then get detailed information about each form.
Imaging Service	SharePoint has picture libraries that users can use to manage pictures. This Web service allows to upload pictures, download pictures, create new folders, delete folders and pictures, and the like.
List Data Retrieval Service	Allows you to run XPath like queries against a list.
Lists Service	This Web service is used to work with lists and list data. You can obtain the collection of lists, add new lists, remove lists, add new list attachments, remove attachments, and so on.
Meetings Service	This Web service is used to work with Meeting Workspaces. You can create a new Meeting workspace, remove an existing Meeting workspace, add new meetings, add new meetings using ICal files, and so forth.
Permissions Service	Sites and lists have permissions assigned to them. This Web service is used to obtain the permissions assigned to a list or site, add new permissions, and update or removing existing permissions.
Site Data Service	The Site Data Web service can be used to return meta-data about a site or list, get the collection of lists, get the attachments for a list item, get the collection of items in a list, and so on.
Site Service	This Web service can be used to return the list of site templates. When you create a new site using the Administration Web service you need to specify the site template name to use that you can obtain through this Web service.
Users and Groups Service	This Web service is used to work with users, site-groups and cross-site groups. You can add, update or remove users, site-groups, and cross-site groups. You can also add users or cross-site-groups to a site-group.
Versions Service	Document Libraries and Picture Libraries can have versioning enabled, which stores a copy of every single file version. This Web service can be used to get the list of available versions, delete versions, and also restore a file version.
Views Service	Lists have views associated that define what fields are shown, what filtering and sorting is applied, what grouping is applied, and so on. This Web service is used to work with list views. You can get the collection of views, add new views, remove views, update the Html code used to display a view, and the like.
Web Part Pages Service	Web Parts are objects that you can place on Web part pages. This Web service is used to work with Web parts and Web part pages. You can get the list of Web parts on a page, you can add or remove Web parts, and so forth.
Webs Service	This Web service is used to work with sites and sub-sites. You can get the list of list-templates, get meta-data about a sub-site, get the list of sub-sites, and so on.
Area Service	Areas are sections used in SharePoint Portal Server to group content. This Web service allows you to manage areas. You can create new areas, update areas, remove areas, get the list of sub-areas, and so forth.
Query Service	The Query Web service is used by clients to search SharePoint. You can send in complex search XML requests and get a result-set of matches.
User Profile Service	Users in SPS have user profiles that are used to target content to audiences (users). This Web service allows you to obtain user profile information. It does not allow you to create or modify user profiles.
SPS Crawl Service	This Web service is undocumented and is used by SharePoint itself for site crawling purposes.
Outlook Adapter Service	Provides the same capabilities as the Alerts Web service of WSS.

Filtering list view with query string parameters

We can filter a list using query parameters and the result will be displayed in list view, So we need a SharePoint list view for the same.
Below are the parameters we can use for filtering.

1. Create any list view you want or use a default one, for example:
http://sharepoint/Lists/MyList/AllItems.aspx

2. Call your view with the following parameters; if you already have parameters, then just add the new ones to the query string:
http://sharepoint/Lists/MyList/AllItems.aspx?FilterField1=Title&FilterValue1=ExactTitle
If here, FilterField1 value is the internally recognizable field name, not a display name or friendly name.

The FilterValue1 is the exact value to filter by, not “contains” or “begins with“, this one is assumed to be “equals“.

In this example you will receive all items, titles of which are equal to ExactTitle
So what if you want to filter by multiple values?

Here is how you do that:

http://sharepoint/Lists/MyList/AllItems.aspx?FilterName=Title&FilterMultiValue=ExactTitle1;ExactTitle2


In this example you will receive all items titles of which are equal to ExactTitle1 OR ExactTitle2

Contains:

http://YourSiteURL/Lists/Test/AllItems.aspx?FilterField1=Skills&FilterValue1=C%23&FilterOp1=Contains

SharePoint Shortcut URL’s

Below is the list of pages & their paths which can be accessed from URL :

· Add web parts to any page: append ?PageView=Shared&ToolPaneView=2
· Create New Site Content: /_layouts/create.aspx
· Create New Site: _layouts/NewsbWeb.aspx
· List Template Gallery: _catalogs/lt/Forms/AllItems.aspx
· Master Page Gallery: _catalogs/masterpage/Forms/AllItems.aspx
· Manage your Alerts: _layouts/SubEdit.aspx
· Create New Alert: _layouts/SubChoos.aspx
· Manage Site Collection Administrators: /_layouts/mngsiteadmin.aspx
· Manage Sites and Workspaces: /_layouts/mngsubwebs.aspx
· Manage People: /_layouts/people.aspx
· Manage User Permissions: /_layouts/user.aspx
· Modify Navigation: /_layouts/AreaNavigationSettings.aspx
· Modify Site Navigation: _layouts/SiteNavigationSettings.aspx
· Recycle Bin: /_layouts/AdminRecycleBin.aspx
· Site Directory: _layouts/SiteDirectorySettings.aspx
· Save Site as Template: _layouts/savetmpl.aspx
· Site Settings page: _layouts/settings.aspx
· Create New Web Part Page: _layouts/spcf.aspx
· Site Template Gallery : _catalogs/wt/Forms/Common.aspx
· Site Column Gallery: /_layouts/mngfield.aspx
· Site Content Types: /_layouts/mngctype.aspx
· Site Content and Structure Manager: /_layouts/sitemanager.aspx
· Site Usage Summary: /_layouts/SpUsageWeb.aspx
· User Alerts: /_layouts/sitesubs.aspx
· View All Site Content: /_layouts/viewlsts.aspx
· Web Part Gallery: _catalogs/wp/Forms/AllItems.aspx
· Web part maintenance mode: append ?contents=1 to the URL of the page
· Open the page in Edit Mode: In Address bar, Type "javascript:MSOLayout_ToggleLayoutMode();"
or "javascript:MSOTlPn_ShowToolPane(’2′);" in the Address bar
· Add Web Parts Pane: ?ToolPaneView=2

Finding SharePoint GUIDs using PowerShell

2010 version:

$site = Get-SPSite http://yourserver/sites/yoursite
$web = $site.OpenWeb("yoursubsite")
write-host "Site: " + $site.id
write-host "Web: " + $web.id
$web.lists | Format-Table title,id -AutoSize
$web.Dispose()
$site.Dispose()

Note: You could change $site.OpenWeb("yoursubsite")  to $site.RootWeb to get just the top site.
 

Here’s what the output looks like:

Column limits in SharePoint 2013

SharePoint Server 2013 data is stored in SQL Server tables. To allow for the maximum number of possible columns in a SharePoint list, SharePoint Server 2013 will create several rows in the database when data will not fit on a single row. This is called row wrapping.
Each time that a row is wrapped in SQL Server, an additional query load is put on the server when that item is queried because a SQL join must be included in the query. To prevent too much load, by default a maximum of six SQL Server rows are allowed for a SharePoint item. This limit leads to a particular limitation on the number of columns of each type that can be included in a SharePoint list. The following table describes the limits for each column type.
The row wrapping parameter can be increased beyond six, but this may result in too much load on the server. Performance testing is recommended before exceeding this limit.

Each column type has a size value listed in bytes. The sum of all columns in a SharePoint list cannot exceed 8,000 bytes. Depending on column usage, users can reach the 8,000 byte limitation before reaching the six-row row wrapping limitation.

Limit	Maximum value	Limit type	Size per column	Notes
Single line of text	276	Threshold	28 bytes	SQL Server row wrapping occurs after each 64 columns in a SharePoint list. The default row wrapping value of six allows for a maximum of 384 Single line of text columns per SharePoint list (6 * 64 = 384). However, because the limit per SharePoint list item is 8,000 bytes, of which 256 bytes are reserved for built-in SharePoint columns, the actual limit is 276 Single line of text columns.
Multiple Lines of Text	192	Threshold	28 bytes	SQL Server row wrapping occurs after each 32 columns in a SharePoint list. The default row wrapping value of six allows for a maximum of 192 Multiple lines of text columns per SharePoint list (6 * 32 = 192).
Choice	276	Threshold	28 bytes	SQL Server row wrapping occurs after each 64 columns in a SharePoint list. The default row wrapping value of 6 allows for a maximum of 384 Choice columns per SharePoint list (6 * 64 = 384); ); however because the limit per SharePoint list item is 8,000 bytes, of which 256 bytes are reserved for built-in SharePoint columns, the actual limit should be 276 Choice columns.
Number	72	Threshold	12 bytes	SQL Server row wrapping occurs after each 12 columns in a SharePoint list. The default row wrapping value of six allows for a maximum of 72 Number columns per SharePoint list (6 * 12 = 72).
Currency	72	Threshold	12 bytes	SQL Server row wrapping occurs after each 12 columns in a SharePoint list. The default row wrapping value of six allows for a maximum of 72 Currency columns per SharePoint list (6 * 12 = 72).
Date and Time	48	Threshold	12 bytes	SQL Server row wrapping occurs after each eight columns in a SharePoint list. The default row wrapping value of six allows for a maximum of 48 Date and Time columns per SharePoint list (6 * 8 = 48).
Lookup	96	Threshold	4 bytes	SQL Server row wrapping occurs after each 16 columns in a SharePoint list. The default row wrapping value of six allows for a maximum of 96 single value Lookup columns per SharePoint list (6 * 16 = 96).
Yes / No	96	Threshold	5 bytes	SQL Server row wrapping occurs after each 16 columns in a SharePoint list. The default row wrapping value of six allows for a maximum of 96 Yes / No columns per SharePoint list (6 * 16 = 96).
Person or group	96	Threshold	4 bytes	SQL Server row wrapping occurs after each 16 columns in a SharePoint list. The default row wrapping value of six allows for a maximum of 96 Person or Group columns per SharePoint list (6 * 16 = 96).
Hyperlink or picture	138	Threshold	56 bytes	SQL Server row wrapping occurs after each 32 columns in a SharePoint list. The default row wrapping value of six allows for a maximum of 192 Hyperlink or Picture columns per SharePoint list (6 * 32 = 192) ); however because the limit per SharePoint list item is 8,000 bytes, of which 256 bytes are reserved for built-in SharePoint columns, the actual limit should be 138 Hyperlink or Picture columns.
Calculated	48	Threshold	28 bytes	SQL Server row wrapping occurs after each eight columns in a SharePoint list. The default row wrapping value of six allows for a maximum of 48 Calculated columns per SharePoint list (6 * 8 = 48).
GUID	6	Threshold	20 bytes	SQL Server row wrapping occurs after each column in a SharePoint list. The default row wrapping value of six allows for a maximum of 6 GUID columns per SharePoint list (6 * 1 = 6).
Int	96	Threshold	4 bytes	SQL Server row wrapping occurs after each 16 columns in a SharePoint list. The default row wrapping value of six allows for a maximum of 96 Int columns per SharePoint list (6 * 16 = 96).
Managed metadata	94	Threshold	40 bytes for the first, 32 bytes for each subsequent	The first Managed Metadata field added to a list is allocated four columns: A lookup field for the actual tag A hidden text field for the string value A lookup field for the catch all A lookup field for spillover of the catch all Each subsequent Managed Metadata field added to a list adds two more columns: A lookup field for the actual tag A hidden text field for the string value The maximum number of columns of Managed Metadata is calculated as (14 + (16 * (n-1))) where n is the row mapping value (default of 6).