Showing posts with label lookup columns. Show all posts
Showing posts with label lookup columns. Show all posts

Tuesday 21 March 2017

Create a Lookup column from a list in a separate web

Problem:  I have a site collection (SPSite) with multiple sites (SPWeb) and I have a reference list in the SPSite (root web) that I want to use from multiple lists in the sites (SPWeb).

Resolution:  I recorded this short video that shows how to create a lookup site column in the site collection and then at the list within a sub web, you can associate the lookup.






Friday 25 July 2014

SharePoint 2013 OOTB lookup list filtering

Problem
I don't want to start customising my solution and I need a lookup column with restricted options.  I read this post from April Dunnam via Google and it's awesome and I wish I had thought of it first.

I have 2 lists: Countries (Country, Continent) & Business (Name, LocationInAsia, LocationInEurope).  I want to be able to specify their head office in each continent.  I could do a lookup list to country and show all the countries or I can use Aprils approach and only show Countries for the specific region.  This is a contrived example but it's a simple way to explain it.

Resolution:
On the Countries list create a Calculated column/field, call it "AsianCountries". 
The formula is =IF([Continent]="Asia",Country,"")
On the Business list, create a lookup called "LocationInAsia" that points to the "AsianCountries" field in the Countries list.

http://www.sharepointsiren.com/2013/05/sharepoint-2007-2010-2013-filtered.html

Summary:
This is pretty useful in that I don't need to write and custom code to have lookup lists that only show the appropriate content.  Sure I need to have extra calculate columns but a good no code way to restrict lists lookups.


 

Sunday 24 April 2011

Referential Integrity between Lists

Problem: SP2010 adds referential integrity between lists using a lookup column.  I wish to push out cascade referential integrity between my lists using declarative CAML to build and link my 2 SharePoint lists.

Initial Hypothesis:  I am going to work through an example to highlight 2 key concepts when deploying a list declaratively using CAML (Using XML which is CAML I am declaratively creating 2 lists namely: Customers and Orders). 
1) I want to display a project field not the ID column that is doing the binding as my lists will make more sense to users updating them.  As shown below:

2)  Setup Cascade referential integrity between the lists (can't be done on the Site Column Lookup).  Referential integrity is done between list instances not on the Site Column.  Therefore, I use a feature receiver to wire up the referential integrity. The result of setting referential integrity is Shown as it will appear in the UI.

There are 3 ways to create lists namely: UI, programmatically or declaratively.  In MOSS the best option for lookup lists was to do it progratically using feature receivers.  My preferred approach is to use the declarative CAML approach.

Tip: The correct way to create a list is to ensure you have followed the 4 steps in this order using CAML: Site Columns, Content Types, List Definition & List Instance.

Tip: CKSDev is an VSIX extension that you can load into Visual Studio 2010 (VS2010) using Microsoft Gallery, it is a codeplex based project that adds functionality to VS2010.  You can use you "Solution Explorer" in VS2010, navigate to the site columns, right click and generate the CAML to declativtively build Content Types and Site columns.  So you can create the content types thru the UI then generate the CAML and now you have a repeatable mechanism of deploying content types and site columns. 

Resolution:
1) Lookup Field (Lookup Site column) should use the customer name not the default ID for linking (This actually links on the customer "ID" but the link displays the "Customers Name".  This is done on the field in the CAML using the attributes "ShowField" and "DisplayName".


2) The second point and title of this posts is about setting referential integrity between lists.  You cannot use the site column declaritive code to create the referential integrity.  The attribute "RelationshipDeleteBehaviour" is used to set the referential integrity behaviour, this can be either None (Default), Restrict or Cascade at a Site Column level.  Using the attribute "RelationshipDeleteBehaviour" does not work as shown below as referential integrity is done between list instances not on the site column.  Why the attribute "RelationshipDeleteBehaviour" is present I have no idea.

Add a new feature to the VS2010 sandbox solution project (Right Click "Features" in "Solution Explorer" and select "Add Feature")
Add the feature Receiver code (Right Click "Feature2" (or whatever you called it), select "Add Event Receiver".
Add the appropriate code to the feature receiver as shown below:
  
Tip:
"You can apply a cascade delete rule. In this case, if a user deletes an item from a parent list, related items in the child list are also deleted. This helps to prevent orphaned items in the child list.
You can apply a restrict delete rule. In this case, users are prevented from deleting an item that is referenced by items in a related list. This helps to prevent broken lookup links in the data model." MSDN

Download:
Download the Visual Studio 2010 project that builds the 2 SharePoint lists in a sandbox solution and adds referential integrity to the list lookup relationship.

More Info:

Wednesday 27 October 2010

Creating a lookup list & project field lookup using a declaritive content type

Problem: Create a list that has a lookup column and a projected field from the lookup column.
Example:
  • Some code I used to create a lookup column (Agency ID) in a list and a field to be brought in from the lookup list also (AgencyName)
  • This should be plugged into the new list's content type (AgencyContact).
  • The column being brought across also (Agency Name) needs to reference the GUID of the lookup column it relies on (Agency ID) using FieldRef.
Thanks to Paul W

Thursday 23 September 2010

Creating Site Columns / fields declaratively

Problem: Adding site columns decoratively has nuances.  This post adds information for getting fields/site columns correct when building them declarative using a feature to deploy to a Site Collection.

Resolution: You can use the following reverse engineering technique on the list.  This will show you the xml/CAML to create lists.  This works in MOSS & SP2010.  I used the list reverse engineering technique to figure out how to create a Lookup site columns with multiple values. 

Steps to Reverse engineering a SharePoint List:
1.> Create a list, content type and site column for the lookup (or field you want) using the SharePoint's UI
2.Then retrieve the list using a formatted url to see the native CAML used to create the site column.  http://url/_vti_bin/owssvr.dll?Cmd=ExportList&List=listguid
This approach saved me a lot of time today.

Tips:
CAML attributes required for each field element are: ID, Name, DisplayName, Group, and Type.
MSDN field element attributes
Attributes explained
Type="Note" will create a site column in the UI showing "Multiple line of text".  Attribute to get full html imput capabilities are RichText="True" (default is false)  & RichTextMode="FullHtml".
Type="Choice" can have a default value selected as shown below:
Built-in Site Column Append-only Comment can be referenced in a content type to provide comments that are appended and basic html is available.
Lookup columns work in SP2010 without having to create an event receiver to setup the lookup relationship using the list guid as is the case in MOSS.  Multiple lookups are possible also simple set the field attributes Type="Lookup" & Mult="TRUE".

Tip: Updated 18 Oct 2010 - CKSDev has the functionality to import Site Columns, this is useful in that you may of created the SiteColumns via the UI on a prototype site and you can not easily get the xml to create the site columns/fields declaritively. http://blog.mastykarz.nl/cksdev-support-importing-site-columns/

Thursday 26 August 2010

Creating lookup columns and list declaratively in SP2010

Problem: When creating lists via a feature, you may want to link SharePoint 2010 lists.
Initial Hypothesis: In MOSS to link to SharePoint 2007 lists you had to do it via the UI or via a deployable feature.  The list was created declaratively using XML (CAML).   Lastly the lookup column needed to be created using a feature receiver.  As in MOSS, in SP2010 you can create a lookup column using the UI but this is not a reusable deployment method and falls prey to errors.  SP2010 allows you to create lookup lists using declarative CAML.

Resolution: Create the lists, add site columns (fields), then add the site columns to a content type and lastly generate the list definition & list instance.  Steps are detailed below:
  • In an elements file declaratively create a lookup column that uses the specified list as shown below: 

  • Add the new lookup column to a content type:
  • Your existing/create a list definition that derives from your content type.
Note: The Lookup Site column needs to specify the list it is looking up when you declare the XML.
More Info:
Creating lists declaritively in SP2010 - Post outlines creating SharePoint lists, coupled with this post you can create lists that are related using a feature to create your lists.
Available field types