Thursday, July 17, 2008

SharePoint Column: Lookup (information already on this site)

Hi All,

This Post will discuss in detail about the options you have to set when using a Sharepoint Lookup Field. When you do a create Column in sharepoint you get it as
Lookup (information already on this site) on the Create column page.

To get a better understanding of the concept lets take up a scenario.

Scenario:

We have 2 lists
1) Company Information
2) Contacts

The relationship between then is established by using a Lookup column
“Company” in contacts

These 2 lists are placed on the Default.aspx (Home page) of the Site in the form of DataView web parts.

Refer the image as below:




What is the requirement?

Click on the “Company” Hyperlink It should take you to the DispForm.aspx of
the list item in "Company Information" List.

Click the “Close” Button of the DispForm.aspx it should bring you back to the Default.aspx (Home page) of the Site where we have the 2 DataView Lists.

Here “Company” column is a Lookup (information already on this site) Field.
To achieve the above requirement it’s very important to set its
Additional Column Settings -> In this column value to:
“Title (linked to item)” and not to “Title”. Why??? Refer the reason below.


Reason:

If you set the Additional Column Settings -> In this column value to : “Title”
In a Lookup Column, then the Hyperlink column created will show a &Root Folder=* at the end of the Hyperlink URL. This will make the navigation to go to AllItems.aspx of the “Company Information” List on clicking “Close” button on the DispForm.aspx

If you set the Additional Column Settings -> In this column value to:
“Title (linked to item)”In a Lookup Column, then the Hyperlink column created will not show any such Root Folder parameter. This will make the navigation to go to Default.aspx (Home page) of the Site
on clicking “Close” button on the DispForm.aspx

Refer the Images for a clear understanding.



24 comments:

Anonymous said...

Only one field has the 'linked to item' associated with it and it is not the column I want! Any way to get around that?

c said...

Thank-you so much for this post, I have been fighting this problem (exact same requirement) for weeks!!!! Easy fix worked exactly as you described.

John Developer said...

Hi,
someone may help the approach to creating lookup fields in visual studio. here is tutorial with screenshots.

http://sharepoint-anthony.blogspot.com - Lookup field

Chinmay said...

Good trick..
I always used to wonder about the difference between Title and Title (Linked to Item)..

I have one specific requirement.. I want to make more than one column as 'Linked to Item'. Is this Possible??

for instance, List Company has 4 columns: Title, Owner, Production & ShareValue.
I want to make all of them as 'Linked to item'.
Let me know, if anyone can get around this.

Thanks

SharePoint Kings said...

chinmay,

by default you can not have multiple link item in list view.

but i think you can add this by programmatically...not sure

we'll check and confirm.

Thierry said...

Do you confirm we can only lookup fields/columns like Title, ID, Comment ... and not the columns created for that list?

SharePoint Kings said...

Thierry,

you can also lookup column created by you. but limitation like you can not lookup a lookup column, multiline text and like that...

John McL said...

Exactly what field types can be used as lookups? And are there any restrictions - e.g. do they have to be unique?

Regards

John

SharePoint Kings said...

there are some field like people picker, multiline textbox,cannot lookup a lookup column.... and some more of them

we don't have a link handy but there is a MSDN link which lists which type it doesn't support.

Anonymous said...

Is it possible to use the alternative columns associated with a lookup column as a document field? I.E. I have a Contacts list and I want to use more than just one column within a document as a field. For example, I want to have a comobo-box for the contact full name, and when selected it will change the fields associated with the other columns, such as the home phone and e-mail columns.

SharePoint Kings said...

Anonymous,

you can do it but with a lot of custom coding

Keith said...

In reply to:

"Is it possible to use the alternative columns associated with a lookup column as a document field? I.E. I have a Contacts list and I want to use more than just one column within a document as a field. For example, I want to have a comobo-box for the contact full name, and when selected it will change the fields associated with the other columns, such as the home phone and e-mail columns"

I assume you mean that you want a lookup in another list (NEWlist), that looks up info in the Contact list and automatically updates several fields in NEWlist.

This is actually very easy to accomplish by building a workflow in Sharepoint Designer to update the additional columns, provided the column you select has unique info.

OR, you could add a field in your contact list called (for example) "ContactInfoAll" and build a workflow that puts into that field Name, Phone and Email as a concatenated field, then use ContactInfoAll as the lookup in NEWlist. Not quite as elegant, but to ensure a unique lookup, you're going to have to use some combination of fields from your Contact list.

(You could build ContactInfoAll as a calculated field, but SP doesn't let you use a calculated field as the target of a lookup.)

Write to me at khudsonllb at gmail dot com if you want help on this, with "Sharepoint Help Wanted" in the subject line.

SharePoint Kings said...

Hay Keith, thanks a lot, it will help a lot

Thanks,
Sharepointkings Team

jelena said...

I am just not sure how to start. Do I need to create one-column lists first? And then use them as a look up columns in the in the list I create?

Parth Patel said...

you have to create two list
one will refer another.

Keith, the Singularity (Keith O Hudson) said...

@Jelena:

Typically, I create a single column list as my lookup list, then add a column in a second, multi-column list referring to the lookup list.

For instance, If I want to create a list of names and addresses of customers, I might want my customers to be able to select their state from a dropdown when they are entering their address information.

So my first step is to create a "States" list with only one column (the Title column). Then, when I create my Customers list, the State column is a lookup on the States list.

I hope that helps.

I've got more info about Sharepoint at keithohudson.com and at www.spmastery.com.

Keith Hudson

Parth Patel said...

Thanks Keith

butterwise said...

here is a page which denotes which column types are supported as lookup columns. however, it states that multi line text columns are supported, but everything else i've seen - including experience - shows this is not the case...

SharePoint Kings said...

butterwise,

yes multi-line is not supported but may be multi-line with HTML is not supported but try with lookup with multi-line plain text. not HTML or Rich Text

Anonymous said...

The lookup value seems sort alphabtically. How I control the sorting sequence? I have tried to set the sorting order in the default view of the lookup list but it does not work.

khariel☎ said...

Great post! I hope you will share more with us. You have discussed all the issues very clearly regarding Reverse Phone Lookup Information. I am sure my visitors will find that very useful.

yakazaa said...

Hello,

I'm searching for something like this as well:
I successfully created a lookup field in my "orders" list, where I can chose a supplier from my "suppliers" list.
But now, I would like to get more, then it's name by this choice:
Once choised it's name, I'd like to see it's ID, phone number / email, as well in a field below.

Any suggestions about this ?
Thanks a lot !

SharePoint Kings said...

yakazaa,
if we understand your question correctly you required second level of look-up
sharepoint can only have one level of it by default.

SharePoint Kings said...

@Anonymous,
good catch, we never noticed this, may be not getting this kind of required :)

but yes behavior you mentioned is correct, will try to get workaround if got chance.




Share your SharePoint Experiences with us...
As good as the SharePointKings is, we want to make it even better. One of our most valuable sources of input for our Blog Posts comes from ever enthusiastic Visitors/Readers. We welcome every Visitor/Reader to contribute their experiences with SharePoint. It may be in the form of a code stub, snippet, any tips and trick or any crazy thing you have tried with SharePoint.
Send your Articles to sharepointkings@gmail.com with your Profile Summary. We will Post them. The idea is to act as a bridge between you Readers!!!

If anyone would like to have their advertisement posted on this blog, please send us the requirement details to sharepointkings@gmail.com