Working with Business Data Column
Level: Beginners
Business Data field type is a new type that enables users to add data from business applications registered in the Business Data Catalog to SharePoint lists and libraries. In the last two articles we saw how to create definition files using Business Data Catalog Definition Editor tool and how to import that definition file into SharePoint. Now we will see how one can make use of the BDC data in a SharePoint list. Following MSDN article has the details on how to add a Business Data column to a list:
How To: Add a Business Data Column To a List
Adding a Business Data column to a list is a straight forward process but the definition file created by the Definition Editor tool has to be modified before it could be used in SharePoint. If you use the definition file generated by default by the Definition Editor and try to use the Business Data column, all you get is an error message. This is what you see when you try to access data in a Business Data column:
Figure 1: Without a filter, BDC data can not be accessed
Using Business Data Catalog Definition Editor
Importing BDC Data into SharePoint
|
For this demo, I used a small SQL database. This database has only one table with the following fields:
Database: MusicStore
Table: Artist
Fields:
1. id [int] Not Null
2. Artist [varchar] (50) Not Null, Primary Key
3. Album [varchar] (50) Null
4. RecordingLabel [varchar] (50) Null
5. NumOfSongs [int] Null
6. DateReleased [datetime] Null
You can download the table creation script from here. |
1. You can select a specific record to be shown when adding the Business Data column. By default, all records are shown to the user, user can select one of them to be displayed in the Business Data column. You can also define a filter to filter the records before selecting any one record. I would like to show you how a filter can be added to the application. Filter is useful if your table contains lots of records and it becomes easier to select a record from the filtered set of data. To add a filter, open the definition file in Business Data Catalog Definition Editor by importing it into the Editor. There will be two methods in the file created by default. One will be Find_Artist (where Artist is the table name and the method names in your file will be according to the table names imported from your database) and the other will be FindAll_Artist. Underneath the method name, Right-click "Filters" and select "Add Filter". Give a reasonable name to the filter.
Figure 2: Add a filter
2. Remove "FindAll_Artist" method.
3. Add a new instance and name it "Find_Artists_Instance". Change "MethodInstanceType" to "Finder". This method is required. Web Part will not display filter unless this method is added to the definition. The other instance has "SpecificFinder" type.
Figure 3: Add a new method instance
4. Execute the "Find_Artists_Instance". Provide a value in the filter box and click the "Execute" button. You should see the result against the value entered in the filter box.
Figure 4: Add a filter value and test the method instance
5. We still need to make some changes in the definition file before it can be used in the SharePoint. Export the definition file and open it in XML editor.
6. Add following lines after the <Entity> tag:
<Properties>
<Property Name="Artist" Type="System.String">Artist</Property>
</Properties>
7. Locate the <FilterDescriptor> tag and add following lines between the <FilterDescriptor> and </FilterDescriptor> tags:
<Properties>
<Property Name="UsedForDisambiguation" Type="System.Boolean">true</Property>
</Properties>
8. Locate the following lines in the file:
<Parameter Direction="In" Name="@Artist">
<TypeDescriptor TypeName="System.System, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IdentifierName="Artist" Name="Artist" />
This is the input parameter definition. Change the closing tag "/>" to ">" and then add following lines after it:
<DefaultValues>
<DefaultValue MethodInstanceName="Find_Artist_Instance" Type="System.String">%</DefaultValue>
<DefaultValue MethodInstanceName="Find_Artists_Instance" Type="System.String">%</DefaultValue>
</DefaultValues>
</TypeDescriptor>
So, the final input parameter definition will look like the following:
<Parameter Direction="In" Name="@Artist">
<TypeDescriptor TypeName="System.String" IdentifierName="Artist" AssociatedFilter="Artist" Name="Artist" >
<DefaultValues>
<DefaultValue MethodInstanceName="Find_Artist_Instance" Type="System.String">%</DefaultValue>
<DefaultValue MethodInstanceName="Find_Artists_Instance" Type="System.String">%</DefaultValue>
</DefaultValues>
</TypeDescriptor>
</Parameter>
You will notice some more changes in the file but that are not required. For example, you can add following lines to each fields' typedescriptor definition:
<LocalizedDisplayNames>
<LocalizedDisplayName LCID="1033">Your field name here</LocalizedDisplayName>
</LocalizedDisplayNames>
<Properties>
<Property Name="DisplayByDefault" Type="System.Boolean">true</Property>
</Properties>
That's it. Now the next step is to import the definition file into SharePoint and use the data in the Business Data column. Click here to view the completed definition file. You can also download the completed definition file from here.
To see how to import the definition file into SharePoint, read this article. After importing the file into SharePoint, follow the steps listed below to add a Business Data column in a library.
1. Go to the SharePoint library where you would like to add the Business Data column.
2. Select "Create Column" from the "Settings" menu.
Figure 5: Create a new column
3. Enter a name for the column in the "Column name" and select "Business data" as the column type.
4. Click the browse button.
Figure 6: Browse the business data applications
5. Select the BDC application from the list. If there are more than one applications on your server, the list will display all of them. Select your file and click Ok.
Figure 7: Select a business application
6. Select the field to be displayed from the drop down that contains all the fields from the BDC. Click OK to add the field.
Figure 8: Select the field to be shown in the column
7. If there is no record in the library, add a new record. If it's a document library, the Word application will allow you to select a value in the Business Data column (shown in the Information panel). If there is already a record available in the library, edit its properties.
Figure 9: Edit document properties
8. Click the browse button. Enter a valid value in the filter box and click Find. Select the record returned and click OK. Click Ok again to go back to the library.
Figure 10: Show filtered data
9. Select "View Profile" from the Business Data column. The profile page will show you the complete details of the record.
Figure 11: See complete data on the profile page
As I also mentioned in my previous article, BDC Meta Man is a lot easier to use and it generates a "ready to use" definition file. You don't have to make any manual changes yourself. If you are an absolute beginner, I would suggest take a look at the BDC Meta Man first. The biggest advantage of Microsoft Business Data Catalog Definition Editor tool is that it's free and it has all the features required to generate a definition file that can be tweaked and used by the developers. Beginners will have some difficulty in acquiring the art of "definition file generation" using this tool.