SharePoint 2007 (MOSS/WSS) – How to add a new column (Custom BDC Column) in Discussion Board (Part2) – Step by Step

Requirement: This is an enhancement of the List Definition project we have created in previous post (SharePoint 2007 (MOSS/WSS) – How to add a new column in Discussion Board showing the Latest Thread for each Discussion – Step by Step). The new requirement is: I want to add new column that will fetch data from BDC data source. This can be done Out of the Box. But I have some additional requirements. I want this column to show CategoryName field from Categories table of Northwind database. And in Display view the column will have a hyperlink which will take you to https://www.contoso.com/?id=ID. Where ID is CategoryID from Categories table.

Step 1:

Start VS 2008 and open our previous project CustomDiscussionBoard and click OK. In the solution explorer right click on the project and choose Add>New Item>Field Control and change the name from FieldControl1 to DataSelector.

Step 2:

Select View from the Menu bar and select Other Windows>WSP View. Go back to Solution Explorer once again and open fldtypes_DataSelector.xml file. Between these 2 tags:

<Field Name="UserCreatable">TRUE</Field>

and

<Field Name="FieldTypeClass">3f2da0f5-4e0a-4d8d-a853-907816e06328</Field>

add following lines:

<Field Name="ShowOnListCreate">TRUE</Field>

<Field Name="ShowOnSurveyCreate">TRUE</Field>

<Field Name="ShowOnDocumentLibrary">FALSE</Field>

<Field Name="ShowOnColumnTemplateCreate">TRUE</Field>

<Field Name="Sortable">TRUE</Field>

<Field Name="Filterable">TRUE</Field>

Before </FieldType> tag, add the following lines:

<RenderPattern Name="DisplayPattern">

            <HTML><![CDATA[<SCRIPT>var linkval="]]></HTML>

            <Column/>

            <HTML>

                  <![CDATA[";</SCRIPT>]]>

            </HTML>

            <HTML><![CDATA[<A HREF="]]></HTML>

            <HTML><![CDATA[https://www.contoso.com?id=]]></HTML>

            <Column/>

            <HTML><![CDATA[">]]></HTML>

            <HTML><![CDATA[<SCRIPT>document.write(linkval.substring(linkval.indexOf('$#')+2));</SCRIPT>]]></HTML>

            <HTML><![CDATA[</A>]]></HTML>

      </RenderPattern>

Step 3:

Right click on Templates folder in Solution Explorer and select Add>New Folder and rename it as controltemplates. Now right click on the controltemplates folder and select Add>New Item. In the Add New Item Window select Visual C# Project Items>text file. Change the name as DataSelector.ascx. Open the file and add these lines into the file:

<%@ Control Language="C#" %>

<%@ Assembly Name="CustomDiscussionBoard, Version=1.0.0.0, Culture=neutral, PublicKeyToken=9f4da00116c38ec5" %>

<%@ Register TagPrefix="SharePoint" Assembly="Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" Namespace="Microsoft.SharePoint.WebControls" %>

<SharePoint:RenderingTemplate Id="DataSelectorTemplate" runat="server">

    <Template>

        <asp:Label runat=server ID="lblDataSelector" Text="Select Data:" />

        <asp:DropDownList runat=server ID="lstDataSelector" />

    </Template>

</SharePoint:RenderingTemplate>

Close the DataSelector.ascx file and open it once again.

Now build the solution and go to bin>debug folder at the solution’s physical file location. Copy the path from the address bar and open Visual Studio Command Prompt window. CD to the path copied above and issue this command sn –T CustomDiscussionBoard.dll. This will give you the public key token for the library. Copy it and replace the public key token given in this line at DataSelector.ascx file:

<%@ Assembly Name=" CustomDiscussionBoard, Version=1.0.0.0, Culture=neutral, PublicKeyToken=

Step 4:

Write click on the project and select Add>New Item>Content Type. Change the name to CTDataSelector and click Add. Select the Base Content Type as Discussion and click Ok without selecting the checkbox.

Step 5:

In the solution explorer open the CTDataSelector.xml file under CTDataSelector folder. Add the following line within <FieldRefs> </FieldRefs> tags.

<FieldRef ID="{D23E7B2E-5895-45e4-AE40-8BDF4090DDD5}" Name="DataSelector" Required="TRUE" ShowInDisplayForm="TRUE" ShowInNewForm="TRUE" ShowInEditForm="TRUE" />

Before </Elements> Tag add following line:

<Field ID="{8DC58E9A-98D8-44e2-96E7-514CC9E2A32B}" Type="DataSelectorField" Name="DataSelector" DisplayName="DataSelector" Sealed="TRUE" StaticName="DataSelector" >

      </Field>

Replace the GUIDs of FieldRef and Field tags using GUIDGen.

Also change the Description and Name tag to “Custom Discussion".

Step 6:

Copy the entire Field tag from CTDataSelector.xml and

Now open the scema.xml file under CustomDiscussionBoard folder and paste it within <Fields></Fields> tag after the first <Field></Field> tag.

Step 7:

Open CTDataSelector.xml once again and copy the <ContentType ID (copy the content within “”). Open Schema.xml file under CustomDiscussionBoard folder and in the first <ContentTypeRef ID Tag replace the content (within “”).

Step 8:

In the solution explorer add a new reference to microsoft.sharepoint.portal. You will find it under: C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\ISAPI\microsoft.sharepoint.portal.dll.

Now open DataSelector.FieldControl.cs file under DataSelector folder. Add these using statements:

using Microsoft.Office.Server.ApplicationRegistry.Runtime;

using Microsoft.Office.Server.ApplicationRegistry.Infrastructure;

using Microsoft.Office.Server.ApplicationRegistry.MetadataModel;

Change the class statement:

public class DataSelectorFieldControl : TextField

to

public class DataSelectorFieldControl : BaseFieldControl

Now add following lines within the opening and closing curly braces of class:

protected DropDownList lstDataSelector;

        protected override string DefaultTemplateName

        {

            get { return "DataSelectorTemplate"; }

        }

        public override object Value

        {

            get

            {

                EnsureChildControls();

              return lstDataSelector.SelectedValue.ToString() + "$#" + lstDataSelector.SelectedItem.Text;

            }

            set

            {

                try

                {

                    EnsureChildControls();

                    string newValue = value.ToString();

                    lstDataSelector.Items.FindByValue(newValue.Substring(0, newValue.IndexOf("$#"))).Selected = true;

                }

                catch { }

            }

        }

        protected override void CreateChildControls()

        {

            if (Field == null) return;

            base.CreateChildControls();

            //Don't render fields if we are just displaying the field

            if (ControlMode == SPControlMode.Display)

                return;

            lstDataSelector = (DropDownList)TemplateContainer.FindControl("lstDataSelector");

            if (lstDataSelector == null)

                throw new ArgumentException("lstDataSelector is null. Corrupted DataSelector.ascx file.");

  if (this.ControlMode == SPControlMode.Edit || this.ControlMode == SPControlMode.New)

            {

                // I have used Nothwind database use your own LOBSystem, LOBSystemInstances and Entities

                LobSystem nwSystem = ApplicationRegistry.GetLobSystems()["NorthwindLOBSystem"];

                LobSystemInstance nwSystemInstance = nwSystem.GetLobSystemInstances()["NorthwindInstance"];

                Entity categoryEntity = nwSystem.GetEntities()["dbo.Categories"];

                FilterCollection fc = categoryEntity.GetFinderFilters();

                IEntityInstanceEnumerator nwInstanceEnumerator = categoryEntity.FindFiltered(fc, nwSystemInstance);

                while (nwInstanceEnumerator.MoveNext())

           {

                    IEntityInstance category = nwInstanceEnumerator.Current;

                    System.Web.UI.WebControls.ListItem newListItem = new System.Web.UI.WebControls.ListItem(category["CategoryName"].ToString(), category["CategoryID"].ToString());

                    lstDataSelector.Items.Add(newListItem);

                }

            }

        }

Step 9:

Create a new BDC metadata file and upload it into your SSP. Here is the sample that I have used for the code. You can see I have used SQL user to connect to the database. You have to make suitable changes for your server, user, password etc.

Step 10:

Now Build the project and deploy it from the Build menu.

Open the destinantion site and go to Site Actions>Create. You will find CustomDiscussionBoard under Communications section. You can create a new discussion board or can use discussion board called testdisc that we have created previously. Once created and opened go to Settings and Discussion Board Settings. Under Views section select Flat. In the next window make sure Make this default view option is selected and select DataSelector column from the Columns section and click OK. Do the same with the Subject view too. Once done you can create new discussions and add replies to those discussions. You can see the DataSelector dropdown available as a new field. You can select a new value and in subject view you can see the CategoryName is shown with a link to https://www.contoso.com/?id=CategoryID$#CategoryName. So although it contains extra element ($#CategoryName) but we can still remove this part in the target page code and can get the desired result.

nw.xml