Posts Tagged ‘External List’

Author: Tobias Zimmergren
http://www.zimmergren.net | http://www.tozit.com | @zimmergren

Introduction

As many of you know, customizing a form for a SharePoint list isn’t very tricky to do – but when it comes to modifying the forms for an External List it becomes a bit more of a challenge. In this article I will walk you through how the BCS (Business Connectivity Services) model can enable you to modify the New- and Editforms by creating and utilizing your own Custom Field Controls and I will also talk about how you can change the behavior and rendering of your DisplayForm using jQuery.

Related articles about BCS that may be worthwhile:

Scenario

So let’s pose that our scenario is that we’ve got a a source of data coming in through BCS and is represented like it’s always represented out of the box with BCS. What we would like to do is to customize the New- and EditForms to allow custom logic and we would also like to change how the DispForm behaves.

In this sample we’ve got a very simplistic BCS model containing one entity with these properties:

  • Identifier (string)
  • Color (string)
  • Published (boolean)

    In SharePoint, our model is represented like this in the out of the box UI:

    Display Form New- and Edit Forms
    ScreenShot1530 image

    What we really want to achieve in this article is to manipulate the behavior of our Display- and New/Edit forms to look something like this:

    Display Form
    Published: Icon instead of Yes/No text.
    Color: Lit up with the selected color in the UI.
    New- and Edit Forms
    Color: Replaced with a custom field control
    image image

    As you can see in the last two images above, the result of our development is that we’ll be using a custom control (DropDown) instead of the standard TextBox for our Color-field, and we’ll change the way the Published-field looks in the Display Form. It should give you an idea of how you can customize and alter the behavior of your BCS External List Forms.

    So without further ado, let’s get started with modifying our forms!

    (I assume that you already have a BCS Model in place and will not iterate the steps for creating one here…)

    Modifying New/Edit forms: Custom Field Control to the rescue

    When it comes to the BCS Model, you should really always edit it through the XML and not through the Visual Studio UI. Start by right-clicking your BCS Model and selecting the "Open With…" alternative:
    image

    Then choose your favorite XML editor:
    image

    When you’re in this mode, you obviously have to be careful not to mistype or misspell anything as it can result in a broken BCS Model. Awesome :-)

    To cut it short, here’s an extract for the "Creating" Method, which references a Custom Field Control called ColorField:

                <Method Name="Create">
                  <Parameters>
                    <Parameter Name="returnEntity1" Direction="Return">
                      <TypeDescriptor Name="ReturnEntity1" TypeName="TOZIT.Samples.BCSExtensions.BdcModel1.Entity1, BdcModel1">
                        <TypeDescriptors>
                          <TypeDescriptor Name="Identifier1" IdentifierName="Identifier1" TypeName="System.String" />
                          <TypeDescriptor Name="Published" IsCollection="false" TypeName="System.Boolean" />
                          <TypeDescriptor Name="Color" TypeName="System.String">
                            <Properties>
                              <Property Name="SPCustomFieldType" Type="System.String">ColorField</Property>
                            </Properties>
                          </TypeDescriptor>
                        </TypeDescriptors>
                      </TypeDescriptor>
                    </Parameter>
                    <Parameter Name="newEntity1" Direction="In">
                      <TypeDescriptor Name="NewEntity1" TypeName="TOZIT.Samples.BCSExtensions.BdcModel1.Entity1, BdcModel1">
                        <TypeDescriptors>
                          <TypeDescriptor Name="Identifier1" IdentifierName="Identifier1" TypeName="System.String" CreatorField="true" />
                          <TypeDescriptor Name="Published" IsCollection="false" TypeName="System.Boolean" CreatorField="true" />
                          <TypeDescriptor Name="Color" TypeName="System.String" CreatorField="true">
                            <Properties>
                              <Property Name="SPCustomFieldType" Type="System.String">ColorField</Property>
                            </Properties>
                          </TypeDescriptor>
                        </TypeDescriptors>
                      </TypeDescriptor>
                    </Parameter>
                  </Parameters>
                  <MethodInstances>
                    <MethodInstance Name="Create" Type="Creator" ReturnParameterName="returnEntity1" ReturnTypeDescriptorPath="ReturnEntity1" />
                  </MethodInstances>
                </Method>

    As you can see above I’ve referenced a property called "SPCustomFieldType" in the code (Read more on BCS Custom Properties), which in turn is referencing something called "ColorField". The ColorField is my CustomField control that is simply a DropDown-box to be represented in the UI instead of the good’ol text box.

    Modifying Display Forms: jQuery to the rescue

    When it comes to modifying the DispForm of the External List, I’ve had several attempts with the documented RendererDefinition in the past and not one single time it worked for me nor any of my clients. So with that experience, I’ve resorted to using jQuery to modify the rendition of the Display Form. Obviously one could use jQuery for the New- and Edit Forms as well, if you want to.

    Short story, the jQuery looks like this:

    // Found this through google, but can't remember the source. 
    // Great script for string replacements!
    String.prototype.replaceAll = function (str1, str2, ignore) {
        return this.replace(new RegExp(str1.replace(/([\/\,\!\\\^\$\{\}\[\]\(\)\.\*\+\?\|\<\>\-\&])/g, "\\$&"), (ignore ? "gi" : "g")), (typeof (str2) == "string") ? str2.replace(/\$/g, "$$$$") : str2);
    };
    
    $(document).ready(function () 
    {
        // Render an icon instead of Yes/No text
        var publishedHtml = $('h3.ms-standardheader:contains("Published")').closest('tr').children(".ms-formbody").html().toString();
        publishedHtml = publishedHtml.replaceAll("Yes", "<img src='/_layouts/images/TOZIT.Samples.BCSExtensions/checked.png' />");
        publishedHtml = publishedHtml.replaceAll("No", "<img src='/_layouts/images/TOZIT.Samples.BCSExtensions/unchecked.png' />");
        $('h3.ms-standardheader:contains("Published")').closest('tr').children(".ms-formbody").html(publishedHtml);
    
        // Render the entire TD in the color that was chosen
        var colorHtml = $('h3.ms-standardheader:contains("Color")').closest('tr').children(".ms-formbody").html().toString();
        var colorHtmlTd = $('h3.ms-standardheader:contains("Color")').closest('tr').children(".ms-formbody").attr("style", "background-color:" + colorHtml + ";");
    });

    Essentially what this jQuery snippet does is that it will find the correct elements in the HTML markup that corresponds to my Fields (you see the :contains("Published") part? That’s where I find the Published-field in the markup). Then I simply alter the text that is sent to us from the server, replacing "Yes/No" with an image of a checked or unchecked checkbox. Pretty simple trick to light up the form a bit without the hassle of trying to get your RendererDefinition working…

    Tip: If you want to easily inject your jQuery to the External List Forms, I’d recommend using a DelegateControl override on the AdditionalPageHead. The code for my AdditionalPageHead DelegateControl looks something like this:

            protected void Page_Load(object sender, EventArgs e)
            {
                string httpRequestUrlString = HttpContext.Current.Request.Url.AbsoluteUri;
    
                // Note: You should create a smarter verification for when to load the scripts than to use a hardcoded value like I've done in this sample. 
                if ((httpRequestUrlString.Contains("/Lists/Sample Entity/") || httpRequestUrlString.Contains("/Lists/Sample%20Entity/")) && httpRequestUrlString.Contains("DispForm.aspx")) // Only render scripts if it's the Display Form on the Sample Entity list
                {
                    // Add a reference to jQuery if it isn't already loaded
                    ScriptLink.Register(this.Page, "/_layouts/TOZIT.Samples.BCSExtensions/Scripts/jquery-1.7.2.min.js", false);
                    ScriptLink.Register(this.Page, "/_layouts/TOZIT.Samples.BCSExtensions/Scripts/bcsDisplayFormScriptSample.js", false);
                }
            }


    The code snippet above simply checks the current request and determines whether it’s your specific list being loaded, and then also determine if it’s the DispForm.aspx file being served. As noted in the comments, you should modify this if-statement to suit your needs, should you decide to create a DelegateControl like this.

    Summary

    Working with BCS is both fun and challenging at the same time. Like most SharePoint projects your requirements always change, and there’s always a need for further investigating the possibilities for how we can deliver quality solutions. In this article I’ve talked about some tips for how you can modify the NewForm and EditForm of your BCS External List by simply using a Custom Field Control. I’ve also talked about how you can deliver customized forms using jQuery as in the DispForm sample above.

    As a final note I’d like to shout out to Scot Hillier who is a fellow SharePoint MVP and author of the Professional Business Connectivity Services in SharePoint 2010 book for valuable tips and awesome discussions regarding this topic.

  • Author: Tobias Zimmergren
    http://www.zimmergren.net | http://www.tozit.com | @zimmergren

    Introduction

    If you’ve been working with BCS and external lists you may have noticed that there’s no RSS feeds enabled for those lists.

    So to get around this problem I’ve created a custom RSS feed generator that essentially provides you with an RSS/ATOM feed for you so you can render it in your browser or any feed-reader of choice.

    Please note that this is a beta with a few things that needs fixing before it should be used, I’d like your feedback on it so I can complete it for you all to utilize.

    External Lists and RSS

    If you go to your SharePoint 2010 installation where you’ve got an external list you will see that you’re lacking the RSS functionality on that specific list.

    Example:
    image

    The RSS feed is by default disabled, and out of the box there’s no way to enable it for this specific type of list.

    Some people gave me the brilliant idea of using REST and the ListData.svc service to pull out the data from the lists. But as you know (or will know from now on) is that REST and External Lists is a no go. You can’t utilize the ListData.svc service to pick out information from your external lists, unfortunately.

    A custom RSS generator for your external list

    I’ve created an RSS generator feature for your external lists, which (when enabled) will give you two new buttons in your External List ribbon menu called "Custom RSS".
    The first button will take you to the Feed, and the second button is there to enable you to toggle the settings for the feed (i.e. what fields to show in the feed etc). The second button is only available to administrators of that list. Pretty sweet.

    Custom RSS Ribbon tab with an RSS Button:
    image

    Custom RSS Feed Settings dialog:
    (The RSS fields title, author, pubDate and description can be mapped to any field in your External List)
    image

    Custom RSS Feed rendered in the browser:
    (Rendered based on settings done in the Settings-dialog)
    image

    Feed opened in the RSS reader in Outlook 2010

    image

    Download and Install the RSS generator

    Alright, so this is not a complete feature as of yet – there’s some glitches that is to be fixed and tuned, but I would love your feedback if you try it out. Please use the contact form and submit any feedback or features you’d like to see in this RSS gadget.

    You can download the current version (not for production) here:
    [BETA]Zimmergren.SP2010.BCS.RSS.wsp

    Author: Tobias Zimmergren
    http://www.zimmergren.net | http://www.tozit.com | @zimmergren

    Introduction

    Article 2 in the small BCS-series:

    1. SP 2010: Getting started with the Business Connectivity Services (BCS)
    2. SP 2010: Programmatically work with External Lists (BCS) in SharePoint 2010
    3. SP 2010: Programmatically work with External Lists (BCS) using the Client Object Model

    In my previous article I talked about how you can set up a simple BCS configuration to fetch and work with external data in SharePoint 2010. In this article I will talk about how you can utilize the SharePoint 2010 object model to work with that external data, directly from the SharePoint API. It’s all really simple!

    Working with data in an External List using the SharePoint Object Model

    The code in this sample doesn’t really differ from the way you fetch information from any other list in SharePoint (2007 or 2010). This – of course – is very welcomed news, as we do not need to learn any new frameworks or tools to work with the data in our external lists. It simply works as any other SPList, basically.

    Retrieving external data, made simple:

    When fetching items from an external list, you can simply do that by utilizing the good-old SPList object. We do not need to work with any other types of namespaces or frameworks in order to do this.

    In my SQL Server I’ve got a table called “ProductList“.
    This list is filled with the following data:

    image

    Fetching some items from the external list, and displaying them in a console app:

    // Product List is my external list, that is working with data in the SQL Server!
    SPList list = web.Lists["Product List"];

    SPQuery q = new SPQuery();
    q.Query =
    “<Where><IsNotNull><FieldRef Name=’ProductID’ /></IsNotNull></Where>”;
    q.RowLimit = 100;

    SPListItemCollection col = list.GetItems(q);

    foreach (SPListItem item in col)
    Console.WriteLine(item["Name"].ToString());

    This will render the following result (fetched from the database):

    image

    The things you see in the console windows is fetched straight from the SQL Server (using a BCS connection through the External List).

    Writing data to the External List (hence, writing to the SQL Server)

    Seriously, this is way too easy as well…

    // Get the external list
    SPList list = web.Lists["Product List"];

    // Use the traditional approach to create SPListItems and hook it up with the list
    SPListItem item = list.Items.Add();
    item["Name"] = “Sample Product Wohoo“;
    item["Description"] = “Sample Description Wohoo“;
    item.Update();

    Upon running this code in your SharePoint application, it will create the SPListItem object and add a Name and Description. When you hit .Update() it will push this data through the data source connection, to your SQL server.

    Here’s what the updated data looks like:
    image

    We’re running a Beta-product!

    As you can imagine, there’s a ton of new cool things to work with in SharePoint 2010 – where the BCS is one. This article discuss the very basics of how you can retrieve information from these lists using the normal API-approach.

    At the time of this writing (during Public Beta) there isn’t any measures on performance and what impact it has on the server in comparison to alternative ways to fetch and work with the data.

    As time goes on, there will be probably be some new information on this – I’ll keep you posted when I know more.

    Summary

    As you can see, working with external data from the SharePoint API isn’t very hard to do. What you need to make sure is to have an external list set up somewhere (see this article for how you can do that) and then you can simply use the normal SPList object from the SharePoint object model to work with the external list and it’s external data from the SQL server (in my case).

    So if you haven’t already: Get on the SharePoint 2010 wagon and enjoy the ride!