Filtering By More Than One Key
As currently implemented, the workspace infrastructure only allows simple relationships to be established, in particular each content part can only be filtered by the value of exactly one field from exactly one other content part.
This is fine for the majority of situations, and often helps when thinking about breaking down a problem into master lists and detail lists. Sometimes, however it would be nice to be able to filter a list by parameters from more than one parent list.
Consider the following scenario: Starting with a list of customers, I want to be able to select a customer and see all of the stock items they have bought and then perhaps drill in further by selecting a stock item and seeing which sales orders were placed to buy this particular item.
The list of customers is pretty straightforward, and we can reuse an existing content part to do this.
The list of stock items is initially a little trickier, but starting a query from SOPStandardItemLinks quickly gives us both the stock item data and customer information to filter on, for example:
return cxt.SOPStandardItemLinks
.Select
(
s =>new
{
CustomerID=s.SOPOrderReturnLine.SOPOrderReturn.CustomerID,
StockItemID=s.StockItem.ItemID,
ItemCode=s.StockItem.Code,
Name=s.StockItem.Name,
LineQuantity=s.SOPOrderReturnLine.LineQuantity,
LineTotalValue=s.SOPOrderReturnLine.LineTotalValue
}
)
.GroupBy
(
t=>new{t.CustomerID,t.StockItemID,t.ItemCode,t.Name},
(t,x)=> new
{
t.CustomerID,
t.ItemCode,
t.StockItemID,
t.Name,
Qty=x.Sum(a=>a.LineQuantity),
Total=x.Sum(a=>a.LineTotalValue),
CustomerItemKey=t.CustomerID.ToString()+"#"+t.StockItemID.ToString()
}
);
Note that we have grouped the initial select in order to give a unique list of items with summary values, rather than a list of all purchase of all items. The other important thing to note is that we have created a new key field ‘on the fly’ that includes both the customer ID and the stock item ID. The point of doing this is that we can then build the third list of sales orders with the same dynamically created key and use this field to tie the two content parts together. In this way, the third list gives us orders for a particular customer AND item.
This approach won’t solve all of the scenarios where you need to filter a list by more than one filter parameter, but it’s a useful approach for situations like this one where we’re drilling down to detail by more than one field at a time.
return cxt.SOPStandardItemLinks
.Select
(
s=>new
{
OrderID=s.SOPOrderReturnLine.SOPOrderReturnID,
StockItemID=s.ItemID,
DocumentNo=s.SOPOrderReturnLine.SOPOrderReturn.DocumentNo,
DocumentDate=s.SOPOrderReturnLine.SOPOrderReturn.DocumentDate,
DocumentType=s.SOPOrderReturnLine.SOPOrderReturn.DocumentStatus.Name,
GrossValue=s.SOPOrderReturnLine.SOPOrderReturn.TotalGrossValue
CustomerItemKey=s.SOPOrderReturnLine.SOPOrderReturn.CustomerID.ToString()+"#"+s.ItemID.ToString()
}
);
