• Category Archives BCS
  • Create a Custom Content Type Lookup Field that Pulls Data From External (SQL) Custom List.

    Creating a custom content type that is a lookup to an external content list is not as straight forward as all that. But once you have completely built one, it’s significantly easier than resources on the web would have you believe. The only downside is there are A LOT of steps.

    We had a scenario where we had several Custom Content Types (CCT). Each of the CCTs needed a custom site column look up field that pulled data from a SQL database. A fairly common business requirement but quite tasking to complete from scratch. As I’m a big proponent of visual aids, I’ll include as many pictures as possible and keep the instructions to a minimum but clear.

    The task consists of the following steps:

    • Secure Store Service configuration
    • SP Designer – Create (ECT), read item & read List & custom external list
    • BDC Service App (permissions)
    • Add to a custom list to test the ECT functionality
    • Verify the new list in the hosted site.
    • Create Custom Site Column

    Secure Store Service (SSS)

    In this situation, we need to pass a security account to the SQL database. This is where the SSS comes into play.

    NOTE: Before beginning, this is assuming you already have an account configured on the SQL side that has the needed access to the data you are querying for.
    Access the SSS in Central Administration > Application Management > Secure Store Service
    1) Create a secure store item by selecting ‘New’.

    2)     In the next window complete the fields below and select ‘next’.

    3) The next steps are titles for the columns for storing the credentials. NOTE: that this is not the credentials themselves.

    4) You can setup custom permissions for a specific set of users to be able to manage this SS item. Select OK.


    5)     Enable the checkbox and hit the “Set” button to add the credentials.


    6)     Enter the owner of the credentials and the account credentials. Hit OK.


    The SS account has now been created and is ready to be utilized in the setup of the External Content Type we will create in SharePoint Designer.

    *Note: If this is to be setup on a farm, the Secure Store Service will need to be enabled on each server hosting a web interface that will utilize this account.


    External Content Type Creation & Configuration

    Now we are going to create the connection that pulls the data from the database into an external SharePoint list.

    1)     Open SharePoint and connect to the site you want to build out your connection to.

    2)     Select ‘External Content Types’ from the Site Navigation window and choose the ‘External Content Type’ button in the top left corner of the ribbon.

    3)  Complete the following fields: Name & Display Name. Then click the link ‘Click here to discover external data sources and define operations’.

    4)    On the next screen, select “Add Connection”. Since our connection is SQL based, we will choose SQL from the drop down.

    5)     Next, complete all the fields, and refer back to what your original SSS Identity was for the Secure Store App ID:

    6)    Now we drill into the database for the table we need to pull data from & create the needed ECT Operations. We do this by locating the respective table, R+Click and choose the needed option. I only need READ access, so I will choose Read of both Item & List options. I will choose Item first.

    *note: At minimum a Read Item and a Read List must be created and configured before this ECT can be saved or an external list created.

    7)    The Operation and Display names are inconsequential to the purpose of my needed functionality. So we will leave these as they are. Click Next.

    8)     The next window shows the Input parameters. We only need a single column to map as an identifier for the columns we are tying to the list we are building. Hit ‘Next’.

    9)     Here we need to make sure we map at least one identifier and check all the fields that we wish to include in the Custom list. Click Finish.

    NOTE: The display name field is the name that will get translated to the column of the external table. So make sure this is a legible name for you & your end users instead of a naming standard for your SQL data column.

    10)     Next is to create the Read List. R+Click on the column again and choose the Read List Operation.


    11)     Again, hit Next. Here you can include filter parameters if you like. At this time, I do not need them. There are plenty of good resources that can walk you through this process. Hit Next again.


    12)     Here in the Return Parameter Configuration we will do the same thing we did in the Read Item Configuration. You also have an option called “Show in Picker”. Checking this will enable the specified column to be displayed in our lookup column (picker content type). We will want to check this for the needed columns pulled from the table. Hit Finish.

    13)     We now need to create the Custom List on the SP site. Hit the Create Lists & Forms button in the ribbon and follow the instructions for the list configuration.

    1)     Once the external list is created, you can view it on the site you connected this to. I would suggest gonig to the site and make sure the data is pulling in properly. At this point, you are now ready to create the custom lookup content type.

    2)     In the site, go to Site Actions > Site Settings > under Galleries, select Site columns.

    3)     Select “Create” at the top of the page.  From here the rest is pretty self-explanatory. Choose the “Lookup” information type and the lower column settings allow you to select the newly created external content list and from all associated columns from within that list. Once you save the custom Site Column, you can now add it to any library/list you wish.

    *NOTE: There is another option at the base of the lookup content type that allows you to add additional columns that are associated with the external content list. This feature, though offered, is unfortunately NOT supported and does not work. Even if you try to build it out programmatically.

    Links to some information I found useful in completing my task:







  • Customizing a SharePoint External Content List with Infopath

    Anyone trying to customize the boring layout of an external list in InfoPath that you made in SharePoint, is warmly greeted with the fun loving error: “An unknown error has occurred. The form template has been published to the server but it can only be opened in InfoPath Filler.”


    The solution is far more simple than having to hard code anything like so many different sites claim. and I won’t go into the how-to’s for creating a Business Connectivity Service connection (BCS) or an External Content Type (ECT). As there are many other tutorials out on Google that will cover it well. A great resource is Clayton Cobb. He doesn’t fancy himself a coder so he actually speaks in a language I can understand. Links to his tutorials directly are the following: BCS & ECT 1 BCS & ECT 2 BCS & ECT 3

    To get right to the heart of the issue, let’s look at the error itself. “An unknown error has occurred. The form template has been published to the server but it can only be opened in InfoPath Filler.” It doesn’t tell you much at all. Except that it doesn’t like it when you try to upload the InfoPath form (template.xsn). When we push that button in SharePoint Designer to convert the External List to an InfoPath form,


    it creates a new folder called “Item”. Then adds the following files to it: displayifs.aspx, editifs.aspx, newifs.aspx & template.xsn. IT also sets the new .aspx files as the default views for the list and the template.xsn file holds all the InfoPath form data for the list.

    With that said, open SharePoint Designer in file view and navigate to the list folder containing the new files. Once there, and with the list already open in InfoPath, delete the template.xsn.

    Now, magically, you can save your new InfoPath form without issue.
    Don’t do your happy dance just yet though! If you try to add/modify/view the items in SharePoint, you’ll get an error. Two things need to happen now.
    1) In InfoPath, create the corresponding views of the item views in SharePoint (New, Edit and Display). Then save the template.xsn file.


    2) Modify the three new .aspx files in SharePoint Designer to point to the new InfoPath template.xsn file by highlighting the list web part and changing the value of FormLocation to the correct location. See the field in the top right corner of the pic below.


    Then in the web part properties of each, set the view to match the corresponding file type.

    Save each of the .aspx files and enjoy your new fully customized external content list form in all it’s glory. *BAM*