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.

image6

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.

image7

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

image8

Product List Data:

image9

"Product Line" List Data:

image10

Views Created:

image11

Views Created:

Query Examples:

image12

Example #1:

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


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

image13

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.

image14

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

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

image15

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

image16

Product List Data:

image17

Site List Data:

image18

Views Created:

image19

Views Created:

image20

Example #3:

image21

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

image22

Product Line list column definition:

In this example a product line has a manager.



image23

Product Line Data:

image24

Views Created:

image25

Views Created:

image26

Example #5:

image27

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.

image28

Site List Definition:

image29

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.

image30

Proposal Library Data:

image31

Proposal Library Version Data:

image32

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.

image33
image34