Understanding the Views Created for SharePoint Server 2007


SPViews generates a script you can use to create SQL views for all lists you selected. There are special data types used in SharePoint that don’t translate directly into a MS SQL relational database. In this section we explain how we handle each of these special types.

Lookup Field Type

Here are examples of how SPViews handles Single and Multiple Value Lookups.


Single Value Lookup Field Examples

Here is the column definition for the "Product" list:  The product list is a single value lookup against the "Product Line" list.


Here is the column definition for the "Product Line" list:


Product List Data:


"Product Line" List Data:


Views Created:


Views Created:

Query Examples:


Example #1:

The single lookup column "Product Line" is included in the view:

Note:  "Description" is a "Rich Text" field and contains formating tags


Example #2:

Here "Product" and "Product Line" are joined to show additional columns from "Product Line"

Multi-Values Lookup Field Examples

For each Multi-Value field a view is created which can be used to display all the values for that lookup field. 

The example below shows the views generated for the Product List and its related Site Multi-Value lookup field.  The name of the lookup view is generated using the name of the origianl view concatinated with the lookup field name.

Use the List Row ID column in the Lookup view (Product-Site) to relate back to the orgianal view.


Here is the column definition for the "Product" list:

The product list is a single value lookup against the "Product Line" list. 


Here is the column definition for the "Site" list:


Product List Data:


Site List Data:


Views Created:


Views Created:


Example #3:


Example #4:

The lookup view also contains a reference back to the lookup list.  Use the LookupListRowID to view additional fields from the looiup list.

Person/Group or Multi-valued Person/Group Field Type

The Person or Group field type is a special SharePoint lookup field and therefore more detail is available in the view.

The SPViews creates six columns for this field type:

ColumnName-Login - This value is the Windows Login ID of the User or Group or NULL in the case of a SharePoint group.

ColumnName-Name - This value is the Display Name you see in your SharePoint list.

ColumnName-SiteID - This value can be used to join to the UserInfo or Groups tables if you need more information about the Person or Group.

ColumnName-ID - This value can be used to join to the UserInfo or Groups tables if you need more information about the Person or Group.

ColumnName-Email - This value is the user's email.  It is NULL in case of a group.

ColumnName-Type - This value is set to “User” if further information is in the UserInfo table and “Group” if it is in the Groups table.

A multi-valued Person/Group field is handled in a similar fashion as the multi-value lookup fields through the use of a reference view.

Person/Group Field Example


Product Line list column definition:

In this example a product line has a manager.


Product Line Data:


Views Created:


Views Created:


Example #5:


Example #6:

Views with Historical Data

SharePoint allows you to keep versions of items in a list or documents in a document library by turning on Item Version History in the list's or library's Versioning Settings.  SPViews can generate views that include these historical versions.

To generate history enabled views using SPViews, use the "History Version Data" area in the Options.  By clicking on the checkbox for "Include Version History" the generated views will include historical versions of each item stored by SharePoint.  For each list item in the view there may be multilple rows returned for the same "ID" column with different "Version" numbers.

Example #7 below shows version data of the "Site" list.  Note the "Company Main Phone" change for the Dallas, TX Office and the "Description" change for the Chicago, IL Office.


Site List Definition:


Site List Data:

Minor Versions

If you are using a document library with the SharePoint option to "Create major and minor (draft) versions" enabled, you can select the SPViews option to "Include Minor Versions (Not Published)".  Examine the "Version" and "Level" columns.  The "Level" column will be equal to "1" for published Major Version data.


Proposal Library Data:


Proposal Library Version Data:


Proposal Library Version Data:

Audit Views

When using version history you can also produce an audit view for your SharePoint list.  The view produced is similar in functionality to the SharePoint "Version History" of a list or library item.  It will produce a view that for each version of an item will show you what was changed from the previous version.  Please note that the first version of an item will show that everything has changed.  The current version of SPViews does not support what has changed in multi-value columns.  The Audit views have the same name as the views they are auditing with "Audit" appended.

The Example #9 below shows how the Cost and Color of the Standard Wiggler product was changed between versions 1 and 2.