SharePoint 2010 - List Relationship

Today, I got the access to the SharePoint 2010 Beta and guess what I saw? The most beautiful thing that could happen to the SharePoint lists...Relationships. Just like Database relations. :o)

Lets take a look at the following screen shot: While creating a lookup column in a list, I noticed following enhancements:

1. I can choose ID Column from the parent list.Very very important for building reports/views.
2. If I choose other column (Title etc), I still can include ID as an additional field.
3. Restrict & Cascade delete option. :o) If I choose restrict and want to delete parent item, having child items...it will not allow me to do so. If I choose cassade and want to delete parent item...it will delete all the child items along with the parent.

SharePoint 2010 Lists rock!

Join Lists Using SharePoint Designer 2007

Background:
There is no parent-child relationship support in MOSS 2007 lists by default. But as a developer, we all know it is the most common functionality while storing and displaying the data. Following is the technique that I discovered somewhere on the internet to display the data using DataView Control in SharePoint Designer(while developing reports for my last project), but unfortunatily I lost the original source and thought to reproduce it in my blog with some enhancements.

Implementation:
Following are the steps to perform Inner Join on two lists using DataView Control in SharePoint Designer:

1. We need following two lists(with data in them):

Department (ID, Title)

Student (ID,Department_x0020_ID, Title)

2. Open SharePoint Designer and create a new ASPX page.

3. Create a linked datasource by following steps:

1. Select Create a new Linked Source... from Data Source Library tab.


2. In Data Source Properties dialog, select Configure Linked Source... button.


3. From Link Data Sources Wizard, select Students and Department Lists and hit Next.


4. Choose Join the contents... option and select Finish.


5. Then select OK to get the New Data Source.


6. Expand the New Data Source and select Show Data option.


7. Under Data Source Details tab, you should see both lists (Department and student).

4. Insert a Data View control into the ASPX page.
5. Under Data Source Details tab, expand the Students list and drag and drop the Title column to Data View control and it will display all the students.
6. Now place your cursur in Title column and select Table -> Insert -> Column to Right. In this column we will display Department.Title.
7. Place your cursor on the first row of new column, select Title Column from Department list and select the Insert selected field as... button and choose Joined Subview opion.
8. In Join Subview dialog, select Department_x0020_ID = ID and hit OK. (Pic 12)
9. Thats it :o).
10. Play with the HTML to make it look like a report.

Get the actual error message in SharePoint Site Page

In web.config change the following:

1. CallStack="false"

2. <customErrors mode="On" />

Now refresh the page to see the actual ASP.NET error message.

Before:


After:

Security on SharePoint Website Controls with SPSecurityTrimmedControl

I came across a requirement while building a site in SharePoint 2007 where I have to display the left menu only to administrator and Thanks to Mark Wagner for his quick and to the point post on SPSecurityTrimmedControl.

What exactly I did was:

1. Open the Default.aspx in SharePoint Designer.
2. Select the left menu and then select Create Custom Content.
3. Arranged the tags as follow:

<asp:content contentplaceholderid="PlaceHolderLeftNavBar" id="Content2" runat="server">
<sharepoint:spsecuritytrimmedcontrol permissionsstring="ManageWeb" runat="server">

All the divs and HTML for the menu goes here.

</sharepoint:spsecuritytrimmedcontrol>
</asp:content>

Now the menu will only appear for users having administrator rights.
Note: Also, you can directly implement this in Master Page for hiding View All Site Content.

SharePoint 2003 to MOSS 2007 Migration

Senario: You have two servers. One has SharePoint 2003 and other have MOSS 2007 installed. You want to migrate a website collection from SharePoint 2003 to MOSS2007.
Scan the SharePoint 2003 Website
1. PreScan Tool
prescan.exe /c preupgradescanconfig.xml /v http://intranet2. Check the Orphan Records
stsadm -o databaserepair -url http://intranet -databasename MyIntranet_Site3. Delete the Orphan Records
stsadm -o databaserepair -url http://intranet -databasename MyIntranet_Site -deletecorruption4. Check the Orphan Records
stsadm -o databaserepair -url http://intranet -databasename MyIntranet_Site
5. PreScan Tool
prescan.exe /c preupgradescanconfig.xml /v http://intranet

Content Database Migration
1. Go to SQL Server and create the website’s Content Database's backup after making it read only.
2. Copy the database's backup file to the New Server.
3. Create a new Database in New SQL Server and Restore the database (overwrite).

Create the MOSS 2007 Website
1. Create the WebApplication in MOSS 2007. Don’t create any site collection.
2. Remove the content application database
stsadm.exe -o deletecontentdb -databasename WSS_Content_Intranet_80 -url http://newintranetsite/
3. Attach the new database
stsadm.exe -o addcontentdb -url http://newintranetsitet/ –databasename Portal_Site_Restore

Ref 1: http://farhanfaiz.wordpress.com/2008/05/23/sharepoint-upgrade-database-migration
Ref 2: http://sharemypoint.blogspot.com/2007/04/prescanexe-finished-with-failure.html

SharePoint Designer 2007 - Hide the Quick Launch Link

Today my manager came up with the requirement that Recycle Bin Link in Quick Launch Menu should be visible to Administrator only.
If it was the case for a webpart then I could easily specify the Target Audience and voila. But unfortunately webpart zone is not available for Quick Launch Menu and I didn’t want to write custom menu just to hide the RecycleBin link so the genius came up with the following dirty trick. (Yes, I have recognized myself as a genius since childhood).

1. Launch the SharePoint Designer.
2. Navigate to _catalogs -> masterpage -> default.master.
3. Create a backup of the master page file and open original for editing.
4. Scroll down till the bottom and paste the following javascript code right before </Body> Tag:

var UserID = document.getElementById('zz8_Menu').innerHTML;
UserID = UserID.split('<')[0]; UserID = UserID.split('\\')[1]; if (UserID != 'Administrator') {  //Hide Recyclebin  var objRecycleBin = document.getElementById('ctl00_PlaceHolderLeftNavBar_idNavLinkRecycleBin'); if (objRecycleBin)  {  objRecycleBin.style.display = 'none';  }  }   

5. Save and it should hide Recyclebin for all users except for the administrator.

Note: Please check for the Ids ctl00_PlaceHolderLeftNavBar_idNavLinkRecycleBin and zz8_Menu by view source.

SharePoint Designer 2007 - Join Two Lists

We all come across situation at times when we have to display data from two different lists in a single view, specially in reporting. I was looking if SharePoint Designer support something like Join View etc when I came across Sahil Malik's following amazing article:

URL: http://blah.winsmarts.com/2007-10-Performing_joins_between_SharePoint_lists.aspx?postID=359

SharePoint Website – Login Dialog Fix

Problem: Windows user get Login dialog again and again when visiting the page with custom webparts, even when they are logged into the site.

Solution:
1. Open Computer Management and select Users.
2. Double click the user and select Member Of tab and add WSS_WPG.



This will add user to WSS_WPG group and it should solve the problem.

Note: Removing users from WSS_ADMIN_WPG will hide Site Actions from the website.

SharePoint: List Event Handler

Sometimes we get business requirements like populate a particular field with some pre-defined specific data when item is added or send an alert when item is added etc. SharePoint Designer Workflows solve these kind of basic problem like sending alerts, update the list item etc BUT you need event handlers to be more flexible and to have more control.

In this post I will explain how to quickly develop and deploy SharePoint List Events.

1. Create a Class Library Project.
2. Add reference to SharePoint DLL.
3. Inherit your class with SPItemEventReceiver
4. Override ItemAdded(SPItemEventProperties properties) function
5. Your code should look like:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SharePoint;

namespace SPListEvents
{
    public class CandidateListHandler: SPItemEventReceiver
    {
        public override void ItemAdded(SPItemEventProperties properties)
        {
            try
            {
                SPListItem item = properties.ListItem;
                item["Status"] = "Added";
                item["Company"] = "Company1";
                item.Update();

            }
            catch (Exception exp)
            {
                  //do something with exp
            }
            finally
            {
                base.ItemAdded(properties);
            }
        }
    }
}

Above code will get fired on the associated list, when a new item will be created. Pretty easy hah.

6. Sign your dll by selecting Project -> Properties -> Signing Tab and should look like:




7. Compile
8. Deploy the SPListEvents.dll to GAC (c:\windows\assembly)

Note down the Public Key Token for the installed DLL to use it in the below deployment code e.g. PublicKeyToken=cd22d2b1bc79915b

[Assign the Event Handler to a specific list]

9. Create a Windows application
10. Add a button and write following code on its click event.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Microsoft.SharePoint;

namespace InstallSPEventHandler
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {

            SPSite site = new SPSite("http://mysite.com:1100");
            SPWeb web = site.OpenWeb();
            SPList survey = web.Lists["Candidates"];

            string assemblyName = "SPListEvents, Version=1.0.0.0, Culture=neutral, PublicKeyToken=cd22d2b1bc79915b";
            string className = "SPListEvents.CandidateListHandler";

            survey.EventReceivers.Add(SPEventReceiverType.ItemAdded, assemblyName, className);
            //survey.EventReceivers.Add(SPEventReceiverType.ItemUpdated, assemblyName, className);
            MessageBox.Show("Done");
        }
    }
}

11. Run the Desktop Application and click the button to register the ItemAdded event handler to Candidate list.

12.Your solution might look like:


13. Add new item in Candidate list and Status and Company fields shoyuld get poulated automatically.
14.That’s it.

SharePoint: CAML Query - AND

I was ANDing three SharePoint fields in CAML Query based on the regular logic (place And between all three fields) and was getting errors with absolutely no clue that what possibaly could be wrong.
Here is the right way...thanks to Google:

Target: field1 && field2 && field3
Correct CAML:

<where>
  <and>
    <and>
      <eq>
        <fieldref name="field1">
        <value type="Text">val1</value>
      </fieldref></eq>
      <eq>
        <fieldref name="field2">
        <value type="Text">val2</value>
      </fieldref></eq>
    </and>
    <eq>
      <fieldref name="field3">
      <value type="Text">val3</value>
    </fieldref>
   </eq>
  </and>
</where>

Happy CAMLing!

Date Comparison in XSL

Since XSL in SharePoint Designer does not have a very good support for date comparisons. I Googled for at least more than half of the day for a decent date comparison and came across this post as a solution by ma man Kotendra. However, this post assumes that date is provided in ISO format (YYYMMDD). This was not my case…so I Googled bit more and bit more and little bit more :o) and here is the solution.

Below is the formula (XSLT Filtering) to compare dates in SharePoint Designer using XSL:

[
number(ddwrt:FormatDateTime(ddwrt:FormatDate(string(@Open_x0020_Date),1033,1),1033,'yyyyMMdd'))
>=
number(ddwrt:FormatDateTime(ddwrt:FormatDate(string($varFromDate),1033,1),1033,'yyyyMMdd'))
]

Above formula will convert date 5/31/2009 into 20093105 and treat it as a number to compare with another number converted from date.

Thanks!

Implementing Search in a SharePoint List

There are times when we want to search in a SharePoint list only, not in the whole portal. I ran into this requirement in my current project and implemented the search on a list using SharePoint Designer. I didn’t want to create a WebPart and with some research I was able to do it in SharePoint Designer. In this post I will explain what exactly I did.

To implement the search I created following two pages:

1. Search.aspx (This page will take input parameters)
2. SearchResults.aspx (This page will display results in DataView Control and filter the data based on the input parameters provided in the Search.aspx)

Let’s start the implementation:

1. I will not go into the details of Search.aspx page. All you can have on the Search.aspx page is a text box and a submit button. Clicking the button will redirect the use to SearchResults.aspx with the parameters attached into the Query String. Our final URL will look like:

http://www.domain.com/sites/MySite/ListName/SearchResults.aspx?skill=Sharepoint

Yup, I am searching for people in my list who have SharePoint skills.

2. Open the SearchResults.aspx in SharePoint Designer and insert the DataView Control. I applied Master Page so those pages don’t look like an alien :o).
3. Assign the DataSource of the list and at this point DataView should display all the items in the list.
4. In the designer, select <WebPartPages:DataFormWebPart> and open the Common Data View Task window and choose Parameters.

5. Create a Parameter varTechnicalSkill and Select Query String from Parameter Source drop down and provide the Query String Variable which is skill in our case.

6. Again, open the Common Data View Task window and choose Filter this time.
7. Select Add XSLT Filtering and hit Edit… button. Don’t get confuse with the varExperience. It’s just another variable and has absolutely nothing to do with our example.

8. In the Advance Condition window, in Edit the XPath expression provide the following formula for search:

[contains(@Skill,$varTechnicalSkill)]

Contains is a XSL function
@Skill is the column in the list
$varTechnicalSkill is the parameter which will get populated by the Query String skill.

Hit OK and then OK again.

9. If you view the source then you should be able to see the following XSL code added to the <XSL:tamplate>:

<xsl:variable name="Rows" select="/dsQueryResponse/Rows/Row[contains(@Tech_x0020_Skill_x0020__x0023_1,$varTechnicalSkill)]"/>

10. Save the SearchResults.aspx page and that’s it. We are good to use the search functionality.

SharePoint: Accessing XSL variables in JavaScript

As we all know for a successful SharePoint project we need SharePoint knowledge, development skills, ability to google and prayers…lots of them.

During my current project I was developing a report which will generate a letter and the body of that letter will be generated by the data stored in Rich Text field in a SharePoint list. Now, in my case that body was a predefined template of the letter and I need to replace few keywords in the letter body using JavaScript. To achieve all this I need to access the Body variable (carrying the letter template) into the javaScript function to play with it.

Following are the steps to access I took to access XSL variable into my JavaScript function:

1. Open the website in SharePoint Designer.
2. Create an empty page Report.aspx.
3. Insert DataForm Webpart by selecting Insert-> SharePoint Controls -> DataView.
4. Hide all the columns and your WebPart should look like the following:

5. Also, you should be able to see the variable @Body in the source code window under <datafields> tag. Both are highlighted in the above image.
6. Now add a hidden field in the page under XSL tag and assign its value to @Body variable. Since, @Body variable is accessible in SharePoint generated XSL only. I added hidden field under one of the <xsl:template> tags.



7. Now you have got the value of @Body into the hidden field and you can access the field’s value easily with the following line of javascript, I called this line at the end of the HTML <body> tag:

var strBody = document.getElementById('txtBody').value;

8. Thats it.