Filtering One Business Data Catalog List from Another by Using Associations in SharePoint Server 2007

Summary:  Learn how to filter one Business Data Catalog list from another by using associations in Microsoft Office SharePoint Server 2007.

Applies to:  2007 Microsoft Office System, Microsoft Office SharePoint Server 2007

John Peltonen, 3Sharp

April 2007

The Business Data Catalog enables you to define multiple entities for a given line-of-business (LOB) system. Furthermore, within the metadata file, you can create associations that define a hierarchy within the entities. For example, if there are two entities defined, such as customers and orders, you can create an association to tie the customer entity directly to the order entity. This allows users within your Microsoft Office SharePoint Server 2007 portal to create master-child behavior. This section describes how to filter one Business Data Catalog list from another by using associations in Microsoft Office SharePoint Server 2007. You can use this Office Visual How To in conjunction with the metadata file that is created in the Office Visual How To titled Creating Business Data Catalog Entities in SharePoint Server 2007. 1. Add the Sales Order Entity to the Metadata File Add the following after the existing Entity element named Customer. This XML defines a new entity named SalesOrder. Note To walk through the process of creating an entity, see Creating Business Data Catalog Entities in SharePoint Server 2007. <Entity Name="SalesOrder"> <Properties> <Property Name="Title" Type="System.String">salesordernumber</Property> </Properties> <Identifiers> <Identifier Name="SalesOrderID" TypeName="System.Int32"/> </Identifiers> <Methods> <Method Name="GetSalesOrders"> <Properties> <Property Name="RdbCommandText" Type="System.String"> SELECT soh.salesorderid, soh.orderdate, soh.shipdate, soh.status, soh.salesordernumber, soh.customerid FROM sales.salesorderheader soh inner join sales.customer c on soh.customerid = c.customerid WHERE c.customertype = 'i' AND (soh.salesorderid &gt; @minSalesOrderID AND soh.salesorderid &lt; @maxSalesOrderID) </Property> <Property Name="RdbCommandType" Type="System.String">Text</Property> </Properties> <Parameters> <Parameter Direction="In" Name="@minSalesOrderID"> <TypeDescriptor TypeName="System.Int32" Name="SalesOrderID" IdentifierName="SalesOrderID"> <DefaultValues> <DefaultValue MethodInstanceName= "SalesOrderFinderInstance" Type="System.Int32">0 </DefaultValue> <DefaultValue MethodInstanceName= "SalesOrderSpecificFinderInstance" Type="System.Int32">0 </DefaultValue> </DefaultValues> </TypeDescriptor> </Parameter> <Parameter Direction="In" Name="@maxSalesOrderID"> <TypeDescriptor TypeName="System.Int32" Name="SalesOrderID" IdentifierName="SalesOrderID"> <DefaultValues> <DefaultValue MethodInstanceName= "SalesOrderFinderInstance" Type="System.Int32"> 9999999</DefaultValue> <DefaultValue MethodInstanceName= "SalesOrderSpecificFinderInstance" Type="System.Int32"> 9999999</DefaultValue> </DefaultValues> </TypeDescriptor> </Parameter> <Parameter Direction="Return" Name="SalesOrders"> <TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken= b77a5c561934e089" IsCollection="true" Name="SalesOrderDataReader"> <TypeDescriptors> <TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="SalesOrderDataRecord"> <TypeDescriptors> <TypeDescriptor TypeName="System.Int32" IdentifierName= "SalesOrderID" Name="SalesOrderID" /> <TypeDescriptor TypeName="System.DateTime" Name="orderdate" /> <TypeDescriptor TypeName="System.DateTime" Name="shipdate" /> <TypeDescriptor TypeName="System.String" Name="status" /> <TypeDescriptor TypeName="System.String" Name="salesordernumber" /> <TypeDescriptor TypeName="System.Int32" Name="customerid" /> </TypeDescriptors> </TypeDescriptor> </TypeDescriptors> </TypeDescriptor> </Parameter> </Parameters> <MethodInstances> <MethodInstance Name="SalesOrderFinderInstance" Type="Finder" ReturnParameterName="SalesOrders" /> <MethodInstance Name="SalesOrderSpecificFinderInstance" Type="SpecificFinder" ReturnParameterName="SalesOrders" /> </MethodInstances> </Method> </Methods> </Entity> 2. Add a New Method to Filter Orders Based on the CustomerID Directly beneath the existing method in the SalesOrder entity, add a new method named GetSalesOrdersForCustomer. This method has a similar SQL statement, with the noted exception of the modified WHERE clause, where you are filtering by the customerID field. Also, note the customerID input parameter (@customerID), which has no default parameters. This method is called only as an association method when a user selects a given customer. Note The input parameter has IdentifierName and IdentifierEntityName attributes associated with it. Although you are using the customerID in the SalesOrder entity, it is really the identifier for the Customer entity. The Business Data Catalog uses the customer identifier when trying to pull back given sales orders for a selected customer. <Method Name="GetSalesOrdersForCustomer"> <Properties> <Property Name="RdbCommandText" Type="System.String"> SELECT soh.salesorderid, soh.orderdate, soh.shipdate, soh.status, soh.salesordernumber FROM sales.salesorderheader soh inner join sales.customer c on soh.customerid = c.customerid where c.customertype = 'i' AND c.customerid = @customerID </Property> <Property Name="RdbCommandType" Type="System.String">Text</Property> </Properties> <Parameters> <Parameter Direction="In" Name="@customerID"> <TypeDescriptor TypeName="System.Int32" Name="CustomerID" IdentifierEntityName= "Customer" IdentifierName="CustomerID"> <!-- Note that we don't have any default values for this. --> <!--Also, note the IdentifierEntityName attribute referes to the customer entity.--> </TypeDescriptor> </Parameter> <Parameter Direction="Return" Name="SalesOrders"> <TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken= b77a5c561934e089" IsCollection="true" Name="SalesOrderDataReader"> <TypeDescriptors> <TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken= b77a5c561934e089" Name="SalesOrderDataRecord"> <TypeDescriptors> <TypeDescriptor TypeName="System.Int32" IdentifierName= "SalesOrderID" Name="SalesOrderID" /> <TypeDescriptor TypeName="System.DateTime" Name="orderdate" /> <TypeDescriptor TypeName="System.DateTime" Name="shipdate" /> <TypeDescriptor TypeName="System.String" Name="status" /> <TypeDescriptor TypeName="System.String" Name="salesordernumber" /> </TypeDescriptors> </TypeDescriptor> </TypeDescriptors> </TypeDescriptor> </Parameter> </Parameters> </Method> 3. Define the Association Between the SalesOrder Entity and the Customer Entity Add the following XML at the end of the LobSystem element (after the </Entities> end tag). The GetSalesOrdersForCustomer method is AssociationMethodName, and the SalesOrder entity is AssociationMethodEntityName. Likewise, the SalesOrders parameter is specified as the return parameter. Finally, the SourceEntity (the entity that drives the relationship) and the DestinationEntity elements are defined. Note The association method can exist in any entity (even an entity other than a source or destination entity). The one constraint is that the entity that contains the association method must exist below the other entities to which it refers. In this example, the SalesOrder entity must exist below the Customer entity, because it refers to the customer identifier. <Associations> <Association AssociationMethodEntityName="SalesOrder" AssociationMethodName="GetSalesOrdersForCustomer" AssociationMethodReturnParameterName="SalesOrders" AssociationMethodReturnTypeDescriptorName="SalesOrderDataReader" Name="CustomerToSalesOrders" > <SourceEntity Name="Customer" /> <DestinationEntity Name="SalesOrder" /> </Association> </Associations> 4. Increment the Version Number of the LOBSystem Root Element Office SharePoint Server 2007 prevents you from uploading an instance of the metadata file that is equal to or less than the version that is currently loaded in SharePoint Server 2007. If you have a version of this metadata file loaded from a previous Visual How To, you need to increase the version of this file before uploading it. 5. Save and Upload the Completed Metadata File into the BDC Shared Service The next step is to save and upload the completed metadata file into the Business Data Catalog shared service. To save and upload the completed metadata file into the Business Data Catalog shared service Save the file. To start the SharePoint 3.0 Central Administration Web page, click the Start button, and then point to All Programs. Point to Microsoft Office Server, and then click SharePoint 3.0 Central Administration. Click your shared service provider (usually named SharedServices1) in the left navigation bar. In the Business Data Catalog section, click Import application definition. Click Browse, locate the saved metadata file, and double-click it. Leave all other application definition settings with their default values, and then click Import. 6. Use the Business Data Web Parts to Filter Orders by Customer The next step is to use the Business Data Web Parts to filter orders by customer To use the Business Data Web Parts to filter orders by customer Within any site in your installation, add a Business Data List and a Business Data Related List Web Part to the site's main page. Within the Business Data List Web Part, click Open the tool pane. In the Business Data List task pane, type Customer in the Type box and press Enter. SharePoint Server 2007 resolves this entry to the Customer (CRMDB) entity. Click OK to see the customer entries exposed in the Web Part. Within the Business Data Related List Web Part, click Open the tool pane. In the Business Data Related List task pane, type SalesOrder in the Type box and press Enter. Note that the CustomerToSalesOrders relationship you defined in the association is automatically selected in the Relationship list. Click Apply. Click the Edit menu in the SalesOrder List Web Part and click Connections, click Get Related Item From, and then click Customer List. Click a customer in the Customer List Web Part to view the corresponding sales orders. Are You Ready for a Third Helping? Associations don't need to stop one level deep. This section describes how to add a LineItem entity and associate it with the sales order. Note This entity is extremely simple. It does not implement any method instances. Therefore, it cannot appear in a search or have any Business Data Catalog actions, or filters associated with it. Its primary and only purpose is to appear as a related entity to the SalesOrder. 1. Create the LineItem Entity Add the following XML directly below the SalesOrder entity. As in the previous section, the order of the entities is important here. This entity must be below the SalesOrder entity because its method specifically includes a reference to the SalesOrder identifier. <Entity Name="LineItem"> <Properties> <Property Name="Title" Type="System.String">Name</Property> </Properties> <Identifiers> <Identifier Name="LineItemID" TypeName="System.Int32"/> </Identifiers> <Methods> <Method Name="GetLineItemsForSalesOrder"> <Properties> <Property Name="RdbCommandText" Type="System.String"> SELECT sod.SalesOrderDetailID, p.Name, p.ProductNumber, sod.CarrierTrackingNumber, sod.OrderQty, sod.UnitPrice, sod.LineTotal FROM Sales.SalesOrderDetail sod INNER JOIN Production.Product p on sod.ProductID = p.ProductID WHERE sod.SalesOrderID = @salesorderID </Property> <Property Name="RdbCommandType" Type="System.String">Text</Property> </Properties> <Parameters> <Parameter Direction="In" Name="@salesorderID"> <TypeDescriptor TypeName="System.Int32" Name="salesorderID" IdentifierEntityName="SalesOrder" IdentifierName="SalesOrderID"> <!-- Note that we don't have any default values for this. --> </TypeDescriptor> </Parameter> <Parameter Direction="Return" Name="LineItems"> <TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true" Name="LineItemDataReader"> <TypeDescriptors> <TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="LineItemDataRecord"> <TypeDescriptors> <TypeDescriptor TypeName="System.Int32" IdentifierName="LineItemID" Name="SalesOrderDetailID" /> <TypeDescriptor TypeName="System.String" Name="Name" /> <TypeDescriptor TypeName="System.Int32" Name="CarrierTrackingNumber" /> <TypeDescriptor TypeName="System.Int16" Name="OrderQty" /> <TypeDescriptor TypeName="System.String" Name="UnitPrice" /> <TypeDescriptor TypeName="System.String" Name="LineTotal" /> </TypeDescriptors> </TypeDescriptor> </TypeDescriptors> </TypeDescriptor> </Parameter> </Parameters> <!-- Note that we have no finder or specific finder. This is because we don't need the line item to show up in anything other than a Related List Web Part. --> </Method> </Methods> </Entity> 2. Add the Sales Order to Line Item Association Add the SalesOrderToLineItems association directly below the existing association. <Association Name="SalesOrderToLineItems" AssociationMethodEntityName="LineItem" AssociationMethodName="GetLineItemsForSalesOrder" AssociationMethodReturnParameterName="LineItems" AssociationMethodReturnTypeDescriptorName="LineItemDataReader" IsCached="true"> <SourceEntity Name="SalesOrder" /> <DestinationEntity Name="LineItem" /> </Association> 3. Upload the Metadata File and Add a Related List Web Part Increment the version number of the metadata file and upload it just as you did previously. Finally, add a new Related List Web Part to the SharePoint site using the steps listed previously. This time, however, select the LineItem entity and the GetLineItemsForSalesOrder association. Beyond merely exposing LOB data in the portal, you can use the Business Data Catalog associations to define how entities within the portal should react in the context of other entities within an LOB system. Business Data Catalog associations allow you to use the Business Data List Web Part to drive the contents of a Business Data Related List Web Part, thus creating master-child behavior. It is important to remember that: The entity that defines the association method must be below all of the entities to which it relates within the XML metadata file. The association method must have input parameters that map to the identifiers of all the source entities. The return parameter of the association method must include the identifiers of the destination entity.

Watch the Video Length: 10:52 | Size: 9.04 MB | Type: WMV file Business Data Catalog Resource Center for SharePoint Server Business Data Catalog Microsoft SharePoint Products and Technologies Team Blog