Relationship in SharePoint Lists

Hi,

In this article we will see the new concept called relationship which was highly anticipated in SharePoint 2010 and which was lacking in MOSS 2007.

After a wait now it’s finally here with SP 2010. We can define the delete constraint over the lists. When we have a lookup column in child list pointing to a parent list and if we have defined the cascading delete constraint then we can allow deleting all child records when parent record is deleted or we can restrict deleting parent record when child record exists. This is the same behavior that we have in SQL Server delete constraint.

So let’s go and open our Employee Leave lists that we created for our earlier posts. If you have not gone through it, I would recommend you go through it to understand more concepts of SP 2010.

Go to list settings – Click on Employee ID lookup field which is pointing to Employee parent list’s Employee ID field and scroll down to find this.

Do remember that to enforce the constraint on field, that field must be indexed. If it’s not, SharePoint will prompt you and index it for you.



Here we have put in a restriction on delete that means if we go and try deleting record from Employee List; it should not allow us to do so. So let’s try that out.

Go to Employee List, and delete any existing record that has lookup item exist in Employee Leave list and see what you get.




Now define the same constraint of Employee Projects that we have in our site. And now try to delete item from Employee List and see what you get.



So what happens is when you have multiple lists having lookup reference to the parent lists, error indicates you list name one by one, not all lists at once. I mean here we should be expecting that error should say that item cannot be deleted because an item in the employee leave and Employee Projects list is related to the Employee list. But then this can be a cumbersome effort because we never come to know that how many lists are referring lookup.

Well, but we now at least have this functionality available ready.

If you have defined two lists which have lookup reference to the parent list and if one list allows cascading delete and one do not, then parent list item will not be deleted because one list does not allow cascading delete. And this is what it should be.

When every list allows cascading delete then when you delete item from the parent list, it delete all related records from all related lists which have lookup field referenced to the parent list field. It also actually shows you the names of all lists which have the association while you are deleting the parent item. Check this out.




It is showing Employee Projects as well as Employee Leave, click on Ok to delete all related items in child lists as well as parent item.

I hope now you will have a fair idea about the new relationship behavior functionality in SharePoint 2010.

Related Lists in SharePoint 2010

Hi

We will see one of the new concepts from SharePoint 2010 which is related lists. We already have employee list with us. All we need to do is create two more lists. I am creating two lists just to show you an example. We can have as many lists we want.

Create Employee Leave list and go to list settings and create one column which is Empoyee ID and take it as a look up column from Employee list and field Employee ID. One new feature here is when you define lookup column. Now we have the ability to fetch fields from that item and display it in the list as well.




So now when you enter the data in the Employee Leaves and select Employee ID from lookup drop down, you get something like this.



So what happens is, here you would be able to see parent item details as well along with the actual list data.

Now go ahead and create one more list and call it an Employee Projects. Do the same thing, add columns and add EmployeeID column lookup to the Employee list and Employee Field.




Now add some data to this list. So what we have now is three list which are connected from Employee ID look up fields. Empoyee is the main list and then we have two lists with lookup to the Employee list employeeID field.

Open the List settings page of Employee List, create one view and call it a RelatedList view. Open the related list view and click on Site actions -Edit Page.

Go to options tab and then see we have new option called related list.




When you click on related list, it will automatically check all lists that have lookup to Employee List and here we have two lists and hence we will get two options.





Add both these lists and see what you have on the page. Now you have connected three lists and based on the selection of Employee item, you get the other two lists populated accordingly.

You will notice one two sided cross arrow symbol. Clicking on it will show related data in to the lists.

Column validation in SharePoint 2010

Hi,

Earlier in MOSS 2007, it was not possible to validate the value of the field at the time of entering or updating the value in the list items. We could do that, but we had to rely on handlers or custom fields.

Yes, still for some requirement we have to go with these options, but at least for the basic and indeed useful now we have something called column validation in SharePoint 2010.

I have one Employee List ready in my SharePoint site and I have added three columns to it. It has Joining Date, Leaving Date and weight.

We will see two things here. First is the field level validation and the other is item level validation. You can treat this as a custom field validation and event handler validation.

Go to list setting and then click on the weight and now we can see one new section which says column validation. So go ahead and add below condition and type in the user message that you would like to see when condition becomes false.




Click on ok.

Now being on the list settings page, you can see one more new option which is validation settings. Click on it to open a page.



In validation settings, you can actually compare one field with the other field from the list and then show a message to the user. Here in our list for example, we have two date fields and of course joining date cannot be greater than Leaving date. Hence we need to validate these two date fields.

Hence we put this formula inside it.



Now go to the list and add new item and enter weight less than 50 and enter joining date greater than leaving date and see validation triggering.



Now this is a cool new feature of SP 2010, isn’t it?

But yes do remember that we can have only one list validation, which is unfortunate i think and column validations are evaluated first and then list validation.

Add web parts to New, Edit and Display form in SharePoint 2010

Hi,

We all know that it is really easy to add web parts in the new, edit and display forms of a list in MOSS 2007. We can simply add ToolPaneView=1, ToolPaneView=2 or ToolPaneView=3 in the query string and we get a web part zones on the page to add web parts.

Well, in SharePoint 2010, we can still do that. You must be wondering that now that when we click on the NewItem or edit or display item, New window comes up. So how we can add them?

Idea is very simple and it is still as simple as it was in MOSS 2007. We can achieve the same in different ways.

For new Item right click and choose between Open in New Tab or Open in New Window. This will actually surpasses the form that pops up when you actually client on New Item link.



Or you can simple type in the web url / lists / {list name}/NewForm.aspx and then pass the toolpane parameters. Same goes for edit form. Use Editform.aspx?ID={ID} in new window and then use toolpane parameters to add web parts. Same way for the DispForm.aspx?ID={ID}.

And in case you want the forms even to open normally while creating, editing or displaying item, then go to list settings – advanced settings – dialogs and then select NO.

Unique column in SharePoint 2010

Hi,

In MOSS 2007, we do not have any option to set unique column value for the field in document library or list. We can still achieve that with the help of event handlers and adding some code in the ItemAdding event. But still there was no direct way to do it.

Now in SharePoint 2010, we have the built in functionality to enforce the uniqueness of the field. But yes do remember that the field must be indexed first before you want to set it as a primary key.

Well, just a quick question. Do we have anything as primary key in MOSS 2007? Yes, we do have and that is ID field in list or library. However that does not solve the purpose as it is just used to identify items and used heavily in coding purpose.

So let’s see how to go ahead with primary key in list field.

Create a list in SharePoint 2010 site and then create a field that you want to have a primary key. Go to list settings. Go to indexed columns and choose that column to be indexed.

You can index up to 20 fields per list as you can see in the screen shot. I have already created one index which is on EmployeeID field in the list.



When you create a field now then we have an option to enforce the unique value. This can be set only when you have made that column indexed. So if you have not indexed this column, SP 2010 will ask you to do it.



Once you set this field as unique key, then go ahead and add one value and try to add another with the same. It will not allow you to do so and show the message.






Unique field is case insensitive. So if you have Microsoft and MICROSOFT, it means same to the field. It is unique.it won’t allow you to save.

Same is enforced even when you update the item.

Supported Column Types
• Single line of text
• Choice field (but not multichoice)
• Number
• Currency
• Date/ Time
• Lookup (but not multivalve)
• Person or Group (but not multivalve)
• Title (but not in a document library)

Do remember that When you enforce uniqueness on a Lookup column, the list item in the target list can have only one list item looking up to it from the child list.

Result is out

Hi All,

Result is out and here is what we have got. Looks like people still not aware about what web matrix is and can do.

Re ordering lists column in SharePoint programmatically

Hi,

We all know that re ordering of fields from SharePoint UI is pretty easy. Yes it is. But in this post, we are going to see how we can do the same with the help of code.

It is very simple. Just write down following code and you should be good to go. Before that just have a look at the following list that I have created just to show you the original order of the fields.




Now write down the following code and that will change the order in which we have specified in the array. Do remember that in case the field name has space in between, then you must give _x0020_ as replacement of the space. If you do not give _x0020_ then also it will work without any problem and you will not be able to see these re ordering of fields on the form and then you might wonder why it is not changing? So take care of that.

I've also shown how to make field required.

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
using (SPSite site = new SPSite("{site_url}"))
{
using (SPWeb web = site.OpenWeb())
{
SPContentType contentType = web.Lists["Reorder Fields Test List"].ContentTypes["Item"];
SPFieldLinkCollection fields = contentType.FieldLinks;

web.AllowUnsafeUpdates = true;

fields.Reorder(new[] { "First_x0020_Name", "Last_x0020_Name", "Blood_x0020_Group", "Title", "Birth_x0020_Date" });
fields["Birth_x0020_Date"].Required = true;
contentType.Update();


web.AllowUnsafeUpdates = false;
}
}
}
}

And see the output.





Well one important thing to note here is that this will change the order of fields in the New and Edit form. but it won't change for display form. for that you need to change the view.

Result is out

Hi,

Finally the result is out and here is what people think.

Remove table of content maximum limit

We know how table of content web part can help us providing a way to show up all links of libraries and lists on one single page for ease of access.

However when it comes to a size limit, it can show up to 50 links at a time. It does not show if you have more than 50 items.

So, how to remove this limitation? Follow the steps below. Make sure that you take a backup of web.config file of the port number of web server. (Do the same for each server if you have farm environment)

1) Open web.config for specific port for web application.
(C:\inetpub\wwwroot\wss\virtualdirectories\port)

2) Find below entries

<add name="GlobalNavSiteMapProvider" description="CMS provider for Global navigation" type="Microsoft.SharePoint.Publishing.Navigation.PortalSiteMapProvider, Microsoft.SharePoint.Publishing, Version=13.0.0.0, Culture=neutral, PublicKeyToken=94de0004b6e3fcc5" NavigationType="Global" EncodeOutput="true" />
<add name="CombinedNavSiteMapProvider" description="CMS provider for Combined navigation" type="Microsoft.SharePoint.Publishing.Navigation.PortalSiteMapProvider, Microsoft.SharePoint.Publishing, Version=13.0.0.0, Culture=neutral, PublicKeyToken=94de0004b6e3fcc5" NavigationType="Combined" EncodeOutput="true" />
<add name="CurrentNavSiteMapProvider" description="CMS provider for Current navigation" type="Microsoft.SharePoint.Publishing.Navigation.PortalSiteMapProvider, Microsoft.SharePoint.Publishing, Version=13.0.0.0, Culture=neutral, PublicKeyToken=94de0004b6e3fcc5" NavigationType="Current" EncodeOutput="true" />
<add name="CurrentNavSiteMapProviderNoEncode" description="CMS provider for Current navigation, no encoding of output" type="Microsoft.SharePoint.Publishing.Navigation.PortalSiteMapProvider, Microsoft.SharePoint.Publishing, Version=13.0.0.0, Culture=neutral, PublicKeyToken=94de0004b6e3fcc5" NavigationType="Current" EncodeOutput="false" />


We need to add DynamicChildLimit="0" before the end tag "/>" on each line to disable the default 50 elements limit.

0 means no limit, If you want to limit it to specific number, specify that number there.

Example

<add name="GlobalNavSiteMapProvider" description="CMS provider for Global navigation" type="Microsoft.SharePoint.Publishing.Navigation.PortalSiteMapProvider, Microsoft.SharePoint.Publishing, Version=13.0.0.0, Culture=neutral, PublicKeyToken=94de0004b6e3fcc5" NavigationType="Global" EncodeOutput="true" DynamicChildLimit="0" />

Remember, you have to add this attribute in all four tags.

Column ordering link does not appear

I was working on one list for some stiff and suddenly I found that I was not able to locate column ordering in list settings page. Well, where did it go? Because I saw it earlier and suddenly it is not there now.

Well, the only change that happened was I enabled the management of content types and hence it went inside the content type.

So if you do not see column ordering and enabled the content types, it will be in content type settings, not in the list settings page.