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 > @minSalesOrderID AND soh.salesorderid < @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
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
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:
|
Length: 10:52 | Size: 9.04 MB | Type: WMV file
|
Note:
