Showing posts with label Lists. Show all posts
Showing posts with label Lists. Show all posts

Wednesday 27 June 2012

SP2010 List View Throttling

Overview:  IT pro's and developers in SP generally understand the properties that can be used in setting list views in SP.  Users of SharePoint and department site collection administrator types seem to find it confussing.


How Lists Throttling Works - Simplified:
SharePoint's default list view threshold is 5000 items.  This does not mean the list is up to 5000 items simply that your list view will querry the list and work with a maximum of 5000.  Now the point to understand is that if you are filtering on 6000 rows event though you are displaying 100, you have passed the threshold as you need to look at all 6000 that you are filtering/sorting.  If you add an index to the property that you are filtering/sorting on, it will work. 

"For a view to quickly filter through a large number of items, the first column that you specify in the filter must be indexed. Other columns you specify in the view filter may or may not be indexed, but the view does not use those indexes. You also need to make sure that the first column of the filter does not return more items than the List View Threshold, even if the final result of the filtered view returns less than the List View Threshold. " from MS.

Other:
The smallest the list view threshold can be set to is 2000 results.
List thresholds can be increase for general users, this can have performance issues.

Glyn Clough explains list view throttling clearly in more detail.

Steve Peschka explains working with large lists.
Tutorial from MS.  This blog and movies explains view limit thresholds perfectly.

Friday 9 December 2011

Add Expiry Date to a List

Problem:  I have a list and each time the list item is approved, I need to add 180 days to the expiry date.

Initial Hypothesis: Create a Date and Time site column in the list.  Edit the "Calculated Value" box with a formula that will add 180 days to the current date.

Resolution: The formula to move the expiry date 180 days from the current date is:
=DATE(YEAR(Today),MONTH(Today),DAY(Today)+180)

Sunday 1 May 2011

SP2010 lists vs DB tables

Overview:  When developing using the SP2010 Server Object Model you don't have to store data in SharePoint lists, especially considering the BCS in SP2010.  This presentation video was part of a session present on data access options for Sharepoint 2010 in April 2011. It specifically looks are comparing database table vs SharePoint lists to help me decide which option to use. 
http://www.youtube.com/watch?v=8ecYVdR3a1g

Sunday 24 April 2011

Referential Integrity between Lists

Problem: SP2010 adds referential integrity between lists using a lookup column.  I wish to push out cascade referential integrity between my lists using declarative CAML to build and link my 2 SharePoint lists.

Initial Hypothesis:  I am going to work through an example to highlight 2 key concepts when deploying a list declaratively using CAML (Using XML which is CAML I am declaratively creating 2 lists namely: Customers and Orders). 
1) I want to display a project field not the ID column that is doing the binding as my lists will make more sense to users updating them.  As shown below:

2)  Setup Cascade referential integrity between the lists (can't be done on the Site Column Lookup).  Referential integrity is done between list instances not on the Site Column.  Therefore, I use a feature receiver to wire up the referential integrity. The result of setting referential integrity is Shown as it will appear in the UI.

There are 3 ways to create lists namely: UI, programmatically or declaratively.  In MOSS the best option for lookup lists was to do it progratically using feature receivers.  My preferred approach is to use the declarative CAML approach.

Tip: The correct way to create a list is to ensure you have followed the 4 steps in this order using CAML: Site Columns, Content Types, List Definition & List Instance.

Tip: CKSDev is an VSIX extension that you can load into Visual Studio 2010 (VS2010) using Microsoft Gallery, it is a codeplex based project that adds functionality to VS2010.  You can use you "Solution Explorer" in VS2010, navigate to the site columns, right click and generate the CAML to declativtively build Content Types and Site columns.  So you can create the content types thru the UI then generate the CAML and now you have a repeatable mechanism of deploying content types and site columns. 

Resolution:
1) Lookup Field (Lookup Site column) should use the customer name not the default ID for linking (This actually links on the customer "ID" but the link displays the "Customers Name".  This is done on the field in the CAML using the attributes "ShowField" and "DisplayName".


2) The second point and title of this posts is about setting referential integrity between lists.  You cannot use the site column declaritive code to create the referential integrity.  The attribute "RelationshipDeleteBehaviour" is used to set the referential integrity behaviour, this can be either None (Default), Restrict or Cascade at a Site Column level.  Using the attribute "RelationshipDeleteBehaviour" does not work as shown below as referential integrity is done between list instances not on the site column.  Why the attribute "RelationshipDeleteBehaviour" is present I have no idea.

Add a new feature to the VS2010 sandbox solution project (Right Click "Features" in "Solution Explorer" and select "Add Feature")
Add the feature Receiver code (Right Click "Feature2" (or whatever you called it), select "Add Event Receiver".
Add the appropriate code to the feature receiver as shown below:
  
Tip:
"You can apply a cascade delete rule. In this case, if a user deletes an item from a parent list, related items in the child list are also deleted. This helps to prevent orphaned items in the child list.
You can apply a restrict delete rule. In this case, users are prevented from deleting an item that is referenced by items in a related list. This helps to prevent broken lookup links in the data model." MSDN

Download:
Download the Visual Studio 2010 project that builds the 2 SharePoint lists in a sandbox solution and adds referential integrity to the list lookup relationship.

More Info:

Tuesday 12 October 2010

NullReferenceExcepetion when viewing/editing an existing list item after the list instance has been deployed

Problem: When viewing or editing and item in a list, I receive an application error (NullReferenceException).  This happens when I redeploy a list, my list is defined using site columns, content type and the list definition is based on the content type.

My ULS logs contained the following information:
Failed to cache field with id
Unable to open Lookup list 'Lists/xxxList'.[Error was 0x8007....]
System.NullReferenceException: Object reference not set to an instance of an object.   at Microsoft.SharePoint.Publishing.FieldCache.TryGetValue

Initial Hypothesis:  ULS logs show me that a lookup list is not being found.  By examining the columns on the list instance I can see they are not being created correctly.  My initial thought was my xml was wrong, once it was reviewed and I found it to be correct I realised it was a caching issue.

Resolution:  Clear the Visual Studio Cache
  1. Right click on "Project" in Solution Explorer and click "Retract";
  2. Close Visual Studio 2010; and
  3. Open the "Project" in Visual Studio 2010 and "Deploy" the solution. 

Monday 20 September 2010

Data access options - SharePoint Lists or Database Tables

Problem:  I have built a composite application that uses SP 2010 lists for storing the application data.  There are 2 related lists within the application that are high transactional lists like a shopping cart (order with order items).  These 2 lists have had many fields added to them over the duration of the project and are no longer light lists. 
Hypothesis: SharePoint 2010 supports up to 50 million lists and I need to insert, edit and read often the information in these 2 related lists.  The lists are getting rather complex and transactional support is not available vie LINQ to SharePoint.  I need a full audit history which is pretty useful in SharePoint.  The volume and complexity of this transactional data is to heavy for SP especially as the lists grow.  I have to use SQL Server to store the data directly and link the appropriate SP lists using BCS and external content types.  My preferred methods of accessing data are the Entity Framework v4 or Linq to SQL.  As my data is pretty small, I don't need a multi tier system and we are using LINQ to SharePoint. 
Resolution: Create the SP lists in SQL, use LINQ to SQL to add and work with the SQL data.  I use tomb tables in SQL Server to keep a full audit history.  There are now 2 areas of storage: SP lists and directly to SQL tables.  Not ideal but worth that change.

Pros of using SharePoint List vs Database tables
SharePoint ListsDatabase Tables
SharePoint Security and PermissionsTransactional
WorkflowPerformance & higher transaction volumes
VisioningDistinct queries and aggregations
Office intregrationGreater Flexibility
Native / Easy setupEasier for existing .NET developers
SP generates UI
Update 18 Oct 2010 - Storing data for SharePoint 2010 comparison

Thursday 26 August 2010

Creating lookup columns and list declaratively in SP2010

Problem: When creating lists via a feature, you may want to link SharePoint 2010 lists.
Initial Hypothesis: In MOSS to link to SharePoint 2007 lists you had to do it via the UI or via a deployable feature.  The list was created declaratively using XML (CAML).   Lastly the lookup column needed to be created using a feature receiver.  As in MOSS, in SP2010 you can create a lookup column using the UI but this is not a reusable deployment method and falls prey to errors.  SP2010 allows you to create lookup lists using declarative CAML.

Resolution: Create the lists, add site columns (fields), then add the site columns to a content type and lastly generate the list definition & list instance.  Steps are detailed below:
  • In an elements file declaratively create a lookup column that uses the specified list as shown below: 

  • Add the new lookup column to a content type:
  • Your existing/create a list definition that derives from your content type.
Note: The Lookup Site column needs to specify the list it is looking up when you declare the XML.
More Info:
Creating lists declaritively in SP2010 - Post outlines creating SharePoint lists, coupled with this post you can create lists that are related using a feature to create your lists.
Available field types

Wednesday 25 August 2010

Creating SharePoint Lists declaratively using Visual Studio 2010

Problem: Create a custom list to hold configuration values in a list.
Resolution Overview: Using VS 2010 create a feature that deploys the new list with content.
1.> Create Site Columns/fields;
2.> Create the Content Type using the Site Columns;
3.> Create the List Definition from the Content Type; and
4.> Add items to the list instance elements.xml.

Resolution Steps:

1.> Create Site Columns by adding a new "Empty Element" item in VS.
Declaratively add the site column to the elements.xml file;
Ensure your element file has been added to a feature for deployment;
Deploy the feature and ensure the Site Column has been add using the UI "Site Setting" > "Site Columns";

2.> Create the Content Type using the Site Columns
Using VS 2010 create a new Content Type;
Add caption
Using the existing Site Column create the Content Type declaratively;
Check the Content Type is create after you have deployed; 

3.> Create the List Definition from the Content Type

Follow the wizard and select the newly created Content Type "Configuration";
Check the List definition has created the elements.xml and schema.xml files;

Edit the Schema.xml File to Show the new columns defined int he content type (otherwise you will only see the "Title" column);

4.> Add items to the list instance elements.xml

Ensure the List is created



Tip: The fields/site columns & FieldRefs used in the elements files can be added thru the VS2010 tools.  VS2010 > View > Toolbar
Tip: Updated 18 Oct 2010 - CKSDev has the functionality to import Site Columns, this is useful in that you may of created the SiteColumns via the UI on a prototype site and you can not easily get the xml to create the site columns/fields declaritively.  http://blog.mastykarz.nl/cksdev-support-importing-site-columns/

Wednesday 9 June 2010

SPMetal not generating correctly on lists with only a Title column

Problem: I have 7 custom lists in my SharePoint 2010 site. I run SPMetal against these lists and only 5 lists as available in LINQ.

Initial Hypothesis: SPMetal is broken followed by alot of frustration which eventually I worked out that a custom list must have additional columns to Show up in SPMetal.

Resolution: Create an optional column if you only have the "Title" column in a list that you wish to use Linq to SharePoint on.

Sunday 6 June 2010

Application Architecture in SharePoint 2010

Problem: I have a client that needs a composite built using 2010. The application is used to manage digital assets for suppliers. Key questions are where to store data and how to access the data.
Hypothesis: Where to store data, previously in MOSS I would of used SQL Server and accessed it via the BDC or custom web parts. In SP 2010 lists have been vastly improved and are now a good option for storing data. The new External Content Type can be used on SQL but goes beyond my requirement.
The key improvements to SharePoint lists are:

  1. Lookup lists are improved and easier to implement;
  2. Query joins and LINQ improve retrieving data;
  3. LINQ gives you strongly typed lists which using the old "Server Object Model" were weakly typed;
  4. LINQ querries are converted to CAML querries and therefore more performant than using the Server object model method and alot cleaner than using CAML to query data;
  5. Formula based validation on fields;
  6. Store level enforcement (MOSS enforce requirements such as null values only at the UI level);
  7. Improved referential integrity between lookup lists.
My choice was between using SQL Server to store the application data and SharePoint lists. SQL tables have the following key advanatages of they are more performant, and are transactionable. The easy of use and adavantages meant that SharePoint lists are the prefered option for storage.
Application for the suppliers and users also has several options however SharePoint provides most of the admin screens. The actual app can be built using application pages or web parts or the method i prefer, the client object model.

Accessing SharePoint Data options to consider are: Client Object Model (Weakly typed), however REST API's are Strongly typed for SP lists only. New in SP2010
Server Object Model (OM) is weakly type lists but offers the most flexibility. The Server OM existed on MOSS but is improved in SP2010. LINQ gives strongly type SP lists that can be read-write (new in SP2010)
Resolution:
Used SP lists to store the digital assets, the ordering data is also stored in SharePoint lists. For speed of development in the prototype I built using application pages using web parts that accessed the lists using LINQ to SharePoint. The final application will be built using the Client OM.