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.

image9
image10

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

image11

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.

image12

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

image13

"Product" List Data:

image14

"Product Line" List Data:

image15

Views Created (no history):

image16

Views Created (no history):

Query Examples:

image17

Example #1:

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

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

image18

Example #2:

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

Multi-Values Lookup Field Examples

image19

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.

image20

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

image21

Product List Data:

image22

Site List Data:

image23

Views Created:

image24

Views Created:

image25

Example #3:

image26

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 Example #6 below we use the multi Person/Group field "Sales Contacts" in the "Product Line" list. 

image27

Product Line list column definition:

image28

Product Line Data:

image29

Views Created:

image30

Views Created:

image31

Example #5:

image32

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.

image33

Site List Definition:

image34

Site List Data:

image35

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.

image36

Proposal Library Data:

image37

Proposal Library Version Data:

image38

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.

image39
image40
image41