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.

image54

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.

image55

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

image56

Product List Data:

image57

"Product Line" List Data:

image58

Views Created:

image59

Views Created:

Query Examples:

image60

Example #1:

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


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

image61

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.

image62

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

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

image63

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

image64

Product List Data:

image65

Site List Data:

image66

Views Created:

image67

Views Created:

image68

Example #3:

image69

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

image70

Product Line list column definition:

In this example a product line has a manager.



image71

Product Line Data:

image72

Views Created:

image73

Views Created:

image74

Example #5:

image75

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.

image76

Site List Definition:

image77

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.

image78

Proposal Library Data:

image79

Proposal Library Version Data:

image80

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.

image81
image82