Support Information
Overview of SPViews
Using SPViews Software
   Connect to a SQL Server
   Select a Content Database
   Set Options
   Select Views to Create
   Generate, Save and Run SQL
Understanding the Views Created for  
SharePoint Server 2007
Understanding the Views Created for  
SharePoint Server 2010
FAQ
Release Information

Understanding the Views Created for

SharePoint Server 2013 and 2010

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 Column Type Person/Group or Multi-valued Person/Group Column Type Views with Historical Data

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 (no history):

    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:


    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

    In Example #5 below we use the single Person/Group field "Manager" in the "Product Line" list. 
    In the Example #6 below we use the multi Person/Group field "Sales Contacts" in the "Product Line" list.

    Product Line list column definition:

    Product Line Data:

    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:

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.